Re: FW: pl/sql array processing?

If mypackage.function(i) is doing some DML operation on i, then the real way to make it faster is to modify the signature of mypackage.function(i) to take an array instead, and to do a "forall ... <dml operation>" within mypackage.function(i).

forall is most useful when you want to minimize context switching between the pl/sql and sql engines for an array. Using forall you'll be switching context only once whereas using a regular for loop you'll be switching context for every member of the array.

Regards,
Dave

mladen_at_wangtrading.com wrote:

>
> 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;
>
> forall i in refTbl.first..refTbllast
> begin
> dbms_output.put_line ('i=' || i);
> str:= my_package.function(i);
> end;
> end;
> /
>
>
>
> Of course, it doesn't make sense because varchar2 variable string
> will be lost after each iteration. May be an actual example that
> makes sense
> would motivate more people to respond.
>
>
> On 01/27/2004 12:54:29 PM, Guang Mei wrote:
>
>> My following message did not seem to make it to oracle-l.freelists.
>> Let me
>> try it again.
>>
>> Guang
>>
>> -----
>> Hi,
>>
>> I have the folliwng pl/sql code for oracle 8173. I am wondering if
>> there is
>> a way to make it faster by not looping each array elements, but doing
>> some
>> kind of "forall" opration to my_package.function?
>>
>> 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;
>> /
>>
>>
>> TIA.
>>
>> Guang
>>
>>
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> --
>> Author: Guang Mei
>> INET: gmei_at_incyte.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).