how do you output a plsql array via ref cursor

Question

I have a legacy procedure that returns a plsql assoc table to a C++ DLL. Now I need to call it via C# and ODP.NET. I have been unable to find any info on how to call a proc that returns a pl/sql assoc array (plenty on how to send data to one that takes as an input).

Thinking that if I can have the proc return a ref cursor then it should not be an issue since I have done it plenty of times

However, have not found examples of doing that and all attempts have failed.

Any help would be welcomed.

code below. it provides an orderid as input. it calls another proc relOrders that returns an array that is put into pl sql table.

TYPE tab_number12 IS TABLE OF NUMBER (12)
      INDEX BY BINARY_INTEGER;

PROCEDURE GetOrders (p_Orderid          IN     NUMBER,
                                    p_subOrderIds            OUT tab_number12)
   IS
      allOrder   DBMS_SQL.number_table;

   BEGIN
      allOrder (1) := p_Orderid;

      relOrders (allOrder);

      FOR i IN allOrder.FIRST .. allOrder.LAST
      LOOP
         p_subOrderIds (i) := allOrder (i);
      END LOOP;
   END;

Answer 1

I couldn't find an example either, so I concocted this:

CREATE TYPE t_tf_row AS OBJECT (
  id           varchar2(20),
  description  VARCHAR2(50)
);
/
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

create or replace package aa2rc as
  FUNCTION get_tab_ptf
    RETURN t_tf_tab PIPELINED;
  PROCEDURE GetOrders (
    p_Orderid     IN  NUMBER,
    p_subOrderIds OUT sys_refcursor);
end aa2rc;
/

create or replace package body aa2rc as
  type allOrder_t is table of varchar2(50) index by varchar2(20);

  allOrder allOrder_t;

  FUNCTION get_tab_ptf
    RETURN t_tf_tab PIPELINED AS
    i varchar2(20);
    BEGIN
          i := allOrder.first;
          while (i is not null) loop
            PIPE ROW(t_tf_row(i, allOrder(i)));
            i := allOrder.next(i);
          END LOOP;

          RETURN;
    END;

  PROCEDURE GetOrders (
    p_Orderid     IN  NUMBER,
    p_subOrderIds OUT sys_refcursor)
    IS

      procedure relorders (p_ord in out allOrder_t) is
        i number;
        begin
          for i in 1 .. 11 loop
            p_ord('Index no. ' || (p_ord('One') + i))
              := 'Value ' || (p_ord('One') + i);
          end loop;
        end;

    BEGIN
      allOrder ('One') := p_Orderid;

      relOrders (allOrder);

      open p_suborderids for
        select * from table(aa2rc.get_tab_ptf);
    END;
end aa2rc;
/

I'm not that sharp with C#, but ran a test with sqlplus:

variable R refcursor
execute aa2rc.getorders(5,:r);
print r

which gave:

ID                   DESCRIPTION
-------------------- --------------------------------------------------
Index no. 10         Value 10
Index no. 11         Value 11
Index no. 12         Value 12
Index no. 13         Value 13
Index no. 14         Value 14
Index no. 15         Value 15
Index no. 16         Value 16
Index no. 6          Value 6
Index no. 7          Value 7
Index no. 8          Value 8
Index no. 9          Value 9
One                  5