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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
