Question Re: Resetting a sequence in Technology Q&A

Resetting a sequence

Former Member — Thu, 18 Sep 2014 13:31:30 GMT

Hi,

Is there any simple way to reset a HDBSEQUENCE?

Suppose I have a sequence whose range is 1-100.

In a procedure I need to reset the sequence and use it but for other procedures I need to just use NEXTVAL.

I have seen the syntax of create sequence with RESET BY option, but I want to just reset the sequence to its initial value .

Regards

S.Srivatsan

Re: Resetting a sequence

Former Member — Fri, 19 Sep 2014 16:10:25 GMT

Have you tried:

ALTER SEQUENCE seq RESTART WITH 1; ?

Cheers,

Fernando

Re: Resetting a sequence

lbreddemann — Mon, 22 Sep 2014 14:24:29 GMT


Srivatsan Sundaravaradan wrote:

In a procedure  I need to reset the sequence and use it but for other procedures I need to just use NEXTVAL.

That's a design bug in your application.

If you need independent sequence numbers then use different sequences.

Remember, your code does not have control over the sequence. Any other process might request a new number any time. Resetting it at runtime is plain wrong.

- Lars

Re: Resetting a sequence

Former Member — Mon, 22 Sep 2014 15:01:11 GMT

Hi @ Fernando,

The Alter Sequence is not supported within the procedure.

@ Lars :

I knew it is not a good design but still want to know how to do it...

Thanks

Srivatsan

Re: Resetting a sequence

lbreddemann — Mon, 22 Sep 2014 15:17:20 GMT

I didn't write that it was bad design. I wrote it is a bug

You can do it via EXECUTE IMMEDIATE 'ALTER SEQUENCE <seq. name> RESTART WITH <value>'; from within a stored procedure.

But it remains a bug.

- Lars