| Subject: | Problem with CHAR-Out-Parameter and 10g |
Perl: 5.8.8
Oracle: 10g Release 2 (10.2.0.3.0)
Oracle-Client: 10g Client Release 2 (10.2.0.1.0)
DBI: 1.58
DBD::Oracle: 1.19
When i want to get the value of an out-parameter from a procedure with
bind_param_inout directly and the out-parameter is of type char, the
value isn't returned correctly with Oracle10g. The same script worked
with Oracle8i.
create these procedures on your database:
----------------------------------------------------------
create or replace procedure char_test(char_value out char)
is
begin
char_value := '1';
end;
/
create or replace procedure varchar2_test(varchar2_value out varchar2)
is
begin
varchar2_value := '1';
end;
/
----------------------------------------------------------
then use the attached script, and you'll see the problem
| Subject: | chartest.pl |
#!/usr/local/bin/perl
use DBI;
my $dbname = 'first10g';
my $dbuser = 'tbo';
my $dbpass = 'tbo';
my $query = q{declare
char_value char(1);
varchar2_value varchar2(1);
begin
char_test(:a);
char_test(char_value);
:b := char_value;
varchar2_test(:c);
varchar2_test(varchar2_value);
:d := varchar2_value;
end;};
my $dbh = DBI->connect('DBI:Oracle:'.$dbname, $dbuser, $dbpass, {AutoCommit => 0, RaiseError => 0});
my $sth = $dbh->prepare($query);
my $a;
my $b;
my $c;
my $d;
$sth->bind_param_inout(':a', \$a, 1);
$sth->bind_param_inout(':b', \$b, 1);
$sth->bind_param_inout(':c', \$c, 1);
$sth->bind_param_inout(':d', \$d, 1);
$sth->execute();
print "a: \"$a\"\n";
print "b: \"$b\"\n";
print "c: \"$c\"\n";
print "d: \"$d\"\n";
$dbh->commit();
exit 0;