Results 1 to 3 of 3

Thread: identity insert

  1. #1
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28

    Unanswered: identity insert

    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.
    Data Climber

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/otnd..._elements6.htm


    rudy

  3. #3
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    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!
    Data Climber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •