CODEWITHSUNDEEP
oracle-database
cracker_chan
cracker_chan

Reputation: 93

exception handling when sequence exceeds max value

Hi very much new in oracle, how do you catch and reset back the sequence number when it reaches max value:

Here is the sequence script:

CREATE SEQUENCE idctr_seq
  MINVALUE 1
  MAXVALUE 99
  START WITH 1
  INCREMENT BY 1
  CACHE 10;

Then here's the code in which it tries to reset the value to 1 when the sequence reaches max. If the code works, I'll later convert it to function, but it fails.

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 till it reaches max value, it doesn't produce an error, but it doesn't also reset itself back to 1.

Upvotes: 3

Views: 4768

Answers (2)

Ben
Ben

Reputation: 52863

This is a use case for the CYCLE keyword; to quote

indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

Your object declaration should become:

CREATE SEQUENCE idctr_seq
  MINVALUE 1
  MAXVALUE 99
  START WITH 1
  INCREMENT BY 1
  CACHE 10
  CYCLE;

Here's a demonstration with a smaller sequence

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

Upvotes: 5

Justin Cave
Justin Cave

Reputation: 231681

What you're trying to do doesn't make sense.

If you want your sequence to start again at 1 after it reaches 99, simply specify CYCLE when you create the sequence rather than the default of NOCYCLE

CREATE SEQUENCE idctr_seq
  MINVALUE 1
  MAXVALUE 99
  START WITH 1
  INCREMENT BY 1
  CACHE 10
  CYCLE;

Of course, there aren't a lot of cases where you really want a sequence to cycle-- normally, you'd declare the column to be large enough to handle however many values you could conceive of generating. A sequence can generate an almost unfathomably large number of values before it reaches its default maxvalue.

Upvotes: 2

Related Questions