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'll feel a small prick in your good programming sense:
cs.registerOutParameter(1, OracleType.CURSOR);

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:


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* 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();

 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";

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

 --the Oracle 9+ stored proc with 1 OUT parameter for the results of a select:
  OPEN st_cursor FOR
  FROM books;
 end JAY_PROC;


* 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