Re: INSERT using BIG subquery

From: R197509 <ramsunders_at_yahoo.com>
Date: 19 Feb 2002 06:23:59 -0800
Message-ID: <1e562f83.0202190623.4ea27201_at_posting.google.com>

Hi David,

   I'm not sure if there is an upper limit that might affect this approach, but don't you have array operations in Oracle 7.3.2.3?

   I mean would the following statement work: DECLARE

   col1_arr TABLE OF col1 INDEX BY BINARY_INTEGER;
   col2_arr TABLE OF col2 INDEX BY BINARY_INTEGER;
   col3_arr TABLE OF col3 INDEX BY BINARY_INTEGER;

BEGIN
   insert into my_table values(col1_arr, col2_arr, col3_arr); END; Regards,
Ram.

"David Raymond" <draymon6_at_csc.com.au> wrote in message news:<a3t2dg$nuo$1_at_gossamer.itmel.bhp.com.au>...
> Hi,
> I have an application which is inserting a 2D array into a table of the
> form:
>
> ID NUMBER(7)
> ROW NUMBER(7)
> COL NUMBER(3)
> VAL VARCHAR2(100)
>
> Rather than do an INSERT for each row I am trying this method:
>
> insert into MY_TABLE(
> select 999, 0, 0, 'A' from DUAL
> union select 999, 0, 1, 'B' from dual
> union select 999, 1, 0, 'C' from dual
> union select 999, 1, 1, 'D' from dual
> ...
> );
>
> It seems to work ok and is quite a lot quicker than individual INSERTS. I
> have tested it on 6500 rows - no problems. Are there any limits to statement
> sizes, union sizes etc.. that may affect this approach?
>
> Oracle Version 7.3.2.3.0
>
> thanks
> regards
> David Raymond
Received on Tue Feb 19 2002 - 15:23:59 CET