Using Oracle Stored Procedures and Java ResultSet

Oracle stored procedures are not my favorite slice of technology, but I have to use them sometimes. In this case I had a stored procedure that I needed to call: it returns a set of rows - a result set. But Oracle Stored procedures do not return anything by design - other database systems like SQLServer and Postgres can, but not Oracle. So what's a Git-R-Done pragmatic developer to do?

The stored procedure in question used an OUT parameter with a "cursor" type, this is how Oracle can "return" the results of a select statement. (I can feel my CS professor grading this now: "D-, NEVER use Out Params!" some languages like Java do not even have out params). And then the NEXT challenge: How do you call that from Java? Good question - this case doesn't happen in the abridged JDBC docs, nor in the Hibernate nor Spring frameworks docs either.

JDBC has standard support for ResultSets returned from a stored procedure CallableStatment, this is how many DB vendors like Postgres and SQLServer work. In those drivers, Statement.exeecuteQuery() returns a ResultSet. But Oracle has a long history of having backwards and low-compatibility issues for JDBC details,and this is another case of Oracle jamming their square-peg stored procedures into the round hole of JDBC.  But you can make it work.

Here are the "challenges":
* Oracle Stored Procedures CANNOT return a result set (it's an old design?)
* Oracle "gives" you SYS_REFCURSOR on an OUT param (not a JDBC standard, so that's an odd feeling)
* Oracle's JDBC driver does not have a JDBC-standard way to do this, they require OracleType.CURSOR
* Oracle's driver rejects the "OTHER" SQL Type, so you cannot use this JDBC Standard either

Step 1: Your stored procedure

Oracle stored procedures can use an OUT param with type SYS_REFCURSOR to return results of a SQL Select:

 CREATE PROCEDURE "JAY_PROC" ( st_cursor OUT SYS_REFCURSOR  ).... --details below

Step 2: CallableStatment

Of course, been there, done that - the ol' JDBC way to call stored procedures:

CallableStatement cs = connection.prepareCall("call jay_proc(?)");

Step 3: register the OUT param

Feeling squeemish? It's OK... we are almost finished ...you'll feel a small prick in your good programming sense:

cs.registerOutParameter(1, OracleType.CURSOR);
cs.execute();

Step 4: blink... cast the ResultSet ??!

err.. Yes, it's true, the object returned is actually a ResultSet, but you have to cast it:

ResultSet rs = (ResultSet) cs.getObject(1);

Step 5: Use the ResultSet as normal, JDBC calls

Whew! It's over.. Now we have what we wanted, a ResultSet:

while( rs.next()){

Conclusion

Oracle does not directly support stored procedures that return ResultSet, but with these 3 or 4 steps, you can get the stored procedure with a cursor to work. I assume that Oracle's JDBC driver does the work of mapping their cursor object to a JDBC-compliant ResultSet, which is nice. But if I had a preference, I would wish for Oracle to support procedures that can return rows from a select. Then you could use the execute() method to return a ResultSet directly without the odd casting operation in step 4.

Ideally you would use a SQL Select statement and the PreparedStatement JDBC object, but I was saddled with an existing stored procedure in this case. I do not recommend using stored procedures for almost any use case, if you can avoid it. Instead I prefer to use Java's standard JPA as much as possible.

Here is the working code:

package com.harpoontech.test.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.datasource.DriverManagerDataSource;


public class TestOracleStoredProcedure {
 
 /**
  * The SQL TYPE for an Oracle CURSOR in a Oracle Stored Procedure.
  * This duplicates the OracleTypes.CURSOR, but with this constant 
  * we do not need to import com.oracle.* jars into this project.
  * However this class is still 100% dependent on Oracle at runtime 
  * and cannot be unit tested without Oracle.
  */
 private int ORACLE_CURSOR_TYPE=-10;
 
 public static void main(String[] args) throws SQLException {
  TestOracleStoredProcedure t = new TestOracleStoredProcedure();
  t.test();
 }

 public void test() throws SQLException {
  
  //let Spring create a Data source for the Oracle connections
  DriverManagerDataSource ds = new DriverManagerDataSource();
  String url = "jdbc:oracle:thin:@myhost:1525:DEVDB";
  ds.setUrl(url);
  ds.setUsername("scott");
  ds.setPassword("tiger");

  CallableStatement cs = ds.getConnection()
    .prepareCall("call jay_proc(?)");
  cs.registerOutParameter(1, ORACLE_CURSOR_TYPE);
  cs.execute();
  ResultSet rs = (ResultSet) cs.getObject(1);
  while( rs.next()){
              System.out.println("title:"+rs.getString(1)+" author:"
                            +rs.getString(2));
  }
  rs.close();
  cs.close();

 }
 
 /*
 --the Oracle 9+ stored proc with 1 OUT parameter for the results of a select:
 CREATE OR REPLACE PROCEDURE "JAY_PROC" ( st_cursor OUT SYS_REFCURSOR  )
     is
 BEGIN
  OPEN st_cursor FOR
  SELECT TITLE, AUTHOR, ISBN_ID 
  FROM books;
 end JAY_PROC;
 */
}

Notes

* I used the Spring DriverManagerDataSource, but you could replace that with straight JDBC calls

* Notice I used my own constant ORACLE_CURSOR_TYPE to avoid importing the oracle dependencies.

* You can pass the ResultSet object to another framework like Spring-JDBC, if you like

* I found most of this by using the debugger to show what Oracle drivers were really doing