In the Oracle example found here, the author describes how one can return a resultset from a Java stored procedure. The example is pretty straightforward and easy to understand. My concern, however, is the Java stored procedure prone to leaks? Since, you are returning a resultset, you cannot explicitly close the database connection, statement, or resultset. However, closing these objects are recomended to prevent things such as cursor leaks. Does anyone know how Oracle 9i handles things such as this?
John, It's important to close the connection/statement/resultset in Java (on an app server) because the JVM is bad at cleaning up resources. If you write a stored proc in PL/SQL, the database server cleans up after itself automatically. So I imagine a Java stored proc would work in a similar way. You could try it to make sure though.