Oracle SQL: adding the same sequence number to the rows of a query

Question

I am sending to a downstream application events and I need in each event a sequence number, as a control number that I don't have gaps, I don't miss sending events. What I am trying to achieve is having a db sequence, to have assigned to the results of a query the same sequence number. I don't want to use a procedure, function, etc, only some SQL.

For example I need a query result like this:

col1row1  col2row1 seq1
col1row2  col2row2 seq1
col1row3  col2row3 seq1
col1row4  col2row4 seq1

The problems I found is that sequence have restrictions is queries or that I have different values for seq1.

I tried this:

SELECT t.*, ROWNUM, 
CASE ROWNUM
  WHEN 1 THEN MY_SEQ.NEXTVAL
  ELSE MY_SEQ.CURRVAL
END as event_seq
from my_table t

or

SELECT t.*, ROWNUM, decode(rownum, 1, MY_SEQ.NEXTVAL, MY_SEQ.CURRVAL) from my_table t

and the sequence is defined:

CREATE SEQUENCE MY_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ;

'... that I don't have gaps...', be aware that it's not possible to avoid gaps in sequences... — wolφi
– wolφi, Commented Jul 11, 2014 at 15:07
The reasons I need no gaps is that the downstream system that receive messages will see and understand there were some exceptional conditions. Other than this situation, by setting NOCACHE, I will get each time one number, there will be no caching and I sould not have gaps. — user2673474
– user2673474, Commented Jul 14, 2014 at 13:46

Bob · Accepted Answer · 2014-07-11 14:24:47Z

1

If you are within the same session, you just just fill MY_SEQ.CURRVAL by making a call from dual with MY_SEQ.NEXTVAL.

select my_seq.nextval from dual;
select my_seq.currval, t.* from my_table t;

answered Jul 11, 2014 at 14:24

Bob

1,0658 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2673474 Over a year ago

Thanks Bob, but I wanted something similar with what you sent me, but in just one SQL call, not in two.

Collectives™ on Stack Overflow

Oracle SQL: adding the same sequence number to the rows of a query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related