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 begins returning to us
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 the parameters are  mismatched ?

- mike


Reply via email to