ODI 11g - Dynamic and Flexible Code Generation

ODI supports conditional branching at execution time in its code generation framework. This is a little used, little known, but very powerful capability - this let's one piece of template code behave dynamically based on a runtime variable's value for example. Generally knowledge module's are free of any variable dependency. Using variable's within a knowledge module for this kind of dynamic capability is a valid use case - definitely in the highly specialized area.

The example I will illustrate is much simpler - how to define a filter (based on mapping here) that may or may not be included depending on whether at runtime a certain value is defined for a variable. I define a variable V_COND, if I set this variable's value to 1, then I will include the filter condition 'EMP.SAL > 1' otherwise I will just use '1=1' as the filter condition. I use ODIs substitution tags using a special tag '<$' which is processed just prior to execution in the runtime code - so this code is included in the ODI scenario code and it is processed after variables are substituted (unlike the '<?' tag).

 So the lines below are not equal ...

  • <$ if ( "#V_COND".equals("1")  ) { $> EMP.SAL > 1 <$ } else { $> 1 = 1 <$ } $>
  • <? if ( "#V_COND".equals("1")  ) { ?> EMP.SAL > 1 <? } else { ?> 1 = 1 <? } ?>

When the <? code is evaluated the code is executed without variable substitution - so we do not get the desired semantics, must use the <$ code. You can see the jython (java) code in red is the conditional if statement that drives whether the 'EMP.SAL > 1' or '1=1' is included in the generated code. For this illustration you need at least the ODI 11.1.1.6 release - with the vanilla 11.1.1.5 release it didn't work for me (may be patches?). As I mentioned, normally KMs don't have dependencies on variables - since any users must then have these variables defined etc. but it does afford a lot of runtime flexibility if such capabilities are required - something to keep in mind, definitely.

Comments:

Hi David.

This is great stuff. Thanks for sharing. Couple of questions:

- Is this documented anywhere?
- Is this used in any of the out of the box KMs?

This is really handy if you write a lot of Java code and rather than hand coding connections to the database to retrieve a vaule you could just use an ODI variable to do some.

The <$ $> prints itself to the Operator. However, you need to use the full syntax. So <$="Stuff"$> won't work but <$ out.print("Stuff"$> will

Posted by Uli Bethke on December 11, 2012 at 02:44 AM PST #

Actually this stuff was possible before in the <@ @> parse phase.

However, it's nice that at the <$ $> stage it now prints out to the Operator

Posted by guest on December 11, 2012 at 08:21 AM PST #

Hi Uli

Yeh it was added in order to set the task name at runtime, and be able to see the code in the operator (and have variables substituted which is a nice bonus).

There is an example in the documentation, but not much (ummm nothing on the meaning of the tags really);
http://docs.oracle.com/cd/E25054_01/integrate.1111/e12645.pdf

Some examples are included such as below, and the description gives a hint about it 'This method sets the name of a task to the taskName value. This value is set at run-time'. Runtime being the key word, because of the <$ tags.

<$=odiRef.setTaskName("Create Error Table " + "<%=odiRef.getTable("L","ERR_
NAME","W")%>") $>
<$=odiRef.setTaskName("Insert Error for " + "<%=odiRef.getFK("FK_NAME")%>") $>
<$=odiRef.setTaskName("Loading " + "<%=odiRef.getTable("L", "COLL_NAME", "W")%>" +
" from " + "<%=odiRef.getSrcTablesList("", "RES_NAME", ",", ".")%>" ) $>

Cheers
David

Posted by David on December 11, 2012 at 08:40 AM PST #

David,

Nice post!

Have you seen any use of that tag for "additional debug" capability since the output is automatically put in Operator? Perhaps "breadcrumb" trails in Operator?

Posted by David Hecksel on January 05, 2013 at 11:57 AM PST #

Thats great David. I got redirected to this post by looking the reply on OTN yesterday.I gave one alternative for this question in OTN https://forums.oracle.com/forums/thread.jspa?threadID=2483436 as i had no direct solution (i was unaware of above flexibility). But this <$$> solved everything.

Thanks for sharing.

Posted by Bhabani on January 09, 2013 at 10:04 PM PST #

Hi David,

I have been trying to use the same technique to dynamically inject variable values when calling OdiStartScen. My situation is that I have a control table and a package that reads the table looking for jobs (scenarios) to run. Since my scenarios can have different input parameters I have a variable that it self holds the variables to be passed to the OdiStartScen for example #RESOURCE_VARIABLE containing value "-PROJ.START_DT=#SRC_START_DT" "-PROJ.END_DT=#SRC_END_DT". A code similar to what you have used i.e. <$ out.print("#RESOURCE_VARIABLE"); $> results in a "Error during task interpretation" message.

Any thoughts or pointers how would it be possible to dynamically print the variable value in a OdiRunScenario command.

Thanks in advance.

Posted by Hamid on July 29, 2013 at 03:01 PM PDT #

Hi Hamid

There are two parts; one is to execute the scenarios, driving that processing from your table, you can use OdiStartScen for example for that, identify the scenario name. The other is to supply the inputs, the inputs will be setup by configuring the dependent variables, you can set or refresh the variables to the values you want prior to executing the screnario.

Cheers
David

Posted by David on July 30, 2013 at 08:09 AM PDT #

Hi Hamid

Something else that may be useful, to execute the latest scenario version, you can use -1 as the version number, see the post below;
https://blogs.oracle.com/dataintegration/entry/odi_tip_of_the_day

Cheers
David

Posted by guest on July 30, 2013 at 08:11 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
4
5
6
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today