Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unanswered: Returning a sequence number

    Hi, hope this hasnt been answered too many times before...
    I want to write a stored procedure to insert a record into a table (the key field being derived from a sequence) and return the sequence value via an out parameter to the stored procedure.

    What I am trying to achieve is something like...


    P_MYTABLE_CREATE_ENTRY
    (OUT_ID_NO Out Number) IS

    Begin
    OUT_OUT_ID_NO_NO:=SEQ_NUMBERS.NextVal;
    INSERT INTO T_MYTABLE (ID_NO)
    VALUES (OUT_ID_NO);
    COMMIT;
    end P_MYTABLE_CREATE_ENTRY;

    Any help appreciated... Cheers!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Instead of a procedure, make it a function ...

    SELECT SEQ_NUMBERS.NextVal
    INTO OUT_ID_NO
    FROM DUAL;

    RETURN(OUT_ID_NO);

    I usually do this with a before insert trigger ...

    HTH
    Gregg

  3. #3
    Join Date
    Feb 2005
    Posts
    2

    Workin fine!

    Cheers! that works fine!

Posting Permissions

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