Oracle adapter – getting multiple results (rowset)

Hi,

I always executed packages with oracle adapter that return some single return value.

Now, im trying to execute a package that should return row set, but the add adapter generator cannot recognize it.

Any help?

This is the package:

 

CREATE OR REPLACE package ampo_bpm_orders_update_pkg is

 

  — Public type declarations

  type bpm_order_rec is record (

  OL_REQUEST_ID  AMPO_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE,

  REQUEST_ID AMPO_REQ_INTERFACE_RESULTS_ALL.Request_Id%TYPE,

  ERROR_DESC AMPO_REQ_INTERFACE_RESULTS_ALL.ERROR_DESC%TYPE,

  PROCESS_FLAG AMPO_ORDER_HEADERS_ALL.PROCESS_FLAG%TYPE);

  TYPE bpm_order_arr IS TABLE OF bpm_order_rec

      INDEX BY BINARY_INTEGER;

 

 

  — Public function and procedure declarations

  Procedure main (order_arr out  bpm_order_arr);

 

end ampo_bpm_orders_update_pkg;

 

 

 

 

 

CREATE OR REPLACE package body ampo_bpm_orders_update_pkg is

  — Function and procedure implementations

  procedure update_process_flag is

  begin

  update AMPO_ORDER_HEADERS_ALL set PROCESS_FLAG = 3 where PROCESS_FLAG = 2;

  commit;

  update AMPO_ORDER_HEADERS_ALL set PROCESS_FLAG = 5 where PROCESS_FLAG = 4;

  commit;

  EXCEPTION

   WHEN OTHERS THEN

         dbms_output.put_line ('*** ERROR *** PROCEDURE: update_process_flag****' || SQLERRM); 

         RAISE;

  end update_process_flag;

 

  Procedure main (order_arr out bpm_order_arr) is

   Cursor main_select is

   select H.ORDER_NUMBER OL_REQUEST_ID,

          R.REQUEST_ID REQUEST_ID,

          R.ERROR_DESC  ERROR_DESC,

          H.PROCESS_FLAG PROCESS_FLAG

   from    AMPO_REQ_INTERFACE_RESULTS_ALL R, AMPO_ORDER_HEADERS_ALL H

   where H.ORDER_NUMBER = R.OL_REQUEST_ID AND (H.PROCESS_FLAG = 2 OR H.PROCESS_FLAG = 4);

  

   i number :=0;

  begin

   FOR main_order IN main_select LOOP

   order_arr(i).OL_REQUEST_ID := main_order.ol_request_id;

   order_arr(i).REQUEST_ID := main_order.request_id;

   order_arr(i).ERROR_DESC := main_order.error_desc;

   order_arr(i).PROCESS_FLAG  := main_order.process_flag;

   i:=i+1;

   end loop;

    update_process_flag;

    EXCEPTION

                                                 WHEN OTHERS THEN

         dbms_output.put_line ('*** ERROR *** PROCEDURE:MAIN****' || SQLERRM); 

         RAISE;

 

  end  main;

 

end ampo_bpm_orders_update_pkg;

/

 

10 xs