vandy02
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;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Can you post the execution plans for the individual statements and the union all?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you reviewed the execution plans from them all?
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.
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.
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
global temp table or in memory plsql table
ASKER
No. I went to the cursors due to slow performance. The cursors are much faster.