Get Row ID of last inserted or updated record

[Fred12345]

Migrating from SQL Server to Oracle. SQL Server Stor Proc
as follows...

Insert Into Table1
(field1, field2,...)
Values (a, b, ...)

Select @@Identity as "Identity"

@@Identity is a global variable which returns the
identity (or row id) of the last inserted record.

What is the equivalent way in Oracle to do the same
thing? Thanks for any replies.

 

[Guest_imported]

You might be able to use a pseudo-column type to return the row id (ROWID or ROWNUM). I believe the ROWNUM can change if the sort order is changed, so ROWID may be better. Here's a URL with more info on ROWID:

http://www.oracle.com/oramag/oracle/98-Mar/index.html?ora.html

 

[karluk]

Oracle insert statements allow one to specify a "returning" clause and store the resulting info in a pl/sql variable. The code to return a rowid would look something like

insert into your_table values (insert values here)
returning rowid into :rowid_var;

The rowid will be 18 characters long.

 

[carp]

However, ROWID can change over time. If you are using the RETURN mechanism, whatever you are using for a Primary Key might be a better choice.