Oracle-L: RE: bulk collect in 9i

From: Sakthi , Raj <rajan_sakthi_at_yahoo.com>
Date: Wed, 27 Mar 2002 14:13:25 -0800
Message-ID: <F001.004355E3.20020327141325@fatcity.com>

IMHO, it still does. You can find other ways to simulate bulk collect though. Checkout tom kyte's site.
www.asktom.oracle.com

I have posted the excerpts below.

What about this then (you still have to create the object type with all 50
columns, NO ESCAPING that)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type myScalarType as object
  2 ( ename varchar2(30),
  3 hiredate date,
  4 sal number
  5 )
  6 /

Type created.

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> select myScalarType( ename, hiredate, sal )
from emp;

MYSCALARTYPE(ENAME,HIREDATE,SAL)(ENAME, HIREDATE, SAL)

MYSCALARTYPE('A', '17-DEC-80', 800)
MYSCALARTYPE('ALLEN', '20-FEB-81', 1600)
MYSCALARTYPE('WARD', '22-FEB-81', 1250)
MYSCALARTYPE('JONES', '02-APR-81', 2975)
MYSCALARTYPE('MARTIN', '28-SEP-81', 1250)
MYSCALARTYPE('BLAKE', '01-MAY-81', 2850)
MYSCALARTYPE('CLARK', '09-JUN-81', 2450)
MYSCALARTYPE('SCOTT', '09-DEC-82', 3000)
MYSCALARTYPE('KING', '17-NOV-81', 5000)
MYSCALARTYPE('TURNER', '08-SEP-81', 1500)
MYSCALARTYPE('ADAMS', '12-JAN-83', 1100)
MYSCALARTYPE('JAMES', '03-DEC-81', 950)
MYSCALARTYPE('FORD', '03-DEC-81', 3000)
MYSCALARTYPE('MILLER', '23-JAN-82', 1300)

14 rows selected.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare
  2      cursor c is select myScalarType( ename,
hiredate, sal ) from emp;
  3      l_data myArrayType;

4 begin
5

  6      open c;
  7      loop
  8          l_data := myArrayType();
  9          fetch c bulk collect into l_data limit 6;
 10          dbms_output.put_line( 'Fetched ' ||

l_data.count || ' rows' );
11

 12          for i in 1 .. l_data.count
 13          loop
 14              l_data(i).ename := 'X' ||
l_data(i).ename;
 15          end loop;
 16  
 17          insert into emp2 ( ename, hiredate, sal )
 18          select * from TABLE( cast (l_data as

myArrayType) );
19

 20          exit when c%notfound;
 21      end loop;
 22      close c;

23 end;
24 /
Fetched 6 rows
Fetched 6 rows
Fetched 2 rows

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from emp2;

ENAME HIREDATE SAL

---------- --------- ----------
XA         17-DEC-80        800
XALLEN     20-FEB-81       1600
XWARD      22-FEB-81       1250
XJONES     02-APR-81       2975
XMARTIN    28-SEP-81       1250
XBLAKE     01-MAY-81       2850
XCLARK     09-JUN-81       2450
XSCOTT     09-DEC-82       3000
XKING      17-NOV-81       5000
XTURNER    08-SEP-81       1500
XADAMS     12-JAN-83       1100
XJAMES     03-DEC-81        950
XFORD      03-DEC-81       3000
XMILLER    23-JAN-82       1300

14 rows selected.

HTH,
Cheers,
RS
--- "Toepke, Kevin M" <ktoepke_at_trilegiant.com> wrote:
> Yes, that limitation still exists in Oracle 9i, R1.
>
> -----Original Message-----
> Sent: Wednesday, March 27, 2002 2:19 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello,
>
> In 8i, the BULK COLLECT and FORALL commands are
> limited
> to working with scalar arrays.
>
> Does this restriction still apply in 9i? We are
> still
> on 8.1.6.
>
> In my case, I would like to do something like:
>
> TYPE tab_type IS TABLE OF oracle_table%ROWTYPE
> INDEX BY BINARY_INTEGER;
>
> plsql_table tab_type;
>
> SELECT * BULK COLLECT INTO plsql_table
> FROM oracle_table;
>
> and
>
> FORALL j IN plsql_table.FIRST..plsql_table.LAST
> INSERT INTO oracle_table VALUES (plsql_table(j));
>
> Is this possible in 9i?
>
> Thanks to any responders.
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Bill Becker
> INET: beckerb_at_mfldclin.edu
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists

>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Toepke, Kevin M
> INET: ktoepke_at_trilegiant.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists

>

Do You Yahoo!?
Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sakthi , Raj
  INET: rajan_sakthi_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Mar 27 2002 - 16:13:25 CST