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.

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