Exception handling when sequence exceeds maximum
Hi, very much new in oracle, how do you catch and reset return the ordinal when it reaches its maximum value:
Here is the script sequence:
CREATE SEQUENCE idctr_seq
MINVALUE 1
MAXVALUE 99
START WITH 1
INCREMENT BY 1
CACHE 10;
Then here's the code where it tries to reset the value to 1 when the sequence reaches max. If the code works, I will convert it to a function later, but it doesn't work.
declare
seq_num number;
exceed_maxvalue exception;
pragma exception_init(exceed_maxvalue,-8004);
begin
seq_num := idctr_seq.nextval;
DBMS_OUTPUT.PUT_LINE(seq_num);
exception
when exceed_maxvalue then
execute immediate 'sequence idctr_seq increment by -99 minvalue 0';
execute immediate 'select idctr_seq.nextval from dual';
execute immediate 'alter sequence idctr_seq increment by 1 minvalue 0';
end;
If I run the code before reaching the maximum value, it does not throw an error, but not reset itself goes back to 1.
source to share
This is a use case for the CYCLE keyword ; to quote
indicate that the sequence continues to generate values after reaching the maximum or minimum value. After the ascending sequence reaches its maximum value, it generates its minimum value. After the descending sequence reaches its minimum, it generates its maximum value.
Your object declaration should look like this:
CREATE SEQUENCE idctr_seq
MINVALUE 1
MAXVALUE 99
START WITH 1
INCREMENT BY 1
CACHE 10
CYCLE;
Here's a demo with less consistency
SQL> create sequence test_seq start with 1 maxvalue 3 nocache cycle;
Sequence created.
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
1
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
2
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
3
SQL> select test_seq.nextval from dual;
NEXTVAL
----------
1
source to share
What you are trying to do doesn't make sense.
If you want your sequence to start again at 1 after reaching 99, just specify CYCLE
when creating the sequence, not the defaultNOCYCLE
CREATE SEQUENCE idctr_seq
MINVALUE 1
MAXVALUE 99
START WITH 1
INCREMENT BY 1
CACHE 10
CYCLE;
Of course, there aren't too many cases where you really want the sequence to be cyclical. Typically, you declare that the column is large enough to handle many of the values you might think of to generate. A sequence can generate an almost incredible number of values before it reaches the default maxvalue
.
source to share