Re: DBD::Oracle and bulk operations

Thank you, this is exactly what I was looking for.

best wishes
hannes

On 06/02/2010 02:31 PM, John Scoles wrote:
> Johannes Gritsch wrote:
>
> DBI does support bulk operations and what you are asking about is the
> array interface.
>
> It does it though the 'execute_array'
> http://search.cpan.org/~timb/DBI-1.611/DBI.pm#execute_array method.
>
> and it is available to all DBD drivers.
>
>
> DBD::Oracle is special as it implements its own version of it that takes
> advantage of Oracle's array interface.  It has been in there since 1.18.
>
> here is an example of its use
>
>
> my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
> my @status;
> my $sth = $dbh->prepare(qq( INSERT INTO foo (id, bar) VALUES
> (foo_id_seq.nextval, ?)));
> $sth->bind_param_array(1,\@in_values);
> $sth->execute_array({ArrayTupleStatus=>\@status});
>
> I have never done a full speed test but with 1.17 it took about 25min to
> insert 2 million records using the same code and 1.18 it took only about
> 6 min.
>
> hope this helps
>
> cheers
> John
>    
>> Hi!
>>
>> Since I came into this topic while writing a script: What does
>> DBD::Oracle know about bulk operations?
>>
>> In PL/SQL you can speed up your cursor loops by using bulk fetches
>> and/or bulk updates/inserts. Instead of operating on single rows you
>> use arrays of rows. Since DBI does not support it directly (from what
>> I know), it could be used when doing select_all_xxx calls. Sounds
>> simple, and, yes, I know, it is not as easy as it sounds. But I think
>> it would speed up this calls. PL/SQL gets a boost by 5 to 10,
>> depending on hardware, OS and other things.
>>
>> When you search for DBD::Oracle and bulk, google only comes up with
>> rather old documents dating back to 2004 and 2003. In one of those
>> (from this mailinglist) I found:
>>
>>   "It is not part of DBD::Oracle yet, though there are plans to include
>> it in a future version."
>>
>> I'm a regular reader of this mailing list and I wonder that this topic
>> did not come up again in recent years - or I just missed it ...
>>
>> cu
>> hannes
>>      
>