How do you output a plsql array via ref cursor

arraycplsql

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;

Best Answer

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

Related Solutions

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Oracle strong ref cursor with dynamic sql

There are probably better ways to achieve your underlying goal, but if you really want values from dynamic SQL into a strongly typed refcursor I guess you could do something like this:

create table TESTTABLE(TESTID number not null);
create global temporary table TEMPTESTTABEL(TESTID number not null);

create or replace procedure testprocedure is 
  type testtablerefcursor is ref cursor return TESTTABLE%rowtype;
  type testtabletable is table of TESTTABLE%rowtype;
  mycursor testtablerefcursor;
  mysyscursor SYS_REFCURSOR;
  mytesttable testtabletable;
begin
  open mysyscursor for 'select * from TESTTABLE';
  fetch mysyscursor bulk collect into mytesttable;
  close mysyscursor;
  forall i in mytesttable.first..mytesttable.last
   insert into TEMPTESTTABEL values mytesttable(i);
  open mycursor for select testid from TEMPTESTTABEL;
end;
/

Best Answer

Related Solutions

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

Oracle strong ref cursor with dynamic sql

Related Question