get Last Inserted ID from Oracle

[umuayo]

Hello I know in SQL Server, I can within the cfquery tag do stuff like select @@identity as "newid" to get the just inserted ID of a record.

How do I do this in Oracle 10 G.

The Sequence that creates the unique key is called from a BEFORE INSERT OR UPDATE Trgger. How do I return the just inserted ID in Coldfusion?

[Owainnorth · Accepted solution]

The simple answer is SEQUENCE_NAME.CURRVAL.

However.

In order for CURRVAL to be set, you need to be within a session. I'm afraid to say I don't know whether a trigger runs within the context of your session or not, that would have to be tested.

I use Oracle, and if I ever need to get back the sequence value reliably, I select it out upfront then pass it to my insert statements. That way there can be an ambiguity or weirdness involved, no matter how CF handles its connections to the database.

[Owainnorth]

Hmm, looks like you might be alright actually, but do make sure you wrap it all up inside a cftransaction block. Testing:

CREATE TABLE TestTable (

  ID NUMBER(2) PRIMARY KEY,

  Name VARCHAR2(20) NOT NULL

) ;

CREATE SEQUENCE Test_seq ;

CREATE OR REPLACE TRIGGER TestTable_Inserted_Trg

BEFORE INSERT ON TestTable FOR EACH ROW

DECLARE

BEGIN

:new.Id := Test_seq.NEXTVAL ;

END ;

/

INSERT INTO TestTable ( Name ) VALUES ( 'Dave' ) ;

SQL> select Test_seq.currval

  2  from dual ;

   CURRVAL

----------

         1