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
