Your first pattern is correct. In the second one you cannot use bind in an in statment like this as it connot correctly quote the params. Not sure if this is an DBI thing or DBD::Orale OCI thing though
"Anand . K . S ." <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hi, This problem has been killing me for a while Script 1 and script 2 (mentioned below) are very much the same. However I have hard coded the query in script 2 whereas constructed in script 1. Also script 1 is implement on an environment (which uses oracle version 9and DBI 1.38) and script 2 on another environment (which uses oracle version 10gand ) In script 1, the sql query is executed successfully, but in script 2 it fails in execute() step.. The bind variable has the following values: Tables => CHARGE NORMALISED_EVENT SUBTOTAL_RATING_VALUE SUBTOTAL_RATING_DELTA NORMALISED_EVENT_ERROR Hard coding bind variables goes trhough fine.. Is this a known bug or am I doing anything silly here.. Please could someone help me out? Script 1: ======== $lsql = "SELECT NVL2(partition_name, segment_name || ':' || partition_name, segment_name) FROM user_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND segment_name NOT IN ("; } # Create the necessary number of bind variable placeholders. $lsql .= '?,' x scalar(@ltables); # Remove the last ",". chop $lsql; # Order by BYTES if we are not splitting customer partitions out. $lsql .= ") ORDER BY bytes DESC"; } print ("\n SQL => $lsql"); # Execute the query and build an array of "other" table names. my @lotherTables; $lcsr = $zdb->prepare($lsql) || zDie("Could not prepare query at line " . __LINE__ . ": " . $zdb->errstr); $lcsr->execute(@ltables) || zDie("Could not execute query at line " . __LINE__ . ": " . $zdb->errstr); Script 2 ======= $lsql = "SELECT NVL2(partition_name, segment_name || ':' || partition_name, segment_name) FROM user_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND segment_name NOT IN (SELECT object_name FROM recyclebin bin) AND segment_name NOT IN (?,?,?,?,?) ORDER BY bytes DESC";#'CHARGE','NORMALISED_EVENT','SUBTOTAL_RATING_DELTA', #'NORMALISED_EVENT_ERROR') ORDER BY bytes DESC"; # Create the necessary number of bind variable placeholders. #$lsql .= '?,' x scalar(@ltables); # Remove the last ",". #chop $lsql; # Order by BYTES if we are not splitting customer partitions out. #if (!$opt_scp) { # $lsql .= ") ORDER BY bytes DESC"; #} #else { # $lsql .= ")"; #} print ("\n sql => $lsql"); # Execute the query and build an array of "other" table names. my @lotherTables; $lcsr = $zdb->prepare($lsql) || zDie("Could not prepare query at line " . __LINE__ . ": " . $zdb->errstr); print ("\nTables => @ltables\n"); $lcsr->execute(@ltables) || zDie("Could not execute query at line " . __LINE__ . ": " . $zdb->errstr); Thanks, Anand.
