Nesting ODI substitution method calls. Part 1.
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
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.
|
1 |
--<%=odiRef.getSession("" + odiRef.getOption("SESSION_PARAM") + "")%> |
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.
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.
|
1 2 3 4 5 6 7 |
<? i=0; ?> BEGIN <%=odiRef.getColList("t", "tEXECUTE IMMEDIATE 'CREATE INDEX IDX_" + odiRef.getInfo("TARG_NAME") + "_" + "<? i=i+1;out.print(i);?>" + " ON " + odiRef.getInfo("TARG_NAME") + "([COL_NAME])" + "'", ";n", ";","UD5")%> END; |
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.
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:
|
1 2 3 4 5 6 7 8 |
<? i=0; ?> BEGIN EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(PROD_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(CUST_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(TIME_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(CHANNEL_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(PROMO_ID)'; END; |
This is then passed on to the next level where at runtime this results in:
|
1 2 3 4 5 6 7 |
BEGIN EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_1 ON SALES_TARGET(PROD_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_2 ON SALES_TARGET(CUST_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_3 ON SALES_TARGET(TIME_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_4 ON SALES_TARGET(CHANNEL_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_5 ON SALES_TARGET(PROMO_ID)'; END; |
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
Jython
The Definitive Guide to Jython: Python for the Java Platform.
Jython Essentials (O’Reilly Scripting)
Uli Bethke
Uli has been rocking the data world since 2001. As the CEO of Sonra, the data liberation company, he’s on a mission to set data free. A self-proclaimed data evangelist, Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker. Uli is a co-founder of DAMA Ireland, helping build a thriving community of data enthusiasts, and a proud Snowflake Data Superhero, leading the charge as the chapter lead of the Snowflake User Group Ireland. Whether sharing insights, empowering others, or championing the coolest data tools, Uli makes data fun. Growing up in Germany and making Ireland his home, Uli brings a global perspective to everything he does. On the academic side, he holds degrees in political science from Freie Universität Berlin, Albert Ludwigs Universität Freiburg, and the University of Ulster, Coleraine, adding a unique interdisciplinary flair to his work.
