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