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