I'm not sure whether this is a DBD::Oracle bug or a bug in the Oracle
client libs, but it only seems to manifest in Perl scripts running on
machines with Oracle client 9.2. The problem is when you have bind
variables that are being bound using bind_param_inout, but those
variables never get values assigned to them, they get filled with
garbage.
Here is a simple script that exhibits the problem ...
#!/usr/bin/perl -w
use strict ;
use DBI ;
my $dbh = DBI->connect('dbi:Oracle:', $ENV{'ORACLE_USER'}, '') ;
my $sth = $dbh->prepare (q(
BEGIN
-- :p1 := :p1 ;
-- :p2 := :p2 ;
IF :p2 != :p3 THEN
:p1 := 'AAA' ;
:p2 := 'Z' ;
END IF ;
END ;
)) ;
my ($p1, $p2, $p3) = ('Hello', 'Y', 'Y') ;
$sth->bind_param_inout(':p1', \$p1, 30) ;
$sth->bind_param_inout(':p2', \$p2, 1) ;
$sth->bind_param_inout(':p3', \$p3, 1) ;
print "Before p1=[$p1] p2=[$p2] p3=[$p3]\n" ;
$sth->execute ;
print "After p1=[$p1] p2=[$p2] p3=[$p3]\n" ;
END {
if (defined $dbh) {
$dbh->disconnect ;
}
}
Here's an example of running it :
[perldev]$ perl sbtest.pl
Before p1=[Hello] p2=[Y] p3=[Y]
After p1=[HelloPÛ o·W<a0t%or&] [EMAIL PROTECTED]&oPÛ o·S<aPv$oà]
p3=[Y]
As you can imagine, the behaviour of the script after this is somewhat
unpredictable. It can play havoc with Apache (and took some tracking
down let me tell you !!).
Fortunately there is a relatively simple workaround. As you can see in
my script above, there are two lines commented out in the anonymous
PL/SQL block. If I uncomment them, everything works just fine. So, if
at the start of the anonymous block you assign each bind variable to
itself, you will never see the problem.
Note that I have only been able to reproduce this problem under the
following circumstances :
You are executing an anonymous PL/SQL block
Your Oracle client version is 9.2
Bind variable(s) are being bound with bind_param_inout
Logic in the PL/SQL block prevents the bind variable from being assigned
a value
There may be other circumstances under which this occurs, but this is as
far as I could test.
Hope this helps someone.
Steve
