Barbara Lindsey wrote:
> I have used prepare_cached successfully with Oracle 9i when executing
> with a single value on the execute statement, but now instead of
> using a query like this:
>
[snip]
> I would like to do this instead:
>
> #####################################################################
> my $query = $conn->prepare_cached(q{SELECT DISTINCT ID, NAME from
> CUSTOMERS where ID in (?)});
>
> $account_list = "701,705,751,754,749,755,756,757,758,780";
> $query->execute($account_list);
> #####################################################################
>
> When I try to do this, I get this error:
>
> *************************************************************************
> DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR:
> OCIStmtExecute) at CustomerSupport.pm line 229.
> CustomerSupport::getCustomersTEST('CustmerSupport=HASH(0x82fec14)',
> '701,705,751,754,749,755,756,757,758,7...') called at
> /path/customers/test.cgi line 136
> DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR:
> OCIStmtExecute) at CustomerSupport.pm line 229.
>
> *************************************************************************
>
> The same query executes fine from the command line in SQLPlus, when I
> expand the account string into it, so I think there is something going
> on in the Perl part.
>
> Is there a way to use prepare_cached in this way?
> Do I need to format the $account_list string in a different way to
> make this work?
>
> Any help appreciated.
You can't do this; you're binding a single string to the single bind
variable in the statement. Because it's a string with commas in, it's not a
NUMBER datatype, so you get the error.
It works in SQL*Plus because you're putting all the values in the literal
SQL statement.
This URL explains it, and a workaround if you want to go down that path:
http://asktom.oracle.com/pls/ask/f?p=4950:8:7244017498582289126::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061,
--
Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
