Re: bind variable problem
- From: scoles@xxxxxxxxxxx (John Scoles)
- Date: Tue, 23 Jan 2007 06:27:07 -0500
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 ." <ks.anand80@xxxxxxxxx> wrote in message
news:9b02676d0701221656p52e2829fpce30cc0757b950e6@xxxxxxxxxxxxxxxxx
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.
.
- Follow-Ups:
- RE: bind variable problem
- From: Ronald Kimball
- RE: bind variable problem
- References:
- bind variable problem
- From: Anand . K . S .
- bind variable problem
- Prev by Date: Re: bind variable problem
- Next by Date: most drivers share error variable for sth/dbh handles?
- Previous by thread: Re: bind variable problem
- Next by thread: RE: bind variable problem
- Index(es):
Relevant Pages
|
