Forum Stats

  • 3,827,061 Users
  • 2,260,734 Discussions
  • 7,897,155 Comments

Discussions

Variable refreshed in Package but used in Load Plan

Jose Vidal
Jose Vidal Member Posts: 15
edited Oct 21, 2016 4:08AM in Data Integrator

Hi All,

Due to ODI limitations with PL/SQL procedures and functions for capturing their output values, we have to follow the steps described in the following link (third situation): How to use PL/SQL procedures and functions in ODI - ODIExperts.com

This workaround works very well on packages. However, on load plans is a bit more tricky. I have tried to refresh the variable to capture the output on the load plan but it doesn't work because the next step in the load plan is always a new session.

I have then created a very small package with the procedure and the variable to capture the output and it works.

The question now is about the scope of the variable. I guess it needs to be setup to keep history (latest value) and that's the only way to refresh and keep the value (in this scenario we have another limitation because we cannot refresh the variable in parallel, it would overwrite the latest value but this is not a problem for me, I just wanted to mention it).

If we want to evaluate this variable within the load plan, it needs to be declared in the "Variables" tab of the load plan. Does it affect the scope of the variable? Will the load plan be able to use the value from the history? If we refer back to the Oracle documentation..

Note:

At startup, Load Plans do not take into account the default value of a variable, or the historized/latest value of a variable in the execution context. The value of the variable is either the one specified when starting the Load Plan, or the value set/refreshed within the Load Plan.

This statement doesn't make the point more clear because we are refreshing the variable within a package which is a step within the load plan, however it is not properly refreshed within the load plan.

Any suggestions?

Thanks,

Jose

Tagged:
ashud1Christyxo

Best Answer

  • Jose Vidal
    Jose Vidal Member Posts: 15
    edited Oct 21, 2016 4:08AM Answer ✓

    Hi All,

    I have already tested this issue and I have got the following conclusion:

    The variables refreshed within packages (even if they are part of the load plan) cannot be evaluated by using the Load Plan steps (Case, When and Else steps). The scope is at session level (as usual) but the history/latest value doesn't work here.

    The solution that I have finally implemented to use most of the Load Plan features requires a package to deal with the last part of the Load Plan. The variables are refreshed & evaluated within the package and the subsequent steps have been implemented within the package as well.

    Every situation might have a different solution. For instance, if your are experiencing this issue at the beginning of the Load Plan, you can always insert the value of the variable refreshed within package into a temporary table and read this value afterwards by using a genuine Load Plan variable which will allow you to evaluate it there.

    Regards,

    Jose

    Jose Vidalashud1Christyxo

Answers

  • ashud1
    ashud1 Member Posts: 483 Blue Ribbon
    edited Oct 15, 2016 3:13AM

    hi,

    Note:

    At startup, Load Plans do not take into account the default value of a variable, or the historized/latest value of a variable in the execution context. The value of the variable is either the one specified when starting the Load Plan, or the value set/refreshed within the Load Plan

    The meaning of the underling statement is that variable would take the default value at load plan run it , unless you are not changing it.

  • Jose Vidal
    Jose Vidal Member Posts: 15
    edited Oct 21, 2016 4:08AM Answer ✓

    Hi All,

    I have already tested this issue and I have got the following conclusion:

    The variables refreshed within packages (even if they are part of the load plan) cannot be evaluated by using the Load Plan steps (Case, When and Else steps). The scope is at session level (as usual) but the history/latest value doesn't work here.

    The solution that I have finally implemented to use most of the Load Plan features requires a package to deal with the last part of the Load Plan. The variables are refreshed & evaluated within the package and the subsequent steps have been implemented within the package as well.

    Every situation might have a different solution. For instance, if your are experiencing this issue at the beginning of the Load Plan, you can always insert the value of the variable refreshed within package into a temporary table and read this value afterwards by using a genuine Load Plan variable which will allow you to evaluate it there.

    Regards,

    Jose

    Jose Vidalashud1Christyxo
This discussion has been closed.