Call Oracle stored procedure with a nested table from .NET

Question

Given the following table:

create table egyen (
  id number not null, 
  tajszam varchar2(9),
  nev varchar2(10),
  primary key(id)
);

And the following package:

create or replace package pck_egyen is
  type egyentab is table of egyen%rowtype;
  procedure list(ret$ out egyentab);
end pck_egyen;
/
create or replace package body pck_egyen is
  procedure list(ret$ out egyentab) is
  begin
    ret$ := egyentab();
    for rec in (select * from egyen) loop
      ret$.extend;
      ret$(ret$.count) := rec;
    end loop;
  end;
end pck_egyen;
/

Can someone provide me with a working .NET code using ODP.NET 11g that can call this stored procedure, without altering the table or the type or the package in any way? I have no idea how to set up an OracleParameter to support the pck_egyen.egyentab type...

I haven't used UDT before, I gave it a shot but it won't recognize the type embedded in the package. Global types don't support %rowtype. Seems like I'm stuck. Originally I wanted to return ref cursors, works like a charm, but not through a dblink. Too many silly limitations.

Answer 1

Try something like

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "pck_egyen.list";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("ret$", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

cmd.ExecuteNonQuery();

// Read the result set
OracleRefCursor orarefcur = (OracleRefCursor) cmd.Parameters[0].Value;
OracleDataReader dr = orarefcur.GetDataReader();    
while (dr.Read())
{
    System.Console.WriteLine(dr["tajszam"]);
}

Answer 2

Its an interesting problem, specifically because of the need to use both .NET to consume the data and stored procedures across dblinks. The limitation of not using cursors across dblinks led you to pl/sql tables, but you cannot use these easily in .NET (without going through the pain of setting up and maintaining global objects).

So, I propose using both ref cursor functions and procedures with output table params. You'll be able to call the ref cursor functions from .NET easily (where you don't need to select across dblinks), and for dblink database work, use the corresponding procedures. For example:

create table test1 (
    col1 number,
    col2 varchar2(10),
    col3 date default sysdate not null
);
insert into test1(col1,col2) values (1,'A');
insert into test1(col1,col2) values (1,'X');
insert into test1(col1,col2) values (2,'B');

commit;

CREATE OR REPLACE package TEST_PKG as
  type t_test1_tab is table of test1%rowtype;
  -- weak ref cursor
  function get_test1_cur (i_num in number) return sys_refcursor;
  -- uses rowtype for table
  procedure get_test1_tab(i_num in number, o_tab out t_test1_tab);
end;


CREATE OR REPLACE package body TEST_PKG as
  function get_test1_cur (i_num in number) return sys_refcursor is
    l_cur sys_refcursor;
  begin
    open l_cur for select * from test1 where col1=i_num;
    return l_cur;
  end;

  procedure get_test1_tab(i_num in number, o_tab out t_test1_tab) is
    l_rec test1%rowtype;
    l_tab t_test1_tab := t_test1_tab();
    l_cur sys_refcursor;
  begin
    l_cur := get_test1_cur(i_num);
    loop
      fetch l_cur into l_rec;
      exit when l_cur%notfound;
      l_tab.extend;
      l_tab(l_tab.last) := l_rec;
    end loop;
    close l_cur;
    o_tab := l_tab;
  end;

end;

Put any logic needed inside of your ref cursor function. The procedure simply calls the function and creates the table (using rowtype).

Use the procedure for db calls across dblinks:

declare
  l_tab test_pkg.t_test1_tab@dblinkA;
begin
  test_pkg.get_test1_tab@dblinkA(1, l_tab);
  -- show count
  dbms_output.put_line('Table has ' || l_tab.count || ' rows.');
end;

Use the function for odp.net calls. For example (code snippet):

OracleConnection con = new OracleConnection(connStr);

// create the command object and set attributes
OracleCommand cmd = new OracleCommand("test_pkg.get_test1_cur", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = false;

// create parameter object for the cursor
OracleParameter p_refcursor = new OracleParameter();
// create any input parameters to the function
OracleParameter p_num = new OracleParameter();

p_refcursor.OracleDbType = OracleDbType.RefCursor;
p_refcursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_refcursor);

// add any input parameters
p_num.OracleDbType = OracleDbType.Int32;
p_num.Direction = ParameterDirection.Input;
p_num.Value = 1;
cmd.Parameters.Add(p_num);

// create a data adapter to use with the data set
OracleDataAdapter da = new OracleDataAdapter(cmd);

// create the data set
DataSet ds = new DataSet();

// fill the data set
da.Fill(ds);

This approach should be easy to maintain, since not only are you using rowtype, but the procedure is simply calling the function.

Answer 3

One approach is to use some PL/SQL to read the data in the table object into PL/SQL associative arrays, one per column, and then read these using ODP.NET:

    private const string PlSqlBlock = @"
        DECLARE
          l_egyen_tab  pck_egyen.egyentab;
        BEGIN
          pck_egyen.list(l_egyen_tab);
          FOR i IN 1..l_egyen_tab.COUNT
          LOOP
            :ids(i) := l_egyen_tab(i).id;
            :tajszams(i) := l_egyen_tab(i).tajszam;
            :nevs(i) := l_egyen_tab(i).nev;
          END LOOP;
        END;";

    public static void ListEgyenTable(OracleConnection con)
    {
        using (var cmd = new OracleCommand(PlSqlBlock, con))
        {
            OracleParameter idParam = cmd.Parameters.Add("ids", OracleDbType.Decimal);
            OracleParameter tajszamParam = cmd.Parameters.Add("tajszams", OracleDbType.Varchar2);
            OracleParameter nevParam = cmd.Parameters.Add("nevs", OracleDbType.Varchar2);

            int arraySize = 1000;
            int[] varcharArrayBindSize = Enumerable.Repeat(4000, arraySize).ToArray();

            foreach (OracleParameter param in cmd.Parameters)
            {
                param.Direction = ParameterDirection.Output;
                param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                param.Size = arraySize;
                if (param.OracleDbType == OracleDbType.Varchar2)
                {
                    param.ArrayBindSize = varcharArrayBindSize;
                }
            }

            cmd.ExecuteNonQuery();

            if (idParam.Value is OracleDecimal[] && tajszamParam.Value is OracleString[] && nevParam.Value is OracleString[])
            {
                List<decimal> ids = (idParam.Value as OracleDecimal[]).Select(dec => dec.Value).ToList();
                List<string> tajszams = (tajszamParam.Value as OracleString[]).Select(str => str.Value).ToList();
                List<string> nevs = (nevParam.Value as OracleString[]).Select(str => str.Value).ToList();

                for (int i = 0; i < ids.Count; ++i)
                {
                    Console.WriteLine("Got id {0}, tajszam {1}, nev {2}", ids[i], tajszams[i], nevs[i]);
                }
            }
            else
            {
                Console.WriteLine("Sorry, returned data not as expected :(");
            }
        }
    }

The only complication here is arraySize. This value needs to be at least the number of rows that will be returned from your stored procedure, and if it is too small you will get an ORA-06513 'PL/SQL: index for PL/SQL table out of range for host language array' error.

I created your table and package, and inserted into the table the following test data:

SQL> select * from egyen;

        ID TAJSZAM   NEV
---------- --------- ----------
         1 abc       defg
         2 def       mnop
         3 ghi       qrstu
         4 jkl       vwxyz

When I ran the C# code above, I got the following output:

Got id 1, tajszam abc, nev defg
Got id 2, tajszam def, nev mnop
Got id 3, tajszam ghi, nev qrstu
Got id 4, tajszam jkl, nev vwxyz