Recommendations: .NET: How to return PK against oracle database?

Question

In SQLServer, it is generally believed that adding SELECT SCOPE_IDENTITY () at the end of your insert is the best way to return the PK of a newly inserted record, assuming you use an auto-increment field for pk.

However, I cannot find an equivalent for Oracle.

It is best practice to use a sequence to generate PK, but there are various options for implementing this. Do you leave it to the developer to insert sequence.nexval or with a trigger?

In any case, getting a new ID response seems like a common problem.

Suggestions and solutions that I came across include:

• creating a stored procedure that returns PK
• running select select identifier from seq.nextval and then passing it to insert
• select max (id) after insertion (Note: do not do this!)
• add the RETURNING clause to the insert

What should be the “best practice” solution for this situation?

Answer 1

You can use the RETURNING clause for this in Oracle stored procedures.

For example:

TABLEA has NAME and EMP_ID. EMP_ID is populated internally when records are inserted.

INSERT TABLE (TITLE) VALUES ('BOB') RETURN EMP_ID INTO o_EMP_ID;

Suppose the string is in a stored proc with the output parameter o_EMP_ID.

Hope that helps ... if not, here's a more detailed example:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm

Answer 2

The RETURNING clause is for this use only, so I would call it best practice for using it.

An alternative would be to select seq.CURRVAL after insertion. This returns the last value obtained from the sequence using this session.

Answer 3

The stored procedure and the return clause have the clear advantage of a single database call; any other solution is inferior. Regardless of whether you are doing this using a stored procedure or using a return clause, there is a whole gang of worms on its own.