Using Oracle ref cursors
I’ve written a small article about the use of oracle ref cursors in java. The article is in dutch and is available here.
Oracle ref cursors allow you to define queries in stored procedures in the database. They can be used in java by executing a call to the stored procedure and then looping through the resultset. The big advantage here is that the oracle developers can provide ready made queries to the java developers, so the java developers do not have to know a lot of sql. I see that a lot of java programmers do not know how to write good sql queries, and lack knowledge about the oracle optimizer and ways to determine query problems (explain plan,etc). Now you can leave this to the oracle developers, and the java developers can do what they are good at, writing java code.
March 27th, 2003 at 3:07 am
I though one of the goals of platform/vendor independce design was to avoid stored procedures? Have I missed soemthing?
March 27th, 2003 at 8:50 am
Hi Andrej,
I’ve read your article (er zijn moffen, die artikelen in het nederlands kunnen lezen
) and there are a few issues I’d like to comment on:
1. The cast to OracleCallableStatement is superfluous. A simple getObject(1) does the trick.
2. This way of returning a cursor is totally and utterly Oracle-specific and thus proprietary. We ended up writing our own JDBC-layer in order to encapsule this difference to other JDBC-drivers.
Even the Merant-driver does it the regular way, i.e. getResultSet().
The best way to tackle this problem is IMHO not to use FUNCTIONs, but PROCEDUREs and to specify the last parameter as an OUT cursor. E.g. that’s the way the Merant driver expects it.
Best regards
Fokko
March 27th, 2003 at 9:30 am
"The big advantage here is that the oracle developers can provide ready
made queries to the java developers, so the java developers do not have to know
a lot of sql". That`s occurs in a perfect world. In the real world the
java developers must create the stored procedures too. I didn´t read your
article about oracle’s ref cursor (I don´t speak dutch and my english is
not too much strong) but the real thing is that working on Oracle, the stored
procedures are the best option because are more efficient than execute sql statements.
On the other side, for a java developer is more comfortable work with stored functions
(that returns the ref cursor) than work with stored procedures. But Oracle developers
prefers working with procedures (I think it´s due they hate us)
October 29th, 2003 at 9:34 pm
A translation of the dutch article can be found here