CODEWITHSUNDEEP
sqloracle-databasesequencereset
Robert Gabriel
Robert Gabriel

Reputation: 1190

How can I reset a sequence in Oracle to value 1 once at the beginning of the year?

I have a table for REPORTS.

Every time a report is validated I'm using a trigger to update the table REPORT_SERIAL_NUMBER using the sequence:

CREATE SEQUENCE  "SSM_ANDROID"."REPORTS_SERIAL_NUMBER_SEQ"  MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

Once per year, on the first report that gets validated I have to reset the sequence to 1.

I can use a cronjob on the server that runs on 1st of January every year. But, what it that server is down? I know, I am a bit pessimistic.

Another way I can do this is by checking every time for the date of the last report, if is not in the same year as SYSDATE then reset sequence. But, regarding performance, is not so suited.

Can it be done from the database automatically?

Thanks in advance for your time.

Upvotes: 1

Views: 373

Answers (1)

Mark Wagoner
Mark Wagoner

Reputation: 1769

You can schedule a database job using Oracle Scheduler to do it

http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034

Upvotes: 1

Related Questions