IBM Sterling Ideas

formerly Watson Supply Chain

Submit new product ideas for Watson Supply Chain solutions. Before you submit, please review existing ideas; if an idea close to yours already exists, it's better to add comments or vote on the existing idea. We will review your ideas and use them to help prioritize our product development. Best of all, the portal will automatically update you when the status of your idea has been changed. Order Management, Store Engagement, Watson Order Optimizer, Inventory Visibility, CPQ and Call Center are now part of Watson Supply Chain

Connect with IBM experts and your peers on the Supply Chain Collaboration Community and the Order Management Interest Group

Submit ideas for other Watson Customer Engagement Products:

Watson Marketing
Watson Campaign Automation
Watson Commerce

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
    December 06, 2018 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.