ORA-02287
From Oracle FAQ
ORA-02287: sequence number not allowed here
[edit]
What causes this error?
An ORA-02287 occurs when you use a sequence where it is not allowed. The usage of a sequence is limited and it can be used only in few areas of PL/SQL and SQL coding.
[edit]
Where can one use sequences
The following are the cases where you can't use a sequence:
For a SELECT Statement:
- In a WHERE clause
- In a GROUP BY or ORDER BY clause
- In a DISTINCT clause
- Along with a UNION or INTERSECT or MINUS
- In a sub-query
Other areas:
- A sub-query of Update or Delete
- In a View or snapshot
- In a DEFAULT or CHECK Condition of a table definition
- Within a single SQL statement that uses CURVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.
[edit]
How to fix it
If your statement fits one of the above said conditions, then you would see this error being raised. I have some possible solutions:
- If you need the sequence value for a WHERE clause or a sub-query or order by, then fetch the sequence value into a variable and use the variable in the necessary places.
- If you want the sequence value to be inserted into the column for every row created, then create a before insert trigger and fetch the sequence value in the trigger and assign it to the column
- If you want to use a sequence in a view, then first create the view with all other columns and conditions without the sequence and then use the sequence when you actually type in "select a, b, c from view".
- If you want to use a sequence in a select query with distinct clause, then you can use distinct in sub query and sequence.nextval in main query as follows -
insert into av_temp(col1, col2) select t.col1, shift_allocation_seq.nextval from (select distinct col1 from av_temp1) t;

