On 03/06/15 14:38, William Bulley wrote:
According to "Martin J. Evans" <boh...@ntlworld.com> 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
