title: exception handling when sequence exceeds max value
body: <p>Hi very much new in oracle, how do you catch and reset back the sequence number when it reaches max value:</p>
  
  <p>Here is the sequence script:</p>
  
  <pre><code>CREATE SEQUENCE idctr_seq
    MINVALUE 1
    MAXVALUE 99
    START WITH 1
    INCREMENT BY 1
    CACHE 10;
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>