Skip to main content
U
umuayo
Inspiring
July 19, 2011
Answered

get Last Inserted ID from Oracle

  • July 19, 2011
  • 1 reply
  • 12781 views

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?

This topic has been closed for replies.
Correct answer Owainnorth

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.

1 reply

Owainnorth
OwainnorthCorrect answer
Inspiring
July 19, 2011

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
Owainnorth
Inspiring
July 19, 2011

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices