0

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.

1

Just alter then increment the sequence.

For example, to increment by 1000:

ALTER SEQUENCE yoursequence INCREMENT BY 1000;

You then have to SELECT the 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!).

  • This way when I choose next value from my sequence it is always come by 1000 delta or increment value which I didn't want – Ashish Feb 9 '15 at 10:00
  • Edited. This is equivalent to marking a number of sequences as used, which is what sp_sequence_get_range appears to do. – Philᵀᴹ Feb 9 '15 at 10:12
  • sp_sequence_get_range it just provide you a range of number without altering ddl and without altering it's INCREMENT BY value, by using this it will ALTER INCREMENT BY value too – Ashish Feb 9 '15 at 10:21
  • Not possible in Oracle. You have to do what I suggest above, you've no choice but to use DDL. – Philᵀᴹ Feb 9 '15 at 10:37
  • Ok, one more question is like in multi-user environment will it work fine. I mean I am afraid what if there are lots of user accessing this system simultaneously, I believe these are chances that it will give some wrong value. What is the locking mechanism can I lock this whole procedure in Oracle like we do in SQL sp_get_applock – Ashish Feb 9 '15 at 10:52
0

since the Procedure structure in MSSQL is:

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;
  • What does "(I don't claim ownership of the function)" mean? Did you copy this from somewhere else? if yes, please add proper attribution. – ypercubeᵀᴹ Feb 9 at 17:06
  • @ypercubeᵀᴹ I have some notes to keep any issue that takes a lot of time for me to solve. I cant recall what is the source URL. – Ahmad Abuhasna Feb 10 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.