Oracle equivalent of @@identity
Category: Computers > Programming
Asked by: tmol-ga
List Price: $10.00
17 Aug 2002 05:27 PDT
Expires: 16 Sep 2002 05:27 PDT
Question ID: 55562
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.
Re: Oracle equivalent of @@identity
Answered By: sycophant-ga on 17 Aug 2002 06:30 PDT
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
rated this answer:
Good, but I still have the problem.
Re: Oracle equivalent of @@identity
From: bribold-ga on 18 Aug 2002 11:02 PDT
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().
If you feel that you have found inappropriate content, please let us know by emailing us at firstname.lastname@example.org with the question ID listed above. Thank you.
|Search Google Answers for|