I guess I was thinking if I could just get the input variable to format correctly, by possibly modifying (or disabling) the LITERAL_PREFIX and LITERAL_SUFFIX in the type_info just for that instance, it would work...and I thought there might be a way to do this. Didnt realize it had to do with the Oracle level.
Cheers!
M. Addlework wrote:
--- 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
-- Barbara E. Lindsey, COG RDC Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162
----
CONFIDENTIALITY NOTICE: The information contained in this electronic message is legally privileged and confidential and intended only for the use of the individual(s) or entity(ies) named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this email or any of it's components is strictly prohibited. If you have received this email in error, please contact the sender.
----
