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.
1. 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

Page:

ORA-02287: sequence number not allowed here

Browser not supported