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).
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) %>”;
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(); %>
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")?>
<?=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!!!
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
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);
%>";
@>
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.
No comments:
Post a Comment