CODEWITHSUNDEEP
oracle-database
Yogus
Yogus

Reputation: 2272

How to reset sequence number every last day of the ending year automatically?

Is there is any possibility to reset the sequence number every year automatically ? For example on 31 Dec the sequence is 3465, it should be 0001 on 1st January .

For now I reset it by below query manually 

select p_seq_name.nextval from dual;
alter sequence p_seq_name increment by -3465 minvalue 0;
select p_seq_name.nextval from dual;
alter sequence p_seq_name increment by 1 minvalue 0;

Thanks

Upvotes: 0

Views: 3281

Answers (2)

Md. Shamim Al Mamun
Md. Shamim Al Mamun

Reputation: 386

First you have to create a procedure which contain the SQL as you mention. Then you have to create a schedule job which will execute the procedure and this job will run once in a year.

CREATE OR REPLACE PROCEDURE RESET_SEQUENCE
IS
v_next_value number;
v_sequence_name varchar2(100):='SEQUENCE01';
begin
    execute immediate   'select ' || v_sequence_name || '.nextval from dual' INTO v_next_value;
    execute immediate   'alter sequence ' || v_sequence_name || ' increment by -' || v_next_value || ' minvalue 0';
    execute immediate  'select ' || v_sequence_name || '.nextval from dual' INTO v_next_value;
    execute immediate  'alter sequence ' || v_sequence_name || ' increment by 1 minvalue 0';
END RESET_SEQUENCE;
/



BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'RESET_SEQ_YEARLY'
      ,start_date      => TO_TIMESTAMP_TZ('2016/01/01 10:20:41.299669 Asia/Dacca','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=YEARLY;BYMONTH=DEC; BYMONTHDAY=31;BYHOUR=23;BYMINUTE=59;BYSECOND=59'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'begin
                                    RESET_SEQUENCE;
                                    end;'
      ,comments        => 'Last day of the year'
    );
   SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'RESET_SEQ_YEARLY');
END;
/

Upvotes: 4

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

You can create a SCHEDULER JOB like this:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'RESET_SEQUENCE'
      ,repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYMONTH=1;BYMONTHDAY=1;BYHOUR=0;BYMINUTE=0'
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN EXECUTE IMMEDIATE ''ALTER SEQUENCE p_seq_name INCREMENT BY -3465 MINVALUE 0''; END;'
        ,enabled         => TRUE;
    );
END;
/

This job will run every year January, 1st after Midnight. In order to run it on December, 31st use repeat_interval like

FREQ=YEARLY;INTERVAL=1;BYMONTH=12;BYMONTHDAY=31;BYHOUR=23;BYMINUTE=59

Upvotes: 0

Related Questions