Home » SQL & PL/SQL » SQL & PL/SQL » Fetching Recorset from a Stored Proc (Oracle 9i, Windows)
Fetching Recorset from a Stored Proc [message #337176] Wed, 30 July 2008 01:51 Go to next message
kanthikota
Messages: 6
Registered: March 2008
Location: Pune
Junior Member

Hi,

I want to fetch a recordset from a Stored Proc. Is there any way that i can retrieve that recordset(large) from a stored proc without using the cursor?
Re: Fetching Recorset from a Stored Proc [message #337205 is a reply to message #337176] Wed, 30 July 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to use a cursor? It is the way to do it.

Regards
Michel
Re: Fetching Recorset from a Stored Proc [message #337209 is a reply to message #337205] Wed, 30 July 2008 02:52 Go to previous messageGo to next message
kanthikota
Messages: 6
Registered: March 2008
Location: Pune
Junior Member

Hi Michel,

I want to avoid the cursor for performance issues. As i am displaying many counts in my report from the front end and when some one click on that counts(larger) it has to fetch all the records with their details. So the queries are different for all the counts and thats why when users(many) try to get the details of different counts, using cursor will be effecting the performance badly.

I just want to know, why we can't do this in Oracle but this can be easily done in SQL Server without a cursor...

Thanks for your feedback.
Re: Fetching Recorset from a Stored Proc [message #337220 is a reply to message #337209] Wed, 30 July 2008 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to avoid the cursor for performance issues.

Cursor is the most efficient way to get result.
Oracle is NOT SQL Server. Cursor in Oracle are efficient.

Regards
Michel

[Updated on: Wed, 30 July 2008 03:22]

Report message to a moderator

Re: Fetching Recorset from a Stored Proc [message #337450 is a reply to message #337176] Wed, 30 July 2008 22:53 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
yes, this is a common mistake. People who use SQLServer always think Oracle is just as bad as SQLServer is.

SQLServer is a toy, Oracle is a real database.

If you are a hard case and have made up your mind to not user cursors in Oracle (against the advice here), then you can always read about how to create customer java classes for oracle collections and go that way. Do a google on the internet for an introduction to it, then start reading and reseraching its practice.

I have an idea. Why don't you do that and then tell us how it went. Was using the customer java class and a collection type, worth all the effort you put into it?

Good luck, Kevin
Previous Topic: describe tablename using sort
Next Topic: avoid muitating table error
Goto Forum:
  


Current Time: Fri Dec 09 23:17:05 CST 2016

Total time taken to generate the page: 0.10764 seconds