This solution requires an extra network hop. The better solution is a before insert trigger to populate the column.
----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Ravi Kongara [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 4:55 PM To: Jean-Pierre Utter Löfgren Cc: [EMAIL PROTECTED] Subject: Re: How to use sequences in DBI/DBD::Oracle with bind Alternative solution could be like this, but it's based on assumption that the sequence is exclusively used by current application/transaction... At the start of program fetch my_id_seq.nextval to a variable and build a sequence array starting with that nextval. my $start_num = $dbh->selectrow_array( "select my_id_seq.nextval from dual"); my @seq = ( $start_num..100,000 ); # some end number $sth->prepare ( "insert into foo (my_id, data) values (?,?)" ); $sth->bind_param( 1, shift(@seq) ); $sth->bind_param( 2, "bar"); $sth->execute(); And set the starting value of my_id_seq to last value of consumed array at the end of program. This very hypothetical but may do better in performance, if it is viable. reg, Ravi K Jean-Pierre Utter Löfgren wrote: > Thanks for your response, Hardy! > > I read that too, but since I dynamicaly build the "meta-sql" code in > another application, I haven't really control over knowing where the > sequence are located and wether the occcur in a statment or not. > > Kind of tricky this one. > > Thanks also Andy and Jon for your mailcontribution. > > So, I think I'll have to do a hybrid of your suggestions and the > present design. > > /JP > > Hardy Merrill wrote: > >> I'm no Oracle expert, but I've always done it the way you originally >> did it: >> >> $sth->prepare ( "insert into foo (my_id, data) values >> (my_id_seq.nextval, 'bar')" ); >> $sth->execute(); >> >> I searched for "seq" in 'perldoc DBD::Oracle' and found this under >> LOB locator stuff: >> ---------------------- >> my $lob_id = $dbh->selectrow_array( <<" SQL" ); >> SELECT lob_example_seq.nextval FROM DUAL >> SQL >> >> my $sth = $dbh->prepare( <<" SQL" ); >> INSERT INTO lob_example >> ( lob_id, bindata, chardata ) >> VALUES ( ?, ?, ? ) >> SQL >> $sth->execute( $lob_id, '', '' ); >> -------------------- >> >> I know your question has nothing to do with LOB locators, but this >> could be another way to solve the same problem. Not sure what >> performance impact doing a SELECT on the sequence nextval and then >> INSERT'ing, but this way you can at least PREPARE your insert just once. >> >> HTH. >> >> Hardy Merrill >> >> >>>>> Jean-Pierre Utter Löfgren <[EMAIL PROTECTED]> 08/31/04 07:19AM >>> >>>> >>>> >> Does anybody have a clue on how to bind oracle sequences. >> >> Background: >> >> Due to the large amount of data to be loaded into our oracle9i db by our >> applications, a bulk-load software would have been our first choice to >> handle the inserts. However, since our data is higly dynamic in its >> construction, this is not an option. >> >> In order to solve this, I've developed a perl package to handle the >> inserts. So far so good, but now our DBAs are running wild since I >> produce a fair amount of latching and statements prepares (400+/s), >> chocking the production database (SUN Enterprise 4500, 14 CPU). >> >> Every row inserted is unique, but some use the same columns to insert, >> so I'd like to use $sth->bind_param or the $sth->execute($1, $2 ....) >> for those, to minimize my preparing of statments. >> >> You might think I should have user bind variables from the start, and I >> tried, trust me, but the problem back then is the same as I will try to >> adress now. >> >> The Problem >> >> ....is however that I use sequences in the database inserts for various >> reasons, as some inserts use the same primary key, sub-data needs to be >> referenced to each other etc. Using DBI/DBD, it handles inserts using >> sequneces very nicely as long as I do >> >> $sth->prepare ( "insert into foo (my_id, data) values >> (my_id_seq.nextval, 'bar')" ); >> $sth->execute(); >> >> But if I rearange the code to be more efficient... >> >> $sth->prepare ( "insert into foo (my_id, data) values (?,?)" ); >> $my_id = "my_id_seq.nextval"; >> $data = "bar"; >> $sth->execute($my_id,$data); >> >> or >> >> $sth->prepare ( "insert into foo (my_id, data) values (?,?)" ); >> $sth->bind_param( 1, "my_id_seq.nextval" ); >> $sth->bind_param( 1, "bar"); >> $sth->execute(); >> >> , I get "ORA-01722: Invalid number" on the sequence. >> >> I tried to find infomation on ora_types argument to indicate the >> argument type in the bind, but am so far unsuccessful. I've even tried >> to bind a sub-select like "(select my_id_seq.nextval from dual)", but >> got the same result... >> >> Anybody got any id.ea if this is possible or not to achieve this, i.e. >> using oracle sequences in binds? >> >> /JP >> >> > > 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.
