Hi Alan,
Can you turn this script into a test using t/23wide_db_al32utf8.t
as a model. You could call it t/23wide_db_al32uft8_sp.t
Please send it to me (and Tim).
If you do, one of us can take a closer look. I think the problem,
however, is the we have no way to know, what the VARCHAR2 being returned
from your stored procedure could be. I don't recall that we have a UTF8
test that includes stored procedure parameter outputs, so either way
this might be useful.
Tim: I'll take an initial look at this if you want. My u8u8 database
instance will be perfect for testing this.
Lincoln
On Tue, 2004-12-21 at 17:12 -0800, Alan Seegmiller wrote:
> DBD::Oracle 1.16
> Oracle 10g (client 10.1.0.3)
> Red Hat Enterprise Linux ES release 3 (Taroon Update 3)
> Perl 5.8.6 (multithreaded disabled)
> NLS_LANG="AMERICAN_AMERICA.AL32UTF8";
> NLS_NCHAR="AL32UTF8"
>
> I have found on my system that when I execute a SQL select statement,
> that the strings returned have Perl's internal utf8 flag turned on, as
> expected.
>
> However, if the same data selected is done via a stored procedure, the
> strings returned have their internal utf8 flag turned off. What I can
> then do is use _utf8_on in the Encode module to turn the flag on, but
> of course I would rather not have to.
>
> Are there other users with similiar set-ups successfully returning
> utf8 strings from Oracle via stored procedures and there is a problem
> with my set-up? Or have I stumbled upon something else?
>
> An example of my test script is below.
>
> (I'm in the process of upgrading from Oracle 8i and DBD::Oracle 1.12)
>
> Thanks,
> Alan
>
> #!/usr/bin/perl
>
> use strict;
> use DBI;
> use DBD::Oracle;
> use Encode qw( encode_utf8 is_utf8 _utf8_on);
>
> $ENV{NLS_LANG}="AMERICAN_AMERICA.AL32UTF8";
> $ENV{NLS_NCHAR}="AL32UTF8";
>
> my $dbh = DBI->connect(
> "dbi:Oracle:host=10.33.80.10;sid=DBSID",
> "name",
> "pass",
> { AutoCommit => 1 ,RaiseError => 1 }
> );
>
> my $sth = $dbh->prepare("SELECT message FROM test WHERE id=1");
> $sth->execute();
> my $string = $sth->fetchrow_array;
>
> my $string_p;
> $sth = $dbh->prepare("BEGIN P_TEST.GET_MESSAGE(:1); END;");
> $sth->bind_param_inout(1, \$string_p, 100);
> $sth->execute();
>
> print "UTF Test on Select String: ".is_utf8($string)."\n";
> print "UTF Test on Procedure String: ".is_utf8($string_p)."\n\n";
>
> print "TURNING ON UTF8 FLAG\n";
> _utf8_on($string_p);
> print "UTF Test on Procedure String: ".is_utf8($string_p)."\n\n";
