In SQL Server, I can create an "autonumber" field as an ID in a table.
 When I insert a new record into the table I can use "SELECT
@@identity;" to get the value of the newly created ID in the same
transaction.  I want to know how to do this in Oracle.

The following code works, but I can't work out how to output the value
of 'a':

-- Create sequence
CREATE SEQUENCE test_seq;

-- Create the table
CREATE TABLE test (
ID INT PRIMARY KEY,
name VARCHAR2(50));

-- Create trigger to automatically increment primary key
CREATE OR REPLACE TRIGGER test_trigger BEFORE INSERT ON test
FOR EACH ROW
WHEN (new.ID IS NULL)
BEGIN
 SELECT test_seq.NEXTVAL INTO :new.ID FROM DUAL;
END;

-- Try to get @@identity 
DECLARE a INTEGER;
BEGIN
INSERT INTO test (name) VALUES ('test') RETURNING ID INTO a;
END;

ie. The value of 'a' is the correct value but I can't seem to access
it.  I can do this: raise_application_error(-20504, a) and check that
the value is correct.  However, I can't seem to do anything like
"SELECT a FROM dual;" or "SELECT a;" to output the value.

I don't want to use the following solution as I don't believe it will
work with concurrent users.

--This example adds a new order with the next order number to the
master order table. It then adds suborders with this number to the
detail order table:

INSERT INTO orders (order_id, order_date, customer_id) 
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106); 

INSERT INTO order_items (order_id, line_item_id, product_id) 
VALUES (orders_seq.currval, 1, 2359); 


-- I also can't seem to get the following to work
DECLARE a INTEGER;
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
INSERT INTO test (name) VALUES ('test') RETURNING ID INTO a;
DBMS_OUTPUT.PUT_LINE(a);
END;

Any leads at all would be appreciated.

The solution you are avoiding, with NEXTVAL and CURRVAL are the best
options, and are safe in a multi-user situation where you will have
concurrent users.

This is because the value of CURRVAL is session-contextual, and rather
than returning the absolute current value of the sequence, it in fact
returns the value of the last NEXTVAL called within the current
session. If another user (in another session/connection) is also
getting values with NEXTVAL they will have different values returned
by a CURRVAL.

You can find further reference to use of NEXTVAL and CURRVAL here:
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/sql_elements6.htm#4062

The user of NEXTVAL is also preferred as a pre-insert trigger is a
fairly expensive database operation and may affect performance.

Sorry I can't provide another way, but I can confirm that the use of
CURRVAL is multi-session safe.

Regards,
sycophant-ga

---

Request for Answer Clarification by tmol-ga on 17 Aug 2002 17:51 PDT

Thanks for the quick answer.  However, I really need to output the
value of the new ID. Even if I do the following, I can't seem to run
this as one SQL statement (ie. one session) in JSP:

INSERT INTO test (name) VALUES ('test');
SELECT test_seq.currval FROM dual;

Do you know how to output the ID in the same session?

---

Clarification of Answer by sycophant-ga on 17 Aug 2002 20:00 PDT

A session refers to a connection, rather than a statement. So you can
execute both queries seperately as long as they executed on the same
connection.

As long as you don't close and reopen your database connection between
queries, you should have no problem and will be in the same session.

If you are not in a session which has had a NEXTVAL called, CURRVAL
will not return a value.

Hope this clarifies it for you.

Regards,
sycophant-ga

Good, but I still have the problem.

Hi tmol,
I haven't done any Oracle for years, but I was wondering when you
said:

>> ie. The value of 'a' is the correct value but I can't seem to
access
>> it.  I can do this: raise_application_error(-20504, a) and check
that
>> the value is correct.  However, I can't seem to do anything like
>> "SELECT a FROM dual;" or "SELECT a;" to output the value.

What do you mean you can't "do anything" with "SELECT a;" ? What
errors are you getting exactly? I think it should be safe to assume
that if you can get the value of "a" at all, it's valid, unless
there's a bug in Oracle or "RETURNING ID" casts some peculiar internal
type.


Oh, and as an aside..

In MSSQL server, never ever ever ever use the @@IDENTITY function!
There's a serious flaw in it: If you insert a value into a table in a
procedure and it causes a trigger to fire which ALSO inserts data,
@@IDENTITY will be set to the value of the LAST inserted value
(globally) which would normally be one of the inserts in the trigger!
It's been fixed in SQL2000 with a function called SCOPE_IDENTITY().