Reset a Sequence according to the year not by min or max value

Question

Create a sequence which will reset according to the year. Consider the sequence with 9 digit starts with 000000001 and max is 999999999.

Let Date is 30/12/2017 and seq is 000012849 So when the date is 01/01/2018, I want the seq to be 000000001.

you have a similar question here - https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle — Sudipta Mondal, Jun 28 '18 at 07:48
What do you mean by "reset according to the year"? Do you want the sequence to reset on Jan1? — Kaushik Nayak, Jun 28 '18 at 07:50
@KaushikNayak yes, check the description again. I edited it. You will understand — soumyarjpanda, Jun 28 '18 at 09:48

MT0 · Accepted Answer · 2018-06-28T09:24:33.583

Create a recurring scheduled job that resets the sequence at midnight of 1st January each year.

Something like (assuming you have a YOUR_SCHEMA.RESET_ANNUAL_SEQUENCE procedure to perform the reset):

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name             => 'reset_annual_sequence_job',
    job_type             => 'PLSQL_BLOCK',
    job_action           => 'BEGIN your_schema.reset_annual_sequence; END;',
    start_date           => ADD_MONTHS( TRUNC( SYSTIMESTAMP, 'YY' ), 12 ),
    repeat_interval      => 'FREQ=YEARLY; BYDATE=0101;', 
    enabled              =>  TRUE,
    comments             => 'Annual sequence reset'
  );
END;
/

Reset a Sequence according to the year not by min or max value

1 Answers1

Linked

Related