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
.

Relevant Pages

  • Re: DBI.pm DBD::Oracle::db
    ... trace level 9 doesn't trigger the tracing detail that it ought to. ... but the server B does not have oracle client ... Tim. ...
    (perl.dbi.users)
  • [NEWS] Multiple Vulnerabilities in Oracle Database (Character Conversion, Extproc, Password Disclosu
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Multiple vulnerabilities were discovered in the (Oracle database server ... password is required to exploit this vulnerability. ...
    (Securiteam)
  • Re: How to make your report run faster
    ... I've done no work with Oracle as the Server DB, ... > if they click the listbox then "Preview report" button is enabled. ... > -I was try to make the old queries for subreport, ...
    (microsoft.public.access.reports)
  • Re: I cant find a SETUP.EXE in the SQL Plus Client ??
    ... someone else's server for testing some SELECT statements I'm ... free client because I don't own the Oracle license. ... SQL statements. ... There is no documentation with that download. ...
    (comp.databases.oracle.tools)
  • RE: sunmanagers Digest, Vol 44, Issue 20
    ... diagnostics from the front-panel. ... Any ideas on what the problem may be or how to get this server powered ... The cluster itself is working, but I'm unable to shut down the nodes. ... Oracle uses shared memory for the communication between the client ...
    (SunManagers)