I am using ODI 11g, with Oracle 11g, I have source, staging and target on same database but different schema.
I have an interface with two lookups into dimension tables.
When I run my interface I am getting below error.
Just wondering can we resolve it from ODI only, or Oracle DBA needs to change some settings.
I will prefer to resolve this from ODI only. Error* ODI-1228: Task 2 ODI_source_table (Integration) fails on the target ORACLE connection conn1. Caused By: java.sql.SQLException: ORA-25156: old style outer join (+) cannot be used with ANSI joins
I will really appreciate in advice on this..
Could you provide the SQL code generated by ODI for this step ?
I guess that the lookup is doing a join with new syntax (ANSI) while you have old joins ( + ) done in your ODI joins.
There is two way to fix that :
* You can exit your lookup properties and select "SQL Expression in the select clause" instead of "SQL left-outer join in the from clause".
* Or, for each join in your canvas, click on it, then at the bottom of the property inspector, tick the "Use ordered join syntax" and assign a different number for each join. It will use the new syntax (ANSI).
I suggest you to check the generated code before/after each change so you can understand the differences.
Hope it helps.
Thanks for your reply,
Actually I went into topology-->oracle--> properties there In SQL tab Under "not ordered" ,value for "outer keyword" was "(+)"
I first removed it, then it was adding word "NULL" after each join so getting error.
then I changed it to 1=1, Now I am OK.