Creating and Using Variables

A variable is an object that stores a single value. This value can be a string, a number or a date. The value is stored in Oracle Data Integrator, and can be updated at run-time.

The value of a variable can be updated from the result of a query executed on a logical schema. For example, it can retrieve the current date and time from a database.

A variable can be created as a global variable or in a project. Global variables can be used in all projects, while project variables can only be used within the project in which they are defined.

Creating Variables

To create a variable:

1. Click the Variables node in a project, or the Global Variables node in the Others view.

2. Right-click and select Insert Variable.

3. Specify a Name,  Action, Datatype and Default Value. See Variable for more information.

4. If you want the variable's value to be set by a query:

1. Select the Refreshing tab.

2. Select the logical schema where the command will be executed, then edit the command text in the language of the schema's technology. You can use the Expression Editor by clicking .

3. Click to check the syntax of your expression.

4. The Refresh button allows you to test the variable by executing the query immediately.

5. Click OK.

The variable appears in the tree view.

To delete a variable:

1. Click the variable in the appropriate tree view.

2. Right click and select Delete.

3. Click OK.

Using variables

To use a variable in a package:

Variables can be used in packages in several different ways, as follows:

• Declaration: When a variable is used in a package (or in certain elements of the topology which are used in the package), it is strongly recommended that you insert a Declare Variable step in the package. This step explicitly declares the variable in the package.

• Refreshing: A Refresh Variable step allows you to re-execute the command or query that computes the variable value.

• Assigning: A Set Variable step of type Assign sets the current value of a variable.

• Incrementing: A Set Variable step of type Increment increases or decreases a numeric value by the specified amount.

• Conditional evaluation: An Evaluate Variable step tests the current value of a variable and branches depending on the result of the comparison.

• In expressions in other steps, such as interfaces, procedures, OS Commands and so forth.

Variable scope

A variable can and should be used by explicitly specifying its scope, using the syntax #GLOBAL.<variable name> for global variables or #<project code>.<variable name> for project variables.

Using the value of the variable

Variables can be used in all Oracle Data Integrator expressions:

• Mapping,

• Filters,

• Joins,

• Constraints,

• ...

To substitute the value of the variable into the text of an expression, precede its name by the '#' character. The agent or the graphical interface will substitute the value of the variable in the command before executing it.

The following example shows the use of a global variable named 'YEAR':

Update CLIENT set LASTDATE = sysdate where DATE_YEAR = '#GLOBAL.YEAR' /* DATE_YEAR is CHAR type */

Update CLIENT set LASTDATE = sysdate where DATE_YEAR = #GLOBAL.YEAR /* DATE_YEAR is NUMERIC type */

Note: the "bind variable" mechanism of the SQL language can also be used, however, this is less efficient, because the relational database engine does not know the value of the variable when it constructs the execution plan for the query. To use this mechanism, precede the variable by the ':' character, and make sure that the datatype being searched is compatible with that of the variable.
For example: update CLIENT set LASTDATE = sysdate where DATE_YEAR =:GLOBAL.YEAR

Note: You can drag-and-drop a variable into most expressions with the Expression Editor. It is also possible to use variables as substitution variables in graphical module fields such as resource names or schema names in the topology. You must use the name of the variable (Example: #GLOBAL.MYTABLENAME) directly in the Oracle Data Integrator graphical module's field.

Using this method, you can parameterize elements for execution, such as the physical names of files and tables (Resource field in the datastore) or their location (Physical schema's schema (data) in the topology)

Passing a variable to a scenario

It is also possible to pass a variable to a scenario in order to customize its behavior. To do this, pass the name of the variable and its value on the OS command line which executes the scenario. For more information, see Launching a scenario from an OS command.