How to call Oracle stored procedure from C# which returns custom Object Type as OUT parameter

Question

I have an Oracle 19 stored procedure which has 2 input parameters and 2 OUT parameters. One of the OUT parameter returns a table. Here is the code I have from C# but doesn't work and throws an error "Parameter binding issue for OUT parameter. I am using VS 2015 and 4.6.1 framework.

This is the code - Oracle stored procedure:

GetSomething(date IN Varachar2, code Varchar2, response OUT table_name, count OUT Number)

C#:

using Oracle.DataAccess.Client;

OracleConnection con = new OracleConnection();
con.ConnectionString = "Connection string here";

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "SP name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

cmd.Parameters.Add("date","01/01/2020");
cmd.Parameters.Add("code", "123");
cmd.Parameters.Add("count", OracleDbType.Int64).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter
{
    ParameterName = "response",
    SourceColumn = "table_name",
    OracleDbType = OracleDbType.Array,
    CollectiveType = OracleCollectionType.PLSQLAssociativeArray
});

If I use CollectiveType, then I am getting a parameter binding error for response. If I remove the collective type, it throws an error saying "wrong number of parameters or type arguments".

Can someone help me here please? What am I doing wrong?

Tried different ways of calling the stored procedure, but I'm always getting an error.

CREATE TYPE table_name AS OBJECT ( column_1 data_type, column_2 data_type, -- this TYPE has 20 columns.. );

Answer 1

In Oracle, there are two scopes where statements can be evaluated:

• The SQL scope where the Oracle engine will parse SQL statements; and
• The PL/SQL scope where the Oracle engine will parse procedural language statements (PL/SQL).

When you define a type using a CREATE TYPE ... AS TABLE OF ... statement then you are defining a nested table collection data type in the SQL scope.

When you define a type in a package or a PL/SQL (anonymous) block then you are defining it in the PL/SQL scope. C# (for some unknown reason) only supports passing PL/SQL Associative Arrays and those are define in the form TYPE ... AS TABLE OF ... INDEX BY ....

You have not included how you define the type you are returning (only including a CREATE TABLE ... DDL statement and not a CREATE TYPE ... or TYPE ... statement); but if you are not using a PL/SQL associative array and are using a nested table or VARRAY collection data type then those types are not supported by C# and you will need to:

• change the procedure to use an associative array; and
• define the type in a package.