Oracle 10g, Stored Procedures and Perl's Internal UTF8 Flag

From: Alan Seegmiller (aseegmiller_at_gmail.com)
Date: 12/22/04 

Date: Tue, 21 Dec 2004 17:12:10 -0800
To: dbi-users@perl.org

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";

Relevant Pages

  • Re: Error in calling stored procedure via DB link
    ... "Paul Clement" wrote: ... Below is an Oracle KB article that documents the issue. ... Calling Stored Procedure over Dblink Using Oracle OleDb Provider Fails ... > symptom: Using command type adCmdStoredProc ...
    (microsoft.public.vb.database.ado)
  • Re: Performance problems with StoredProcedure in Web application
    ... you could probably just ignore my comments about stored procedure performance as compared to textual queries because they ... As for your question about how to avoid using a cursor (as I suggested was possible in Oracle) read the following paragraph for more ... The .NET Framework Data Provider for Oracle does not support batched SQL statements. ... CURSOR output parameters to fill a DataSet, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Passing Back Table Information from Oracle .NET Stored Procedures
    ... stored procedure, not in the typical PL/SQL sense. ... I don't care if it's MS SQL in-line code or Oracle in-line code, MS SQL Server, Oracle Proc, or this so called .NET Stored Procedure using a Select statement, they are all returning a RESULT SET whether it be one row or multiple rows. ... I used SqlContext above because I don't want to bother registering at the Oracle site to download their Oracle Database Extensions for .NET in order to get at the presumably extant OracleContext class. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: EXEC syntax for SP which returns row set
    ... >>> Oracle experts, none of whom seem to know. ... >> SQL> create or replace package returncur is ... > I don't know why you are supplying the above example. ... to illustrate HOW you'd call a stored procedure and return ...
    (comp.databases.oracle.server)
  • Re: inserting XML Document into Oracle database using a stored procedure
    ... What i am trying to do is i want to insert a xml file into the Oracle database using a stored procedure which takes a XmlType type parameter. ...
    (perl.dbi.users)