How to get a link to an Oracle link in a table variable?

Question

I'm trying to load data from a 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 several tables, so let me try to demonstrate an example of what I'm trying to do, and someone 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.

Answer 1

The variable name in the code above is misleading. Your outtable variable is in type table . It is not possible to get the record data in the record table, but you can get it in the 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 get all the data for the best performance of your application, you need to use the 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 larger than without.

The most important thing to remember when you learn and start using features such as BULK COLLECT is that there is no free lunch. There is almost always a compromise. The compromise with BULK COLLECT, like many other performance-enhancing features, is "faster, but more memory intensive." ( Oracle Magazine )

But if you just load and process the lines - the line at a time, there is no need for a BULK statement, just use the FOR LOOP cursor. ( Ask Tom )

Answer 2

Another way to do this:

 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;