REF CURSORS using multiple cursors [message #353281] |
Mon, 13 October 2008 04:07  |
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 #353288 is a reply to message #353285] |
Mon, 13 October 2008 04:45   |
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 #353312 is a reply to message #353305] |
Mon, 13 October 2008 05:28   |
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 #353317 is a reply to message #353281] |
Mon, 13 October 2008 05:33   |
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   |
 |
Barbara Boehmer
Messages: 9104 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>
|
|
|
|