Re: BULK COLLECT INTO Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 12 Nov 2001 12:09:07 -0800
Message-ID: <9spa9301miq@drn.newsguy.com>

In article <RLJH7.61495$%94.8951951_at_news02.optonline.net>, "Robert" says...
>
>Can one BULK COLLECT entire rows INTO a Nested Table
>of TYPE Record ?
>
>e.g:
>
>DECLARE
>
>local_rec emp%ROWTYPE;
>TYPE local_rec_tab IS TABLE OF local_rec%ROWTYPE;
> v_tab local_rec_tab;
>
>BEGIN
>
>SELECT *
>BULK COLLECT INTO v_tab --<---ORA-06550
>FROM emp;
>
>END;
>
>
>Thanks
>jane
>janeyiu_at_optonline.net
>
>
>

You don't use bulk collect -- you use a multi-set BUT -- it must be a SQL type -- not a plsql type.

It will look like this:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type myScalarType as object 2 ( x int, y date, z varchar2(30) )
3 /

Type created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type myArrayType as table of myScalarType
2 /

Type created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare 2 l_data myArrayType;
3 begin

  4      select cast( multiset( select user_id, created, username
  5                               from all_users )
  6                   AS myArrayType )
  7        into l_data
  8        from dual;
  9  
 10      for i in 1 .. l_data.count
 11      loop
 12          dbms_output.put_line( l_data(i).x || ',' || l_data(i).y );
 13      end loop;

14 end;
15 /
0,28-AUG-01
5,28-AUG-01

11,28-AUG-01
16,28-AUG-01
27,28-AUG-01
28,28-AUG-01
25,28-AUG-01
63,05-NOV-01
30,09-SEP-01
37,28-SEP-01
38,28-SEP-01
46,11-OCT-01
50,20-OCT-01
58,26-OCT-01
59,26-OCT-01
76,09-NOV-01
77,09-NOV-01
78,09-NOV-01

PL/SQL procedure successfully completed.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Mon Nov 12 2001 - 14:09:07 CST