Tip Tuesday | Reverse Engineering Tables in Oracle ODI

When you are reverse engineering tables in Oracle ODI, especially from large systems like EBS, you can hit an issue where the reverse engineering operation is successful but the table you import has no columns.

This is caused by the access you have to the source system.  If you are accessing the source table via a synonym in a schema that does not own that table, ODI cannot access the table metadata.

ODI has a workaround which works in most cases: edit the Data Server in the Topology tab, go to the JDBC tab and add a new property with the key set to  "includeSynonyms" and the value set to "True".       

Save the data server and try the reverse engineering again.

(See the Oracle Support note:  ODI Reverse Engineering Does Not Function on Table Synonyms and DBLINK-ed Oracle Tables (Doc ID 423924.1))

Another use for these properties is when accessing Salesforce as a source.  These were essential to get the load running properly – in this case by switching off the Bulk fetch and getting it to refresh the schema on every load to keep everything in sync. 

For a full list of options see:  https://docs.progress.com/bundle/datadirect-salesforce-jdbc-60/page/Mapping-Properties.html