Re: Bug in Oracle driver ?

Interesting.

Please revert back to the basic 'division_order_id = :2' form and
try setting $dbh->{ora_ph_type} instead.

use DBD::Oracle qw(:ora_types);

...
$dbh->{ora_ph_type} = ORA_STRING; # or ORA_VARCHAR2
my $sql = ...

Also, a few questions...
- what are the Oracle version numbers of your client and server
- what does this print:

$nls = $dbh->ora_nls_parameters();
print "Server $nls->{NLS_CHARACTERSET} / $nls->{NLS_NCHAR_CHARACTERSET}\n";
print "Client $ENV{NLS_LANG} / $ENV{NLS_NCHAR}\n";

Thanks.

Tim.

On Thu, Dec 15, 2005 at 04:28:02PM -0600, Igor Korolev wrote:
> >Hello,
> >
> >It looks like the Oracle driver incorrectly binds VARCHAR2 variables in case a number is passed from perl.
> >
> >Here is an example. This table has a primary key on division_id + division_order_id, so such selects should run very quickly (and they do run very quickly from sqlplus or toad).
> >
> >However, if I execute this using selectrow_array method of DBI.pm
> >
> >my $sql = 'select ' . join(',',@$sum_fields) . ' from ' . $table .
> > qq[ where division_id = :1 and division_order_id = :2];
> >
> >It takes up to 1 second per select:
> >
> >Order [1], select took: [0.730479001998901]
> >Order [2], select took: [0.441529989242554]
> >Order [1130979589-6919-090637], select took: [0.462771892547607]
> >Order [3], select took: [0.424475193023682]
> >Order [947647153], select took: [0.513856172561646]
> >Order [4], select took: [0.49094295501709]
> >Order [6], select took: [0.523995161056519]
> >Order [7], select took: [0.558786153793335]
> >Order [8], select took: [0.452331066131592]
> >Order [131635406], select took: [0.451704978942871]
> >
> >However, a simple addition forcing VARCHAR2, dramatically improves performance
> >
> >my $sql = 'select ' . join(',',@$sum_fields) . ' from ' . $table .
> > qq[ where division_id = :1 and division_order_id = '' || :2];
> >
> >Order [1], select took: [0.00937604904174805]
> >Order [2], select took: [0.00504183769226074]
> >Order [1130979589-6919-090637], select took: [0.0115630626678467]
> >Order [3], select took: [0.00729489326477051]
> >Order [947647153], select took: [0.00545406341552734]
> >Order [4], select took: [0.00602984428405762]
> >Order [6], select took: [0.00556111335754395]
> >Order [7], select took: [0.00576210021972656]
> >Order [8], select took: [0.00520086288452148]
> >Order [131635406], select took: [0.00546598434448242]
> >
> >Thank you,
> >
> >Igor