Loading...

Tuesday, September 02, 2008

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.

1 comment:

Charlie L said...

Thank you so much!

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles