RE: binding multiple values and retain blanks

From: Ron Reidy (Ron.Reidy_at_arraybiopharma.com)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 13:35:29 -0600
To: <Ravi.Kongara@Sun.COM>, "DBI-Users" <dbi-users@perl.org>

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:Ravi.Kongara@Sun.COM]
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.

Relevant Pages

  • binding multiple values and retain blanks
    ... while loop { ... 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)
  • BIND 9.4.1 performance on FreeBSD 6.2 vs. 7.0
    ... I have been benchmarking BIND 9.4.1 recursive query performance on an ... using the resperf utility (dns/dnsperf in ports). ... BIND 9.4.1 from the base system was used for the threaded ... interesting and representative zone file and query data. ...
    (freebsd-current)
  • BIND 9.4.1 performance on FreeBSD 6.2 vs. 7.0
    ... I have been benchmarking BIND 9.4.1 recursive query performance on an ... using the resperf utility (dns/dnsperf in ports). ... BIND 9.4.1 from the base system was used for the threaded ... interesting and representative zone file and query data. ...
    (freebsd-performance)
  • RE: Oracle 10g and DBD::Oracle
    ... Where are the bind variables? ... before and after my query block. ... The above SQL Block creates only 1 cursor not 2. ... >the same sql query when I execute a SELECT statement via my small ...
    (perl.dbi.users)
  • RE: How to perform JOINs with DBI
    ... How to perform JOINs with DBI ... What does the query do in SQL*Plus? ... intended recipient, please be aware that any disclosure, copying, distribution ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)