ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Passing Values to ODI Variables in Packages/Scenarios

Hi Everyone,

Here is my first post, hope you will enjoy this.

This post is about passing values to variables in packages/scenarios and see how the variables behave, the value stays the same in declare step and changes in refresh step and you can override the value during execution.

We need static values during ETL window. Also you can rerun packages and be certain that the sub packages take the same correct variable values. ETL date can be a good example for static. Dynamic values are needed when you have a task changing daily and takes recent value such as exchange rates. Dynamic values can be calculated at run time for example looping through a list.

I created 3 packages to watch variable values:

  • SUB_DECLARE declares the variable
  • SUB_DECLARE_REFRESH declares and refreshes
  • SUB_REFRESH only refreshes the variable.

1_SUBS

And I created MAIN package begins with declares and refreshes the variable. The MAIN package continues to call the scenarios with no variable value and also  passing the given variable value in serial order one by one.

2_MAIN

The MAIN package executed with variable value : 20141217, default value is 1 and when the variable is refreshed value is 20141216 as sysdate-1, as I executed the main package at 17 Dec 2014.

When we execute the MAIN package, we can see the steps:

3_EXC_MAIN

First child session are not taken any variable value from the MAIN package, second scenarios are taken the variable values from the MAIN package.

To see how the variable changes before and after the scenario execution , we wil look variable values from repository tables in total as below:

4_VAR_VAL

At the first row, SUB_DECLARE scenario is not given any variable, as it continues with default value: 1. Second row of SUB_DECLARE scenario takes the MAIN package variable value 20141217.

When we look at SUB_DECLARE_REFRESH scenario values, first two rows does not take any value from main package, and variable begins  with default value and when it is refreshed it takes the etl_date as sysdate-1.  When the MAIN package pass variable value to SUB_DECLARE_REFRESH scenario, the value begins and ends with the same value: 20141217.

In the last scenarios, first row of SUB_REFRESH scenario begins with default value and ends with sysdate-1; 20141216. In the second row, SUB_REFRESH scenario takes only the MAIN package variable value.

In conclusion, passing a variable value to a scenario overrides the scenario value, it is always dominant. Declaring a variable creates a static value and refreshing a variable creates dynamic values, in execution begin and end values of refresh step is changing.

3 Comments

  1. Really great article. Explained about variables very well manner. Thanks a lot.

  2. Hi,

    We use odi for migration of data from Legacy (files .dat) to R12. While doing that we have used refreshing variables, they are behaving very odd.

    If the file have less no.of records say 15 records then the refreshing variables are working as expected but if we have 1000 to 2000 records the refreshing variable is erroring with the below message.

    ODI-1226: Step N_CHECK_THRESHOLD fails after 1 attempt(s).
    ODI-1236: Error while refreshing variable HR_TRANSACTIONS.N_CHECK_THRESHOLD.
    Caused By: oracle.odi.core.repository.UncategorizedRepositoryAccessException: ODI-10182: Uncategorized exception during repository access.
    Connection has already been closed.

    Can you please help me in understanding the issue and getting this fixed.

    Thanks,
    Kalyana

  3. Thanks for the post friend, I wonder if it’s possible to pass an object as a parameter to a Java class?

Leave a Reply

Required fields are marked *.