Re: Oracle and prepare_cached question

[Ian Harisay]

You have to cycle individually thru your account list.  The following 
should work fine.

my $sth = $conn->prepare_cached(q{SELECT DISTINCT ID, NAME from 
CUSTOMERS where ID in (?)});
my $recs = [];
foreach my $acct_id (701,705,751,754,749,755,756,757,758,780){
 $sth->execute($acct_id);
 while( my $rec = $sth->fetchrow_hashref('NAME_lc') ){
   push(@{$recs}, $rec);
 }
}

Now, this is a bit in efficient because of all the calls we end up 
making to the database.  It does work fine for small tables however.  
Another thing I have done in the past is dynamically build my sql 
statement.  Like so:

$acct_ids = [701,705,751,754,749,755,756,757,758,780];
my $sql = 'SELECT DISTINCT ID, NAME from CUSTOMERS where ID in (';
foreach (@{$acct_ids}){
 $sql .= '?,';
}
$sql =~ s/,$/)/;
my $sth = $conn->prepare_cached($sql,{},1);
$sth->execute(@{$acct_ids});
my $recs = [];
while(my $rec = $sth->fetchrow_hashref('NAME_lc') ){
 push(@{$recs}, $rec);
}
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:

#####################################################################
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.

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.