Link to home
Start Free TrialLog in
Avatar of vandy02
vandy02Flag for United States of America

asked on

RETURN DATA FROM MULTIPLE CURSORS IFROM A REFCURSOR PROCEDURE

IN THE FOLLOWING REFCURSOR PROCEDURE I AM TRYING TO RETURN THE DATA FROM A COMBINATION OF TWO CURSORS.  I MADE THE EXAMPLE AS SIMPLE AS POSSIBLE SO I CAN EXPLAIN WHAT I AM TRYING TO ACCOMPLISH.  I.e. REFER TO 'RETURN THE FOLLOWING DATA:' WITHIN THE PROCEDURE.  IS THERE A WAY TO RETURN THE DATA FROM A FOR LOOP CURSOR INTO A REFCURSOR?   IS THERE ANOTHER WAY TO RETURN DATA FROM TWO CURSORS INTO THE REF CURSOR?  ALSO, I CANNOT CREATE A TABLE IN THE DATABASE TO HOLD THE DATA I CAN ONLY RUN SQL.

THANKS

=====
CREATE OR REPLACE
PROCEDURE myproc (p_recordset OUT SYS_REFCURSOR) AS

CURSOR c_1 is
select location
from locations;

CURSOR c_2 (i_location VARCHAR) is
select name, hours
from laborhours
where location = i_location;

BEGIN
      FOR r_1 in c_1 LOOP      
            FOR r_2 in c_2(r_1.location) LOOP
                  RETURN THE FOLLOWING DATA:
                                                     r_1.location, r_2.name, r_2.hours            
            END LOOP;
      END LOOP;
END;
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vandy02

ASKER

Can you not take the selects and create a union all query and make a single cursor?

No.  I went to the cursors due to slow performance.  The cursors are much faster.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Not sure why two cursors are faster than a 'union all'.

Can you post the execution plans for the individual statements and the union all?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vandy02

ASKER

Because I am using several cursors.  I am not using just the two I provided.  I just showed two to make it simple to understand.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have you reviewed the execution plans from them all?
Avatar of vandy02

ASKER

Yes I have.  Is there a way to return the data from multiple cursors to a ref cursor?
>>Is there a way to return the data from multiple cursors to a ref cursor?

I have already answered this: I don't know of any way other than I have already posted.

Maybe another Expert will come up with a way that will meet your requirements.
Avatar of vandy02

ASKER

Oh I apoligize. I missed it when I was reading the posts while trying to research a resolution

global temp table or in memory plsql table