DBD::Oracle error when switching between utf8 and non-utf8

Hi list.

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 =3D $dbh->prepare("select ? from dual") || die $dbh->errstr;
=20
my $non_utf8 =3D "X";
$sth->execute($non_utf8) || die $sth->errstr;

my $utf8 =3D 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 made a script for the test suite to reproduce the problem. You can
get it from
http://www.pgarrett.net/rebind_nchar.t

Is this the appropriate forum for this kind of thing? Should I post
elsewhere also?

Thanks much,
Philip


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=3DHASH(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=3DHASH(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)=20
bind :p1 <== 'X' (size 1/1, otype 1, indp 0, at_exec 1)
rebinding :p1 with UTF8 value so setting csform=3DSQLCS_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

RE: DBD::Oracle error when switching between utf8 and non-utf8

> -----Original Message-----
> From: Garrett, Philip (MAN-Corporate)
[mailto:Philip.Garrett@manheim.com]=20
> Sent: Friday, March 03, 2006 1:50 PM
> To: dbi-users@perl.org
> Subject: DBD::Oracle error when switching between utf8 and non-utf8
>=20
[snip]
>
> DBI: 1.4.1

Probably obvious, but I meant 1.41.

Philip

Re: DBD::Oracle error when switching between utf8 and non-utf8

Interesting I will see if I can recreate it. You can also report this bug
here

http://rt.cpan.org/Public/Dist/Display.html?Status=Active&Na me=DBD-Oracle

and we can track it.

Cheers
John Scoles
""Garrett, Philip (MAN-Corporate)"" wrote in
message news:D9C13100F14E4C4795A1E83B125B4035D6CCEC@MSCEXCHS02.man.c o...
> -----Original Message-----
> From: Garrett, Philip (MAN-Corporate)
[mailto:Philip.Garrett@manheim.com]
> Sent: Friday, March 03, 2006 1:50 PM
> To: dbi-users@perl.org
> Subject: DBD::Oracle error when switching between utf8 and non-utf8
>
[snip]
>
> DBI: 1.4.1

Probably obvious, but I meant 1.41.

Philip

Re: DBD::Oracle error when switching between utf8 and non-utf8

Well I just spent an hour or so trying to recreate it. Importunely (or
fortunately) I was not able to recreate it.

here is the test result

"t/rebind_nchar..........ok"

I am running

Redhat 2.3
Linux 2.4
Perl 5.8
DBI 1.48
Oracle Inastantclient Linux32 10.2.0.1

I will also check it on windows later today.

The only thing that I noticed was that I have

export NLS_LANG=.WE8ISO8859P15

set in my profile.



""John Scoles"" wrote in message
news:20060306121448.24890.qmail@lists.develooper.com...
> Interesting I will see if I can recreate it. You can also report this bug
> here
>
> http://rt.cpan.org/Public/Dist/Display.html?Status=Active&Na me=DBD-Oracle
>
> and we can track it.
>
> Cheers
> John Scoles
> ""Garrett, Philip (MAN-Corporate)"" wrote in
> message news:D9C13100F14E4C4795A1E83B125B4035D6CCEC@MSCEXCHS02.man.c o...
> > -----Original Message-----
> > From: Garrett, Philip (MAN-Corporate)
> [mailto:Philip.Garrett@manheim.com]
> > Sent: Friday, March 03, 2006 1:50 PM
> > To: dbi-users@perl.org
> > Subject: DBD::Oracle error when switching between utf8 and non-utf8
> >
> [snip]
> >
> > DBI: 1.4.1
>
> Probably obvious, but I meant 1.41.
>
> Philip
>
>

Re: DBD::Oracle error when switching between utf8 and non-utf8

Opps Forgot to add

DBD:Oracle 1.17


""John Scoles"" wrote in message
news:20060306121448.24890.qmail@lists.develooper.com...
> Interesting I will see if I can recreate it. You can also report this bug
> here
>
> http://rt.cpan.org/Public/Dist/Display.html?Status=Active&Na me=DBD-Oracle
>
> and we can track it.
>
> Cheers
> John Scoles
> ""Garrett, Philip (MAN-Corporate)"" wrote in
> message news:D9C13100F14E4C4795A1E83B125B4035D6CCEC@MSCEXCHS02.man.c o...
> > -----Original Message-----
> > From: Garrett, Philip (MAN-Corporate)
> [mailto:Philip.Garrett@manheim.com]
> > Sent: Friday, March 03, 2006 1:50 PM
> > To: dbi-users@perl.org
> > Subject: DBD::Oracle error when switching between utf8 and non-utf8
> >
> [snip]
> >
> > DBI: 1.4.1
>
> Probably obvious, but I meant 1.41.
>
> Philip
>
>

Re: DBD::Oracle error when switching between utf8 and non-utf8

Just ran the same test in the windows environment and the test did fail as
follows

t\rebind_nchar..........skipped
all skipped: Unable to encode utf8

I will have to look into it further in windows anyway,

cheers

""John Scoles"" wrote in message
news:20060306133741.10369.qmail@lists.develooper.com...
> Opps Forgot to add
>
> DBD:Oracle 1.17
>
>
> ""John Scoles"" wrote in message
> news:20060306121448.24890.qmail@lists.develooper.com...
> > Interesting I will see if I can recreate it. You can also report this
bug
> > here
> >
> >
http://rt.cpan.org/Public/Dist/Display.html?Status=Active&Na me=DBD-Oracle
> >
> > and we can track it.
> >
> > Cheers
> > John Scoles
> > ""Garrett, Philip (MAN-Corporate)"" wrote
in
> > message news:D9C13100F14E4C4795A1E83B125B4035D6CCEC@MSCEXCHS02.man.c o...
> > > -----Original Message-----
> > > From: Garrett, Philip (MAN-Corporate)
> > [mailto:Philip.Garrett@manheim.com]
> > > Sent: Friday, March 03, 2006 1:50 PM
> > > To: dbi-users@perl.org
> > > Subject: DBD::Oracle error when switching between utf8 and non-utf8
> > >
> > [snip]
> > >
> > > DBI: 1.4.1
> >
> > Probably obvious, but I meant 1.41.
> >
> > Philip
> >
> >
>
>

RE: DBD::Oracle error when switching between utf8 and non-utf8

> -----Original Message-----
> From: John Scoles [mailto:scoles@pythian.com]=20
> Sent: Monday, March 06, 2006 8:54 AM
> To: dbi-users@perl.org
> Subject: Re: DBD::Oracle error when switching between utf8 and
non-utf8
>=20
> Just ran the same test in the windows environment and the test did
fail as follows
>=20
> t\rebind_nchar..........skipped
> all skipped: Unable to encode utf8
>=20
> I will have to look into it further in windows anyway,


Unfortunately, that's probably just my inexperience with the encoding
modules showing through. There may be a way to make it work in Windows,
but I don't know off the top of my head what it is.

Philip

RE: DBD::Oracle error when switching between utf8 and non-utf8

> -----Original Message-----
> From: John Scoles [mailto:scoles@pythian.com]=20
> Sent: Monday, March 06, 2006 8:37 AM
> To: dbi-users@perl.org
> Subject: Re: DBD::Oracle error when switching between utf8 and
non-utf8
>=20
> Well I just spent an hour or so trying to recreate it. Importunely (or
> fortunately) I was not able to recreate it.

That's surprising and distressing. =20

>=20
> here is the test result
>=20
> "t/rebind_nchar..........ok"
>=20
> I am running
>=20
> Redhat 2.3
> Linux 2.4

FWIW, I'm running SuSE Enterprise 9.

> Perl 5.8
> DBI 1.48
> Oracle Inastantclient Linux32 10.2.0.1
>=20
> I will also check it on windows later today.
>=20
> The only thing that I noticed was that I have
>=20
> export NLS_LANG=3D.WE8ISO8859P15
>=20
> set in my profile.

What is the database characterset for your test database? Just curious
if it's
AL32UTF8.


> ""John Scoles"" wrote in message
news:20060306121448.24890.qmail@lists.develooper.com...
> > Interesting I will see if I can recreate it. You can also report
this=20
> > bug here
> >
> >
http://rt.cpan.org/Public/Dist/Display.html?Status=3DActive& Name=3DDBD-Or=
a
> > cle
> >
> > and we can track it.

Done.

Thanks,
Philip

Re: DBD::Oracle error when switching between utf8 and non-utf8

Well find a few things

The windows error comes from the fact that I did not have Encode.pm
installed so I fixed that and it runs.

It also looks like this may be 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/msg 00013.html

I will still poke around with it though.

By the way my database is set up for USASCII

Cheers


""Garrett, Philip (MAN-Corporate)"" wrote in
message news:D9C13100F14E4C4795A1E83B125B4035D6CCEF@MSCEXCHS02.man.c o...
> -----Original Message-----
> From: John Scoles [mailto:scoles@pythian.com]
> Sent: Monday, March 06, 2006 8:37 AM
> To: dbi-users@perl.org
> Subject: Re: DBD::Oracle error when switching between utf8 and
non-utf8
>
> Well I just spent an hour or so trying to recreate it. Importunely (or
> fortunately) I was not able to recreate it.

That's surprising and distressing.

>
> here is the test result
>
> "t/rebind_nchar..........ok"
>
> I am running
>
> Redhat 2.3
> Linux 2.4

FWIW, I'm running SuSE Enterprise 9.

> Perl 5.8
> DBI 1.48
> Oracle Inastantclient Linux32 10.2.0.1
>
> I will also check it on windows later today.
>
> The only thing that I noticed was that I have
>
> export NLS_LANG=.WE8ISO8859P15
>
> set in my profile.

What is the database characterset for your test database? Just curious
if it's
AL32UTF8.


> ""John Scoles"" wrote in message
news:20060306121448.24890.qmail@lists.develooper.com...
> > Interesting I will see if I can recreate it. You can also report
this
> > bug here
> >
> >
http://rt.cpan.org/Public/Dist/Display.html?Status=Active&Na me=DBD-Ora
> > cle
> >
> > and we can track it.

Done.

Thanks,
Philip

RE: DBD::Oracle error when switching between utf8 and non-utf8

> -----Original Message-----
> From: John Scoles [mailto:scoles@pythian.com]=20
> Sent: Monday, March 06, 2006 11:29 AM
> To: dbi-users@perl.org
> Subject: Re: DBD::Oracle error when switching between utf8 and
non-utf8
>=20
> Well find a few things
>=20
> The windows error comes from the fact that I did not have Encode.pm
> installed so I fixed that and it runs.
>=20
> It also looks like this may be the old issue related to DBI not being
> fully compatible with UTF8 .
>=20
> Check out this link
>=20
> http://www.mhonarc.org/archive/html/perl-unicode/2003-12/msg 00013.html

Perhaps. I'd be surprised if that was the case, though, because that
deals
with variables that DBI/DBD *create*.

This might help. This is what DBI->trace says when I bind the utf8
before ever
having bound a non-utf8:

rebinding :p1 with UTF8 value so setting csform=3DSQLCS_IMPLICIT
bind :p1 <== "X" (in, is-utf8, csid 1->0->871, ftype 1,
csform 0->2, maxlen 1, maxdata_size 0)

But when I've bound a non-utf8 to that statement before, this is what I
get
when binding utf8:

rebinding :p1 with UTF8 value so setting csform=3DSQLCS_IMPLICIT
bind :p1 <== "X" (in, is-utf8, csid 873->0->873, ftype 1,
csform 0->2, maxlen 1, maxdata_size 0)
=20
I don't really know anything about OCI, so I can't interpret the
difference
between 1->0->871 and 873->0->873. Does that mean anything to you?

Thanks,
Philip