Oracle Stored Procedure with User Defined Type in C#

Question

I am trying to use a stored procedure for

variable data refcursor;
execute MyPkg(to_date('2010-08-11','yyyy-MM-dd'), TEXT_ARRAY('tempa','tempb'), :data);

Where TEXT_ARRAY is a udt of type TABLE OF varchar2(255)

C# Code Snippet:

        var cmd = new OracleCommand("BEGIN MyPkg(:Param1, :Param2, :Param3); END;", con);
        var param1 = cmd.Parameters.Add("Param1", OracleDbType.Date);
        var param2 = cmd.Parameters.Add("Param2", OracleDbType.Varchar2);
        var param3 = cmd.Parameters.Add("Param3", OracleDbType.RefCursor);

        param1.Direction = ParameterDirection.Input;
        param2.Direction = ParameterDirection.Input;
        param3.Direction = ParameterDirection.Output;

        param1.Value = DateTime.Now;


        param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

        param2.Value = new[] { "tempa", “tempb”};
        param2.Size = 2;
        param2.ArrayBindSize = new[] { 20, 20 };

        cmd.ExecuteNonQuery();

And am getting error:

An exception of type 'Oracle.ManagedDataAccess.Client.OracleException' occurred in Oracle.ManagedDataAccess.dll but was not handled in user code Additional information: External component has thrown an exception. -- ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'MyPkg‘\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored"

Any clues on how can I fix this?

Answer 1

You can use OracleDbType.Array instead of varchar2 for param2.