Return a value from a insert statement.

Question

Working with an Oracle 9i database from an ASP.NET 2.0 (VB) application using OLEDB. Is there a way to have an insert statement return a value? I have a sequence set up to number entries as they go into the database, but I need that value to come back after the insert so I can do some manipulation to the set I just entered in the code-behind VB.

Answer 1

If this value is the key the database creates, you've ran into a good example why you should use UUIDs as your table key, and generate them in code.

This method will give you faster performance in your setup.

Answer 2

Oracle seem to have a keywod called "returning" which can return a given column of the inserted row, however that might require you to set the "autoincrement" field manually by invoking the next value in your sequence.

Check this discussion about it:

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

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

Answer 3

First use a SELECT statement to get the next sequence. You may use the Oracle dual table to do this.

SELECT my_seq.nextval FROM dual

Use the sequence that you retrieved in subsequent INSERT statements.

INSERT ...
INSERT ...

Answer 4

Some possibilities:

1) Use the RETURNING clause:

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

2) Use the sequence CURRVAL:

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.