Home » SQL & PL/SQL » SQL & PL/SQL » Please suggest
Please suggest [message #36121] Tue, 06 November 2001 10:42 Go to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
Hello,

Below is the requirement :

I have a main table which returns multiple rows and for each row, a bunch of procedures are being called to populate different fields from different tables.

This slows the process tremendously as from the client, each procedure is being called in a loop. I intend to create a cursor in my package which will extract these initial rows and then for each, within my package, call respective procedures. Do everything at the backend and return an exhaustive record set to the front end.

BUT the problem is, how do I return all this? It is most appropriate if I can return a cursor will all data. Please explain how.

Thanks in Advance

Please suggest what is the solution??

----------------------------------------------------------------------
Re: Please suggest [message #36126 is a reply to message #36121] Tue, 06 November 2001 17:15 Go to previous messageGo to next message
Brian
Messages: 38
Registered: October 1999
Member
Hello,

You could pre-process the data and store it all into the table. Then the query will just retrieve the records required.

But that would mean you would need to re-run the calculations in a timely manner.

Is that a viable solution? Not very good if you have real time data. Good if you have time to do the calculations.

----------------------------------------------------------------------
Re: Please suggest [message #36144 is a reply to message #36121] Wed, 07 November 2001 11:26 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
in general, if you can write the DML in a single SQL statement it will be more efficient that calling the proc once for each row in the outer loop. An update using a correlated subquery is close to what you describe.
Storing the data in a temporary table should be a last resort because you can store it in a pl/sql table too which is faster.
See ref cursors (conventional cursors could probably do the job too). ref cursors are often used in Java etc but are also common just in pl/sql. Their syntax isn't the most intuative though...
http://osi.oracle.com/~tkyte/ResultSets/index.html

http://oradoc.photo.net/ora817/DOC/appdev.817/a77069/01_oview.htm#777

----------------------------------------------------------------------
Previous Topic: Re: Call an Oracle Stored Function using ADO, VB6 and the Oracle OLE DB Provider
Next Topic: What kind of primary key for a table which contains constants?
Goto Forum:
  


Current Time: Sat May 28 00:11:25 CDT 2022