Page Comparison

...

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

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

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

The following are the cases where you can't 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 DI 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

...

Panel
bgColor #fff
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

...