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!

how to query a nested table?

mlov83Jun 30 2011 — edited Apr 18 2017
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
This post has been answered by William Robertson on Jul 1 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 29 2011
Added on Jun 30 2011
7 comments
1,708 views