Re: help with odd DBI perpare/execute errors

On 03/06/15 14:06, William Bulley wrote:
> Environment Perl script trying to query Oracle 11g database:
>
>     FreeBSD 9.3-STABLE
>
>     DBI 1.633
>
>     oracle8-client 0.2.0
>
>     DBD::Oracle 1.19
>
> I have no trouble connecting with the Oracle database.  And I do
> recover data when I use the temporary workaround described below.
>
> I have a query/prepare setup outside a foreach loop where I execute()
> the prepared query something like this, only more complex:
>
>     my $query = "select column from table where column = ?";
>
>     my $sth = $dbh->prepare ($query);
>
>     foreach ()
>     {
> 	$sth->execute($value);
>     }
>
> I was getting invalid string ORA-0911 errors at the question mark.
> I then replaced the question mark with a number (555) and made the
> execute() call just "$sth->execute();"

So, when this fails, what is the value of $value. Assuming you have RaiseError set, you can just put an eval around the execute and if $@ is set, print out $value.

> This worked.  But I really needed to bind to the $value variable
> in the foreach loop.
>
> In reading the DBI POD it said for Oracle the "?" is turned into
> ":p1" (in this case).  So I replaced the question mark with :p1.

Strictly speaking you do not need to do this - ? is fine. DBD::Oracle also supports named parameters e.g., ':myparam' which only has an advantage if you want to use :myparam more than once in the SQL.
  
> The prepare statement no longer generated an error, instead the
> execute statement generated the error:
>
>     DBD::Oracle::st execute failed: called with 1 bind variables
>     when 0 are needed [for Statement ... ] at script.pl line xxx.

Either because you omitted the value from execute but more likely because you need to associate $value with the NAMED parameter i.e., call bind_param.
  
> Can any one help me figure out this confusing situation?  BTW, I
> have been using Perl for twenty years and DBI for perhaps ten,
> and I have used this query/prepare/bind/execute methodology in
> the past with success.  Something is different, but I don't know
> what to look for.
>
> Regards,
>
> web...
>

Tell us the column type of 'column' and the value of $value when the original code fails. If this does not enlighten you, reduce this to a small reproducible script and re-run with ora_verbose set to 7. Paste the output somewhere we can view it.

Martin