Re: Oracle Magazine Blooper

Daniel Morgan wrote:
>
> Serge Rielau wrote:
>
> > I didn't miss that and I stated CONNECT BY as the Oracle way of doing
> > it, presuming soemone knows how to translate. Unfortunetely I neither
> > have CONNECT BY experience, nor (obviously) an Oracle system to try it.
> >
> > The point I tried to make however is simple enough:
> > If there is a way to pull the INSERT out of the loop then that would be
> > better coding style. The means used (varray, table function, recursion,
> > ..?) are secondary.
> > If it can't be done I withdraw my example :-)
> >
> > BTW, doesn't Oracle support a FOR loop which "blocks" the body and
> > pushed the whole beast deeper into the engine? That as well would be
> > better :-)
> >
> > Cheers
> > Serge
>
> The best way, in Oracle, would be no loop at all. For example:
>
> CREATE OR REPLACE PROCEDURE fast_way IS
>
> TYPE PartNum IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER;
> x PartNum;
>
> TYPE PartName IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER;
> y PartName;
>
> BEGIN
> SELECT part_num, part_name
> BULK COLLECT INTO x, y
> FROM t1;
>
> FORALL i IN x.FIRST .. x.LAST
> INSERT INTO t2
> (part_num, part_name)
> VALUES
> (x(i), y(i));
> COMMIT;
> END fast_way;
> /
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)

or

insert into t2
select part_num, part_name
from t1;

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------