Re: binding multiple values and retain blanks

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

  • Next message: Ron Reidy: "RE: binding multiple values and retain blanks" 
    Date: Fri, 22 Oct 2004 14:47:14 -0700
To: "Reidy, Ron" <Ron.Reidy@arraybiopharma.com>

    I got to try this in terms of performance. I'm running this program for
    more than 100 different tables parallelly. So i have to create so many
    temp tables at a time and populate them with the given data and then
    run the query by joining with temp tables. I think this increases the
    overhead
    on database.Else i have to create the temp tables once and keep
    truncating everytime
    before each run.Then those temp tables turn out to be permanent.
    Another obstacle is to take permission from dba to create those massive
    temp tables.

    Instead i think i can live with a for loop to bind one value at a time.

    Thanks,
    Ravi

    Reidy, Ron wrote:

    >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.
    >
    >
    >

  • Next message: Ron Reidy: "RE: binding multiple values and retain blanks"

    Relevant Pages

    • 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)
    • 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)
    • 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: Man in the middle
      ... If dVar1> dVar2 Then ... temp = dVar2 ... Create in inner loop that loops from 0 to the value of the outer loop ...
      (microsoft.public.excel.programming)
    • Re: do expression
      ... loop variable i by binding, ... To make your transformation fully general, ... (inits (mapcar #'second vars)) ... ;; evaluate init forms and save results in temp vars ...
      (comp.lang.scheme)