Home » SQL & PL/SQL » SQL & PL/SQL » ref cursors
ref cursors [message #39443] Wed, 17 July 2002 11:03 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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;
Previous Topic: very urgent
Next Topic: Re: urgent
Goto Forum:
  


Current Time: Fri Apr 26 21:25:50 CDT 2024