Reset sequence every year

Asked

Viewed 60 times

0

I am creating an identifier that has the following formatting sequence/ano, for this reason I need my Quence count to 1, again, whenever there is a turn of the year. Is there any way to do this automatically or will I have to drop and recreate the sequence every year manually?

sql oracle

  • Without you showing a snippet of code or anything like that it gets very complicated to help you.

  • But I have no doubt in a code that I’m creating, I want to know if there’s any way I can reset a number from what it is for 1 every time I turn the year...

  • 1

    Have some ideas here. They are in English but it seems that there is no answer 100%

  • 1

    Be careful when using sequences as they ensure uniqueness but not integrity , if a numbering cannot fail they are not recommended.

  • I don’t need integrity, just oneness anyway.

1 answer

1


In versions less than 12 you need to set the increment property with the current value only negative and call nextval to reset the current and return the increment to the pattern you use, thus "zeroing" to Quence, example: 

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 sequences: 

alter sequence Seq_teste restart start with 1;

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

  • But then it wouldn’t be easier to create a process that simply drops and re-creates this Quence every year?

  • 1

    Yeah, I just gave you an option besides dropping and recreating Quence, but the level of complexity would be the same. To automate, in pure PLSQL would not have much to escape from Jobs.

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.