How to fetch Oracle reference cursor into table variable?

Question

I am trying to load data from reference cursor into a table variable (or array), the reference cursor works if the table variable is based on existingtable %Rowtype but my reference cursor gets loaded by joining multiple tables so let me try to demonstrate an example what i am trying to do and some one can help me

--created table
create table SAM_TEMP(
    col1 number null,
    col2 varchar(100) null
);

--created procedure which outputs results from that table

CREATE OR REPLACE
PROCEDURE SP_OUT_RefCur_PARAM(
    C_RESULT OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN C_RESULT FOR 
        SELECT COL1,COL2
        FROM SAM_TEMP;
END SP_OUT_RefCur_PARAM;

--seeing the output works like this
DECLARE 
    REFCUR SYS_REFCURSOR;   
    outtable SAM_TEMP%rowtype ;  
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO outtable;
        EXIT WHEN REFCUR%NOTFOUND;
        dbms_output.put_line(outtable.col1);
    END LOOP;
    CLOSE REFCUR;
END;


--but when i try to run below script it is giving error,i think i am missing something
DECLARE 
    REFCUR SYS_REFCURSOR;   
    TYPE REFTABLETYPE IS RECORD (COL1 NUMBER, COL2  VARCHAR(100));
    TYPE TABLETYPE IS TABLE OF REFTABLETYPE;
    outtable TABLETYPE; 
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO outtable;
        EXIT WHEN REFCUR%NOTFOUND;
        dbms_output.put_line(outtable.col1);
    END LOOP;
    CLOSE REFCUR;
END;

Error report:

ORA-06550 line 9, column 21:
PLS-00597 expression 'OUTTABLE' in the INTO list is of wrong type
ORA-06550 line 9, column 3:
PL/SQL SQL Statement ignored
ORA-06550 line 11, column 32:
PLS-00302 component 'COL1' must be declared

Not sure what i am missing, Thanks in advance for your help

Just saw type in my answer. Actually sentence should be "Your variable outtable actually is table type." — Alexander Myshov, Dec 06 '13 at 05:02

Alexander Myshov · Accepted Answer · 2013-12-06T21:58:01.267

The name of variable in code above misleaded you. Your variable outtable is in table type. It isn't possible to fetch record data into table of records, but you can fetch it into record itself.

DECLARE 
    REFCUR SYS_REFCURSOR;   
    TYPE RECORDTYPE IS RECORD (COL1 NUMBER, COL2  VARCHAR(100));
    outtable RECORDTYPE; 
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO outtable;
        EXIT WHEN REFCUR%NOTFOUND;
        dbms_output.put_line(outtable.col1);
    END LOOP;
    CLOSE REFCUR;
END;

Update: If you want to fetch all data for better performance your application you need to use BULK COLLECT statement:

DECLARE 
    REFCUR SYS_REFCURSOR;   
    TYPE RECORDTYPE IS
        RECORD (COL1 NUMBER, COL2  VARCHAR(100));
    TYPE TABLETYPE IS
        TABLE OF REFTABLETYPE
        INDEX BY PLS_INTEGER;
    outtable TABLETYPE; 
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO BULK COLLECT outtable;
        EXIT WHEN outtable.COUNT = 0;

        FOR indx IN 1 .. outtable.COUNT 
        LOOP
            dbms_output.put_line(outtable(indx).col1);;
        END LOOP;
    END LOOP;
    CLOSE REFCUR;
END;

Note: memory consumption with the BULK statement is much more than without.

The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." (Oracle Magazine)

But if you are just fetching and processing the rows - a row at a time there is no needs in BULK statement, just use the cursor FOR LOOP. (Ask Tom)

thanks alexander,that worked, so if we are inserting record by record we need to insert into record itself, if we are bulk inserting (bulk collect) then we need to create a table variable instead of table type variable right? — sam, Dec 06 '13 at 14:58
@sam As I understand you, if you want to use `BULK COLLECT` you can to use just of collection of records. I just updated my answer, so you can find more information now. — Alexander Myshov, Dec 06 '13 at 21:23

score 1 · Answer 2 · answered Dec 06 '13 at 07:03

1

Another way to do it is this one:

DECLARE 
    REFCUR SYS_REFCURSOR;   
    TYPE REFTABLETYPE IS RECORD (COL1 NUMBER, COL2  VARCHAR(100));
    TYPE TABLETYPE IS TABLE OF REFTABLETYPE;
    outtable TABLETYPE; 
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    FETCH REFCUR BULK COLLECT INTO outtable; 
    FOR i in outtable.First..outtable.Last Loop
        dbms_output.put_line(outtable(i).col1);
    END LOOP;
    CLOSE REFCUR;
END;

answered Dec 06 '13 at 07:03

Wernfried Domscheit

54,457
9
76
110

thanks wernfried this is what i was doing and wanted to see if the other one (direct loop cursor) is faster and i got mixedup – sam Dec 06 '13 at 15:02
BULK COLLECT should be faster by default. – Wernfried Domscheit Dec 06 '13 at 15:31

How to fetch Oracle reference cursor into table variable?

2 Answers2

Linked