Home » SQL & PL/SQL » SQL & PL/SQL » REF CURSORS using multiple cursors (Oracle 10G Release 2)
REF CURSORS using multiple cursors [message #353281] Mon, 13 October 2008 04:07 Go to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
hello,

create or replace package trial as

type rc_pay_details is ref cursor;

procedure p1 (p_id in number,
p_get_pay_details out rc_pay_details
);
end trial;
/

create or replace package body trial is

procedure pl (p_id in number,
p_get_pay_details out rc_pay_details
) is

cursor c_1 is
select col1, col2, col3
from table1 tab1,
table2 tab2
where tab1.column = tab2.column;

cursor c_2 is
select col1, col2, col3
from table1 tab3,
table2 tab4
where tab3.column = tab4.column;

cursor c_3 is
select col1, col2, col3
from table1 tab5,
table2 tab6
where tab5.column = tab6.column;

cursor c_4 is
select col1, col2, col3
from table1 tab7,
table2 tab8
where tab7.column = tab8.column;

begin
???
???


end;
/
show errors

my question is how would I get values from above cursors(c_1, c_2, c_3, c_4) into the refcursor - rc_pay_details,

It would have been a simple case if I would have had only one cursor

Because of the business retrictions I cannot use Object Types.

any help would be highly appreciated.

thanking you in advance

10GUser
Re: REF CURSORS using multiple cursors [message #353284 is a reply to message #353281] Mon, 13 October 2008 04:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Combine the separate queries using a UNION or UNION ALL.
Re: REF CURSORS using multiple cursors [message #353285 is a reply to message #353281] Mon, 13 October 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Thu, 13 September 2007 13:20
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel


Re: REF CURSORS using multiple cursors [message #353288 is a reply to message #353285] Mon, 13 October 2008 04:45 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
CREATE OR REPLACE PACKAGE Trial
AS
  TYPE rc_Pay_Details IS REF CURSOR;
  
  PROCEDURE p1(p_Id  IN NUMBER,p_Get_Pay_Details  OUT RC_PAY_DETAILS);
END Trial;
/ 
   
create or replace package body trial is
   
   procedure pl  (p_id in number,
                  p_get_pay_details  out rc_pay_details                                               
                  ) is
   
      cursor c_1 is
      select col1, col2, col3
        from table1 tab1,
             table2 tab2 
       where tab1.column = tab2.column;
  
     cursor c_2 is
      select col1, col2, col3
        from table1 tab3,
             table2 tab4 
       where tab3.column = tab4.column;
    
    cursor c_3 is
      select col1, col2, col3
        from table1 tab5,
             table2 tab6 
       where tab5.column = tab6.column;

      cursor c_4 is
      select col1, col2, col3
        from table1 tab7,
             table2 tab8 
       where tab7.column = tab8.column;  
         
begin
???
???
    
  
end;
/
show errors


Quote:
I cannot use union/union all here because I'm using output of c_1 as input to c_2, output of c_2 as input to c_4,

Apologies I didnt relfect this in my TRIAL code.
Re: REF CURSORS using multiple cursors [message #353290 is a reply to message #353288] Mon, 13 October 2008 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel
Re: REF CURSORS using multiple cursors [message #353293 is a reply to message #353288] Mon, 13 October 2008 04:53 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
While I'm investigating, I found CAST TABLE, is that something that can be used ?
Whats the alternative if not this ?

Thanks
Re: REF CURSORS using multiple cursors [message #353304 is a reply to message #353293] Mon, 13 October 2008 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are digging your grave.

Regards
Michel
Re: REF CURSORS using multiple cursors [message #353305 is a reply to message #353304] Mon, 13 October 2008 05:22 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Could you please suggest best option to cater for this need ?

Thanks
Re: REF CURSORS using multiple cursors [message #353312 is a reply to message #353305] Mon, 13 October 2008 05:28 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

Because of the business retrictions I cannot use Object Types.


Quote:

While I'm investigating, I found CAST TABLE, is that something that can be used ?



Both Are Contradictory Statements.

Regards,
Rajat Ratewal
Re: REF CURSORS using multiple cursors [message #353314 is a reply to message #353305] Mon, 13 October 2008 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change the whole code.
I bet you don't need the cursors.

Regards
Michel
Re: REF CURSORS using multiple cursors [message #353317 is a reply to message #353281] Mon, 13 October 2008 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only way I can think of doing it would be to load all the data from the cursors into a collection, or plsql table, and then use that collection as the datasource for the ref cursor.

I wouldn't do it that way - I'd just write the query for the ref cursor as a UNION all of the individual cursor queries, and put a big comment at the top o the proedure waring people that changes to induvudual cursors need to be reflected in the Ref_Cursor query.
Re: REF CURSORS using multiple cursors [message #353432 is a reply to message #353288] Mon, 13 October 2008 21:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Your statements don't match your examples. You have said that you are using the output from one cursor as input to another, but your examples do not reflect that. If that is actually the case, then you should just use the select statement from each cursor that is used as "input" as an inline view (a subquery in the from clause) as if it were a table. If you need to return a bunch of ref cursors as you originally seemed to indicate, then you can open and return multiple ref cursors. I have included examples of both below.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE test_proc
  2    (p_ref OUT SYS_REFCURSOR)
  3  AS
  4  BEGIN
  5    OPEN p_ref FOR
  6    SELECT tab2.deptno, tab2.dname, tab1.ename, tab1.job
  7    FROM   emp tab1,
  8  	      (SELECT *
  9  	       FROM   dept
 10  	       WHERE  dname = 'SALES') tab2
 11    WHERE  tab1.deptno = tab2.deptno;
 12  END test_proc;
 13  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXECUTE test_proc (:g_ref)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

    DEPTNO DNAME          ENAME      JOB
---------- -------------- ---------- ---------
        30 SALES          ALLEN      SALESMAN
        30 SALES          WARD       SALESMAN
        30 SALES          MARTIN     SALESMAN
        30 SALES          BLAKE      MANAGER
        30 SALES          TURNER     SALESMAN
        30 SALES          JAMES      CLERK

6 rows selected.


SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE test_proc
  2    (p_ref1 OUT SYS_REFCURSOR,
  3  	p_ref2 OUT SYS_REFCURSOR)
  4  AS
  5  BEGIN
  6    OPEN p_ref1 FOR SELECT deptno, dname FROM dept WHERE dname = 'SALES';
  7    OPEN p_ref2 FOR SELECT deptno, ename, job FROM emp WHERE deptno = 30;
  8  END test_proc;
  9  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref1 REFCURSOR
SCOTT@orcl_11g> VARIABLE g_ref2 REFCURSOR
SCOTT@orcl_11g> EXECUTE test_proc (:g_ref1, :g_ref2)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref1

    DEPTNO DNAME
---------- --------------
        30 SALES

SCOTT@orcl_11g> PRINT g_ref2

    DEPTNO ENAME      JOB
---------- ---------- ---------
        30 ALLEN      SALESMAN
        30 WARD       SALESMAN
        30 MARTIN     SALESMAN
        30 BLAKE      MANAGER
        30 TURNER     SALESMAN
        30 JAMES      CLERK

6 rows selected.

SCOTT@orcl_11g> 


Re: REF CURSORS using multiple cursors [message #353636 is a reply to message #353432] Tue, 14 October 2008 07:40 Go to previous message
10guser
Messages: 16
Registered: September 2007
Junior Member
Many Thanks Barbara,

Magical inline view did the trick...
Previous Topic: Need help BIG time! even willing to pay a couple bucks for newbie help...
Next Topic: Selecting with Condition
Goto Forum:
  


Current Time: Sat Dec 03 01:28:16 CST 2016

Total time taken to generate the page: 0.10859 seconds