Oracle Data Integrator. SubstitutionAPI: The order of the substitutions. Part 1

The%? $ @ Substitutions

Calm “%? $ @” Is not a euphemism, but special characters used for different types of BeanShell substitutions. Although in this way the developer could say about them, who decided to use them all together, and without understanding a number of subtleties. Meanwhile, it is an extremely powerful tool that provides flexible generation of code executed in scripts.

When and where are the substitutions performed? Do they repeat in the Target code for each source line? Can I embed substitutions of different levels? And the same? If I declared a Java variable, where can I still use it? Why sometimes the Substitution API functions do not work, and sometimes they work (it is not documented at what level of substitutions which functions are applicable)? And so on. A series of articles is devoted to these poorly documented subtleties. And this is the first one.

A common feature of any substitutions is that the code in the language BeanShell Script is executed inside the substitution. Handling substitutions is like parsing JSP or PHP. Similarly, there are two syntax substitutions: short and full. In short, the substitution contains only an expression whose result replaces this substitution when executed.

<%=odiRef.getSysDate("yyyy-MM-DD HH:mm:ss.SSS")%> 

The full syntax contains a full BeanShell code that can “print” something using out.print () , or not do it at all, and do other useful work on the sly. In the latter case, after performing the substitution, it is simply removed from the code without leaving any traces there.
')

 <%out.print(odiRef.getSysDate("yyyy-MM-DD HH:mm:ss.SSS"));%> 

Substitution levels

% Level

Substitutions of the form <% ...%> are executed immediately at the start of the session even before, and physically this substitution is performed on the host that performs the launch. That is, if you run an ODI session from the designer, then the% substitution takes place on your workstation, and if the substitution script decides to access, for example, the file system (and in beanshell, in addition to all the Java features, there are also native functions ), then it will see your local disk. When an already assembled ODI script is launched — a call to an InvokeStartScen operation or a launch from the scheduler — then the client initiating the launch is the ODI agent itself: the physical place of the% substitution is the ODI agent server.

If an exception occurs during the execution of the% -flash, then a crash does not occur. The whole stack of errors falls into output, it becomes the result of the substitution. This result becomes the source code (already invalid), which will try to be interpreted at the next level, and the session will be dropped there.

This substitution does not work inside the Set Variable and Evaluate Variable steps. That is, it is perceived there as plain text and is not processed in any way.

From this level, you can not connect to either the source or the target connection, but only to the work repository. Those. odiRef.getJDBCConnection ("WORKREP") is already available, but neither this function with other arguments nor getJDBCConnectionFromLSchema () work. Because no connections yet exist: the work of the session has not yet begun.

Level?

So, the script is already in the agent and started to run. Session created. For the first step (step), the final code generation takes place: an entry appears in SNP_STEP_LOG , and the final code of all tasks of this step is formed. This is where the <? ...?> Substitution is performed. After successful execution (or in the absence of substitutions) records are created in SNP_SESS_TASK_LOG , where the result is placed - the final code. If an error occurs during interpretation, the entry in SNP_SESS_TASK_LOG is not created, and the error message must be searched above - in SNP_STEP_LOG . When the whole step is prepared, it immediately begins running. The interpreter? -Computing will resume work before the next step.

The? substitution is similarly ignored in the Set Variable and Evaluate Variable steps. It is already possible to use source- and target-connections, receiving them with the odiRef.getJDBCConnection function, in this substitution. This means that for flexible code generation, you can attract data lying somewhere in the tables.

Level $

This is a special level that is performed immediately before the execution of the task (task), and the result of this substitution is used to update the record in SNP_SESS_TASK_LOG. Moreover, if the $ -permutation prints something, and within the substitution, ODI-variables are used, then in the logs you will see the values ​​of the variables, and not the names, as usual. That is, this substitution can be effectively used to see the value of the variable in the operator logs.

In addition, $ -permutation is the only place where you can call the function odiRef.setTaskName () , which does not print anything, but changes the name of the task in the logs. In fact, these are the only two applications where $ substitution is useful.

And this substitution also does not work in the Set Variable and Evaluate Variable steps.

Level @

Not so final is the final task code, as it turned out. The interpretation and execution of the final code is carried out in the language corresponding to the technology, but in the code of any technology, you can include the @ -substitution with Java BeanShell code. Naturally, the substitution is performed first. If it prints something, then it additionally modifies the “final” task code before execution.

The @ -substitution can be applied in the Set Variable step. Thus, an ODI variable can be easily assigned to the result of a java-expression. There is still nothing in the Evaluate Variable. If an exception occurs during the substitution, then the session falls at the current step.

Source or Target, who is first?

Any session in ODI consists of steps, in each step there is one or several tasks, and in each task there are always 2 levers — source and target. Even, for example, refresh or set by an ODI variable is the same task, just with an empty body source. Since substitutions can be on both shoulders, it is interesting, and sometimes it is important to know which one will be executed before. Because it is important to first declare and assign a java-variable, and then use, and not vice versa.

Surprisingly, but the% substitutions? and $ are executed first for the target leverage, and then for the source. But the @ -subscription is the opposite.

Knowing this property, you can correctly initialize variables, functions, script objects, and the usual BeanShell classes, and then use them correctly.

Repetition of @ -substitution

With other levels, it's simple. Target is interpreted first and then Source. With level @ everything is different, and depends on many factors.

Suppose we have selected Oracle technology on sorce- and on target-leverage, which supports prepareStatement. And also on both shoulders there are @ -compositions. In such a situation, there is a limitation: the source can only use select. It is impossible to perform both there and there, for example, a pl / sql-block.

If there is a non-empty code on the source-shoulder, then it is executed first. Accordingly, before executing the source code, the substitutions are performed in it once.

Source gives us a ResultSet, which ODI is obliged to fetch, and for each fetch try to fulfill the target leverage. You can access the Source-fields in different ways. You can use the entry : FILAMENT NAME or # FAMILY NAME . If the target-technology driver supports the prepareStatement operation (this is typical for all JDBC drivers) and only the notation is used : field_NAME for the fields, then ODI performs the @ -substitution and prepareStatement once. And then for each fetch, it performs only the binding of variables and the execution of the operator.

If at least once a reference is used for the field via # (which means simple text substitution), or the driver does not support prepareStatement, then the formation of the target operator will be special (individual) at each iteration, so the @-substitutions will be performed multiple times.

Consider an example:

Source:

 <@long i=0L;@> select f1, f2 from table /*    10  */ 

Target (option 1):

 begin stored_Procedure(<@=++i@>, :f1, :f2); /*     = 1,      1   prepareStatement */ end; 

Target (option 2):

 begin stored_Procedure(<@=++i@>, #f1, #f2); /*    = 1, 2, 3.....   ,       ,       i   10, ..   */ end; 

In the first variant, @ -subblings are performed 1 time when prepareStatement occurs. And in the second, each fetch gives rise to the formation of a new operator text and a new implementation of @ -substitution.

If the target-technology does not provide for the ability to do prepareStatement, (for example, OdiTools), then accessing the source-fields through the colon is either impossible or not different from #.

In future articles, we will consider other difficulties associated with substitutions and the use of the Substitution API. In particular, next time we will tell about the nested, including recursive, interpretation of substitutions of the same and different levels. A lot of unexpected discoveries are waiting for you and you will exclaim more than once: “Oh, here’s why it works that way!”