Best practices: .NET: How to return PK against an oracle database?

Question

With SQLServer, it seems to be generally accepted that adding a SELECT SCOPE_IDENTITY() to the end of your insert is the best way to return the PK of the newly-inserted record, assuming you're using an auto-increment field for the pk.

However, I can't seem to find the equivalent for Oracle.

Best practice seems to be to use a sequence to generate the PK, but there are different options for how to implement even that. Do you leave it up to the developer to insert sequence.nexval, or use a trigger?

In either case, getting the new ID back seems to be a common problem.

Suggestions and solutions I've run across include:

• creating a stored proc that returns the PK
• running a select id from seq.nextval, then passing that to the insert
• select max(id) after insert (Note: Don't do this!)
• add a RETURNING clause to the insert

What should the "best practice" solution be for this situation?

Answer 1

The RETURNING clause is intended for just this kind of usage, so I would call it a best practice to use it.

An alternative would be to select seq.CURRVAL after the insert. That returns the last value obtained from the sequence by this session.

Answer 2

You can use the RETURNING clause to do this in Oracle stored procs.

For example:

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

INSERT INTO TABLEA(NAME) VALUES ('BOB') RETURNING EMP_ID INTO o_EMP_ID;

That's assuming that line is in a stored proc with an output parameter of o_EMP_ID.

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

http://www.samoratech.com/PLSQL/swArtPLSQLReturn.htm

Answer 3

The stored procedure and the returning clause have the distinct benefit of a single database call any other solution is inferior. Whether you do it via a stored procedure or you use a returning clause is a whole can of worms in itself.