How to pass an array of strings from C# to an Oracle stored procedure

Question

I have the following code to pass a table of strings to the Oracle stored procedure called spTest:

using (OracleConnection oracleConnection = new OracleConnection(connectionString))
{
    oracleConnection.Open();
    OracleCommand oracleCommand = new OracleCommand();

    oracleCommand.Parameters.Add(new OracleParameter
    {
        ParameterName = "eventids",
        Direction = ParameterDirection.Input,
        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
        Value = new string[] { "Test1", "Test2" },
        Size = 2,
        UdtTypeName = "T_STRING_TAB"
    });

    oracleCommand.Connection = oracleConnection;
    oracleCommand.CommandText = "spTest";
    oracleCommand.CommandType = CommandType.StoredProcedure;

    using (OracleDataReader oracleDataReader = oracleCommand.ExecuteReader())
    {
        while (oracleDataReader.Read())
        {
            int fieldCount = oracleDataReader.FieldCount;
        }
    }               
}

I have defined the type and stored procedure as follows in Oracle:

create type T_STRING_TAB is table of varchar2(260) index

create or replace procedure spTest(eventids in T_STRING_TAB)
as
starteventid integer;
begin
starteventid := 1000000;
end;

When I run the code I get the following error:

Oracle.ManagedDataAccess.Client.OracleException
HResult=0x80004005
Message=ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SPTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Source=Oracle Data Provider for .NET, Managed Driver

I am using Oracle.ManagedDataAccess.dll version number 2.0.18.3.

Does anyone know what I am doing wrong?

Thanks Ian

Answer 1

I have defined the type and stored procedure as follows in Oracle:

create type T_STRING_TAB is table of varchar2(260) index

This is syntactically invalid as it has an extra INDEX keyword at the end but if you fix that:

create type T_STRING_TAB is table of varchar2(260);

That is a collection data type defined in the SQL scope. C# does not currently support passing a non-associative array.

Your C# code is expecting an associative array defined in the PL/SQL scope:

CollectionType = OracleCollectionType.PLSQLAssociativeArray,

To make a PL/SQL associative array you need to do it inside of a package and to include an INDEX BY <numeric data type> clasue:

CREATE PACKAGE package_name AS
  TYPE STRING_MAP IS TABLE OF VARCHAR2(260) INDEX BY BINARY_INTEGER;
END;
/

Then your procedure can be:

create or replace procedure spTest(
  eventids in PACKAGE_NAME.STRING_MAP
)
as
  starteventid integer;
begin
  starteventid := 1000000;
end;
/