Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    8

    Question Unanswered: I Need a help about SCOPE_IDENTITY()

    Hi All,

    In SQL Server stored procedure
    SCOPE_IDENTITY() will return the IDENTITY value inserted in Table, which was the last INSERT that occurred in the same scope.

    can any one give me the syntax in Oracle ?

  2. #2
    Join Date
    Apr 2004
    Posts
    69
    Hi,

    It is a little difficult (but possible) to simulate that in Oracle. You need have a trigger generated for the table (which had the identity column in SQL Server) and from within in that trigger load the sequence value (SEQUENCES are equivalents for IDENTITY columns in Oracle) into a GLOBAL temporary table. In the original proc code, select the value updated in the temporary table into a local variable and use that instead is SCOPE_IDENTITY function.

    I recently came across a tool called Swissql which provided such an automated conversion. Not sure if it an optimized solution but looked OK.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't get it

    in oracle you have to specifically ask for CURRVAL and NEXTVAL

    why wouldn't you do that before inserting the record?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    Posts
    69
    You can do a CURRVAL but in a multi-user/multi-session environment it is possible that the sequence has been updated with another incremental value (say NEXTVAL) before your assignment statement is executed (i.e you assign the currval to some local variable). Hence you need to put the currval value from within the trigger into a temporary table to store the sequence value that was used by the current session. (data in temporary tables are session specifc so even if some other session does a seq.nextval the same will not be updated in the current session).

    So in the proc assignment code you need to select the value from the temporary table (instead of doing a currval).

  5. #5
    Join Date
    Aug 2004
    Posts
    8

    Smile

    Thanks for your reply.

    (Frankly telling i am a beginer in Oracle.
    I know sql server, I'm migrating all stored stored procedures into oracle database)

    I have Master and detail records to update into the related tables
    First I'm inserting Master records after i get identity value by using scope_Identiry()

    @thisId --> output parameter in that stored procedure.
    SET @thisId = SCOPE_IDENTITY();

    from the application i'm passing detail records with @thisId value to detailInsert Stored procedure.


    I'll try to use your logic. If i find any difficulties I'll let you know.
    Thanks again.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I guess what you need is as follows then:

    1) Create a new sequence to populate the ID on the master table.
    2) Create a trigger to populate the ID of the master table. (auto-increment value).
    3) Use the RETURNING INTO clause in the INSERT INTO statement, to get the value of the current ID you just inserted.

  7. #7
    Join Date
    Aug 2004
    Posts
    8

    Smile

    Cool... it's working.

    Thanks to all for sharing your valuable ideas.
    I may come up with different questions soon.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by strawstun
    You can do a CURRVAL but in a multi-user/multi-session environment it is possible that the sequence has been updated with another incremental value (say NEXTVAL) before your assignment statement is executed (i.e you assign the currval to some local variable). Hence you need to put the currval value from within the trigger into a temporary table to store the sequence value that was used by the current session. (data in temporary tables are session specifc so even if some other session does a seq.nextval the same will not be updated in the current session).

    So in the proc assignment code you need to select the value from the temporary table (instead of doing a currval).
    No, CURRVAL is specific to your session - it is not updated by calls to NEXTVAL made by other sessions. The whole point of CURRVAL is to give you back the last sequence number you used.

    Here is a demo with 2 sessions called user1 and user2:
    Code:
    user1> create sequence test_seq;
    
    Sequence created.
    
    user1> select test_seq.nextval from dual;
    
       NEXTVAL
    ----------
             1
    Over to user2...
    Code:
    user2> select test_seq.nextval from emp;
    
       NEXTVAL
    ----------
             2
             3
             4
             5
             6
             7
             8
             9
            10
            11
            12
            13
            14
    
    13 rows selected.
    
    user2> select test_seq.currval from dual;
    
       CURRVAL
    ----------
            14
    Now back to user1:
    Code:
    user1> select test_seq.currval from dual;
    
       CURRVAL
    ----------
             1

  9. #9
    Join Date
    Apr 2004
    Posts
    69
    Tony,

    What you said is true but even then there is a scenario when the sequence.currval will fail. This scenario is when a sequence definition is used across multiple tables (it is generally not preferred by I have come across such instances.. after all you just need to ensure that the rows are unique)

    So let us take two tables t1 and t2 sharing the same sequence definition. t1 has a trigger which inserts data into t2. So when you try to insert a value in t1, there will be a seq.nextval. Now the trigger of t1 will again do a seq.nextval to insert into t2 and hence when you read the currval from within the procedure, you'll read a wrong value. This necessitates storing the sequence values specific to tables.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, very true. It would not be good idea to rely on the value of CURRVAL outside the scope of the code that performed the insert, because you cannot then be sure what else happened in between!

Posting Permissions

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