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

    Thumbs up 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 ?
    Last edited by VLove; 09-01-04 at 04:46.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    CURRVAL
    select seq.currval from table
    The select gives the current from the table specified.

  3. #3
    Join Date
    Aug 2004
    Posts
    8
    Quote Originally Posted by Kaiowas
    CURRVAL
    select seq.currval from table
    The select gives the current from the table specified.

    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.

    can we use CURRVAL function in the multi user environment ?

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    Any reason why not to have master and detail records inserted by one sp? I could think of a number of ways your data might get mixed up. It would seem to me, that, applications committing data into several tables such as master/detail, have a tendency in having an awkard bundle of code trying to keep the data straight.

  5. #5
    Join Date
    Aug 2004
    Posts
    8
    Quote Originally Posted by Kaiowas
    Any reason why not to have master and detail records inserted by one sp? I could think of a number of ways your data might get mixed up. It would seem to me, that, applications committing data into several tables such as master/detail, have a tendency in having an awkard bundle of code trying to keep the data straight.

    The reason to have 2 stored procedure, Due to huge data's.
    Master and detail have lot of informations. I just got another solution

    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.


    What do you think about this procedure ? Lemme check this....

    Anyway thanks for sharing your ideas. I may come up with different questions soon.

Posting Permissions

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