Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    22

    Unanswered: How to get the Currently inserted primary key of a table

    Is there any way to get the Primay key of the recent inserted record. AS this functionality is available in most of the DBMSes. Like in MS SQL Server we write
    Identity_scope("TableName")

    to get the last id that is inserted in "Table_Name" in the current scope.

    Is the same functionality is available in Oracle. To be specific Oracle=Oracle 10g.

    At times we need to return Primary Keys of the inserted record in a table.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    In Oracle you do in a completely different way as there is no such thing as an auto-increment column.

    You create a SEQUENCE that will create the primary keys for your table, then you either get the sequence value before you do the insert, or you retrieve the lates sequence value after the insert:

    Code:
    CREATE TABLE my_table (id integer primary key);
    CREATE SEQUENCE my_id_sequence;
    Possibility one, retrieve the ID after the insert:
    Code:
    INSERT INTO my_table (id) values (my_id_sequence.nextval);
    SELECT my_id_sequence.currval FROM dual
    Possibility two, retrieve the ID first then do the insert:
    Code:
    SELECT my_id_sequence.nextval 
      INTO my_id_variable
    FROM dual;
    INSERT INTO my_table (id) values (my_id_variable)

  3. #3
    Join Date
    Jun 2009
    Posts
    22
    Thanks for a quick reply.

    What if I write a pre insert trigger and isert the value in the primary key column by taking sequence.nextval.

    Sequece.currval will still return the actual value that was inserted last in the current session?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by sayyed_kamran View Post
    What if I write a pre insert trigger and isert the value in the primary key column by taking sequence.nextval.

    Sequece.currval will still return the actual value that was inserted last in the current session?
    Yes, currval is always valid in the current transaction, regardless where nextval was called.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Or...

    Or also:
    Code:
    INSERT INTO my_table  (id)
         VALUES (my_id_sequence.nextval)
      RETURNING id INTO v_id;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Good point.
    I always forget about the RETURNING clause

Posting Permissions

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