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

• user151263: Thanks! It worked great. Now there is an example out there for anybody to use. Talks for the contribution.
• Alexander: I wonder how to return array structrure from Oracle into C# using Oracle UDF... No any example around.
• Gerard H. Pille: @Alexander why not as above, returning row per row from your array and reconstruct it in C#.
• Alexander: I've already succeeded reading ref cursor output variable via Read. Thanks :) just had no ready example, so had to reconstruct the code from different sources...
• Gerard H. Pille: I had never worked with C#, so I installed Mono on Linux and so a was able to read the results from a select on a 12c database.