--- Barbara Lindsey <[EMAIL PROTECTED]> 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:
>
> #####################################################################
> my $query = $conn->prepare_cached(q{SELECT DISTINCT ID, NAME from
> CUSTOMERS where ID=?});
> $query->execute($account);
> #####################################################################
>
> The above code (one ID at a time) works fine.
>
> 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.
It's not really the same query in SQLPlus, because in SQLPlus you are
using *substitution* variables, not bind variables. A substitution
variable is a variable that can contain any arbitrary chunk of
text, and the replacement is (must be) done *before* the SQL is parsed.
For example, you could do this:
SQL> define HALF_MY_TABLE_NAME=bar
SQL> create table fu&HALF_MY_TABLE_NAME (n number);
and you've created the table FUBAR.
Obviously the SQL cannot be parsed before the statement is executed,
because the SQL makes no sense before variable substitution.
Bind variables, on the other hand, are variables for which the
substitution is performed *after* the SQL statement is parsed,
and bind variables can only be placeholders for scalar values in
where clauses.
So what you were trying to do in DBI is actually more analagous to this
in SQLPlus (i.e using SQLPlus bind variables):
SQL> var clause varchar2(100);
SQL> exec :clause := '1,2,3,4';
SQL> select * from TABLE where COLUMN in (:clause);
Can't work...
Looked at the other way, what you are able to do in SQLPlus is
precisely analagous to this in DBI:
$account_list = "701,705,751,754,749,755,756,757,758,780";
$sql = "select * from TABLE where COLUMN in ($account_list)";
$query = $dbh->prepare($sql);
In other words, you are fully constructing the SQL before you
parse and execute it. SQLPlus was doing that too -- you just
didn't realize it.
If you want the benefit of prepare'ing in advance, you *must*
have different SQL statements for each select with a different
number of items in the IN() clause.
So you can do something like this:
@accounts = (701,705,751,754,749,755,756,757,758,780);
$sql = "select * from TABLE where COLUMN in (";
for (0..$#accounts) {
$sql .= "?,";
}
chop $sql;
$sql .= ")";
$dbh->prepare($sql);
(There are other more "elegant" but obscure ways to construct the sql)
There is a chapter in the SQLPlus manual that further explains the
difference betwen bind variables and substitution variables.
Mark
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
