What if some code in a trigger on the table did a NEXTVAL on the sequence? Probably not applicable in this case, but I think it is possible without being in a threaded environment.
I happen to agree with Ron for all his reasons plus one other. If you put the code in a trigger, it "guarantees" the column will be populated correctly regardless of where the insert is coming from. If you are putting a unique constraint on that column, and someone is inserting rows from somewhere else, you can't guarantee that they will be doing the right thing - so your code that uses .NEXTVAL in the INSERT statement could conceivably fail. Steve -----Original Message----- From: Ian Harisay [mailto:[EMAIL PROTECTED] Sent: Wednesday, 16 June 2004 1:26 PM To: [EMAIL PROTECTED] Subject: RE: Best Way to Auto Increment with Oracle I would disagree with this last statement. you are gauranteed to get the correct value from $seq.currval in a non-threaded environment and your session is not shared by anything else. And if you are threading (say in Java. I still don't with perl) I would hope that each of your worker threads are not sharing a single session. Because if they are, you have much bigger problems than just getting the correct value returned to you from $seq.currval. >>>Reidy, Ron <[EMAIL PROTECTED]> 06/16 2:58 am >>> Yes, using the SP hides the details. Getting the value just inserted via currval is not guaranteed to return the value you just fetched. Better to use 'insert ... returning col into' to get the value used in your session's insert statement. ----------------- Ron Reidy Senior DBA Array BioPharma, Inc. -----Original Message----- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 9:46 AM To: [EMAIL PROTECTED] Subject: Re: Best Way to Auto Increment with Oracle On 2004-06-16 09:14:25 -0600, Reidy, Ron wrote: >Better? Maybe. Consider. > >Your program connects via SQL*net to the DB. At insert time, you need to fetch a sequence, so you issue 'select seq.nextval from dual'. Seems harmless enough until ... > >1. The select statement must be passed across the SQL*Net connection. >2. The statement must be parsed. Maybe the worst you will have here is a soft parse, but non the less, you are using the CPU for this. >3. The statement is executed and the fetch occurs. >4. The result is passed back to your program. >5. You bind the value (hopefully) to your statement handle. >6. You then execute the insert statement. > >If you use a BEFORE INSERT trigger to fetch the sequence, you will eliminate steps 1, 2, 3, 4, 5 from the flow. You can get the next value from a sequence in the insert statement (see my previous mail in this thread), so with or without the trigger, you have to parse (once) and execute (possibly many times) only the insert statement. The difference is: With a trigger, you hide the details of the sequence from the insert statement. This makes the insert statement simpler and more portable. OTOH, you don't know which value you just inserted (you can get at it with select $sequence.currval from dual but then you lose these advantages). hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED] | -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 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.
