hi -

this behavior is not holding us back as its easy to work around, but its
something that should be documented/clarified/fixed, since it can create
extremely hard to track-down hangs.  its also possible that something in
our oracle database is configured wrong, which I would appreciate feedback
on since im the application guy here, not the DBA.

we are running DBD::Oracle against Oracle 9i client libraries.  The
character encoding of the database is utf8.

When we enable NLS_LANG=AMERICAN_AMERICA.UTF8, DBI properly begins
returning   utf8-flagged perl scalars in rowsets.  If you then put one of
those  returned scalars into a bind parameter for a prepared statement
that is  querying a large table, the query slows down to a super-crawl, as
though indexes have been disabled (or in some cases, it produces an oracle
character-set mismatch error).  A less-than-a-second query suddenly takes
like three minutes to complete.

the workaround for this is one of:

1. turn off NLS_LANG.  but then, youre not using utf8.

2. turn off the utf8 flag on all scalars being sent as bind parameters.
this can be achieved via utf8::encode or also via the Encode module.  but
then, youre not using utf8.

3. use a feature that the DBD man page claims is "Rarely needed", without
any other explaination for its importance unless you are already an OCI
guru, which is to set ora_csform to SQLCS_IMPLCIT on each utf8 bind
parameter, as in:

my $options = {};
$options->{ora_csform} = SQLCS_IMPLICIT if utf8::is_utf8($value);
$sth->bind_param($key, $value, $options);

it seems like DBD::Oracle should figure this option out automatically,
based on the fact that NLS_LANG is turned on and the utf8 flag on the
parameter is set.  and why is it so disastrous when the parameters are
mismatched ?

- mike

Reply via email to