Invoking an select script through ODI -Cons and error logging

Hello All,

I am wondering about below 2 options:

Option 1:

I have select script handy with me which fetch data by joining many source tables and performs some transformations like aggregations (group by), data conversion, sub-string etc.

Can I invoke this script through ODI mapping and return results (transformed data output) can be inserted into target of ODI mapping ?


Convert the select script into equivalent ODI mapping by using equivalent ODI transformations , functions , look ups etc and use various tables (tables in join clause) as source of mappings.

Basically develop ODI mapping which is equivalent to provided select script plus a target table to insert records into it.

I need to know pros and cons of both options in above (if option 1 is possible).

Is it still possible to track transformation errors, join source tables and where clause condition related errors etc through ODI with option 1?

Log file for mapping failure will have as granular level details as offered by option 2?

Can I still enable Flow Control at Knowledge Module and redirect select script errors into E$_ error tables provided by ODI?



posted on 08 Apr 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds


Is ODI - Oracle data integrator ?

General good practice is to push down the load to the DB Server whenever possible. With that said, since all of your source data in a DB (homogeneous) and i assume your target is the same DB as well. For this instance, I would strongly advise option-1, SQL is easier to write, maintain and migrate than an ETL mapping. And its much easier to check for errors and then handle it in SQL itself (eg. check for ISNULL and replace with "NA")

Use ETL mappings when your sources are Heterogeneous. (multiple discrete file sources)


Thanks npack, 

Yes ODI is -Oracle Data Integrator.

May I know about a bit more?

What about data lineage offered by ETL tool like ODI, I guess that benefit will be compromised?

Also I am wondering if I would be able to get same level of detail in ODI log files as with option 2, do you think that option 1 will display limited details of error as  compare to those offered by an ETL tool like ODI.

Also what is your opinion , if it is wisely option to use ETL tool just as script invoker?

Is it still worth to spend money for ETL tool when most of logic is part of SQL script?




Its fastest when you do it in SQL. Period!

  • Lets get facts straight - Data Lineage is good to have, but it has zero return on investment from value perspective
  • Detailed Logs are good - But a seasoned SQL developer can tell you what went wrong with just a one-line error message the db throws
  • Just because you purchased an ETL tool, you don't want to do everything in ETL to justify the costs (that's a bias and its called sunk cost fallacy)


Thank you !!!