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. It also happens when you are using it wrong in an sql statement like: .NextVal() instead of .NextVal (notice the missing brackets)

[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.)

• If you want to use a sequence in an insert query, remember it's not like in pl/sql (at least upto 10g). You mustn't specify the brackets. Might be obvious to some people but I bumped into it a few times and spent quite some time googling until I figured it out.

insert into table (id, blabla) values (generator.NextVal, blabla) <- notice NO brackets.