hello guys im trying to push myself from doing the usual and actually learn something new. i have been experimenting with nested tables and in fact i have now used them in a couple of projects of mine. the problem im having now is that if I have a plslq program that is basically filling up a nested table and at the end i want to retrieve all the item in the nested table via a refcursor I cant?... so here is the question if i have something like this.
create or replace
PROCEDURE quicktest(trans_out sys_refcursor)
AS
TYPE local_table IS TABLE OF VARCHAR2(30);
acct_code local_table := local_table();
TYPE rec_type IS RECORD( -- local RECORD type
f1 INTEGER
,f2 VARCHAR2(4)
);
TYPE rec_type_arr IS TABLE OF rec_type
INDEX BY BINARY_INTEGER;
r2 rec_type_arr; -- local type
num NUMBER(17) := 0;
BEGIN
FOR lcntr IN 1 .. 20
LOOP
acct_code.EXTEND;
acct_code(lcntr) := 'I' || lcntr;
r2(lcntr).f1 := num + 1;
r2(lcntr).f2 := 'J' || TO_CHAR(num + 1);
num := num + 1;
END LOOP;
DBMS_OUTPUT.put_line(acct_code.COUNT);
DBMS_OUTPUT.put_line(r2.COUNT);
FOR i IN acct_code.FIRST .. acct_code.LAST
LOOP
DBMS_OUTPUT.put_line(acct_code(i));
END LOOP;
FOR i IN r2.FIRST .. r2.LAST
LOOP
DBMS_OUTPUT.put_line(r2(i).f1 || ' :: ' || r2(i).f2);
END LOOP;
END quicktest;
how can I use a refcursor like "TransOut OUT SYS_REFCURSOR" to return values. I have been banging my head for a few hours and i know there is problably and easy way to do this.
i typically do something like such
open transout for
select * from local_table
but this gives me an error on my plsql code
any help i would be much appreciatvice