NEXTVAL sequence
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: NEXTVAL sequence

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    NEXTVAL sequence

    Hi,
    I have a requirement that I need to populate table of one column and this column is ser_no

    based on the below sequence start and end value how I can populate/insert all the seq range into table of col ser_no.

    create sequence batch
    start with &st_val
    increment by 1
    maxvalue &end_val;

    Is there way using sql*plus? or I have to use pl/sql for/while loop?
    Last edited by aph; 03-24-2004 at 02:14 PM.

  2. #2
    Join Date
    Feb 2004
    Posts
    77
    just use seq.nextval in update.

    or

    you can write a function that returns nextval of the sequence and use that function value to update srno.

  3. #3
    Join Date
    Jul 2001
    Posts
    334
    Thanks, but how I will insert into empty table with the start/end range.

    Thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about ...
    Code:
    insert into
       my_table
    select
       &st_value+rownum-1
    from
       all_objects
    where
       rownum <= &end_val
    do a 'select count(*) from all_objects' first to makesure there's enough rows in there.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jul 2001
    Posts
    334
    slimdave,
    it works, but let's say my starting val = 100 and end val = 110.
    Now instead it create 11 values from 100 .....110, it create 110 numbers. Please advise Thanks.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I advise you to think about how to get it to return the correct number of rows.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2004
    Posts
    77
    Try this in the where clause instead.

    where rownum + &st_val -1 <= &end_val

  8. #8
    Join Date
    Jul 2001
    Posts
    334
    oracle_faq & slimdave,
    Did I say before how wonderful you are? if not then you are the man(both).

    Thanks and I really appreciate of your professional approach.

Posting Permissions

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



Click Here to Expand Forum to Full Width