How Substitution API works !?

Introduction

This post devotes to Oracle Data Integrator substitution API.  I was inspired for writing this post by one community thread. At first sights it looks very simple, but all obvious answers doesn’t work. I tried to research how substitution API really works. There is a result of my researching and thinking.

How does substitution API works?

Substitution API simply replaces java code fragments by result of code execution. For example , if we want to get value of previous step log messages into the Variable value:

Before execution this step ODI calculates previous step message and inserts result of function call. You can read more there: https://docs.oracle.com/cd/E29542_01/integrate.1111/e12645/api_intro.htm#ODIKD890

There are two types of substitution API functions:
1) functions which calculates at package “compile “ stage (before interface or package execution)
2) functions which calculates during step execution: for example  odiRef.getPrevStepLog(), this function know nothing about previous step result until step not  finished (successful or not). 

What’s about our simple code?  Why it doesn’t work?
   String t = "<%=odiRef.getFrom().replace((char)34,(char)20) %>”;

It has to replace double quotes on spaces in the source table name. But we have errors on “compilation” stage.

It is impossible to get such error, because our function has right syntax, and we don’t call snpRef.getObjectName. Then I’ve got it, may be ODI doesn’t call odiRef function? May be it replaces it by string and then only calculates value … I tried to dump  odiRef call into file.

<%
PrintWriter writer = new PrintWriter("c:\\temp\\subst.txt", "UTF-8");
writer.println(odiRef.getFrom());
writer.close(); %>

Obviously we had to get source table in the subst.txt, but I got:
<?=snpRef.getObjectName("L", "%COL_PRF0TEST", "ORATEST", "W")?>

What is it??? It is a string which contains call of snpRef.getObjectName.  Now we know what happening and why we got error and we can reproduce compilation stages.
1. Stage - code precompilation
odiRef.getFrom().replace((char)34,(char)20);
ODI replace odiRef.getFrom() by STRING  and we get new code row.
“<?=snpRef.getObjectName("L", "%COL_PRF0TEST", "ORATEST", "W")?>”.replace((char)34,(char)20)
2. Stage - code compilation
ODI executes replace function and we get result row:
“<?=snpRef.getObjectName( L , %COL_PRF0TEST ,  ORATEST ,  W )?>”.replace((char)34,(char)20)
3.Stage - code execution
ODI executes <?=snpRef.getObjectName(…)?> , but we have already removed all double quotes,  and therefore we get SYNTAX error on this stage!!!

Now we know reasons of this error, but how to solve it???

Solution

Solution goes from definition of substitution API, result is always string, we have to put replace function into the string and on the stage 2 we have to get this string:

“<?=snpRef.getObjectName( L , %COL_PRF0TEST ,  ORATEST ,  W )?>.replace((char)34,(char)20)”

I used this trick : I replaced closed parent and tag “)?” with string with replace function

<@
String t="<%
String replaceStr=").replace((char)34,(char)20)?";
String replacedString=odiRef.getFrom().replace(")?",replaceStr);
out.print(replacedString);
%>";
@>

Final  Test

We solve our problem, but lets do final tests. I put this script in  KM task.

Script contains different method calls therefore we could see behavior of each method group.Lets try to generate scenario which contains interface with our KM. And what can we see??? File subst.txt was created during scenario generation. File contains next rows:

1
C1_C1, C2_C2
2
<?=snpRef.getInfo("DEST_USER_NAME") ?>
3
<?=snpRef.getCatalogName() ?>
4
<?=snpRef.getPrevStepLog("MESSAGE") ?>
5
<?=TESTsnpRef.getObjectName("L", "%COL_PRF0TEST", "ORATEST", "W")?>We could see that only first method was returns end value on compilation stage, all other methods replaced by snpRef instance calls

Conclusion

Substitution API is not that we see) it makes many transformations before execution, odiRef instances executes only on compilation stage and than replaces by string with snpRef instance call.  Therefore we need to be careful  when using such tricks like in my post. 

P.S. 

All my researches were done with ODI 11.1.1.7 and 11.1.1.9