Best strategy to reset Oracle sequence monthly

Question

I'm looking for the best strategy to reset a sequence which is used to generate unique keys. These keys are build using a prefix value, also generated by a combination of month and year values, like the following example:

2020060100000001 - Year(4d)Month(2d)Sequence(8d)

The sequence value must be restarted at the first second of each new month.

Is there any Oracle event that allows calling a function or procedure based in this situation to do this job?

Can anyone help me with opinions and experiences?

Thanks a lot! Rodrigo

That is one something normally done with oracle sequences. You could create a scheduled job to run on your required interval and either drop/re-create, or see here https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle — OldProgrammer, Jun 01 '20 at 19:00

score 1 · Accepted Answer · answered Jun 01 '20 at 19:08

If you really want to set the value of a sequence you can use something like the following:

PROCEDURE SET_SEQUENCE(pinSequence_owner IN VARCHAR2,
                       pinSequence_name  IN VARCHAR2,
                       pinNew_next_value IN NUMBER,
                       pinDebug          IN BOOLEAN := FALSE)
IS
  strSQL               VARCHAR2(4000);
  nNext_number         NUMBER;
  nOriginal_increment  NUMBER;
  nNew_nextval         NUMBER;
  nNew_last_number     NUMBER;
BEGIN
  strSQL := 'SELECT s.LAST_NUMBER, INCREMENT_BY ' ||
              'FROM DBA_SEQUENCES s ' ||
              'WHERE s.SEQUENCE_OWNER = ''' || pinSequence_owner || ''' AND ' ||
                    's.SEQUENCE_NAME = ''' || pinSequence_name || '''';
  EXECUTE IMMEDIATE strSQL INTO nNext_number, nOriginal_increment;

  -- Note that DBA_SEQUENCES.LAST_NUMBER represents the *next* number which will be
  -- returned by a call to NEXTVAL.

  IF pinNew_next_value NOT IN (nNext_number-1, nNext_number)
  THEN
    strSQL := 'ALTER SEQUENCE ' || pinSequence_owner || '.' || pinSequence_name ||
              ' INCREMENT BY ' || TO_CHAR(pinNew_next_value - nNext_number) || ' NOCACHE';
    EXECUTE IMMEDIATE strSQL;

    strSQL := 'SELECT ' || pinSequence_owner || '.' || pinSequence_name || '.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE strSQL INTO nNew_nextval;

    strSQL := 'ALTER SEQUENCE ' || pinSequence_owner || '.' || pinSequence_name ||
              ' INCREMENT BY ' || nOriginal_increment || ' NOCACHE';
    EXECUTE IMMEDIATE strSQL;

    strSQL := 'SELECT s.LAST_NUMBER FROM DBA_SEQUENCES s WHERE s.SEQUENCE_OWNER = ''' || pinSequence_owner ||
                ''' AND s.SEQUENCE_NAME = ''' || pinSequence_name || '''';
    EXECUTE IMMEDIATE strSQL INTO nNew_last_number;
  END IF;
END SET_SEQUENCE;

score 0 · Answer 2 · answered Jun 01 '20 at 19:03

0

Resetting sequence every month is not a valid approach. Basically you are violating the utilization of sequence. From your question i could understand, you wanted to append year and month to your column. In this case you can simple concatenate while inserting,

year||month||sequence_name.nextval

Eg: 2020||02||sequence_name.nextval

If you still wanted to reset, you can create a trigger to reset the sequence every month,

answered Jun 01 '20 at 19:03

Jim Macaulay

4,709
4
28
53

Reset it with a trigger? And what event causes said trigger to fire? No, it would be a scheduled job, not a trigger. – EdStevens Jun 01 '20 at 21:37
Trigger can be set up to execute with respect to Sysdate in Oracle. It does not require any scheduling process – Jim Macaulay Jun 02 '20 at 07:07
could you point me to the documentation? I'm not finding it in the 12.1 PL/SQL Language Reference. – EdStevens Jun 02 '20 at 14:00

Best strategy to reset Oracle sequence monthly

2 Answers2