DBI inconsistency (ORA-01008)

thenaz has asked for the wisdom of the Perl Monks concerning the following question:

Here be a strange issue.

The code below works the first time I run it. Subsequent times produce the error message: "DBD::Oracle::st execute failed: ORA-01008: not all variables bound". I can then proceed to add whitespace somewhere in the query and run it again. It will work once and then consistently not work. (Unless I add whitespace somewhere, yet again, and recurse.)

use DBI;
use Data::Dumper;

our $dbh = DBI->connect("dbi:Oracle:host=bla;sid=bla;port=1521","bla",
+"bla",
  { AutoCommit => 1 });
die "Could not connect to database" unless $dbh;

our $q = 'SELECT field1, field2 FROM a_view WHERE param1 = 1 AND param
+2 = 2 AND foo_id = ? AND rownum < 5' ;

our $qp = $dbh->prepare($q);
$qp->bind_param(1, '152');
$qp->execute();

while (my $r = $qp->fetchrow_hashref) {
  print Dumper( $r );
}
[download]

Has anyone ever seen this type of behavior?

Comment on DBI inconsistency (ORA-01008) Download Code

Replies are listed 'Best First'.
Re: DBI inconsistency (ORA-01008) by Anonymous Monk on Aug 04, 2011 at 18:43 UTC
I never understood this use case for bind_param, why not skip it, and just give execute the argument? `->execute(152)`?	[reply] [d/l]
Re^2: DBI inconsistency (ORA-01008) by thenaz (Beadle) on Aug 04, 2011 at 18:45 UTC
Tried that. Same behavior.	[reply]
Re: DBI inconsistency (ORA-01008) by mje (Curate) on Aug 05, 2011 at 15:16 UTC
Is this really the code and what do you mean by "subsequent times produce error message". Do you really mean if you put your code in a file and run it from the command line it works once then never works again until you edit the code - I doubt it. Parameters once bound are sticky so there must be more to this than your code example shows.	[reply]
Re^2: DBI inconsistency (ORA-01008) by thenaz (Beadle) on Aug 07, 2011 at 03:11 UTC
Actually, that is what I mean. And (other than replacing the oracle host/sid/user/pass with "bla") that is the _exact_ script I'm running. I'm not sure if the Oracle server is maintaining some kind of state associated with the host I'm connecting from or what. But when I put that into a pl file, and invoke it from the shell, it works the first time, but fails with subsequent invocations. (Which is really strange since each invocation is a separate process, separate connection to the DB, etc.)	[reply]
Re: DBI inconsistency (ORA-01008) by graff (Chancellor) on Aug 07, 2011 at 03:46 UTC
Why are you using "our" for the database and statement handles? Have you tried using "my" instead, and does this give the same behavior?	[reply]
Re: DBI inconsistency (ORA-01008) by thenaz (Beadle) on Aug 08, 2011 at 13:54 UTC
Ok, I have a fix. Immediately after connecting, I run: `$dbh->do("ALTER session SET cursor_sharing = 'exact'") or die "Could not alter session\n";` [download] And viola! Works every time. Apparently cursor sharing does some evil stuff. Anyway, thanks for everybody's input!	[reply] [d/l]