Thanks for the info. I'll take a look.
Tim.
On Fri, Oct 17, 2003 at 08:36:09AM +1000, Steve Baldwin wrote:
> 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
