Re: help with odd DBI perpare/execute errors

On 03/06/15 14:38, William Bulley wrote:
> According to "Martin J. Evans" <[email protected]> on Wed, 06/03/15 at 09:15:
>>
>> So, when this fails, what is the value of $value.
>
> I just ran it again.  The value is 547.

Sounds ok but the error is "invalid string"

ORA-0911
You tried to execute a SQL statement that included a special character.

http://www.techonthenet.com/oracle/errors/ora00911.php
lists various causes.


>> Assuming you have RaiseError set, you can just put an eval
>> around the execute and if $@ is set, print out $value.
>
> I don't.  I have a print statement in front of the execute to
> show my what I am passing to the execute() method.

I'm not sure I'd trust that - doesn't that mean you are expecting stdin and stout to be in order. If you can easily do it I would stick an eval around it and trap it that way. Also, if you trap it you can print the SQL using
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#Statement
and the parameters using
http://search.cpan.org/~timb/DBI-1.633/DBI.pm#ParamValues

>> 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 query is quite complex -- two SELECT statements connected
> by a UNION statement -- and the "column = ?" syntax is used
> twice.  I changed the "?" to ":myparam" in both places and I
> still get the error:
>
>     DBD::Oracle::st execute failed: called with 1 bind
>     variables when 0 are needed [for Statement...
>
>> Either because you omitted the value from execute
>
> In this case, it was not omitted.

I would not bother changing from ? to named - I seriously doubt this is the issue.

>> but more likely because you need to associate $value
>> with the NAMED parameter i.e., call bind_param.
>
> That will be my next test, but I don't hold out much hope for
> that working either (I've never had to do this in the past).
>
>> 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.
>
> The Oracle type for the column in question is NUMBER.  I assumed
> that any integer value would be compatible.  The value is 547 for
> the run that just failed.
>
> Never heard of ora_verbose -- where is this to be set?  Just in
> my code somewhere, or on the DBI->connect() method, or where?

http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#ora_verbose

Can be set in the connect attributes.

> Regards,
>
> web...
>

If I were you I'd try and simply the original case down as much as possible but getting a trace with ora_verbose might help identify the problem.

Martin