ORA-02287: sequence number not allowed here
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:
- When using the INTEGRATION Semarchy for MDM template, as this uses Changed Data Capture (CDC) which issues a WHERE clause
- When using the INTEGRATION Oracle template and using the setting Useless Update Detection method.
- If this is set to "not exists", "minus" etc it also issues a WHERE clause
Solution
To work around this Oracle restriction:
- Make sure this target table attribute (mapped to a sequence) is not in the logical key for the target table
- Add an ignore TAG on the attribute
- Alternatively you can disable the Useless Update Detection method, but this may involve processing more data which is not necessarily required
Related articles