Same seq_name.nextval in one request. ORACLE

Question

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

I have googled this and just can't get an answer.

To be clear, this is what I need, for example:

select seq.nextval as v1, seq.nextval as v2 from dual (I know this does not work)

I also tried UNION, I just can’t get around it.

Answer 1

All these are excellent answers, unfortunately, this was not enough. Will definitely return to this page when he will fight in the future.

I went the other way. I asked a new question and got an answer.

You can check it out here .

Answer 2

If you always need exactly two values, you can change the sequence to increase 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 really, but he has to do the job.

UPDATE

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

Answer 3

Frank's answer has a flaw:

You cannot use it in a transactional system because ALTER SEQUENCE executes any pending DML.

Sean's answer only pulls the sequence once. As far as I understand, you want to pull out two values. As an alternative to Seans solution, you can also choose twice from .nextval, since ORACLE gives you the same value twice.

I would rather complete the sequence in the procedure. This oracle trick draws a 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 it in general, you might like it:

 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; / 

Answer 4

There are some restrictions on the way you use NEXTVAL . There's a list in Oracle docs . Moreover, the link includes a list of conditions under which NEXTVAL will be called more than once.

The only script named on the page where the direct SELECT will call NEXTVAL more than once is in the "Top Level SELECT Statement". A rough request that will call NEXTVAL two times:

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

Unfortunately, if you try to insert this into a subquery to reduce the values ​​to a single row with columns v1 and v2 (as in your question), you will get ORA-02287: sequence number not allowed here .

It is as close as possible. If the above request does not help you get what you need, check out the other answers that were posted here. When I printed this, several excellent answers were sent.

Answer 5

Saying that you just call sequence.nextval several times will be boring, so here is 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. In this example, 7 numbers are sequentially drawn 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, since this is possible, you might want to remove numbers from the list before iterating (or, sometimes, the sequence cache can increase the numbers by more than 1).