-
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 03:14 PM.
-
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.
-
Thanks, but how I will insert into empty table with the start/end range.
Thanks
-
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.
-
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.
-
I advise you to think about how to get it to return the correct number of rows.
-
Try this in the where clause instead.
where rownum + &st_val -1 <= &end_val
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|