Create a global temporary table and insert the values used in thewhere clause into it. Join this table to the main table in your query.
----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Ravi Kongara [mailto:[EMAIL PROTECTED] Sent: Friday, October 22, 2004 1:32 PM To: DBI-Users Subject: binding multiple values and retain blanks Hi, I'm trying to run query like this: ........................... use DBI; use strict; $dbh = DBI->connect( "dbi:Oracle:$host_db", $host_user, $host_passwd, { AutoCommit => 1, RaiseError => 1, PrintError => 0 } ); $sql = "select col1,col2,col3,col4,col5,col6 from tab1 where (col1,col2,col3) in ( (?,?,?), (?,?,?), (?,?,?), (?,?,?) ...500 times )"; $sth = $dbh->prepare( $sql ); while loop { my @bind_values = ( $val1, $val2, $val3,....$val500 ); $sth->execute(@bind_values); $result_set_ref = $sth->fetchall_arrayref(); } end loop # A while loop is required here as i can't bind all of values ( > 10,000 ) at a time as # i 'm hitting Oracle's SQL query length limit of 64k ( bytes? ). ........................... When i do like above, trailing spaces are lost, as DBI treats default type as VARCHAR2. So i have to bind one value at a time and specify type as CHAR in order to retain blanks. ex: for my $i ( @bind_values ){ $sth->bind_param( $i, $bind_values[$i], {ora_type => ORA_CHAR} ); } My question here is, can i bind all the 500 values at a single step, something like " $sth->bind_params( @bind_values, {ora_type => CHAR} ); (or) Can i set a flag to tell DBI to retain blanks..?. I guess Chopblanks() works only if we bind it as CHAR. Is there a way we can set Chopblanks() for VARCHAR2/default type. (or) Can i execute() by specifying type, like "$sth->execute(@bind_values, {ora_type => CHAR} );" Any suggestion is highly welcome. Thanks, Ravi This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
