Passing array to oracle SP

[Zoom1234]

Hi,

Can someone tell me how to pass an array as input paramter to oracle in perl?
Thanks

 

[Zoom1234]

I googled it and found that bind_param_array() can be used for the purpose.
Here is my code

use DBI ;
sub spTest {
	my (@id) = (1,2,3,4) ;
	my %attr = () ;
	my $dbh = connectDB();
		
	my $SQL="DELETE FROM <tablename> WHERE id = ?";
	my $st = $dbh->prepare($SQL) ;
	#my $seq;
	$dbh->bind_param_array(1, \@id, \%attr);
	#$st->execute()   or debug("Couldn't execute statement: " . $st->errstr);	
	$st->execute_array(\%attr) ;	
}

but its giving me error 'Can't locate object method "bind_param_array" via package "DBI::db"'
Do i have to include specific module or something?

Pls help

 

[dmazzini]

It might be the case that you need to use For structure.

foreach $value (@array) {
my $SQL= DELETE FROM <tablename> WHERE id = $value";
my $sth = $dbh->prepare($MRSQL);
$sth->execute();
}

Oracle as well support IN function, if you know array size, you can use it easily:

my $SQL= DELETE FROM <tablename> WHERE (id in ('$array[0]','$array[1]','$array[2]'));

Cheers
dmazzini



dmazzini
GSM System and Telecomm Consultant