binding multiple values and retain blanks

From: Ravi Kongara (Ravi.Kongara_at_Sun.COM)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 12:31:37 -0700
To: DBI-Users <dbi-users@perl.org>

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

Relevant Pages

  • RE: binding multiple values and retain blanks
    ... Join this table to the main table in your query. ... When i do like above, trailing spaces are lost, as DBI treats default ... So i have to bind one value at a time and specify type as CHAR in order ...
    (perl.dbi.users)
  • Re: binding multiple values and retain blanks
    ... run the query by joining with temp tables. ... Instead i think i can live with a for loop to bind one value at a time. ... >use DBI; ...
    (perl.dbi.users)
  • Re: How to turn $_ into a hashref?
    ... Philip, thank you very much. ... If I don't need or want DBI handle attributes, how can I define a null hash reference? ... the how point of using bind values is you should $dbh->prepare ... then $sth->executein the loop. ...
    (perl.beginners)
  • Re: How to turn $_ into a hashref?
    ... Philip, thank you very much. ... If I don't need or want DBI handle attributes, how can I define a null hash reference? ... the how point of using bind values is you should $dbh->prepare ... then $sth->executein the loop. ...
    (perl.beginners)
  • Re: Counting help
    ... time through the loop, that should fix your immediate problem. ... broken the DBI steps into prepare/execute. ... support some sort of counting ability. ...
    (perl.beginners)