Ron, (Apologies for top-posting ...)
In most cases there will be no difference, but it is possible for trigger based logic to subsequently bump the value of a sequence. This means fetching seq.currval after the insert could give you a value higher than the one actually used in the insert. What we do is have the primary key populated in a BEFORE INSERT trigger, and then (as suggested) use the RETURNING clause in the insert. This is the only way to *guarantee* you are getting the correct value. However if you are using an older version of Oracle that doesn't support the RETURNING clause, or have no control over the insert statement and can therefore not use it, there are still ways you can avoid the approach you describe. One is to use the trigger mechanism to populate the primary key from the sequence, and also use the same value to set a PL/SQL package variable. After the insert you can retrieve the package variable in a separate anonymous PL/SQL block. If you need more info, let me know and I will provide an example. Steve -----Original Message----- From: Ron Savage [mailto:[EMAIL PROTECTED] Sent: Friday, 7 October 2005 8:07 AM To: List - DBI users Subject: RE: Oracle and dbh -> last_insert_id On Thu, 06 Oct 2005 09:43:09 -0400, Hardy Merrill wrote: Hi Hardy > SELECT [sequence name].NEXTVAL as my_seq_nextval FROM dual Also, is this really any different from calling seq.currval after the insert? I assume not. -- Cheers Ron Savage, [EMAIL PROTECTED] on 7/10/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
