Kettle vs Oracle REF CURSOR

[Latest News Headlines]

Dear Kettle fans,

PDI-200 has been out there for a while now. *Jens created the feature request a little over 3 years ago. *I guess the main thing blocking this issue was not as much a technical problem but more of a licensing and dependency one (Oracle JDBC dependency and distribution license).

However, now that we have the User Defined Java Class step we can work around those pesky problems. That is because the Java code in there only gets compiled and executed at runtime so it’s perfectly fine to create any sort of dependency in there you like.

The following transformation reads a set of rows from a stored procedure as described on this web page.

In short, our UDJC step executes the following code:

begin ? := sp_get_stocks(?); end;

The result is a set of rows and the parameter is in this case a single numeric value.



The step contains mostly Java code but thanks to configuration options you only need to do 2 things to make this work for your own REF CURSOR returning procedures…

First you need to specify the output fields of the rows…



And then you need to specify the parameters:



The source code for this sample transformation is over here and runs on Pentaho Data Integration version 4.x (or higher). *All in all it only took a few hours to write these 150 lines of Java so perhaps it can serve as inspiration for other similar problems you might have with Oracle or other databases.

Until next time,

Matt



More from Matt Casters on Data Integration (Pentaho) Blog...