Home » Developer & Programmer » JDeveloper, Java & XML » What is the BEST way to return a result set from PL/SQL
What is the BEST way to return a result set from PL/SQL [message #91961] Wed, 24 December 2003 06:48 Go to next message
Dan White
Messages: 48
Registered: September 2003
Member
We have a major debate on the best way to return a result set from PL/SQL to a java serverlet. The best way that I have found, especally in a dynamic environment, is to return a reference cursor to the java app. Our DBA's here have a fear of open cursors. Does any one know what would happen to the sessions in a connection pool if connection to the database was lost from the webserver?
Re: What is the BEST way to return a result set from PL/SQL [message #91983 is a reply to message #91961] Mon, 12 January 2004 15:20 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If you weren't using connction pooling through your app server, your Java would nees a "finally" clause to close the jdbc connection after all other error handling. If not, you would have a connection "leak" and eventually you would hit your Oracle session limit (I have experienced this).

If your connection pool loses a connection to the DB, then pmon will end the orphaned session and the open cursor(s) will die with it. Ref Cursors do need closing unless they exist at a lower level scope (does not apply in your case as your top level call returns the ref cursor handle to the client).
Previous Topic: sqlj and jsp question
Next Topic: Testing JDBC Connection checkup
Goto Forum:
  


Current Time: Sat Jul 31 16:11:51 CDT 2021