ORA-02287: sequence number not allowed here

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be easily reproduced by executing a non-sense query like:

create sequence test_seq;

select 1 as a
 from dual
order by test_seq.nextval;

ORA-02287: sequence number not allowed here

But we can also see that a query like the following is raising the same problem:

select 1 as A, test_seq.nextval as B
 from dual
union all
select 2 as A, test_seq.nextval as B
 from dual;

ORA-02287: sequence number not allowed here

Oracle doesn't like the fact that we are using the sequence in conjunction with a set operator like UNION ALL (or any other set operator of your liking...).

Note however that i can rewrite the query above in a slightly more convoluted way:

select a, test_seq.nextval as b
from (
 select 1 as a
   from dual
 union all
 select 2 as a
  from dual
 order by a);

A B
- -
1 1
2 2

I added the ORDER BY clause inside the query because you might want to spawn sequence numbers according to the ordering of some other column (either ascending or descending), so here is the correct way of achieving that result.
Please note that Oracle will raise ORA-02287 again if you put the ORDER BY clause in the outer query:

select a, test_seq.nextval as b
from (
 select 1 as a
   from dual
 union all
 select 2 as a
  from dual)
order by a;

ORA-02287: sequence number not allowed here

See message translations for ORA-02287 and search additional resources.