ORA-12704: character set mismatch problem with DBD::Oracle

I'm trying to select from a table where the serial number apparently
looks like an octal number to Oracle and I haven't been able to figure
out how to convince it otherwise.

My last attempt looks like:
my $rc = $sth->bind_param(1,$serial,{ora_type=>ORA_STRING,
ora_csform=>SQLCS_NCHAR});
I get the error when I do the execute. $serial = 0800690C0A

If serial = abc or 123 it works fine. If I remove the leading zero it
works fine, which leads me to believe that something is converting that
into something else, like an octal number which obviously will fail.
I've tried ORA_CHAR andORA_VARCHAR.

This is and 8.1.7 Oracle installation on Linux.

Any suggestions?

Thanks,
Jim.

RE: ORA-12704: character set mismatch problem with DBD::Oracle

use warnings;
use strict;

$serial = 0800690COA; #is being converted to an octal number by perl
$serial = abc; # will give you an error with the above pragmas set.

Lincoln

-----Original Message-----
From: Jim Lynch [mailto:jwl@sgi.com]
Sent: Tuesday, January 25, 2005 2:27 PM
To: dbi-users@perl.org
Subject: ORA-12704: character set mismatch problem with DBD::Oracle


I'm trying to select from a table where the serial number apparently
looks like an octal number to Oracle and I haven't been able to figure
out how to convince it otherwise.

My last attempt looks like:
my $rc = $sth->bind_param(1,$serial,{ora_type=>ORA_STRING,
ora_csform=>SQLCS_NCHAR});
I get the error when I do the execute. $serial = 0800690C0A

If serial = abc or 123 it works fine. If I remove the leading zero it
works fine, which leads me to believe that something is converting that
into something else, like an octal number which obviously will fail.
I've tried ORA_CHAR andORA_VARCHAR.

This is and 8.1.7 Oracle installation on Linux.

Any suggestions?

Thanks,
Jim.

Re: ORA-12704: character set mismatch problem with DBD::Oracle

It's not being set that way. It is a variable that was loaded by a
select from another table. It's a string as far as perl is concerned
but in this case the DBD routines are screwing it up, I believe. My
mistake for not showing compilable code, what I meant to say was if I
set the variable $serial to the character string "abc" it works, but if
it is set to the character string "0800690C0A" it fails.

Jim.
Here's more of the code:

sub getSysInfo($)
{
my $serials = shift;
my $db = shift;

my $sql = qq {
select sp.serial_no ...
from table_site_part sp ...
where sp.serial_no = ?
....
};
foreach my $serials (@$serials) {
my $sth = $db->prepare($sql);
my $rc = $sth->bind_param(1,$serial,{ora_type=>ORA_STRING,
ora_csform=>SQLCS_NCHAR});
$sth->execute();
....

BAXTER, LINCOLN A wrote:

>use warnings;
>use strict;
>
>$serial = 0800690COA; #is being converted to an octal number by perl
>$serial = abc; # will give you an error with the above pragmas set.
>
>Lincoln
>
>-----Original Message-----
>From: Jim Lynch [mailto:jwl@sgi.com]
>Sent: Tuesday, January 25, 2005 2:27 PM
>To: dbi-users@perl.org
>Subject: ORA-12704: character set mismatch problem with DBD::Oracle
>
>
>I'm trying to select from a table where the serial number apparently
>looks like an octal number to Oracle and I haven't been able to figure
>out how to convince it otherwise.
>
>My last attempt looks like:
> my $rc = $sth->bind_param(1,$serial,{ora_type=>ORA_STRING,
>ora_csform=>SQLCS_NCHAR});
>I get the error when I do the execute. $serial = 0800690C0A
>
>If serial = abc or 123 it works fine. If I remove the leading zero it
>works fine, which leads me to believe that something is converting that
>into something else, like an octal number which obviously will fail.
>I've tried ORA_CHAR andORA_VARCHAR.
>
>This is and 8.1.7 Oracle installation on Linux.
>
>Any suggestions?
>
>Thanks,
>Jim.
>
>

Re: ORA-12704: character set mismatch problem with DBD::Oracle

--W13SgbpmD6bhZUTM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2005-01-25 14:43:54 -0500, Jim Lynch wrote:
> It's not being set that way. It is a variable that was loaded by a=20
> select from another table. It's a string as far as perl is concerned=20
> but in this case the DBD routines are screwing it up, I believe. My=20
> mistake for not showing compilable code, what I meant to say was if I=20
> set the variable $serial to the character string "abc" it works, but if=
=20
> it is set to the character string "0800690C0A" it fails.

I have a similar problem. However the difference between "it works" and
"it doesn't work" seems to be if the parameter is a perl UTF-8 string:

% export PERL_UNICODE=3DSAL
% /usr/local/bin/perl =3Ddumpsql 'select * from login where logname=3D?' hjp
DBD::Oracle::st execute failed: ORA-12704: character set mismatch (DBD ERRO=
R: error possibly near <*> indicator at char 37 in 'select * from login whe=
re logname=3D:p1<*>') [for Statement "select * from login where logname=3D?=
" with ParamValues: :p1=3D"hjp"] at /home/hjp/bin/dumpsql line 68.
ID LOGNAME PERSON MD5
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhap=
s you need to call execute first) [for Statement "select * from login where=
logname=3D?" with ParamValues: :p1=3D"hjp"] at /home/hjp/bin/dumpsql line =
88.

% export PERL_UNICODE=3DSL
% /usr/local/bin/perl =3Ddumpsql 'select * from login where logname=3D?' hjp
ID LOGNAME PERSON MD5
5731 hjp 5611
bernon:~/tmp 15:01 186%

("dumpsql" is a small perl script which does basically only connect,
prepare, execute, fetchrow_array. "A" in PERL_UNICODE means command line
arguments are UTF-8).

Note that the string "hjp" itself doesn't contain any non-ascii characters.

This happens with

perl, v5.8.6 built for i686-linux
DBI 1.48
DBD::Oracle 1.16
linked against Oracle 8.1.7, I believe.
The database is also Oracle 8.1.7. It seems to work if the database is
Oracle 9.2.

I cannot reproduce the problem with=20

perl, v5.8.3 built for i386-linux-thread-multi
DBI 1.46
DBD::Oracle 1.15

on the same machine.

hp

--=20
_ | Peter J. Holzer | In our modern say,learn,know in a day
|_|_) | Sysadmin WSR | world, perhaps being an expert is an
| | | hjp@wsr.ac.at | outdated concept.
__/ | http://www.hjp.at/ | -- Catharine Drozdowski on dbi-users.

--W13SgbpmD6bhZUTM
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQ2DTR1LjemazOuKpAQEsJwXTBQSUgnHf33TL9jW9i9odXnqNziGG j1Ks
mOWCWeUI3gFJDpLjPsg+eKl34pOml7+TMENiFv47n6/0i8MbBLm4pOT6wT5m aFXJ
fI7S4An0AtVFMrP5nPw10gNzdWFmKNYaal1/fR5o1eQJ7lGP/OSRrRlByenw uWgS
jV2Z2GLnGSNaszOplgHaQOme3rbjY/5PKH7batkJvvWeoLqstxsDT5uR9vB5 MoOP
XZfpaDHzLJHvBOCLSgwtJUA2tQ==
=ENTm
-----END PGP SIGNATURE-----

--W13SgbpmD6bhZUTM--