Nesting ODI substitution method calls. Part 1.

Uli Bethke Oracle Data Integrator (ODI), Oracle Warehouse Builder

I am not getting around doing too much blogging on ODI these days. I have recently started a new project on OBIEE and ODI and it must be two years or so since I have last used OBIEE. I can tell you that there is a lot of catching up to do. Anyway, today's post is about why I love ODI and one of the features that makes it the best ETL tool on the planet.

First we will have a look at nesting ODI methods at the same parse level. This is fairly straightforward:

What we do here is create a procedure with just one step. For this procedure we create an Option SESSION_PARAM. We use this option to make a call to the getSession method. As value for SESSION_PARAM we could pass in SESS_NO or SESS_NAME.


We just use double quotes and the plus sign to append the second substitution method call. The first double quote is used to as part of getSession and the second pair is used to allow us make a nested call to the substitution API.

Note: The double minus -- is used to comment out the output of our API calls in the Oracle technology as otherwise we would throw an error.

Output of the above step is as follows ( I passed SESS_NO as the value of the option).


Next we look at some more interesting stuff and I’ll show you how you can nest calls to the substitution API at different levels of the ODI multi pass parser.

Recently I’ve had a requirement to create indexes on the fly for temporary interfaces. I was able to solve this by using the UD5 marker and nesting of ODI substitution calls.

We will first modify the IKM SQL Control Append and insert a step at the end of the knowledge module.


In the above step we are nesting calls to the API at two different levels. Calls at the <% %> level are always executed before calls to the API at level <? ?>. As you can see we are using the UD5 marker to filter out columns where the UD5 checkbox has been selected.

Note: Inside the delimiters we are using the Java BeanShell scripting language.

When we use the knowledge module in a temp interface, the result of the first parse pass at runtime is as follows:

This is then passed on to the next level where at runtime this results in:

Eventually this is executed by the Oracle technology as dynamic SQL. You need to be careful when you nest different parse levels with escaping quotes. I will deal with this in the next part of this series.

Once you get your head around this stuff you can literally meet any requirement that is thrown at you. What a great tool.

Don't you feel the love yourself?

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O'Reilly Scripting)

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.