• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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;
0
vandy02
Asked:
vandy02
  • 5
  • 4
3 Solutions
 
slightwv (䄆 Netminder) Commented:
I don't know of a way from two declared cursors without a mess of internal in-memory plsql tables.

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

The number of columns and data types would need to match but some null columns in the selects will take care of that.
0
 
vandy02Author Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Geert GOracle dbaCommented:
check if you have a index on location

i would argue the faster cursors ...

why not simply join ?

select l.location, h.name, h.hours
from locations l, laborhours h
where l.location = h.location
  and l.location = i_location;
0
 
vandy02Author Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
Well the answer is that I can't think of a way to do what you want with multiple cursors without a global temp table or in memory plsql table.

I assume you have tested returning full result sets from multiple cursors versus a union all?  By 'full' I mean all rows not something like Toad bringing back results.  Toad buffers data and can mislead you.  The CBO should make them comparable.
0
 
slightwv (䄆 Netminder) Commented:
Have you reviewed the execution plans from them all?
0
 
vandy02Author Commented:
Yes I have.  Is there a way to return the data from multiple cursors to a ref cursor?
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
vandy02Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now