Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Sequence created.

SQL> create sequence test_seq2 start with 10 increment by 10 cache 10;

Sequence created.

set serveroutput on
declare

ct number;
v_cycle varchar2(10);
v_order varchar2(10);
v_owner varchar2(30):=’LJCATT’;

begin

for seq in(select * from dba_sequences where sequence_owner=v_owner)
loop

ct :=seq.last_number+100;

if seq.cycle_flag = ‘N’ then
v_cycle :=’nocycle’;
else
v_cycle :=’cycle’;
end if;

if seq.order_flag = ‘N’ then
v_order :=’noorder’;
else
v_order :=’order’;
end if;

if seq.cache_size>0 then

dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.sequence_name||’ start with
‘||
ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxvalue
‘||seq.max_value||
‘ ‘||v_cycle||’ ‘|| v_order ||’ cache ‘||seq.cache_size||’;’);
else
dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.sequence_name||’ start with
‘||
ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxvalue
‘||seq.max_value||
‘ ‘||v_cycle||’ ‘|| v_order ||’ nocache;’);
end if;
end loop;
end;
/

SQL> set serveroutput on
SQL> declare
2
3 ct number;
4 v_cycle varchar2(10);
5 v_order varchar2(10);
6 v_owner varchar2(30):=’LJCATT’;
7
8 begin
9
10 for seq in(select * from dba_sequences where sequence_owner=v_owner)
11 loop
12
13 ct :=seq.last_number+100;
14
15 if seq.cycle_flag = ‘N’ then
16 v_cycle :=’nocycle’;
17 else
18 v_cycle :=’cycle’;
19 end if;
20
21 if seq.order_flag = ‘N’ then
22 v_order :=’noorder’;
23 else
24 v_order :=’order’;
25 end if;
26
27 if seq.cache_size>0 then
28
29 dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.seque
nce_name||’ start with
30 ‘||
31 ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxv
alue
32 ‘||seq.max_value||
33 ‘ ‘||v_cycle||’ ‘|| v_order ||’ cache ‘||seq.cache_size||’;’);
34 else
35 dbms_output.put_line(‘create sequence ‘||seq.sequence_owner||’.’||seq.sequ
ence_name||’ start with
36 ‘||
37 ct||’ increment by ‘||seq.increment_by||’ minvalue ‘||seq.min_value||’ maxv
alue
38 ‘||seq.max_value||
39 ‘ ‘||v_cycle||’ ‘|| v_order ||’ nocache;’);
40 end if;
41 end loop;
42 end;
43 /
create sequence LJCATT.TEST_SEQ2 start with
110 increment by 10 minvalue 1
maxvalue
999999999999999999999999999 nocycle noorder cache 10;
create sequence LJCATT.TEST_SEQ start with
101 increment by 1 minvalue 1
maxvalue
999999999999999999999999999 nocycle noorder nocache;

PL/SQL procedure successfully completed.