Write Java Stored Procedure / Function - Oracle Example

Create Java Stored Procedure / Function in Oracle - Example

In this tutorial, we will explain how to write Java stored procedures in Oracle with full working code example. In order to work with the examples provided in this section, you need to have an Oracle database with Java installed. You also need to have a bit of background on what Java programming is all about. The procedure we will be writing will dump a static string back to the output when queried via SQL. The various steps we will be discussing to create a Java stored procedure is provided below:

Create a Java Stored Procedure / Function in Oracle - Step by Step Guide

The steps are briefly explained below:

1. Create a Java Procedure / Function – Here we create a simple Java program that returns a string when invoked from SQL. 
2. Generate Procedure Class File – We compile the Java program created earlier and generate a class file. 
3. Load Procedure to Oracle DB – In this step, we will load the class file into Oracle database by using a tool called “loadjava”. 
4. Check if Procedure is Loaded – In this step, we will verify if the class file is loaded successfully and is in a VALID state. 
5. Create Oracle Function Mapping to Class File – We will create a SQL Function in this step, which will point automatically to the method in the class file created in step (1) 
6. Execute Java Stored Procedure – We will see couple of different approaches to test the stored procedure / function we have created 
7. Check Output – Final verification on the output

These modules are not complicated. They are the small chunks which we should conquer to get our first Java stored procedure running. So, let us get started.

1-  Create a Java Stored Procedure / Function

In this step, we will write a simple Java procedure that we can load into Oracle and test. This procedure / function, will be named “fixedString.java”. It will have a single static method 'quote', that will return a fixed string when invoked from SQL. Writing this Java code is no brainer. Here is the Java code for the procedure:

public class fixedStrings
{
public static String quote()
{
return "Java Function from Oracle";
}
}

In the next step, we will compile this Java program  and generate a class file out of it.

2- Generate Procedure / Function Class File

You don’t need any special libraries to compile this code. Just a “javac” on the source file and you are done. You now have the class file with you, which you can load into Oracle. One word of caution here is make sure that you use the same version of Java that is supported by your database. Otherwise, you will get a runtime error, when executing the method from Oracle. (More about this error and how to fix it at last) A screenshot provided below, shows the source file and the class file generated by the program

Java Stored Procedure / Function - Source / Class Files created for Loading

3- Load Class File to Oracle Database

In this step, we will use a utility called “loadjava” to load the Java procedure to Oracle. This is a simple tool available with Oracle and you invoke this tool by passing the following arguments:

• User ID – schema into which the class file needs to be loaded
• Class File name – which is fixedStrings.class in our case.

For testing purposes, we will load the procedure to HR schema. We start by invoking the tool from the command line as shown below:

loadjava -user hr fixedStrings.class

After this the utility will prompt for password for the schema. Upon entering the password correctly, you will not (yes!) get an indication if the load is successful.  You will get the following error message if you enter the password incorrectly when prompted

SQL Error while connecting with oci8 driver to default database: ORA-01017: invalid username/password; logon denied

Error while processing class fixedStrings
    Exception oracle.aurora.server.tools.loadjava.ConnectionError: could not open connection
SQL Error while connecting with oci8 driver to default database: ORA-01017: invalid username/password; logon denied

exiting  : could not open connection

There are multiple different options to load your class files apart from using loadjava utility. We will cover some of them later. Note that “loadjava” will not work for Oracle Express editions of the database.

4 – Check if Procedure is Loaded into Database

Well, there are different approaches to check if the class file you loaded, is in fact loaded.  The easiest approach is to query USER_OBJECTS with object type of “JAVA CLASS”. The SQL query is provided below:

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'JAVA CLASS'

You should see your JAVA CLASS in a VALID state, as indicated by the output screenshot below

Check Java Procedure Load in Oracle Database - Output of the SQL Query

If your procedure is successfully loaded, then we are good to go to the next step. Otherwise, you should fix what had went wrong before progressing.

5-Create Oracle Function Mapping to Java Procedure

In this step, we create an Oracle Function, which when invoked would call the Java method for us directly. This step is a must as this function will form a logical layer, which will connect the SQL query to the actual Java method. In Oracle terms, we are writing a call specification for the Java method (and you must do this for every Java method), that is exposing the entry point of the method to the Oracle database. Here is how we can write a call specification for the “quote” method in our Java program.

CREATE OR REPLACE FUNCTION myTestFunction RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'fixedStrings.quote() return java.lang.String';

The key components here are the function name, type of function (LANGUAGE JAVA), and the name of the class.method and the return value type.  Our Java procedure does not accept any user arguments, so you don’t want to worry about this headache now. But, we will soon provide a Java function that accepts inputs from SQL layer, and returns some values by doing manipulation on the user input.

If the function is created successfully, and is in a VALID state, then you are done. You are now ready to test your first every Java stored procedure from Oracle.

6 – Execute Java Stored Procedure / Check Output

Again, there are different approaches to execute your Java procedure. The easiest one among them is to query your function through a SQL against Dual table. This is shown below:

Test Java Stored Procedure / Function from Oracle SQL Query

As you can see from the screenshot above, the output is dumped as we wrote in the Java source file. The code is working!!!

The other approach, is to use CALL statement from SQL plus.  A step by step tutorial to test your Java function by using CALL statement is shown below:

SQL> VARIABLE my_test_function VARCHAR2(50);
SQL> CALL myTestFunction() INTO :my_test_function;

Call completed.

SQL> PRINT my_test_function;

MY_TEST_FUNCTION
--------------------------------------------------------------------------------

Java Function from Oracle

As you can infer from the code segment above, we have very simple steps to invoke the function from SQL Plus. We declare a variable, invoke the CALL method outputting the result to the variable declared, and then use the PRINT method to dump the value of the variable. Easy!


A Common Exception at Runtime:

Sometimes, when you test your procedure, you will end up getting the following exception

ORA-29516: Aurora assertion failure : Assertion failure at exoc.c:359
Uncaught exception System error: java/lag/UnsupportedClassVersionError

You may get this error, if you compile your Java procedure on a higher version of Java in your machine and load this into Oracle database, that is running a lower version of Java. In my case, I was running 1.7  in my system and the Oracle database was at 1.5. To fix this exception, you need to make sure the Java version on which you are compiling the function is ideally the same as your database version. There are different approaches to check the Java version of your database. The easiest one among them is listed below:

#cd $ORACLE_HOME/jdk/bin
#java -version

With that, we come to the end of the beginner tutorial, to write a Java stored procedure / Function in Oracle and verify the execution. We will see some more advanced examples in Java procedures as we move on in subsequent tutorials. Meanwhile, you can try this example , and if you have a question you can post it in the comments section.