What is ODI Substitution Tags <%>, <'?'>, <@> and <$> ?

Answer :- 

Hi all, I bet that all ODI developers already got confused when working with ODI substitution tags. Every time that I need to work with those, I need to repeat all my previous tests again because I forget which tag is the right one to use (even after many years working with ODI….). That’s one of the reasons why I decided to write this post, so I can read my own post in future references (memory, memory, where are you???). Also because it is nice to share our experiences with others, so they can benefit from it (at least I hope so ) if they face similar issues.

For all those that are not familiar with it, what are ODI substitution tags? Substitution tags are those characters that you find when you open a Knowledge Module or a procedure in ODI, like these ones: <%>, <'?'>, <@> and <$>. Here is one example:

Those tags are part of ODI Substitution API and they are used to produce dynamic code based on several things like ODI variables, procedure options, data models, interface diagram and so on. Basically the Substitution API is the greatest development power in ODI (that’s why I love ODI so much), since it can produce dynamic code based in the existing information around it. Oracle has a good documentation about it here: Oracle Doc

Although Oracle talks about substitution API in their documentation, it does not state about the tags themselves and in their examples they only talk about the most common tag that is the <%>. But in reality we have four types of it, each one behaving in its own way. Basically each one of them is executed in a specific order, which affects their results in a way greater than you could imagine.

I’ll explain each one of them using a real situation that happened to me some time ago. I was working in a project that contained several different servers (ODI agents) with different Operational Systems on them. I was developing a dynamic component code that had to change its behavior based on which OS that component was going to be executed, so if the OS server was Linux I would execute one particular command and if it was Windows another command. To test in which OS the component was in, I used the following Substitution API command with the following tag (the only one that I knew back then):

<%= System.getProperty(“os.name”) %>

It didn’t work as I expected. The result of this command was always returning the OS name of the machine in which I was launching the execution (the ODI client) not the one that the command was going to be executed (the ODI agent). It puzzled me, so I started to study which options I could use… that’s when I learned about , <@> and later on<$> tags. Each one of them has a different effect in ODI code generation because each one of them has its own parse phase! This is a very simple concept and it truly affects the ways that ODI generate its dynamic code.

So let me show you what I did to test all those options. First consider that my ODI client is in a Windows 2003 OS and the ODI agent is a Windows Server 2008 R2 OS (the Linux OS was not available for me anymore, but this Windows 2008 will suffice for this example, since we just need different types of OSs). I created the following procedure:

It is a simple procedure with a Jython code that will “raise” the name of the OS as an error. I just marked “Ignore Errors” as well, so I could add more steps to this procedure. I did the same for all substitution tags:

Then you execute the procedure, making sure that it uses the ODI agent (that resides in a different OS):

Let’s begin with <%>. After you execute the procedure and go to “Code” tab, we have the following:

Very interesting and it matches my previous experience. The <%> is returning the ODI client OS which indicates that the substitution API is generating the command when it is sent to the agent, but before it gets to the agent! Obviously if we go to “Definition” tab, we will get the execution of this command, which will be “Windows 2003”:

Let’s see what it did using <'?'> substitution tag:

Nice! This is exactly what I previously wanted: get the ODI agent OS and decide which command was going to be executed based on that OS. So ODI is generating the code in the agent server, before the command is sent to Operator and before ODI variables are substituted (what, Operator, variables??? More on those topics in a few moments…). If we go to “Definition” tab, we will get the execution of this command, which will be “Windows Server 2008 R2”:

Ok, let’s see <@>:

Humm….. More interesting stuff and that’s why I mentioned Operator/ODI variables before. This code is being generated in the agent, but after the command appears in the Operator and after ODI variables are substituted. When we go to “Definition” tab, we will get the execution of this command, which will be “Windows Server 2008 R2” again:

The <@> tag is great for you to evaluate ODI variables content and depending on that variable value, do something different in your code, like the example below:

The interface filter in this example will change accordingly to #V_COND variable. If #V_COND is 1, then it will filter all records with BANK_ACCOUNT_TYPE = ‘1’. If #V_COND is different from 1, then it will load all records.

Those three examples perfectly show us that each tag executes in a different parse phase and each one has precedence over the other. And now you must be thinking: does it means that we can mix and match those tags and create even more dynamic code??? Oh sure yes!

They show us how we can create “loops” using one tag and apply that result to another tag that will get executed right after that, which give us an extreme dynamic tool for developing. I already told you that I love ODI right?

Ok, but what about <$>? I left this one for the end because this tag was added later to the game (it seems that it is available from 11.1.1.6 version on). This tag resides between <'?'> and <@> which give us the unique ability of generating the code in the agent server, before the command is sent to Operator but AFTER ODI variables are substituted. Ok, this is crazy stuff Why do we need it? The key here is that ODI will contain all ODI variables already evaluated (meaning containing its correspondent refresh/set values) and the code will be displayed in Operator already parsed with the correct ODI variables logic, which makes your code much more readable for the Operators users. Besides that, having a fourth parse phase is great to do even crazier dynamic self-constructed code (long live dynamic coding!!!). Here is “the same” example using <$> in an ODI interface from Oracle ODI blog (the place where I learned about the existence of <$>):

Oracle Blog

In our OS example, the <$> result will not be different from what we got using <'?'>:

And in Definition tab:

That’s it folks, so in resume here is the list of substitution tags and their parsing phase order:

<%>: generates the command when it is sent to the agent, but BEFORE it gets to the agent;

<'?'>: generates the code in the agent server, BEFORE the command is sent to Operator and BEFORE ODI variables are substituted

<$>: (available from 11.1.1.6 on): generates the code in the agent server, BEFORE the command is sent to Operator but AFTER ODI variables are substituted

<@>: generates the code in the agent server, AFTER the command appears in the Operator and AFTER ODI variables are substituted