Same seq_name.nextval in one query. ORACLE

Question

how can you select the same sequence twice in one query?

I have googled this and just cant get an answer.

Just to be clear, this is what I need, example :

select seq.nextval as v1, seq.nextval as v2 from dual (I know that doesn't work)

I have tried UNION as well, Just cant get my head around it.

Answer 1

If you always need exactly two values, you could change the sequence to increment by 2:

alter sequence seq increment by 2;

and then select the current value and its successor:

select seq.nextval,
 seq.nextval + 1
 from dual;

Not pretty, but it should do the job.

UPDATE

Just to clarify: the ALTER SEQUENCE should be issued only once in a lifetime, not once per session!

Answer 2

These are all great answers, unfortunately it was just not enough. Will definitely be able to return to this page when struggling in the future.

I have gone with a different method. Asked a new question and got my answer.

You can go check it out here.

Answer 3

Telling you to just call sequence.nextval multiple times would be boring, so here's what you can try:

create type t_num_tab as table of number;

CREATE SEQUENCE SEQ_TEST
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 100
  NOORDER;

create or replace function get_seq_vals(i_num in number) return t_num_tab is 
    seq_tab t_num_tab;
begin

select SEQ_TEST.nextval
bulk collect into seq_tab
from all_objects
where rownum <= i_num;

return seq_tab;

end;

And you can use it as follows. This example is pulling 7 numbers at once from the sequence:

declare
    numbers t_num_tab;
    idx number;
begin
    -- this grabs x numbers at a time
    numbers := get_seq_vals(7);

    -- this just loops through the returned numbers
    -- to show the values
    idx := numbers.first;
    loop
        dbms_output.put_line(numbers(idx));
        idx := numbers.next(idx);
        exit when idx is null;
    end loop;
end;

Also note that I use "next" instead of first..last as its possible you may want to remove numbers from the list before iterating through (or, sometimes a sequence cache can results in numbers incrementing by more than 1).

Answer 4

There are some limitations on how NEXTVAL can be used. There's a list in the Oracle docs. More to the point, the link includes a list of conditions where NEXTVAL will be called more than once.

The only scenario named on the page where a straight SELECT will call NEXTVAL more than once is in "A top-level SELECT statement". A crude query that will call NEXTVAL twice is:

SELECT seq.NEXTVAL FROM (
  SELECT * FROM DUAL
  CONNECT BY LEVEL <= 2) -- Change the "2" to get more sequences

Unfortunately, if you try to push this into a subquery to flatten out the values to one row with columns v1 and v2 (like in your question), you'll get the ORA-02287: sequence number not allowed here.

This is as close as I could get. If the query above won't help you get where you want, then check out the other answers that have been posted here. As I've been typing this, a couple of excellent answers have been posted.

Answer 5

The Answer by Frank has a downside:

You cannot use it in transactional system, because the ALTER SEQUENCE commits any pending DML.

The Answer of Sean only pulls the sequence once. As I understand, you want to pull two values. As an alternative to Seans solution, you could also select two times from .nextval, due ORACLE gives you the same value twice.

I'd prefer wrapping up the sequence in a procedure. This tricks oracle to pulling the sequence twice.

CREATE or replace FUNCTION GetSeq return number as

nSeq NUMBER;

begin

select seq.nextval into nSeq from dual;

return nSeq;

end;
/

If you need this generically, maybe you'd like:

CREATE or replace FUNCTION GetSeq(spSeq in VARCHAR2) return number as

nSeq NUMBER;
v_sql long;

begin

v_sql:='select '||upper(spSeq)||'.nextval from dual';
execute immediate v_sql into nSeq;

return nSeq;

end;
/