NEXTVAL sequence

[aph]

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?

[oracle_faq]

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.

[aph]

Thanks, but how I will insert into empty table with the start/end range.

Thanks

[slimdave]

How about ...

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.

[aph]

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.

[slimdave]

I advise you to think about how to get it to return the correct number of rows.

[oracle_faq]

Try this in the where clause instead.

where rownum + &st_val -1 <= &end_val

[aph]

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.