Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Sequence Q. Why is this value the LAST_NUMBER

    Hi All. I've got a sequence: TBL_Owr_batch_Seq on the ID table of the corresponding table. It's all linked to an ASP page, where I want to do an insert into the tbl_owr_batch table. I then want to take the value of the ID column(which is generated by that sequence) and place the value in another table which corresponds to the recod. In terms of the insert, everything is fine. My problem is (the age old) if I do; select tbl_owr_batch_seq.nextval from dual; The num is incremented, and of course I cannot use currval until because I have to call nextval first to avoid the error. Well... I was doing some research and it looks like I can just do something like: (please correct me if i am wrong on this...)

    SELECT LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'TBL_OWR_BATCH_SEQ';

    and then subtract one, before moving that value into the other table. And here is where my question is. If I select the LAST_NUMBER of TBL_OWR_BATCH_SEQ from ALL_SEQUENCES, I get 242. However, the last number the sequence generated is 227. Take a look...

    Code:
    SQL> desc all_sequences;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SEQUENCE_OWNER                            NOT NULL VARCHAR2(30)
     SEQUENCE_NAME                             NOT NULL VARCHAR2(30)
     MIN_VALUE                                          NUMBER
     MAX_VALUE                                          NUMBER
     INCREMENT_BY                              NOT NULL NUMBER
     CYCLE_FLAG                                         VARCHAR2(1)
     ORDER_FLAG                                         VARCHAR2(1)
     CACHE_SIZE                                NOT NULL NUMBER
     LAST_NUMBER                               NOT NULL NUMBER
    
    SQL> SELECT LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'TBL_OWR_BATCH_SEQ';
    
    LAST_NUMBER
    -----------
            242
    
    SQL> SELECT MAX(ID) FROM TBL_OWR_BATCH;
    //this is the value the seq. last generated
       MAX(ID)
    ----------
           227
    
    SQL> SELECT TBL_OWR_BATCH_SEQ.NEXTVAL FROM DUAL;
    
       NEXTVAL
    ----------
           228         
    
    SQL>
    In short, how could 242 be the value of last number for this sequence? I just need a way of getting the current value of the sequence out of the table to move into the other table...is there a better way to do this?

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    What does the sequence increment by, and how many are cached?
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Quote Originally Posted by dbtoo2001
    What does the sequence increment by, and how many are cached?
    Sorry, I should have specified. The sequence increments by 1. However cached at 20.

    Code:
    SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
    ------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
    DB1                           TBL_OWR_BATCH_SEQ                     222 1.0000E+28            1 N N         20         242

  4. #4
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Quote Originally Posted by RhythmAddict
    Sorry, I should have specified. The sequence increments by 1. However cached at 20.

    Code:
    SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
    ------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
    DB1                           TBL_OWR_BATCH_SEQ                     222 1.0000E+28            1 N N         20         242

    Min_value + cache_size = last_number?
    222 + 20 = 242.

    Currently used value from the cache is 227, next value is 228.

    242 - 228 = 14 sequence values left to use, before it caches 20 more.

    http://download-west.oracle.com/docs...03sch.htm#1355
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  5. #5
    Join Date
    Dec 2003
    Posts
    148
    Thank you for the link, makes perfect sense...However, does this mean I can change the cache to 1 and do something like...

    SELECT LAST_NUMBER
    FROM ALL_SEQUENCES
    WHERE SEQUENCE NAME = 'TBL_OWR_BATCH_SEQ';

    (and some pseudo code..)

    CurrentValue = LAST_NUMBER - 1

  6. #6
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Quote Originally Posted by RhythmAddict
    Thank you for the link, makes perfect sense...However, does this mean I can change the cache to 1 and do something like...

    SELECT LAST_NUMBER
    FROM ALL_SEQUENCES
    WHERE SEQUENCE NAME = 'TBL_OWR_BATCH_SEQ';

    (and some pseudo code..)

    CurrentValue = LAST_NUMBER - 1
    That's not the way I would do it, is this multithreaded, may the sequence number get incremented by another web user? If you have the two inserts in the same procedure, stash the sequence into a variable and then reference the variable in both inserts. Otherwise, select the id value you just wrote from where you wrote it to (seems redundant doesn't it?)
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  7. #7
    Join Date
    Dec 2003
    Posts
    148
    Quote Originally Posted by dbtoo2001
    That's not the way I would do it, is this multithreaded, may the sequence number get incremented by another web user? If you have the two inserts in the same procedure, stash the sequence into a variable and then reference the variable in both inserts. Otherwise, select the id value you just wrote from where you wrote it to (seems redundant doesn't it?)
    Yeah, you're right - I realized a bit after that post that doing what I mentioned is essentially the same (in terms of silliness) as doing something like select Max(id) from tbl_owr_batch = X and then inserting X which is fundamentally flawed do simultaneous sessions. The simlpest approach as you mentioned is selecting the value I just inserted - the problem is the table doesn't have anything uniquely identifying the rows, like a timestamp (other than the ID col). This is due in part to the fact that the app is being converted over from a SQL Server environment where select @@identity was used. Perhaps I should just add a timestamp column to the table and call it a day...

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There is the RETURNING clause:

    insert into table1 ... returning id into v_id;

    But really your problems comes from having too much logic on the ASP side. If the inserts into both tables were in a stored procedure, you'd be OK.

Posting Permissions

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