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.

Not sure if this link is of any help - oracle.com/technetwork/issue-archive/2007/07-jan/… — OldProgrammer
– OldProgrammer, Commented Mar 26, 2015 at 0:21
Unfortunately, not really. The example is returning multiple arrays of individual things like varchar and number whereas mine is returning an array of records. So, for example, I don't know what to put for the parameter's OracleDbType in that case. And if I ignore that issue and try to set mine up like that, I get the error OracleParameter.ArrayBindSize is invalid. And even if I try to set up the ArrayBindSize, it never seems to like any of the different things I've tried for that. — Pete
– Pete, Commented Mar 26, 2015 at 14:11

Community · Accepted Answer · 2017-05-23 12:28:25Z

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

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Mar 26, 2015 at 16:22

Christian Shay

2,63315 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pete Over a year ago

Thanks. What a pain in the rear. I'm not a big fan of Oracle. This is just one more reason to add to the list. We ended up writing a procedure that dumps the results to a table. We're mainly trying to diagnose problems with this old code and I'm trying to figure out what's going on underneath (no documentation, messy code and poor table, column and variable naming contributing to the problems...)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related