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

Sven Boden added a comment - 21/Sep/07 4:47 PM 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

Jake Cornelius added a comment - 09/Oct/07 9:23 AM 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; /

Jens Bleuel added a comment - 08/Jan/08 4:08 PM - 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

Jens Bleuel added a comment - 22/Jul/08 4:59 AM 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

Jens Bleuel added a comment - 26/Nov/08 3:48 AM A plug-in would be viable when it is depending from Oracle JDBC drivers.

Sven Boden added a comment - 26/Nov/08 4:31 AM +1 for the separate plug-in

Matt Casters added a comment - 16/Nov/10 5:44 PM 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

Ulf Sturegren added a comment - 31/Oct/11 3:26 AM 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.

Matt Casters added a comment - 31/Oct/11 4:26 AM Done a while back in a UDJC : http://www.ibridge.be/?p=191