Returning a table of records from an Oracle function into a C# application

Question

We have an oracle package that returns a table of a record type. The definition is:

  TYPE t_daily_array_table IS TABLE OF r_daily_array_rec INDEX BY BINARY_INTEGER;

where r_daily_array_rec is a record with a number of fields in it. We then have a function in the package that's defined as follows:

FUNCTION f_daily_array_table
         (ip_contract_code IN contract.contract_code%TYPE)
RETURN t_daily_array_table
IS ...

And this all works. But I'd like to call this function from my C# application and I've tried about a hundred different variations using 2 different Oracle client drivers (the .NET OracleClient and Oracle's odp.net) and I can't seem to find a way to make it work.

I don't want to document all the different ways I've tried, but my latest iteration using the odp.net driver is this:

using (OracleConnection conn = new OracleConnection("Data Source=dbname;User Id=username;Password=password"))
{
    using (OracleCommand cmd = new OracleCommand("FEED_SCHEDULE_PKG.f_daily_array_table", conn))
    {
        cmd.BindByName = true;
        cmd.Parameters.Add("ip_contract_code", 77116);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        OracleParameter p = new OracleParameter("t_daily_array_table", OracleDbType.Varchar2);
        p.Direction = System.Data.ParameterDirection.ReturnValue;
        cmd.Parameters.Add(p);
        conn.Open();
        object ob = cmd.ExecuteNonQuery();
    }
}

I've tried variations of SQL Statements, such as:

select * from Table(FEED_SCHEDULE_PKG.f_daily_array_table(ip_contract_code)); or begin FEED_SCHEDULE_PKG.f_daily_array_table(ip_contract_code); end;

I've tried ExecuteNonQuery with a return parameter. I've tried ExecuteReader into an IDataReader. I've tried 'returnvalue' for the return value parameter name.

I've searched around and tried everything I can find. None of the example functions is quite like ours. Like I said, I've tried lots of variations. I can't seem to hit on the right setup.

I'd really appreciate some help on this.

Answer 1

You cannot bind to PL/SQL Records from ODP.NET. However, you could use anonymous PL/SQL or a Stored Procedure wrapper to convert it into another type. Here is one example of a workaround:

Using ODP.NET to get a RECORD from PL/SQL function, without touching PL/SQL code