While using ODI to implement your ETLs, you may need to have loops. Let’s look at the examples, where I will implement loops that will iterate n times (for loops) and loops that will iterate while they ensure the condition. (while loops)
For Loop
In programming we implement for loop as follows,
for (i = 0; i < 10; i++){
//statements
}
This is a simple loop which iterates ten times, if we parse the part in the parenthesis we can see in the first part we assign a value to a variable, second part we define the condition and the last part is change of variable value per iteration.
In ODI 11g we can implement this as follows:
1- Create a variable
I created a variable called V_FOR_LOOP which is numeric and does not have a refreshing code.
2- Create a package
I create a package and name it as P_FOR_LOOP, I will put a screenshot of package’s final status when we complete all steps.
3- Set variable
Set a value to our variable V_FOR_LOOP as an initialization value. I will set it as 0. Also name the step as set initial.
4- Evaluate variable
Evaluate V_FOR_LOOP against iteration condition. I will use “less than 5” as iteration value. You can choose between the options as you wish or your requirement. Name step as Evaluate Value.
5- Place your statements
Now it is time to place your statements which will iterate. I will only put one interface.
6- Increment your variable
Increment your variable one step using SET VARIABLE object’s Increment option, I will increment by one and name this step as Increment.
7- Connect your Increment step to Evaluate Value step
Until this step every object was connected to its following object with an OK line, now connect Increment to Evaluate Value with an OK line. Now it will go back to evaluation and iterate until the evaluation is false.
Here is how our package looks in final form:
And the operator screen when we run the package:
As seen above steps numbered 1,2,3 repeats 5 times, then Evaluate Value runs one more time, decides that V_FOR_LOOP < 5 is not true enough and package finishes its run.
While Loop
In programming we can implement while loop as follows:
while (flag == true){
//statements
}
So this will iterate unknown times until its condition becomes incorrect. Confession time : I have to admit that I have never felt need of using while loop in ETL/ODI but you may need.
Before implementing this step-by-step, I created a table includes two columns c1 and flag, where I will use flag as my condition. My data is as follows :
C1 F
— –
1 T
2 T
3 T
4 T
5 F
6 T
7 T
8 T
9 T
10 F
Now let’s implement while loop:
1- Create a variable to hold flag value
I create a variable called V_WHILE_LOOP which is alphanumeric and refreshing by : select flag from variable.test where c1 = #V_FOR_LOOP
I will use my V_FOR_LOOP to select flag values, in this sample case. Your case will contain different logic than this sample for sure.
2- Create a package
I create a package named P_WHILE_LOOP.
3- Set Variable (in my case)
Since I am refreshing my flag depending on V_FOR_LOOP, I set this as first step.
4- Refresh Flag
Refresh your flag variable.
5- Check Flag
Evaluate flag variable.
6- Statements
Place your statements, I will put my sample interface and also I will increment V_FOR_LOOP as I will need this to reach an invalid flag.
7- Set your connections
Until the end of your statements every step will be connected by an OK, when you reach the end connect it to Refresh Flag step, so you will refresh, check and start your statements again and again until flag is false.
Here is a view of package :
And the view from operator:
You can see it hits the end when we refresh flag for the 5th time since it will return F as flag value, which is not suitable to our condition.
So here we are at the end of the post, now with the knowledge of “How to implement loops in ODI 11g”
Thank you for your patience to read, and if you have any questions or comments please drop a comment and I will read (and reply if it’s a question) it for sure.
Canburak Tümer's Blog 