Resetting an ORACLE Sequence Generator 1

[weberm]

I have an ORACLE sequence generator that periodically has to be reset (it feeds into a fixed-length field of a COBOL app). I do this by running a script that drops the sequence and the creates it:

DROP SEQUENCE RCPTNUM_SEQ;

CREATE SEQUENCE RCPTNUM_SEQ
  START WITH 1
  MAXVALUE 99999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

My question is two-fold:
1) Is there a way to alter the sequence without dropping and recreating it?
2) Is there a way to create a stored procedure that performs this action?

 

[carp]

If the only problem is your numbers are getting too long, why not set maxvalue to 999 (assuming 3-digit maximum) and change nocycle to cycle? That way, when you get to your limit, it would just start over again by itself.

 

[Dagon]

I had to set the seqeunce to have a minvalue of 0 to get it to reset back to 1, but this works quite well otherwise.

drop sequence testseq;

create sequence testseq minvalue 0;

declare
  id number;
  last_id number;
begin
  for i in 1..1000 loop
    select testseq.nextval into id from dual;
  end loop;
  --
  select last_number
    into last_id
    from user_sequences
   where sequence_name = 'TESTSEQ';
   dbms_Output.put_line(last_id);
  --
  execute immediate 'alter sequence testseq increment by '||to_char((-1 * last_id)+1);
  select testseq.nextval into id from dual;
  execute immediate 'alter sequence testseq increment by 1';
end;

select last_number
    from user_sequences
   where sequence_name = 'TESTSEQ';

BTW, last_number is very confusingly named and actually means the next number the sequence will give you rather than the last one it did give you. I know this to my cost, having made an idiot of myself in the past by thinking it meant the opposite.

 

[weberm]

I wish we could do it that way, carp, but alas, my application cannot handle when a batch of receipt numbers "wraps around" during nightly processing. If there aren't enough numbers left in the sequence, we have to reset the counter and start from the beginning of the sequence.

 

[carp]

Ah - so if the sequence hits the wall the batch run fails, you recreate the sequence, and then rerun the batch?
If that is the case, then I rather like Dagon's approach (to the point of purple stardom!) - and I would just slip this snippet into your batch code so it always starts off with a fresh set of numbers. And it might help your batch run's performance if you set the increment by value to the full sequence capacity instead of just 1 (assuming the batch job is the only consumer of this sequence):

CREATE SEQUENCE testseq MAXVALUE 1000 INCREMENT BY 999 CACHE NOCYCLE;

This way, all of your numbers would be cached and it would eliminate sequence maintenance during your run.

 

[Dagon]

Carp,

Don't you mean you want to set the cache value to 999 rather than the increment? As it stands, you'll only get two values before the sequence runs out - 1 and 999.

 

[Dagon]

Or 1 and 1000, I should say.

 

[carp]

Absolutely right, Dagon. My mistake - I hate it when Mondays come on Friday!