Reset sequence every year

Question

Reset sequence every year

Navigation

#1 by (1 votes)

0

I'm creating an identifier that has the following sequence/ano formatting, which is why I need my sequence to count from 1, again, whenever there is a year turnaround. Is there any way to do this automatically or will I have to drop and re-create the sequence every year manually?

sql oracle

asked by anonymous 05.06.2018 / 21:44

1 answer

Account with data and mysql data Overlapping content on page

score 1 · Accepted Answer

In versions lower than 12 you need to set the increment property to the current value only negative and call the nextval to zero the current one and return the increment to the pattern you use, thus "zeroing" the sequence, eg

declare
  ValorAtual integer;
begin
  select Seq_teste.NEXTVAL * -1 into ValorAtual from dual;
  execute immediate 'ALTER SEQUENCE Seq_teste INCREMENT BY '||to_Char(ValorAtual) || ' minvalue 0';
  execute immediate 'SELECT Seq_teste.NEXTVAL FROM dual' into ValorAtual;
  execute immediate 'ALTER SEQUENCE Seq_teste INCREMENT BY 1';
end;

In version 12 there is a command to reset the sequences:

alter sequence Seq_teste restart start with 1;

To automate this process, you can create a procedure with the required script and create a scheduled job to run at every turn of the year.