| What is the BEST way to return a result set from PL/SQL [message #91961] |
Wed, 24 December 2003 06:48  |
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  |
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).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
