Do we have sp_sequence_get_range equivalent in Oracle.
I need to advance my sequence by some numbers always how can I achieve this.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Just alter then increment the sequence.
For example, to increment by 1000:
ALTER SEQUENCE yoursequence INCREMENT BY 1000;
You then have to
NEXTVAL from the sequence and re-alter it to increment by the usual value to restore "normal" operation.
To check the current value without incrementing:
SELECT yoursequence.CURRVAL FROM DUAL;
If you need to decrease the sequence, just
ALTER SEQUENCE by a negative amount (very intuitive!).
since the Procedure structure in
EXEC sys.sp_sequence_get_range @sequence_name = ‘CyclingSequence’, @range_size = 3, @range_first_value = @firstValue OUTPUT, @range_last_value = @lastValue OUTPUT; SELECT FirstValue = CONVERT(INT, @firstValue), LastVlaue = CONVERT(INT, @lastValue), NextValue = NEXT VALUE FOR CyclingSequence GO
Then we can convert it to:
CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value;
Or you can make ORACLE function for example (I don't claim ownership of the function):
declare v_initial number := 1; temp number := 0; cmd varchar2(100); begin cmd := 'alter sequence SEQ increment by -1'; execute immediate cmd; while true loop cmd := 'select SEQ.nextval from dual'; execute immediate cmd; select last_number into temp from user_sequences where sequence_name = 'SEQ'; if temp = v_initial then exit; end if; end loop; end;