ODI Variables and the Operator Module

Published on March 14, 2010

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
One of the annoying things in ODI is that you can’t print out the value of an ODI variable to the Operator module without a workaround. In this workaround you throw an exception in Jython or the Java BeanShell and pass the ODI variable as an argument to the exception method,e.g. in Jython you can use raise(‘#name_of_project.name_odi_var’) to achieve this.
What is quite cumbersome, however, is that you need to manually put this piece of code into each package for each variable you want to debug.
I have written a generic procedure that will make this easier. This procedure uses the workaround described above to print out each variable in a package to the Operator module.
We use a mix of JBS and Jython to achieve this. In JBS we connect to the ODI work repository and retrieve the names of the variables used in the package in question. We then pass these values to Jython and raise the exception.
The source code for the procedure is as follows. You need to set the technology to Jython.

On lines 6-9 we connect to the ODI work repository
On line 11 we print the start of raise method to Jython
The SQL on line 12 will get us the variable and project name for the variables used in our package. Note that we need to pass in the name of the package as an Option to the procedure. So far I have not been able to find a way how to get this at runtime.
On lines 13-19 we iterate over the resultset of the above SQL and pass the name of the variable and package to Jython
Finally we do some clean up work
Let’s see the procedure in action.
First we assign values to two variables
As a last step in the package we execute the ODI debug variable procedure
The result can be seen from the Operator module