Front page | perl.dbi.users |
Postings from January 2007
Re: bind variable problem
Thread Previous
From:
Martin Hall
Date:
January 23, 2007 03:02
Subject:
Re: bind variable problem
Message ID:
45B5EB24.3090905@oracle.com
Bit difficult without an error message. Suggest you print out the $lsql
variable before executing and then try running the same string using
SQL*Plus or similar, see if you can get an error from it. Suspect a
syntax problem somewhere.
Martin
Anand.K.S. wrote:
> 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.
>
Thread Previous