Kettle vs Oracle REF CURSOR

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

4 comments

  • Hi Matt, this is great, but actually does not cover REF Cursors in Stored Procedures, but in Function (See the DDL: CREATE OR REPLACE FUNCTION sp_get_stocks…).

    As far as I know, one of the difference is that the function returns the Ref Cursor, while the Stored Procedure sets the Ref Cursor to an in out parameter. So with this said, the Java Code for a real Stored Procedure (your code works for functions) would need to generate the following query: “{call myStoredProc (?,?,?,?,?)}”, where one of the ? is the actual Ref Cursor (In most of the cases either the first or the last). In the real case I’m working at, they have the Ref Cursro in the first ? (And I think that’s a requirement for these type of SPs to work with Crystal Reports) so I modified your code to be as follow:

    String query = “{call “+getParameter(“PROCEDURE”)+”(?,”;
    for (int i=0;i0) query+=”,”;
    query+=”?”;
    }
    query+=”)}”;

  • jorge

    I set my connection to my database user and password is datamart oralcle

    public boolean init(StepMetaInterface stepMetaInterface, StepDataInterface stepDataInterface)
    {
    if (parent.initImpl(stepMetaInterface, stepDataInterface))
    {
    String dbName = getParameter(“oracle_datamart”);
    databaseMeta = getTransMeta().findDatabase( dbName );
    if (databaseMeta==null) {
    logError(“Connection not found: “+dbName);
    return false;
    }
    logDetailed(“Using connection “+databaseMeta.getName());
    database = new Database( databaseMeta );
    try {
    database.connect();
    } catch(Exception e) {
    logError(“Unable to connect to database”, e);
    return false;
    }
    }

    return true;
    }

  • jorge

    please help, my user name and password is datamart database is oracle oracle
    my procedure is

      PROCEDURE PA_LISTADOS (P_RESULT ref_cursor OUT) AS
       BEGIN
         / * Task requires implementation * /
         P_RESULT OPEN FOR SELECT * FROM DATAMART_GPS. “Dimtiempo”;
       
       PA_LISTADOS END;

  • jorge

    hola ayuda en CONNECTION debe colocar “jdbc:oracle:thin:@localhost:1521:xe”,”datamart_gps”,”oracle” , le estoy colocando pero no me sale