ORA-02287

From Oracle FAQ

ORA-02287: sequence number not allowed here

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.

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.

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;