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