Data Access using Java Stored Procedures in Oracle Database

Recently I had to troubleshoot a problem related to Java stored procedures in Oracle Database. SQL access has been a bit slow, and I wanted to create a small test case where my stored procedure accesses information in a table. To my surprise I didn’t find any suitable reference/tutorial so I thought I’d quickly write up how to access the SQL layer from within a Java stored procedure.

Java stored procedures are covered in the Java Developer’s Guide, chapter 5.

Java Stored Procedures

Here is my little code example. In contrast to my earlier post I am going to use the SQL (JDBC server-side) driver to query the database.

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleDriver;

public class SQLAccessDemo {
    
    public static String whoAmI() 
        throws SQLException
    {

        String username = "nobody";
        OracleDriver ora = new OracleDriver();

        try (
            Connection conn = ora.defaultConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select user from dual");
        ) {

            while (rs.next()) {
                username = rs.getString("user");
            }
        }

        return username;
    }
}

The code isn’t particularly sophisticated, and it isn’t too different from client-side code either. The way you “connect” to the database is the main difference between client-side JDBC and server-side JDBC. In client-side JDBC you need to authenticate first before you can issue commands against the database. In the context of (Java) stored procedures you are already connected and don’t need to worry about connection pools, username or how to pass the password in a secure manner. The server-side JDBC driver is explained in the JDBC Developer’s Guide and Reference, chapter 7. My example uses the defaultConnection() method, but there are others you might be interested in.

Loading the Java Code into the Database

I decided to use the loadjava tool this time to load the code into the database. Here is how you can load and resolve the code:

$ loadjava -thin -user martin/superSecretPassword@localhost/pdb1 -resolve -verbose SQLAccessDemo.java 
arguments: '-user' 'martin/***@localhost/pdb1' '-thin' '-resolve' '-verbose' 'SQLAccessDemo.java' 
creating : source SQLAccessDemo
loading  : source SQLAccessDemo
created  : CREATE$JAVA$LOB$TABLE
resolving: source SQLAccessDemo
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

In case there are errors with the code loadjava will print these on the terminal screen. In my case there were 0 errors, allowing me to proceed. I can see that both a JAVA SOURCE and JAVA CLASS have been created in my schema.

Creating a Call Specification

With that done all I need to do is create a call specification to publish whoAmI() to PL/SQL and SQL. Here is an example of an appropriate call specification:

create or replace function who_am_i return varchar2
as language java
name 'SQLAccessDemo.whoAmI() return java.lang.String';
/

The call specification maps the Java function to a PL/SQL code unit. In this example, a PL/SQL function named who_am_I returns a VARCHAR2 (“String”) after invoking whoAmI() in (Java) class SQLAccessDemo.

Invoking the stored procedure in SQL

Using the call specification I can call the function from SQL:

SQL> select who_am_i from dual;

WHO_AM_I
------------------------------------
MARTIN

This is of course a trivial example, but it should allow you to create your own Java stored procedures, accessing the database using server-side JDBC.

Summary

Java stored procedures aren’t too dissimilar from client-side JDBC code, which makes it easy for experienced developers to switch to writing server-side code in Java if needed. The main difference is the use of the server-side driver, see above for a link to the docs.

Advertisement