Re: help with odd DBI perpare/execute errors

[Martin J. Evans]

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