No you don't. Using the for loop is highly inefficient compared to what you will see using global temp tables. Global Temp tables are session specific - one temp table will suffice for all sessions. You do not need to truncate/delete.
You create the global temp tables before you start your process. It is a part of some schema. Read about them on metalink, note #68098.1 and at http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2062857. Check it out with your DBA. My guess is he'll approve when you make the case. ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Ravi Kongara [mailto:[EMAIL PROTECTED] Sent: Friday, October 22, 2004 3:47 PM To: Reidy, Ron Cc: DBI-Users Subject: Re: binding multiple values and retain blanks 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:[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. > > > 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.
