What exactly is your business requirement here? Are you being asked to ensure there's no gaps? Or do you have a table with 15 rows (or other fixed number) and you always need the numbers from 1-N?
In either case, instead of trying to "loop through the sequence" you're probably better off:
- Generating all the numbers in your range
- Excluding from this the numbers in your table
- Inserting the result
For example:
create table t as
select rownum x, chr(rownum+64) y from dual connect by level <= 15;
delete t
where x in (4, 5);
insert into t
with rws as (
select rownum x, chr(rownum+79) y from dual connect by level <= 15
)
select x, y from rws
where not exists (select * from t where rws.x = t.x);
select * from t;
X Y
1 A
2 B
3 C
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
5 T
4 S
If for some reason you must use the sequence, then you could do something like:
- Find the first value with a gap after it
- Fetch values from the sequence until you hit this
create sequence s start with 1 increment by 1 maxvalue 15 cycle cache 2;
select s.nextval from t
where rownum <= 10;
NEXTVAL
1
2
3
4
5
6
7
8
9
10
delete t
where x in (4, 5);
declare
mx int;
seq_val int;
begin
select max(x) into mx from t
start with x = 1
connect by prior x = x - 1;
loop
seq_val := s.nextval;
exit when seq_val = mx;
end loop;
end;
/
insert into t values (s.nextval, 'Z');
select * from t;
X Y
1 A
2 B
3 C
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
4 Z