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