best practices net how to return pk against an oracle database

best practices net how to return pk against an oracle database  using -'.net,oracle'

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?

asked Sep 21, 2015 by atulpariharmca
0 votes

3 Answers

0 votes

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.


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:

answered Sep 21, 2015 by sandeep bhadauria
0 votes

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.

answered Sep 21, 2015 by ajit.chavhan
0 votes

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.

answered Sep 21, 2015 by sandeep bhadauria