Pentaho Data Integration - Kettle
  1. Pentaho Data Integration - Kettle
  2. PDI-200

As an ETL Designer, I want support for REF CURSOR in Oracle (DB PROC step)

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Not Planned
    • Component/s: Step
    • Labels:
      None
    • PDI Sub-component:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

        Issue Links

          Activity

          Hide
          Sven Boden added a comment -

          I have some experience with this and it's tricky and Oracle specific. It goes outside of the JDBC spec so you would need the Oracle JDBC drivers to compile Kettle. I don't know whether we want to go that far.

          I never required it for a BI solution. I'm not really pro this one.

          Sven

          Show
          Sven Boden added a comment - I have some experience with this and it's tricky and Oracle specific. It goes outside of the JDBC spec so you would need the Oracle JDBC drivers to compile Kettle. I don't know whether we want to go that far. I never required it for a BI solution. I'm not really pro this one. Sven
          Hide
          Jake Cornelius added a comment -

          This has also been requested by BPM, here is their specific request:

          They would like the ability to call a stored procedure and to give a pointer to the resulset as an Oracle Cursor. For example, here is how the stored procedure should be called:

          CREATE OR REPLACE PACKAGE spk_proc_bo AS

          --

          type rec_uf is record
          (
          code varchar2(10),
          description varchar2(40)
          );
          er spk_proc_bo.rec_uf;

          --

          type my_cursor_type is ref cursor return rec_uf;
          END spk_proc_bo;
          /
          Stored Procedure:
          REATE OR REPLACE PROCEDURE bo_sel_ufo(start_uf in varchar2,end_uf in varchar2,cursor_ufo in out spk_proc_bo.my_cursor_type) is
          BEGIN

          --

          open cursor_ufo for select nuufuf,libruf from ufo where nuufuf between deb_uf and fin_uf;
          END bo_sel_ufo;
          /

          Show
          Jake Cornelius added a comment - This has also been requested by BPM, here is their specific request: They would like the ability to call a stored procedure and to give a pointer to the resulset as an Oracle Cursor. For example, here is how the stored procedure should be called: CREATE OR REPLACE PACKAGE spk_proc_bo AS – -- – type rec_uf is record ( code varchar2(10), description varchar2(40) ); er spk_proc_bo.rec_uf; – -- – type my_cursor_type is ref cursor return rec_uf; END spk_proc_bo; / Stored Procedure: REATE OR REPLACE PROCEDURE bo_sel_ufo(start_uf in varchar2,end_uf in varchar2,cursor_ufo in out spk_proc_bo.my_cursor_type) is BEGIN – -- – open cursor_ufo for select nuufuf,libruf from ufo where nuufuf between deb_uf and fin_uf; END bo_sel_ufo; /
          Hide
          Jens Bleuel added a comment - - edited

          I believe it is pretty complicate to integrate into Kettle especially when more then one out parameter would be a result set (ref cursor). Also it is not clear how to handle 1:n relations since we can get more rows out then in - this could lead into a join...

          My simplification proposal (not beeing an Oracle guru and not sure if it really works):

          Would it be possible to treat the procedure as a function when it is clear that one result set is returned. More then one result set would not make sense to me to process with Kettle.

          In this case a simple select could be used with the Table Input or other steps that allow a select statement like

          SELECT function(params) FROM Dual;

          I believe this is not possible with a procedure, but may be the procedure can be incapsulated into a function?

          [The table DUAL is used here as a dummy and available on all Oracle systems. DUAL has one column called DUMMY and one row containing the value "X".]

          Other databases do it in this SQL syntax and return result sets:
          DB2: CALL(params)
          MS SQL: EXEC params

          Show
          Jens Bleuel added a comment - - edited I believe it is pretty complicate to integrate into Kettle especially when more then one out parameter would be a result set (ref cursor). Also it is not clear how to handle 1:n relations since we can get more rows out then in - this could lead into a join... My simplification proposal (not beeing an Oracle guru and not sure if it really works): Would it be possible to treat the procedure as a function when it is clear that one result set is returned. More then one result set would not make sense to me to process with Kettle. In this case a simple select could be used with the Table Input or other steps that allow a select statement like SELECT function(params) FROM Dual; I believe this is not possible with a procedure, but may be the procedure can be incapsulated into a function? [The table DUAL is used here as a dummy and available on all Oracle systems. DUAL has one column called DUMMY and one row containing the value "X".] Other databases do it in this SQL syntax and return result sets: DB2: CALL(params) MS SQL: EXEC params
          Hide
          Jens Bleuel added a comment -

          Confirmed circumvention for MS SQL:

          You can use a statement like this in a table input
          EXEC STOREDPROCEDURENAME 40, 'C'
          Which will then return the result set from the stored procedure

          Show
          Jens Bleuel added a comment - Confirmed circumvention for MS SQL: You can use a statement like this in a table input EXEC STOREDPROCEDURENAME 40, 'C' Which will then return the result set from the stored procedure
          Hide
          Jens Bleuel added a comment -

          A plug-in would be viable when it is depending from Oracle JDBC drivers.

          Show
          Jens Bleuel added a comment - A plug-in would be viable when it is depending from Oracle JDBC drivers.
          Hide
          Sven Boden added a comment -

          +1 for the separate plug-in

          Show
          Sven Boden added a comment - +1 for the separate plug-in
          Hide
          Matt Casters added a comment -

          Attachement "Read from Oracle Ref Cursor.ktr" works on PDI 4.x and reads from an Oracle REF cursor as described over on this page: http://www.enterprisedt.com/publications/oracle/result_set.html

          Show
          Matt Casters added a comment - Attachement "Read from Oracle Ref Cursor.ktr" works on PDI 4.x and reads from an Oracle REF cursor as described over on this page: http://www.enterprisedt.com/publications/oracle/result_set.html
          Hide
          Ulf Sturegren added a comment -

          I really would like this ...
          We have ALL business logic in Oracle database/SP's and our clientsoftware uses SP's for everything. We are now limited in what/how we can use Pentaho, since Pentaho cannot retrieve data in the same way as client application or website.

          Show
          Ulf Sturegren added a comment - I really would like this ... We have ALL business logic in Oracle database/SP's and our clientsoftware uses SP's for everything . We are now limited in what/how we can use Pentaho, since Pentaho cannot retrieve data in the same way as client application or website.
          Hide
          Matt Casters added a comment -

          Done a while back in a UDJC : http://www.ibridge.be/?p=191

          Show
          Matt Casters added a comment - Done a while back in a UDJC : http://www.ibridge.be/?p=191

            People

            • Assignee:
              Unassigned User
              Reporter:
              Jens Bleuel
            • Votes:
              5 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: