- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Variable refreshed in Package but used in Load Plan
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..
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.