Get seq number used by insert statement called from C# (oracle)

Question

I need to insert a record into a table and set the value of a column (e.g. orderid) to a unique #. And return that number used.

I thought the process would be to do use a sequence and an insert statement with nextval:

insert into ordersTable(orderid) values(ordernums.nextval);

But how to I get the number that was used? My thought is that I have to get it back from the insert call otherwise the nextval (or currval) could have changed if I re-query.

But I'm not sure how to do that. One way around this I thought would be to, on insert, also add my only unique value to a field, then requery for that value.

Is there another way to do this? I figure I probably missing something at the sql level?

CtC

Answer 1

Far as I know, Oracle 9i+ has the RETURNING clause:

DECLARE v_orderid ORDERSTABLE%TYPE;

INSERT INTO ordersTable
 (orderid) 
VALUES
 (ordernums.nextval)
RETURNING orderid INTO v_orderid;

The alternative is to populate the variable before the INSERT statement:

Within a function:

DECLARE v_orderid ORDERSTABLE%TYPE := ORDERNUMS.NEXTVAL;

INSERT INTO ordersTable
 (orderid) 
VALUES
 (v_orderid);

RETURN v_orderid;

Using an OUT parameter:

CREATE OR REPLACE PROCEDURE ORDER_INS(out_orderid OUT ORDERSTABLE%TYPE) 
AS
BEGIN

out_orderid := ORDERNUMS.NEXTVAL;

INSERT INTO ordersTable
 (orderid) 
VALUES
 (out_orderid);

END;

In addition, be aware that the CURRVAL of a sequence is session-specific. No matter how long since you called NEXTVAL in the current session and no matter how many other sessions may have called NEXTVAL on the same sequence, ordernums.currval will always return the last value of the sequence that was given to your session. So although you have various options for retrieving the value from the INSERT, it may not be necessary-- you may simply want to reference ordernums.currval in subsequent statements. Of course, since CURRVAL is session-specific, it does not make sense (and would return an error) if you called CURRVAL in a session before you had called NEXTVAL for the same sequence.