Wednesday, September 22, 2021

ODI - Types of variables and uses

  • A variable is an object that stores a single value. This can be string  number or date etc. The value is stored in ODI and can be updated at runtime.
  • A variable can be created as a global or in a project. Global variables can be used in all projects where as project variables can only be used within the project in which they are defined.
  • The value of a variable can be updated from the result of a query executed on a logical schema. For example, it can retrieve current date and time froma  database.
Types of variable:

Declare variable:
When a variable is used in a package, it si strongly recommended that you insert a declare variable step in the package. This step explicitly declares the variable in the package.

Refresh variable:
This variable step refreshes the variable by running the query or command specified in the variable definition.

Set variable:
Assign - It sets the current value of a variable
Increment - increases or decreases a numeric value by the specified amount.

Evaluate variable:
This variable step type compares the value of a variable with a given vaule axcording to an operator. If the condition is met, then the evaluation step is true, otherwise it is false, this step allows for branching in package.

Create variable:

Click the variables node in a project  or the Global variables node in the Global Objects.

Right click and new variable
Specify name, data type and default value

If you want the variable's value to be set by a query:
Select the refreshing tab
Select the logical schema where the command will be executed.
Select query to be executed.

To delete a variable:

Click the variable in thr appropriate tree view
Right click and select delete.
Click ok

Variable scope:

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

Using the value of the variable:
Variables can be used in all ODI expressions:
  • Mapping
  • Filters
  • Joins
  • Constraints  etc.

To substitute the value of the variable into the text of an expressiob , precede its name by the # character.
For instance, you want to use the global variable Month, so use it like #GLOBAL.Month

To insert a variable step in a packege:

For instance , declare variable
  • Open the package editor and go to the diagram tab
  • In the designer navigator, select the variable to add to the package from the projects tree for a project variable or from global sections tree for a global variables.
  • Drag and drop the variable into the diagram. A variable step appears 
  • Click on the step icon in the diagram. The properties panel opens 
  • In thr general tab, fill in the step name field and select step type as declare variable.
  • From the file menu, click save.

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...