Scenario:
Sometimes when ODI interface is run, we may encounter an error like below
Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
If we check the code, it generates a code like below
select 1 from APPS.MTH_ORGANIZATIONS_L T
where
and ((T.ORGANIZATION_NAME = S.ORGANIZATION_NAME) )
here after WHERE there should have been (1=1) followed by AND condition.
Reason:
This occurs if none of the target columns have been defined as a key.
Solution:
Make one of the columns in the target table as Key by check the box. Now run the interface again. It should solve the issue.
I would like to add a small remark to the reason. In my case, there was a key defined on the target table, but this key was not in any way related to the source. In other words, there was no match between the update key in the interface and a key in one of the source tables.
ReplyDeleteBy changing the update key on the target table to a key that matched with a key on one of the source tables, the interface run smoothly.