sequence - editing is buggy

[Bernhard S]

Hello,

when editing the start value of a sequence, the created statements for that doesn't make sense and won't work either.

I have created a sequence:

create sequence my_sequence
minvalue 1
maxvalue 9999999999999
start with 1
increment by 1;

I count up that sequence to 6.

select * from user_sequences shows:

SEQUENCE_NAME MY_SEQUENCE
MIN_VALUE 1
MAX_VALUE 9999999999999
INCREMENT_BY 1
CYCLE_FLAG N
ORDER_FLAG N
CACHE_SIZE 20
LAST_NUMBER 21

current values of sequence is 6 :
select my_sequence.currval from dual;
=> 6

Now when I use pl/sql developer sequence - edit to change the sequence I see there
Next Number: 21
Increment by: 1
Cache size: 20

"Next Number" here is not the next number you would see in the sequence. It's just taken from "last number" in user_sequences and just that high because of the cache.

Changing next number now to 1 and viewing the SQL I get:

-- Modify the last number
alter sequence MY_SEQUENCE increment by -20 nocache;
select MY_SEQUENCE.nextval from dual;
alter sequence MY_SEQUENCE increment by 1 nocache;
declare
LastValue integer;
begin
loop
select MY_SEQUENCE.currval into LastValue from dual;
exit when LastValue >= 1 - 1;
select MY_SEQUENCE.nextval into LastValue from dual;
end loop;
end;
/
alter sequence MY_SEQUENCE increment by 1 cache 20;
--------------------------------------------------
When I try to execute that, I get:
ORA-08004: sequence MY_SEQUENCE.NEXTVAL goes below MINVALUE and cannot be instantiated
select MY_SEQUENCE.NEXTVAL from dual
--------------------------------------------------

Why do you do a change of sequence that complicated and not working anyway?

In the Oracle docu Oracle

 

[Marco Kalter]

Why don't you just do that and follow the Oracle docu to change the sequence?

If you drop and recreate a sequence, all objects that refer to the sequence will be invalidated, and all privileges must be restored.

We'll look into the issue though.

 

[Gustavo]

That's an interesting approach, but we make it a lot different:
- make it CYCLEable
- maxvalue = currval
- increment 1
- restore cycle/nocycle and maxvalue.

I think there are more steps, but you got the point.