Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: sequences, updates, last val

    When I update a customer table with data I use a sequence to get the next customer number I have additional updates that I'd like to make using the same customer just created. How can I return the last seq number used? Here is and example...

    Code:
    -- begin inserting test values
    INSERT INTO TMOSER.CZCS_CUSTOMERS
          (CUSTID,
           CUSTOMER_NAME,
           LOGO_PATH,
           NPC,
           UPDATE_DATE,
           CREATE_DATE,
           UPDATE_USERID)
    VALUES
          (custid.nextval,
           'Tims PC repair',
           'logos/90001.gif',
           'TIMS',
           SYSDATE,
           SYSDATE,
           1);
    
    INSERT INTO TMOSER.CZCS_CUSTOMER_OPTIONS
          (CUSTID,
           OPTIONID,
           OPTION_CHOICE,
           UPDATE_DATE,
           CREATE_DATE,
           UPDATE_USERID)
    VALUES
          (THIS IS WHERE THE ID WILL GO,
           109,
           'Yes',
           SYSDATE,
           SYSDATE,
           23061 )

    Thanks in advance for your help.

  2. #2
    Join Date
    Jan 2003
    Posts
    67

    well...

    I don't like it but I did the obvious; I did a select after the first insert. Anyone have a better idea (w/o a sp if possible)
    Thanks

  3. #3
    Join Date
    Sep 2003
    Posts
    8

    Hi,timmoser

    You may use Currentval to make it,like following,
    INSERT INTO TMOSER.CZCS_CUSTOMERS
    (CUSTID,
    CUSTOMER_NAME,
    LOGO_PATH,
    NPC,
    UPDATE_DATE,
    CREATE_DATE,
    UPDATE_USERID)
    VALUES
    (custid.nextval,
    'Tims PC repair',
    'logos/90001.gif',
    'TIMS',
    SYSDATE,
    SYSDATE,
    1);

    INSERT INTO TMOSER.CZCS_CUSTOMER_OPTIONS
    (CUSTID,
    OPTIONID,
    OPTION_CHOICE,
    UPDATE_DATE,
    CREATE_DATE,
    UPDATE_USERID)
    VALUES
    (custid.currentval, -- It refers to the custid that created above
    109,
    'Yes',
    SYSDATE,
    SYSDATE,
    23061 )

  4. #4
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    Re: sequences, updates, last val

    Oracle always provides you with the ID number of the record you inserted. It's up to you to catch it and use it. For example,
    DECLARE
    V_NEWID NUMBER;
    BEGIN
    INSERT INTO BOOBEAR (ID, COMMENTS)
    VALUES (MYSEQ.NEXTVAL, 'HIMOM')
    RETURNING :NEW.ID INTO V_NEWID;
    INSERT INTO FRUFRU (FKID, COMMENTS)
    VALUES (V_NEWID, 'NOW YOU KNOW);
    END;
    /

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    the are two ways :
    1. using the currval expression (this is a correction to the reply of xjug). the code would be :
    INSERT INTO TMOSER.CZCS_CUSTOMER_OPTIONS
    (CUSTID,
    OPTIONID,
    OPTION_CHOICE,
    UPDATE_DATE,
    CREATE_DATE,
    UPDATE_USERID)
    VALUES
    (custid.currval, -- It refers to the custid that created above
    109,
    'Yes',
    SYSDATE,
    SYSDATE,
    23061 )
    The currval expression returns the current value of the sequence. This only works if the nextval is requested before. The only danger here is when somebody else requests the nextval from the sequence at the same time only a millisecond later, before you competed your insert. This might cause the problem of using the wrong value.

    option 2.
    use a cursor to select the value from dual ( select cust_id.nextval from dual) and store it in a local variable. This way the truly used value is stored and you can use it in both insert statement.

    Good luck.
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Don't use currval

    Do not use the custid.currval in the second insert statement because if there is nother transaction that has just done the first insert you will get that value.

    So why can't you get the currid.nextval in a temparary variable and use that variable in both insert statements.The statement for doing that is

    SELECT custid.nextval FROM DUAL

    Using the above assign it into a temporary number variable and then use that in the insert statements. In the same manner you can get the currval also if you need it for something else.

    Cheers,
    Suren.

  7. #7
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    Re: Don't use currval

    I agree you should not use currval. It returns the wrong number compared to what you want to do. Nextval shows you the next available number, where currval shows the last number someone (maybe you, maybe not) was issued. There is no way to ensure currval shows YOUR last number.

    in the prior example you should add the INTO clause. for example, SELECT custid.nextval INTO v_myid FROM DUAL;

    this is still not as fast and the inline method I posted previously. however, the inline method has a drawback to be considered. You might want to check the value (i.e., maybe you want to see if there is already a record with that ID number to avoid throwing an exception.) Pulling the ID from a sequence into a variable allows you to do processing, whereas the inline method is faster but does not allow processing.

    you might be thinking now that sometimes you should use one method, and sometimes another method. this would be correct, but complicated to maintain. this is why so many people set their IDs using triggers. It is a standard way to set IDs, and it allows maximum processing of data directly in the database where it will run faster than external code (faster than processing the IDs in C or VB, for example.)

    -Mark

Posting Permissions

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