asked on

Retrieving last created sequence index

Let's say I have a table with 2 columns,

dogindex auto-generating sequence number
dogname varchar2(30)

If I insert a row into the table, how do I retrieve the last created index? I am not looking for the following answer:

select max(dogindex) from dogtable

Rather, I'm looking for an Oracle equivalent of MS SQL Server's @@Identity system variable that will return the last created index.

Thanks.

Guy Hengel [angelIII / a3]

In Oracle I was used to query a new sequence number into a variable, and use that variable to create (insert) the new record. I could then use that variable to return to the application...

CHeers

vbDoc

Ah, check out ROWNUM. The ROWNUM can also be used to update a table and to add a sequential number to each record. If a column named custiomer_id2 to datatype NUMBER(7) was added to a table called s_customer, then the following update can be performed to uniquely number each record.

UPDATE s_customer
SET customer_id2 = ROWNUM;

HTH
John

BCao

ASKER

Thanks guys for the replies, but unfortunately I cannot change the table design.

joekendall

You could retrieve the currval from the sequence which should show the last value inserted into the table. Something like this would work:

SELECT SequenceName.currval FROM dual;

Thanks!

Joe

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BCao

ASKER

Thanks, your thourough answer was very insightful.