Posted by : Arjun Lagisetty Sunday, January 1, 2017

Here is a simple question: How to use Option values with in a substitution API?

In our design we created a procedure which look table name as option value (TABLE_NAME) and it created a DML statement around it. For this procedure to work we needed to use odiRef.getOption() function with in odiref.getTableName() function.

My first take on it was to write the following code

OK, here is my first take at the answer. HINT: This does not work.

<%= odiRef.getTableName("<%=odiRef.getOption("TABLE_NAME")%>") %>

Why does it not work?

As some of you are aware, Odi's procedure language is based on Java Bean Shell. It is interpreted i.e. the code is executed top down and left to right. So, the first function it encounters is getTableName function and it executes that first. At this point getOption function is not called yet. So the instead of receiving the option value, getTableName function receives entire string "odiRef.getOption("TABLE_NAME")" as input.

Typically in most languages code is executed once. Since ODI is based on java beanshell code, the interpreter takes multiple passes through the code. To understand multiple passes in detail, please refer to the ODI cookbook chapter 5.

To cut through the chase here is the final answer for the above problem.
<%= odiRef.getTableName("<?=odiRef.getOption("TABLE_NAME")?>") %>

Also, refer to this blog post by Uli Bethke at Sonra for another example on substitution passes. 

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post


Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty