ORA-02287: sequence number not allowed here

Owned by Former user (Deleted)
Last updated: Mar 13, 2018 by Matthew Dahlman (Deactivated)

Problem

When using a sequence in some of the Semarchy Integrator templates to populate an Oracle table, the following error is relatively common:

SQL Exception Error : ORA-02287: sequence number not allowed here.

The usage of a sequence is limited. It can be used only in few areas of PL/SQL and SQL coding. It also happens when you are using it incorrectly in an sql statement like: .NextVal() instead of .NextVal (notice the missing brackets)

The following are the cases where you cannot use a sequence (in Oracle):

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.

http://www.orafaq.com/wiki/ORA-02287

Within in the Semarchy Integrator templates, there are a few areas that cause this issue:

  1. When using the INTEGRATION Semarchy for MDM template, as this uses Changed Data Capture (CDC) which issues a WHERE clause
  2. When using the INTEGRATION Oracle template and using the setting Useless Update Detection method.
    1. If this is set to "not exists", "minus" etc it also issues a WHERE clause

Solution


To work around this Oracle restriction:

  1. Make sure this target table attribute (mapped to a sequence) is not in the logical key for the target table
  2. Add an ignore TAG on the attribute
  3. Alternatively you can disable the Useless Update Detection method, but this may involve processing more data which is not necessarily required