Watson Supply Chain Ideas

Use this form to submit an idea for a new product feature. The product team will review your input and provide status updates as decisions are made regarding the request.

Before you submit a new idea, please view requests that have already been submitted. If your idea has already been submitted, you can add comments or vote on the existing idea, thereby indicating your agreement with the idea. We may use this information to help prioritize development of new features.

 

Submit ideas for Watson Marketing and Watson Commerce products

WTX doesnt support REFCURSOR type from Oracle DB

REFCURSORs are NOT supported by the WTX Oracle adapter.
  • Avatar32.5fb70cce7410889e661286fd7f1897de Guest
  • Jan 9 2018
  • Under Consideration
How will this idea be used?
What is your industry?
What is the idea priority? Urgent
DeveloperWorks ID DW_ID87358
RTC ID RTC_ID500346
Link to original RFE http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=87358
  • Attach files
  • Admin
    Luke Raiano commented
    06 Dec 20:53

    USE CASE

    Our customer isn't able to retrieve data from Oracle Cursor defined in Packaged function.

    create or replace
    Package          ESB_TEST_TRN
    AS

    FUNCTION get_trans(ptxt_status IN NUMBER, pnum_rows IN NUMBER)
        RETURN SYS_REFCURSOR;

      esbpokusrec    x_esb_pokus%ROWTYPE;
      TYPE TableTx IS TABLE OF x_esb_pokus%ROWTYPE;
    END ESB_TEST_TRN;
    /*********************************************************************/
    create or replace
    Package Body          ESB_TEST_TRN
    AS
    FUNCTION get_trans(ptxt_status IN NUMBER, pnum_rows IN NUMBER)
       RETURN sys_refcursor
    IS
    lrcu_result  sys_refcursor;
    BEGIN

       OPEN lrcu_result FOR
         SELECT transaction_id,
                status,
                col01,
                col02,
                col03,
                col04,
                col05,
                col06,
                col07,
                col08,
                col09,
                col10,
                col11,
                col12,
                col13,
                col14,
                col15
           FROM x_esb_pokus
          WHERE status = ptxt_status
            AND rownum< pnum_rows + 1;

       RETURN lrcu_result;

    END;
    END ESB_TEST_TRN;

    In SQL we would normally select data from the Function using:
    set serveroutput on;

    declare
      v_rc sys_refcursor;
      esb_pokus_row x_esb_pokus%rowtype;
    begin
      v_rc := esb_test_trn.get_trans(1,100);
      loop
        fetch v_rc
        into  esb_pokus_row;
        exit when v_rc%notfound;
        dbms_output.put_line(esb_pokus_row.transaction_id || ' | ' || esb_pokus_row.status || ' | ' || esb_pokus_row.col01);
      end loop;
      close v_rc;
    end;

    However this isn't supported by Database Interface Designer.