Re: FW: pl/sql array processing?

forall should be used as follows:

forall index in lower_bound..upper_bound

    <sql statement>;

Putting anything other than a sql statement (e.g. a pl/sql block) in a forall statement defeats its purpose.

If you think about it, forall achieves its performance improvement by binding arrays to the arguments of a sql statement, instead of binding individual elements of the array to the sql statement. This is faster because now the entire array is passed from the pl/sql engine to the sql engine all in one shot, and so this minimizes context switching between the pl/sql engine and the sql engine. This is analogous to the array binding in OCI if you're familiar with OCI or Pro*C programming.

Putting anything other than a sql statement in a forall statement does not achieve any benefit because you're not switching context to the sql engine.

So Guang, I think what you should do is move the forall closest to where you're doing the sql (DML) operation. If you're doing the DML within mypackage.function(...) then pass the entire array into mypackage.function(...) and then do the forall within mypackage.function.

Regards,
Dave

gmei_at_incyte.com wrote:

>Sorry I did not make it clear that the number I used here (1, 9, 15,99) are
>just examples, the actual element index is a varible and they are not
>continuous. Yes, refTbl can be defined into a package. I guess what I am
>asking is if there is a way in pl/sql to do something like
>
>-- FORALL array element indexes (they are non-continuous)
> call a package function (parameter: element index)
>-- end for
>
>without looping the array.
>
>
>-- orginal code:
>declare
> type numTbl is table of number index by binary_integer;
> refTbl numTbl;
> i number;
> str varchar2(30);
>begin
> refTbl (1) := 1;
> refTbl (9) := 1;
> refTbl(15) := 1;
> refTbl(99) := 1;
>
> i := refTbl.first;
> while i is not null loop
> dbms_output.put_line ('i=' || i);
> str:= my_package.function(i);
> i := refTbl.next(i);
> end loop;
>end;
>/
>
>
>Guang
>
>-----Original Message-----
>Mladen Gogala
>Sent: Tuesday, January 27, 2004 2:29 PM
>To: Multiple recipients of list ORACLE-L
>
>
>On 01/27/2004 02:09:25 PM, "Jesse, Rich" wrote:
>
>
>>Couldn't the declarations be put into a package? We've done this in
>>order
>>to maintain values for the life of the session.
>>
>>
>
>Yes, they could, I didn't see it in this example.
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mladen Gogala
> INET: mladen_at_wangtrading.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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.net
-- 
Author: David Hau
  INET: davehau123_at_netscape.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).