Re: What is the technique to supply a list of values to a where clause?

Larry W. Virden wrote:
> I am working on a quick tool to update some records in an oracle
> database.
> 
> The code is similar to some other tools I've cobbled together, but in
> this case, the where clause has a "column in list" clause.  The
> relevant pieces of code are:
> 
> my $aenlist = "(1127, 1557, 1661, 3030, 3982, 5205, 453, 8838,
> 9338)";
> my $tmpsuper1 = 406;
> 
> 
>     $updateSuper = $oraProdDBH->prepare
>     (
>         "UPDATE HR_INFO SET " .
>             "SUPERVISOR_EMP_NO=? , REC_MOD_DATE=SYSDATE " .
>         "WHERE " .
>             "ALT_EMP_NO IN ? AND SUPERVISOR_EMP_NO = 0"
>     ) or die "Failed to prepare - $DBI::errstr\n";
>     $urc = $updateSuper->execute($tmpsuper1, $aenlist) or die "Failed
> to execute - $DBI::errstr\n";
> 
> When I try to run the code, DBI returns an error saying that the value
> supplied by $aenlist isn't a number. And that's true - it is a string.
> 
> So, I tried setting $aenlist to (1127, 1557, 1661, 3030, 3982, 5205,
> 453, 8838, 9338); and perl tells me there's a useless constant. That's
> when I thought that what I really need to do was use @aenlist. Alas,
> when I changed the execute to say
>  my @aenlist = (1127, 1557, 1661, 3030, 3982, 5205, 453, 8838, 9338);
> :
> :
>  my $urc = $updateSuper->execute($tmpsuper1, @aenlist) or die "Failed
> to execute - $DBI::errstr\n"
> 
> ; now I get a DBD::Oracle::st execute failed error that I called
> execute with 10 variables when 2 were needed.
> 
> So, I am hoping someone might be able to point me to some information
> about how to encode the execute so that I pass a list to the where
> clause.
> 
> Thank you so much for your help.
> 
> 

I'm presuming you have one SQL statement to do updates and a list of
values for your where clause. You cannot do what you've tried above
because DBI sees 2 parameters in your SQL not the 10 you've passed. You
need to do:

1. Change your SQL to construct a where clause using "in"

e.g.,
$sql = q/update HR_INFO SET SUPERVISOR_EMP_NO=? , REC_MOD_DATE=SYSDATE
WHERE SUPERVISOR_EMP_NO = 0 AND ALT_EMP_NO IN (/ . join(',', @aenlist) .
')';

Personally I don't like this and some database engines have a limit on
how many items they can handle in the "in" clause.

2.

prepare(q/update HR_INFO SET SUPERVISOR_EMP_NO=? , REC_MOD_DATE=SYSDATE
WHERE ALT_EMP_NO IN ? AND SUPERVISOR_EMP_NO = 0/);

foreach (@aenlist) {
  execute($tmpsuper1, $_);
}

3. use execute_array - see the DBI docs. This would probably be my
preferred method but it is a little more complicated to use than 2 above
especially if you need to know exactly which updates had an effect.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com