Use a before insert trigger to populate the column. ----------------- Ron Reidy Senior DBA Array BioPharma, Inc.
-----Original Message----- From: Jean-Pierre Utter Löfgren [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 5:20 AM To: [EMAIL PROTECTED] Subject: How to use sequences in DBI/DBD::Oracle with bind 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.
