Home » SQL & PL/SQL » SQL & PL/SQL » Oracle StoredProcedure returning Object instead of Cursor
Oracle StoredProcedure returning Object instead of Cursor [message #361160] Tue, 25 November 2008 04:22 Go to next message
contactanantha
Messages: 3
Registered: November 2008
Junior Member
Hi,

I have a storedprocedure which should return an object (list of values), i can return a cursor which contains a list of values and cast that into ResultSet in Java class and get the value. But the Cursors are really slow and it fetches the data in a row by row manner , hence is there any way i can return a object without returning Cursor as an OUT parameter in SP to improve the performance? Any suggestions are much appreciated.

Thanks,
OracleCrazy
Re: Oracle StoredProcedure returning Object instead of Cursor [message #361179 is a reply to message #361160] Tue, 25 November 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But the Cursors are really slow and it fetches the data in a row by row manner

Only if you use badly it.
Cursor is indeed the way to do it.

Regards
Michel
Re: Oracle StoredProcedure returning Object instead of Cursor [message #361231 is a reply to message #361160] Tue, 25 November 2008 08:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could fetch all the rows into a collection (Table, Object or VARRAY) in the procedure, and return this as an Out parameter
Re: Oracle StoredProcedure returning Object instead of Cursor [message #361393 is a reply to message #361231] Wed, 26 November 2008 03:59 Go to previous messageGo to next message
contactanantha
Messages: 3
Registered: November 2008
Junior Member
Thanks Guys... I have a couple of doubt,

1) I am planning to get the collection as a VArray. Is there any example to show how to set the list of values in a VArray and return it StoredProcedure.
2) If i am going to return a VArray from StoredProcedure , how i can get the values that are set in the VArray in Java class using the CallableStatement?

Please do let me know your opinions..Thanks in advance.
Re: Oracle StoredProcedure returning Object instead of Cursor [message #361396 is a reply to message #361393] Wed, 26 November 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals
JDBC Developer's Guide and Reference

Regards
Michel
Re: Oracle StoredProcedure returning Object instead of Cursor [message #361402 is a reply to message #361396] Wed, 26 November 2008 04:48 Go to previous messageGo to next message
contactanantha
Messages: 3
Registered: November 2008
Junior Member
Thanks Michel.
Re: Oracle StoredProcedure returning Object instead of Cursor [message #361438 is a reply to message #361393] Wed, 26 November 2008 07:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is a fair enough approach if your result set is small.
If you're going to return a lot of rows, you are going to use a significant amount of memory this way.

Previous Topic: Extracting Days,Hours between 2 dates
Next Topic: how to insert spaces between
Goto Forum:
  


Current Time: Fri Dec 09 15:40:55 CST 2016

Total time taken to generate the page: 0.18854 seconds