Returns the value from the insert statement

Question

Work with an Oracle 9i database from an ASP.NET 2.0 (VB) application using OLEDB. Is there a way for the insert statement to return a value? I have a sequence configured for numeric entries when they enter the database, but I need this value to be returned after insertion, so I can do some manipulations with the set that I just entered into the VB code.

Answer 1

Some features:

1) Use the RETURNING clause:

INSERT INTO emp (empno, ename) VALUES (emp_seq.NEXTVAL, 'ANDREWS') RETURNING empno INTO :variable; 

2) Use the CURRVAL sequence:

 INSERT INTO emp (empno, ename) VALUES (emp_seq.NEXTVAL, 'ANDREWS'); SELECT emp_seq.CURRVAL INTO :variable FROM DUAL; 

CURRVAL returns the last sequence value generated by your session.

Answer 2

It seems that Oracle has a keyboard called "return" that can return the given column of the inserted row, however this may require you to manually set the "auto-increment", causing the next value in your sequence.

Check out this discussion:

http://forums.oracle.com/forums/thread.jspa?threadID=354998

However, you can always select the current sequence number in the second query, like MySQLs last_insert_id()

Answer 3

If this value is the key created by the database, you come across a good example of why you should use the UUID as a table key and generate them in code.

This method will give you better performance in your setup.

Answer 4

First use the SELECT statement to get the following sequence. You can use the double Oracle table for this.

 SELECT my_seq.nextval FROM dual 

Use the sequence that you received in subsequent INSERT operations.

 INSERT ... INSERT ...