identity insert [Archive]

climber

06-14-02, 17:57

Hi

I'm a SQL Server Programmer having a first touch with Oracle.

Does anyboady know the equivalent of @@IDENTITY in PL-SQL? This is used in SQL Server to get the value of the last inserted identity field.

for exemple:
I have a table 'user' with an identity field 'id_user'.

_______
INSERT user (name, password) VALUES ('climber','pokomoonshine')
SELECT @@IDENTITY
_______

Last statement is supposed to give me the value created by my INSERT statement in 'id_user', so you don't have to write SELECT MAX(id_user)...

I didn't find any topics in this forum about it yet.

Any help is welcome.

r937

06-17-02, 17:03

oracle uses sequences instead of identity fields, and sequences are separate from tables

you would use SEQUENCE.NEXTVAL in the insert statement, and then you can use SEQUENCE.CURRVAL (in the same session) for the number that was used

here's a good example --

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);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);

the above example is from http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/sql_elements6.htm

rudy

climber

06-17-02, 17:12

This is validating the information I got.

I learned about Oracle and saw you have to work with those functions and define your ID autoincrement into a trigger wich is a different (but interesting!) approach from SQL Server.

Thanks a lot!