From Oracle FAQ
Jump to: navigation, search

ORA-02287: sequence number not allowed here

What causes this error?[edit]

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[edit]

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[edit]

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.