OWB 11gR2 – Template mapping variables?
By David Allan-Oracle on Oct 25, 2009
Here we'll see how you can add runtime variables and reference them from code template mappings in OWB 11gR2; variables can be used in many places including expressions, filters, joiners and execution unit code template assignments. This lets you control how some aspects of the mapping will behave at execution time. Traditional database resident mappings support mapping input parameters, in 11gR2 the template mappings can be extended in many ways and this is one illustration.
This is essentially a small extension where a table holding the variable names and values is defined, a data source in the agent to identify where the variable table is defined and there is a JAR file with the support for setting and getting variable values.
There are a few steps to configure OWB to support this (the zip is here) using a small custom extension I have created. Firstly copy the owbutils.jar file to the owb/lib/ext directory of the CCA host, then stop and start the agent. The table OWB_CT_VARIABLES should be defined in a schema of the user's choice, there is a SQL script supplied for Oracle named variables_oracle.sql in the zip, execute this in a schema of your choice.
The table OWB_CT_VARIABLES is defined with the following columns to represent the variable name and the variable value:
So to define a new variable insert a name and value into this table, for example the variable VAR_REGEXP is added below with the vale orderitems.*gz;
insert into owb_ct_variables values ('VAR_REGEXP', 'orderitems.*gz');
You can refer to variables inside expressions in OWB, for example within filter, join and expression operators, they can also refer to variables from within code template options in a mapping.
Configuring the Agent
Finally create a data source named jdbc/OWBCTVariablesDS in the Control Center Agent (CCA). The data source points to the schema containing the table used for storing the variables. Below is an example of a data-sources.xml file (in owb/jrt/config) that has a data sources added.
The data source named jdbc/OWBCTVariablesDS is used by the variable API that provides operations on variables to get and set variable values (the API is available when the jar file is copied to owb/lib/ext) - there are also increment/decrement operations for integers.
To illustrate lets look at an example that loads sets of files into a target table. The file list is dynamic and needs to be a regular expression that can be supplied to load all order items files with the gz (compression) suffix in one run. Another example will load all records from files with a transaction date greater than some date we define at runtime. The table OWB_CT_VARIABLES has the variable names and values;
Taking the mapping from the post on bulk loading files, if we look at code template options we can also use variables here too, so we can solve our challenge by getting the variable value for the filename regular expression to use, the example will use the VAR_REGEXP variable name to obtain the expression for file names at runtime.
Another illustration using a filter
In this example we define a filter to get all records after the date defined in the variable, we define the condition below, using the API (ctvariable.get) passing the variable name;
Note the \u0022 character which will give us the required quotes through the code generation. Depending on where the expression is defined will change how the code is written, inside a mapping expression the Unicode character is required, in the mapping template options use the quotation mark.
Here we have seen how you can add runtime variables and reference them from code template mappings in OWB 11gR2; the variables can be used in many places including expressions, filters, joiners and execution unit code template assignments letting you control aspects of the execution at runtime.