Re: Oracle Magazine Blooper

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)