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.
