Oracle Data Integrator. SubstitutionAPI: Substitution Procedure. Part 1

Who is this article for?
Статья предназначена для опытных разработчиков ODI (Oracle Data Integrator). Здесь рассмотрены плохо документированные аспекты, связанные с порядком выполнения BeanShell-подстановок.

Substitution%? $ @


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

When and where are the substitutions performed? Are they repeated in the target code for each source string? Can substitutions of different levels be nested? And the same? If I declare a Java variable, then 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 substitution which functions are applicable)? And so on. A series of articles is devoted to these poorly documented subtleties. And this is the first of them.

Common to any lookup is that BeanShell Script is executed inside the lookup . Substitution processing is reminiscent of JSP or PHP parsing. Two substitution syntaxes are similarly distinguished: shortened and full. In abbreviated form, substitution contains only an expression whose result replaces this substitution at execution.

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

The full syntax contains full BeanShell code that can "print" something using out.print () , or not do this at all, but do other useful work quietly. 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"));%>

Lookup levels


% Level


Substitutions of the form <% ...%> are performed immediately at the start of the session even before the physical substitution is performed on the host that launches it. That is, if you start an ODI session from the designer, then the% substitution is performed on your workstation, and if the substitution script decides to access, for example, the file system (and beanshell has its own native functions for this, in addition to all Java features) ), then it will see your local disk. When the already assembled ODI script is launched - by invoking the InvokeStartScen operation or if it is starting from the scheduler, then the client initiating the launch is the ODI agent itself: the physical location for executing the% substitution is the ODI agent server.

If an exception occurs during the execution of the% substitution, then the fall will not occur. The whole stack of errors falls out in output, 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 crash there.

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

From this level, you cannot connect to either the source or 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 formation of the code occurs: an entry appears in SNP_STEP_LOG , and the final code of all the tasks of this step is generated. This is where the substitutions <? ...?> Are performed. After successful execution (or in the absence of substitutions), entries are created in SNP_SESS_TASK_LOG , where the result is placed - the final code. If an error occurs during interpretation, then an 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 to be executed. The? Substitution interpreter will resume work before the next step.

? -Substitution is likewise ignored in the Set Variable and Evaluate Variable steps. Using source and target connections, getting them with the odiRef.getJDBCConnection function, is already possible in this substitution. This means that for flexible code generation, you can use the data lying somewhere in the tables.

Level $


This is a special level that is performed immediately before the task is completed, and the result of this substitution is used to update the record in SNP_SESS_TASK_LOG. Moreover, if the $ substitution prints something, and ODI variables are used inside the substitution, 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 a variable in the operator logs.

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

And this substitution just doesn't work in the Set Variable and Evaluate Variable steps either.

Level @


Not so final is the final task code, as it turned out. Interpretation and execution of the final code is carried out in a language that corresponds to the technology, but you can include @ -substitution with Java BeanShell code in the code of any technology. Naturally, the substitution is performed first. If she prints something, then she additionally modifies the "final" code of the task before execution.

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

Source or Target, who is the first?


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

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

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

Repeating @ substitutions


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

Let's say that we have selected Oracle technology on sorce and target shoulders that supports prepareStatement. There are also @ substitutions on both shoulders. In this situation, the restriction applies: only select can be used on source. It is impossible to execute, for example, a pl / sql-block both there and there.

If the source-shoulder has non-empty code, then it is executed first. Accordingly, before executing the source code, it performs permutations in it once.

Source gives us a ResultSet, which the ODI needs to catch, and for each fetch try to execute the target leverage. There are many ways to access Source fields. You can use the entry : FIELD_NAME or# FIELD_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 fields, then ODI performs @ substitution and prepareStatement once. And then for each fetch it only performs variable binding and statement execution.

If, at least once, a call through # is used for the field (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 @ substitution will be performed many times.

Consider an example:

Source:

<@long i=0L;@>
select f1, f2 fromtable/* пусть это даёт 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, @ substitutions are performed 1 time when prepareStatement occurs. And in the second, each fetch gives rise to the formation of a new operator text and the new execution of @-substitution.

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

In future articles, we will look at other difficulties associated with substituting and using the Substitution API. In particular, next time we will talk about a nested, including recursive interpretation of permutations of the same and different levels. Many unexpected discoveries await you and you will exclaim more than once: “Ah, that's why this works like that!”

Also popular now: