ref cursors [message #39443] |
Wed, 17 July 2002 11:03 |
ian
Messages: 27 Registered: September 1999
|
Junior Member |
|
|
I have a type (objTableType) which is a table of objects (objType):
PROCEDURE MyProc(p_cursor OUT rc) IS
...
LOOP
...
data.EXTEND;
data(i) := objType(name, relation, member_id)
i := i + 1;
END LOOP;
open p_cursor for
select * from TABLE ( cast ( data as objTableType) );
END MyProc;
How do I write a procedure to print out the values of the cursor?
My attempts to fetch from the ref cursor only give me:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
Many thanks,
Ian
|
|
|
Re: ref cursors [message #39482 is a reply to message #39443] |
Thu, 18 July 2002 14:11 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
I don't know about object tables, but see if this helps.
CREATE TABLE ABC (A NUMBER);
insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);
CREATE OR REPLACE PACKAGE ref_cur_tst
IS
-- dummy cursor to get %rowtype below
CURSOR c1 IS SELECT a FROM abc;
TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;
-- or just use abc%rowtype to match whole table structure.
-- TYPE t_cur IS REF CURSOR RETURN abc%ROWTYPE;
PROCEDURE get_abc (cv_cur IN OUT t_cur);
END ref_cur_tst;
/
CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
PROCEDURE get_abc (cv_cur IN OUT t_cur)
IS
BEGIN
OPEN cv_cur FOR SELECT a FROM abc;
END get_abc;
END ref_cur_tst;
/
set serveroutput on;
DECLARE
a abc.a%TYPE;
cv_c1 ref_cur_tst.t_cur;
BEGIN
ref_cur_tst.get_abc (cv_c1);
LOOP
FETCH cv_c1 INTO a;
EXIT WHEN cv_c1%NOTFOUND;
DBMS_OUTPUT.put_line (a);
END LOOP;
CLOSE cv_c1;
END;
/
|
|
|
Re: ref cursors [message #39489 is a reply to message #39443] |
Fri, 19 July 2002 03:09 |
ian
Messages: 27 Registered: September 1999
|
Junior Member |
|
|
Thanks for the response Andrew.
I was trying to fetch into a record or some sort of structure, however, this is what I did in the end:
PROCEDURE getCurs IS
--rc is type ref cursor;
p_c rc;
BEGIN
MyProc(p_c);
--These are the elements of objType;
loop
fetch p_c into name, relation, member_id;
exit when p_c%NOTFOUND;
dbms_output.put_line(name);
end loop;
END getCurs;
|
|
|