[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Geert GruwezOracle 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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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