Monday, May 27, 2013

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

5 comments:

Kepa Gutierrez said...

And what about SENDING a ResultSet?

I'll Explain, I must send a list of tickets to one oracle procedure that i can't modify

The procedure statement its more or less in this way

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE Tickets (IDUSER IN VARCHAR2,
TICKETS IN T_CURSOR,
coderror OUT VARCHAR2) IS

it's posible to send from java to Oracle?

Johnny Clark said...

Great stuff. This saved me a lot of time and frustration. There aren't many good examples out there.

Johnny Clark said...

Great stuff. Thanks.

Question does the '?' in the call (call jay_proc(?)) somehow represent the "ResultSet"?

Anonymous said...

Thanks for this. My JDBC CallableStatement sample that works with Microsoft SQL Server, MySQL, ASE, SQL Anywhere, and HANA had to be modified considerably to work with Oracle.

StickyBandit said...

I tried this code and get the following error.

InternalError: Cannot convert [JavaPackage com.oracle.OracleType.CURSOR] to java.lang.Integer (dba9d706-1002-4f2f-862d-268d24d0ed64#4660)