Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 18012
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: champoux [...] pythian.com
Requestors: philip.garrett [...] manheim.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in:
  • 1.16
  • 1.17
Fixed in: (no value)



Download (untitled) / with headers
text/plain 2.3k
I believe I have tripped a DBD::Oracle bug in the way it binds utf8 parameters. If I create a statement and execute it with a non-utf8 parameter, it works. If I then execute that *same sth* with a utf8 parameter (scalar with UTF8 flag on), I receive the following error: ORA-01460: unimplemented or unreasonable conversion requested Example: use Encode qw(decode); my $sth = $dbh->prepare("select ? from dual") || die $dbh->errstr; my $non_utf8 = "X"; $sth->execute($non_utf8) || die $sth->errstr; my $utf8 = decode('utf8', $non_utf8); $sth->execute($utf8) || die $sth->errstr; # DIES The problem does *NOT* occur when: 1) the order is reversed (utf8 before non-utf8) or 2) the statement is prepared again before the second execute or 3) NLS_LANG does not indicate utf8 or 4) ora_csform is specified as SQLCS_NCHAR or SQLCS_IMPLICIT. I attached a script for the test suite to reproduce the problem. All of my DBI and DBD::Oracle tests passed. Perl: 5.8.3 DBI: 1.4.1 DBD::Oracle: 1.17 OS: Linux 2.6.5 Oracle server: 9.2.5 Oracle client: 9.2.4 Oracle database charset: US7ASCII Oracle nchar charset: AL16UTF16 NLS_LANG env var: AMERICAN_AMERICA.AL32UTF8 Here are the bits from DBI trace that looked relevant: First execution (non-utf8): -> execute for DBD::Oracle::st (DBI::st=HASH(0x84121f8)~0x8416818 'X') thr#814f008 bind :p1 <== 'X' (type 0) rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0) bind :p1 <== 'X' (size 1/2/0, ptype 4, otype 1) bind :p1 <== 'X' (size 1/1, otype 1, indp 0, at_exec 1) bind :p1 <== 'X' (in, not-utf8, csid 1->0->1, ftype 1, csform 0->0, maxlen 1, maxdata_size 0) Second execution (utf8): -> execute for DBD::Oracle::st (DBI::st=HASH(0x84121f8)~0x8416818 "X") thr#814f008 bind :p1 <== "X" (type 0) rebinding :p1 (is-utf8, ftype 1, csid 0, csform 0, inout 0) bind :p1 <== "X" (size 1/2/0, ptype 4, otype 1) bind :p1 <== 'X' (size 1/1, otype 1, indp 0, at_exec 1) rebinding :p1 with UTF8 value so setting csform=SQLCS_IMPLICIT bind :p1 <== "X" (in, is-utf8, csid 1->0->871, ftype 1, csform 0->2, maxlen 1, maxdata_size 0) dbd_st_execute SELECT (out0, lob0)... in ':p1' [0,0]: len 1, ind 0 OCIErrorGet after OCIStmtExecute (er1:ok): -1, 1460: ORA-01460: unimplemented or unreasonable conversion requested
Subject: rebind_nchar.t
Download rebind_nchar.t
text/x-perl 1.4k
#!perl -w # # ensure we can bind utf8-flagged scalars to the same parameters that # used to bind non-utf8s. # use strict; use Test::More; unshift(@INC,'t'); require 'nchar_test_lib.pl'; use DBD::Oracle qw(SQLCS_NCHAR SQLCS_IMPLICIT); $| = 1; my $dbh; SKIP: { plan skip_all => "Unable to run 8bit test, perl version is less than 5.6" unless ( $] >= 5.006 ); plan skip_all => "Oracle charset tests unreliable for Oracle 8 client" if ORA_OCI() < 9.0 and !$ENV{DBD_ALL_TESTS}; my $non_utf8 = 'X'; my $utf8; eval { require Encode; Encode->import(qw(is_utf8 decode)); $utf8 = decode('utf8',$non_utf8); die unless is_utf8($utf8); }; plan skip_all => "Unable to encode utf8" if $@; # force client charset to AL32UTF8 set_nls_nchar('AL32UTF8',1); $dbh = db_handle(); plan skip_all => "Not connected to oracle" if not $dbh; plan tests => 3; my $sql = "select ? from dual where 0 = 1"; my $sth = $dbh->prepare($sql); $sth->execute($non_utf8); # # If bug is present, this will cause: # ORA-01460: unimplemented or unreasonable conversion requested # ok($sth->execute($utf8), "utf8 after non-utf8"); $sth->bind_param(1, $utf8, { ora_csform => SQLCS_NCHAR }); ok($sth->execute, "utf8 after non-utf8 using SQLCS_NCHAR"); $sth->bind_param(1, $utf8, { ora_csform => SQLCS_IMPLICIT }); ok($sth->execute, "utf8 after non-utf8 using SQLCS_IMPLICIT"); } $dbh->disconnect;
From: JohnS
Download (untitled) / with headers
text/plain 296b
Trying to recreate this error but was unable to did discover a few things as this may ve the old issue related to DBI not being fully compatible with UTF8. Check out this link http://www.mhonarc.org/archive/html/perl-unicode/2003-12/msg00013.html I will still poke around with it though.
Download (untitled) / with headers
text/plain 462b
After updating to the newer version of DBI 1.50 I was still not able to recreate the erorro However the orginaly poster of the problem came up with a work around as follows ora_ph_csform to NCHAR which can be implimented either this way use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR ); ... $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR }); or this way $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders
From: Marcos.Marado [...] sonae.com
Download (untitled) / with headers
text/plain 746b
On Tue Mar 07 07:56:34 2006, PYTHIAN wrote: Show quoted text
> After updating to the newer version of DBI 1.50 I was still not able to > recreate the erorro
Well, I just stumbled upon the error, and google indicates we're not alone ;-) Show quoted text
> However the orginaly poster of the problem came up with a work around as > follows > > ora_ph_csform to NCHAR > > which can be implimented either this way > > use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR ); > ... > $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR }); > > or this way > > $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future > placeholders
Any plans to fix this on the next version of DBD::Oracle? Or do we have to stick with that workaround? Best regards, Marcos Marado
From: marcos.marado [...] sonae.com
Download (untitled) / with headers
text/plain 595b
On Tue Mar 07 07:56:34 2006, PYTHIAN wrote: Show quoted text
> However the orginaly poster of the problem came up with a work around as > follows > > ora_ph_csform to NCHAR > > which can be implimented either this way > > use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR ); > ... > $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR }); > > or this way > > $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future > placeholders
BTW, IMHO (or at least for my case) the best workaround is to utf8::downgrade($parameter, 1); for utf8 parameters that are being passed to DBD::Oracle...
From: John Scoles
Download (untitled) / with headers
text/plain 669b
Well I have spent some time looking at this. I have narrowed it down to a few things bu unfortunetly I have not been able to receate the problem as my test DB is not set up for unicode. Looking at the output of 21nchar I get this t/21nchar............... Database and client versions and character sets: Database 9.2.0.7.0 CHAR set is US7ASCII (Non-Unicode), NCHAR set is UTF8 (Unicode) Client 10.2.0.1 NLS_LANG is '.WE8ISO8859P15', NLS_NCHAR is '<unset>' you can see my system is siliar but not the same. can you re-run the make test and post the output of test 21nchar so I can recreate the exact enviornment when I create a new DB to recreate the problem.
On Fri Jun 23 14:03:49 2006, guest wrote: Show quoted text
> Well I have spent some time looking at this. I have narrowed it down > to a few things bu unfortunetly I have not been able to receate the > problem as my test DB is not set up for unicode. > > Looking at the output of 21nchar I get this > > t/21nchar............... Database and client versions and character > sets: > Database 9.2.0.7.0 CHAR set is US7ASCII (Non-Unicode), NCHAR set is > UTF8 (Unicode) > Client 10.2.0.1 NLS_LANG is '.WE8ISO8859P15', NLS_NCHAR is '<unset>' > > you can see my system is siliar but not the same. > > can you re-run the make test and post the output of test 21nchar > so I can recreate the exact enviornment when I create a new DB to > recreate the problem.
Sorry, I did not see your message till just now. Here's my 21nchar output: t/21nchar............... Database and client versions and character sets: Database 9.2.0.7.0 CHAR set is US7ASCII (Non-Unicode), NCHAR set is AL16UTF16 (Unicode) Client 9.2.0.4 NLS_LANG is '<unset>', NLS_NCHAR is '<unset>' Thanks, Philip
Download (untitled) / with headers
text/plain 302b
well Going to mark thi one as resolved as I think it is an Oracle bug of some sort seems when you have a 9.2.?.? client with either the NSL_LANGUAGE or NSL_CHAR not set one will get this error I will have to confim this as soon asI can figure out how to set NSL_LANGUAGE or NSL_Char for the client


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.