REF CURSOR

From: Curtis VonAncken <cvonancken_at_orkand.com>
Date: 2 Apr 2002 12:37:08 -0800
Message-ID: <b712ca72.0204021237.6c8e6d92_at_posting.google.com>

Hey there folks I am looking for the most efficient way to do the following. I am rather new to PL/SQL and am trying to get these results into a ref cursor in a package. I have what is now my best try below though I am getting compile errors. I think what I want to do is take the results of these three queries and put them in cursors which I will then combine in one record. But how then do I get that data into another cursor for the ref cursor. Mind you I am knew to this and might be way off...

table1

col1	col2	col3	col4
1	4	5	A
1	5	23	A 

table2
col1	col2	col3	col4
1	4	27	B
1	5	55	B
table3
col1	col2	col3	col4
1	4	66	C
1	5	3	C

REF CUSRSOR RESULT
col1	col2	col3	col4
1	4	5	A
1	4	27	B
1	4	66	C
1	5	23	A 
1	5	55	B
1	5	3	C

CREATE OR REPLACE PACKAGE pkg_ACR
AS

	TYPE lookup_cur IS REF CURSOR;
	PROCEDURE sp_table1a(p_cursor OUT lookup_cur);

END pkg_ACR;
/

CREATE OR REPLACE PACKAGE BODY pkg_ACR
AS
PROCEDURE sp_table1a(p_cursor OUT lookup_cur) DECLARE
CURSOR table1a_cur1 IS
select * from table1;
CURSOR table1a_cur2 IS
select * from table2;
CURSOR table1a_cur3 IS
select * from table3;
IS
BEGIN

OPEN table1a_cur1;
OPEN table1a_cur2;
OPEN table1a_cur3;
FETCH table1a_cur1 INTO table1_rec;

FETCH table1a_cur2 INTO table1_rec;
FETCH table1a_cur3 INTO table1_rec;

	WHILE table1a_cur1%FOUND
	LOOP
FETCH table1a_cur1 INTO table1_rec;

FETCH table1a_cur2 INTO table1_rec;
FETCH table1a_cur3 INTO table1_rec;

        END LOOP;

CLOSE table1a_cur1;
CLOSE table1a_cur2;
CLOSE table1a_cur3;

END sp_table1a;
END pkg_ACR;
/

CREATE OR REPLACE PACKAGE BODY pkg_ACR
*
ERROR at line 1:
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-906: Compilation is not possible Received on Tue Apr 02 2002 - 22:37:08 CEST