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-Donepragmatic 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:

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.