If you need the value you inserted can you not get it with the RETURNING clause? (To be fair I've never tried this with DBI before so I don't know if it is supported)
Ken. -- IT Infrastructure Manager beCogent Ltd T: +44 1236 628140 M: +44 7770 573749 E: [EMAIL PROTECTED] -----Original Message----- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: 16 June 2004 16:46 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
Any opinions expressed in this E-mail may be those of the individual and not necessarily the company. This E-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that you have received this E-mail in error and that any use or copying is strictly prohibited. If you have received this E-mail in error please notify the beCogent postmaster at [EMAIL PROTECTED] Unless expressly stated, opinions in this email are those of the individual sender and not beCogent Ltd. You must take full responsibility for virus checking this email and any attachments. Please note that the content of this email or any of its attachments may contain data that falls within the scope of the Data Protection Acts and that you must ensure that any handling or processing of such data by you is fully compliant with the terms and provisions of the Data Protection Act 1984 and 1998.
