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 CURRVAL 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;

• If you want to use a sequence in a subquery, use a helper function that returns the next value of the sequence.

WITH org as (
   select org_id, org_id_parent, [other fields], package.function_to_get_next_ou_id ou_id
     from table@dblink
) select org_id, org_id_parent, ..., ou_id, prior ou_id ou_id_parent
    from org
 connect by prior org_id = org_id_parent
   start with org_id_parent IS NULL;

(used this for a migration script, the sequence trick was easy to not have to 'remember' the target ou_id's for the source org_id's.)