| Subject: | Errors returned by procedures are not unicode strings |
Hi,
This probably applies to all errors but my example is from a procedure.
We have a procedure which calls RAISE_APPLICATION_ERROR with a unicode
string but when the error is retrieved from DBD::Oracle, the string is
valid UTF-8 but not marked as such. This means when it is printed to a
file handle with utf-8 encoding it is double encoded (unlike unicode
data retrieved from a result-set due to a select).
I've not looked into DBD::Oracle code as yet but this suggests to me
data retrieved from selects is marked utf-8 but errors are not. Here is
a small self contained example (with output):
#!/usr/bin/perl
use warnings;
use strict;
use Encode;
use DBI;
# Note: NLS_LANG=AMERICAN_AMERICA.AL32UTF8
binmode(STDOUT, ":utf8");
my $dbh = DBI->connect('dbi:Oracle:host=xxx.easysoft.local;sid=devel',
'xxx',
'xxx');
eval {$dbh->do(q/drop procedure p_martin/)};
my $proc = <<'EOT';
create procedure p_martin(arg IN VARCHAR2) IS
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Error ' || arg);
END;
EOT
$dbh->do($proc);
$dbh->{RaiseError} = 0;
$dbh->{PrintError} = 0;
my $arg = "\x{20ac}";
my $sth = $dbh->prepare(q{BEGIN p_martin(?); END;});
$sth->bind_param(1, $arg);
if (!$sth->execute) {
my $error = $dbh->errstr;
print "Is utf8: " .
(Encode::is_utf8($error, Encode::FB_CROAK) ? 'yes' : 'no') . "\n";
print "error: $error, \n";
print "Should be $arg\n";
my $utf8 = Encode::decode_utf8($error);
print "Error decoded: $utf8\n";
}
which outputs:
$ perl dbd_oracle_errors_not_unicode_example.pl
Is utf8: no
error: ORA-20001: Error â¬
ORA-06512: at "BET.P_MARTIN", line 3
ORA-06512: at line 1 (DBD ERROR: OCIStmtExecute),
Should be €
Error decoded: ORA-20001: Error €
ORA-06512: at "BET.P_MARTIN", line 3
ORA-06512: at line 1 (DBD ERROR: OCIStmtExecute)
I would have expected "Error €" but the euro was double utf-8 encoded as
it was not marked as utf-8 internally in perl.
Martin
--
Martin J. Evans
Wetherby, UK