ODI Variables and the Operator Module
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.
[big_data_promotion]
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.
|
1 |
<% import java.sql.*; import java.io.*; BufferedWriter bw = new BufferedWriter(new FileWriter("d:log.txt")); StringBuilder strB = new StringBuilder(); Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@bethke:1521:orcl"; Connection con = DriverManager.getConnection(url,"odi_dev","odi_dev"); stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); bw.write("raise('"); out.print("raise('"); String str = "SELECT c.var_name,d.project_name FROM snp_package a JOIN snp_step b On (a.i_package = b.i_package) JOIN snp_var c ON (b.i_var = c.i_var) JOIN snp_project d ON (c.i_project = d.i_project) WHERE UPPER(pack_name) = UPPER('" +odiRef.getOption("PCK_NAME")+"')"; ResultSet rs = stmt.executeQuery(str); while(rs.next()){ String var_name = rs.getString("var_name"); String project_name = rs.getString("project_name"); out.print(var_name+" #" +project_name+"."+var_name+"n "); bw.write("#STUFF."+var_name); } bw.write("')"); out.print("')"); rs.close(); bw.close(); stmt.close(); con.close(); %> |
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
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
