Re: Perl and Oracle

On Sun, 09 Apr 2006 18:18:56 -0700, krichine wrote:

>
> specifically
> http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/sld017.htm
>
> gives an example of how to use array bulk fetches.

This is MySQL example, for goodness sake! I'm complaining about DBD::Oracle. What does MySQL have to with anything? I'm talking specifically about calls DBI::bind_param_array and DBI::execute_array. With DBD::Oracle, these calls are internally converted into loops. This is the problem
use DBI;
my $INS="insert into TAB values (:A,:B,:C)"; my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd ) ||

   die($DBI::errstr . "\n" );
my (@A,@B,@C);
my @stat;

my $sth=$dbh->parse($INS);
   $sth->bind_param_array(":A",\@A);
   $sth->bind_param_array(":B",\@B);
   $sth->bind_param_array(":C",\@C);   
   $sth->execute_array( { ArrayTupleStatus => \@stat} );

With DBD::Oracle the last call will be broken into loop of $sth->execute() calls with $DBI::err returned into the corresponding @stat array element. Oracle can do that in a single call. It's called "array interface" and it exists since Oracle 6 which is 16 years old (appeared in 1990). As a matter of fact, Oracle was the first database to have such an interface and yet DBD:Oracle still doesn't support it. Any general purpose language interface which doesn't support that feature can be deemed mediocre at best.
In addition to that, I used to work with a table which had 933 million rows and spanned more then 100GB. It was a table that contains claims for a large national HMO in US. I think that any "fetchall" call on that table would be an extremely interesting proposition, even if it went only after the partition containing the data for the last quarter. I wish you good luck with "fetchall" calls if you ever encounter such a monster. I believe though that few guys on this group are working with monsters of that magnitude or bigger on a daily basis.

-- 
http://www.mgogala.com