On Tue, Apr 05, 2005 at 05:47:00PM -0400, mike bayer wrote:
> 
> 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

You've not said what your database CHAR and NCHAR charsets are.
(Both are relevant.)


> (or in some cases, it produces an oracle character-set mismatch error).

A reproducible test case would help - ideally as a patch to an existing test.


> 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.

Yes. And if you enable DBD::Oracle tracing (try trace level 5) you can
try to find out if/why it hasn't.

But looking at the code I can see something that's possibly what's
tripping you up. I've hacked the code and attached a patch - but it's
completely untested (may not even compile). Give it a go and let me know.

If it works then please do me and the world a favor by adding some
tests to the test suite to reproduce the original problem (ie the
test should fail without the patch). Thanks.

> and why is it so disastrous when the parameters are mismatched ?

I don't know. Perhaps some Oracle expert will make some suggestions,
but it'll probably be guesswork unless you do use Oracle's tracing
facilities to find out what's really going on inside Oracle.

But sorting out automatic SQLCS_IMPLICIT is the first priority.

Tim.

--- dbdimp.c    (revision 514)
+++ dbdimp.c    (working copy)
@@ -1326,12 +1323,15 @@

     csform = phs->csform;

-    if (SvUTF8(phs->sv) && !csform) {
-       /* try to default the csform to avoid translation through non-unicode */
-       /* given Oracle policy that NCHAR==Unicode this should be fine */
-       csform = SQLCS_NCHAR;
-       /* in some cases this isn't right for LOBs but those are rare and */
-       /* the application can use an explicit ora_csform bind attribute. */
+    if (!csform && SvUTF8(phs->sv)) {
+       /* try to default csform to avoid translation through non-unicode */
+       if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT))        /* prefer implicit */
+           csform = SQLCS_IMPLICIT;
+       else if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR))
+           csform = SQLCS_NCHAR;
+       else if (trace_level)                           /* leave csform == 0 */
+           PerlIO_printf(DBILOGFP, "       rebinding %s with UTF8 value but 
neither CHAR nor NCHAR are unicode\n",
+                   phs->name);
     }

     if (csform) {

Reply via email to