How to get the Currently inserted primary key of a table

sayyed_kamran · #1 (**permalink**) 07-27-10, 06:06

Is there any way to get the Primay key of the recent inserted record. AS this functionality is available in most of the DBMSes. Like in MS SQL Server we write
Identity_scope("TableName")

to get the last id that is inserted in "Table_Name" in the current scope.

Is the same functionality is available in Oracle. To be specific Oracle=Oracle 10g.

At times we need to return Primary Keys of the inserted record in a table.

shammat · #2 (**permalink**) 07-27-10, 06:16

In Oracle you do in a completely different way as there is no such thing as an auto-increment column.

You create a SEQUENCE that will create the primary keys for your table, then you either get the sequence value before you do the insert, or you retrieve the lates sequence value after the insert:

Code:

CREATE TABLE my_table (id integer primary key);
CREATE SEQUENCE my_id_sequence;

Possibility one, retrieve the ID after the insert:

Code:

INSERT INTO my_table (id) values (my_id_sequence.nextval);
SELECT my_id_sequence.currval FROM dual

Possibility two, retrieve the ID first then do the insert:

Code:

SELECT my_id_sequence.nextval 
  INTO my_id_variable
FROM dual;
INSERT INTO my_table (id) values (my_id_variable)

sayyed_kamran · #3 (**permalink**) 07-27-10, 06:27

Thanks for a quick reply.

What if I write a pre insert trigger and isert the value in the primary key column by taking sequence.nextval.

Sequece.currval will still return the actual value that was inserted last in the current session?

shammat · #4 (**permalink**) 07-27-10, 06:49

Quote:

Originally Posted by sayyed_kamran

What if I write a pre insert trigger and isert the value in the primary key column by taking sequence.nextval.

Sequece.currval will still return the actual value that was inserted last in the current session?

Yes, currval is always valid in the current transaction, regardless where nextval was called.

LKBrwn_DBA · #5 (**permalink**) 07-27-10, 10:13

Or also:

Code:

INSERT INTO my_table  (id)
     VALUES (my_id_sequence.nextval)
  RETURNING id INTO v_id;

shammat · #6 (**permalink**) 07-27-10, 10:24

Good point.
I always forget about the RETURNING clause

Internet Services

Web Development

Digital Marketing

Consumer Tech