Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sys_refcursor rowtype

pollywogMay 24 2012 — edited May 24 2012
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
this works fine.
DECLARE
   myrefcursor   SYS_REFCURSOR;

   TYPE myrecord IS RECORD
   (
      mydate     DATE,
      mynumber   NUMBER
   );

   TYPE mytable IS TABLE OF myrecord;

   mylist        mytable;
BEGIN
   OPEN myrefcursor FOR
          SELECT SYSDATE + LEVEL mydate, LEVEL mynumber
            FROM DUAL
      CONNECT BY LEVEL <= 10;

   FETCH myrefcursor
   BULK COLLECT INTO mylist;

   CLOSE myrefcursor;

   FOR indx IN mylist.FIRST .. mylist.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            'indx: '
         || indx
         || ' mydate: '
         || mylist (indx).mydate
         || ' mynumber: '
         || mylist (indx).mynumber);
   END LOOP;
END;
indx: 1 mydate: 25-MAY-12 mynumber: 1
indx: 2 mydate: 26-MAY-12 mynumber: 2
indx: 3 mydate: 27-MAY-12 mynumber: 3
indx: 4 mydate: 28-MAY-12 mynumber: 4
indx: 5 mydate: 29-MAY-12 mynumber: 5
indx: 6 mydate: 30-MAY-12 mynumber: 6
indx: 7 mydate: 31-MAY-12 mynumber: 7
indx: 8 mydate: 01-JUN-12 mynumber: 8
indx: 9 mydate: 02-JUN-12 mynumber: 9
indx: 10 mydate: 03-JUN-12 mynumber: 10
however if I attempt to define mytable as a table of myrefcursor%rowtype it becomes upset.
DECLARE
   myrefcursor   SYS_REFCURSOR;



   TYPE mytable IS TABLE OF myrefcursor%ROWTYPE; -- this dog don't hunt

   mylist        mytable;
BEGIN
   OPEN myrefcursor FOR
          SELECT SYSDATE + LEVEL mydate, LEVEL mynumber
            FROM DUAL
      CONNECT BY LEVEL <= 10;

   FETCH myrefcursor
   BULK COLLECT INTO mylist;

   CLOSE myrefcursor;

   FOR indx IN mylist.FIRST .. mylist.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            'indx: '
         || indx
         || ' mydate: '
         || mylist (indx).mydate
         || ' mynumber: '
         || mylist (indx).mynumber);
   END LOOP;
END;
ORA-06550: line 6, column 29:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 6, column 4:
PL/SQL: Item ignored
ORA-06550: line 16, column 22:
PLS-00597: expression 'MYLIST' in the INTO list is of wrong type
ORA-06550: line 15, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 26, column 27:
PLS-00487: Invalid reference to variable 'MYREFCURSOR%ROWTYPE'
ORA-06550: line 22, column 7:
PL/SQL: Statement ignored
I was reading the documentation but my tiny little brain is having a hard time understanding why this construct does not work.

is there a way to do this?
I only ask because my real cursor/s select alot of stuff and it is a pain to create a record for each sys_refcursor
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 21 2012
Added on May 24 2012
3 comments
3,365 views