Re: PL/SQL Table Question

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Thu, 06 Apr 2000 14:55:47 GMT
Message-ID: <8ci8h9$svi$1_at_nnrp1.deja.com>

In article <8ci3s8$ngh$1_at_nnrp1.deja.com>,   mcconje_at_my-deja.com wrote:
> In article <8ci30f$mo2$1_at_nnrp1.deja.com>,
> Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> > In article <8ci231$lft$1_at_nnrp1.deja.com>,
> > mcconje_at_my-deja.com wrote:
> > > Hi All,
> > >
> > > I recently heard from a co-worker that Oracle 8i allows a "bulk
> > commit"
> > > from a PL/SQL table to a standard database table. I have been
> lookinf
> > > for more information on this but as of yet been unable to unearth
> > > anything. Does anyone anyone know if this is possible and how to
> > > accomplish it.
> > >
> > > Thanks
> > > Jeff McConnell
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > I think you mean BULK BINDS not commits
> >
> > this allows you to more easily do array inserts/updates/deletes and
> > selects from plsql (without having to use DBMS_SQL)
> >
> > See
> >
>
http://technet.oracle.com/doc/oracle8i_816/appdev.816/a77069/04_colls.ht
> > m#23723
> >
> > thats the plsql manual for 8i
> >
> > --
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries
> > http://osi.oracle.com/~tkyte/index.html
> > --
> > Opinions are mine and do not necessarily reflect those of Oracle
Corp
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Is a bulk bind an insert/update/delete into a normal database table ?
>

All is explained in:

http://technet.oracle.com/doc/oracle8i_816/appdev.816/a77069/04_colls.ht m#23723

a bulk bind allows you to bind a PLSQL table (a local variable in a plsql routine) to a SQL statement. Lets say you want to insert 5 rows. You could (in the old fashioned way) execute an insert statement 5 times in plsql. Using BULK BINDS I can fill up a plsql table type (an array in effect) with 5 values and execute the insert once.

Here is an example of the OLD way (insert a row 5,000 times) vs the NEW way (insert 5,000 rows in one statement)

ops$tkyte_at_8i> begin

  2      for j in 1 .. 5000 loop
  3          insert into insert_into_table values ( j );
  4      end loop;
  5      commit;

  6 end;
  7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.69
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare

  2      type numTab is table of number(4) index by binary_integer;
  3      data numTab;
  4  begin
  5      for j in 1 .. 5000 loop
  6          data(j) := j;
  7      end loop;
  8
  9      forall i in 1 .. 5000
 10          insert into insert_into_table values( data(i) );
 11      commit;

 12 end;
 13 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Apr 06 2000 - 16:55:47 CEST