<y preference is to use SPs wherever possible and avoid as much network interaction as possible. I saw a follow up post to the original after I had sent me reply where the responder suggested a statement like 'insert into tab values (select seq.nextval, col, col from dual)'. This would certainly work, but my preference would be to remove this detail from the main flow of code. Some developers I have worked with prefer to fetch the sequences themselves. As a DBA, I advocate strongly the use of SPs and before insert triggers.
----------------- Ron Reidy Senior DBA Array BioPharma, Inc. -----Original Message----- From: David Goodman [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 9:27 AM To: [EMAIL PROTECTED] Subject: RE: Best Way to Auto Increment with Oracle Hello Ron: Let me naively ask, why you would not use a stored procedure to get rid of the network interaction? That or just bundle up the two statements in one execution. regards, David --- "Reidy, Ron" <[EMAIL PROTECTED]> 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. > > ----------------- > Ron Reidy > Senior DBA > Array BioPharma, Inc. > > > -----Original Message----- > From: Jim [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 16, 2004 6:21 AM > To: [EMAIL PROTECTED] > Subject: Best Way to Auto Increment with Oracle > > > Hi > I am looking for suggestions on the best way to auto > increment a key column in a oracle table. I know I > can > do this inside of Oracle by creating a sequence and > then a trigger. BUT, is it better to make DBI do > this > when I insert the data? If so, how would I go about > it? > Thanks > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We > finish. > http://promotions.yahoo.com/new_mail > > 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.
