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.
Martins Blog 
You must be logged in to post a comment.