ORA-02287

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

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
• Along with an ORDER BY clause

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.

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.