bind variable problem

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.

• bind variable problem by Anand . K . S .
• Re: bind variable problem by John Scoles
• RE: bind variable problem by Ronald Kimball
• Re: bind variable problem by Martin Evans
• RE: bind variable problem by Ronald Kimball
• Re: bind variable problem by Martin Evans
• Re: bind variable problem by Martin Hall