Wsrtjtyk

I am having a problem using Oracle Data Provider with .NET . I am using an array of a User-Defined Objects as an IN parameter to a stored procedure. I have added the database schema to Visual Studio 2015 Server Explorer and generated the Custom Type Class corresponding to the UDT I am using. I am using the following code to call the procedure.

OracleCommand cmd = DataBase.Connection.CreateCommand();

cmd.CommandText = "MYPROCEDURE";

cmd.CommandType = CommandType.StoredProcedure;

cmd.BindByName = true;



MY_TYPE arr = new MY_TYPE[2];

arr[0] = new MY_TYPE(1, 2);

arr[1] = new MY_TYPE(3, 4);



OracleParameter pEntries = new OracleParameter();

pEntries.ParameterName = "ENTRIES";

pEntries.Direction = ParameterDirection.Input;

pEntries.OracleDbType = OracleDbType.Array;

pEntries.UdtTypeName = "MY_TYPE";

pEntries.Value = arr;

pEntries.Size = 2;



cmd.Parameters.Add(pEntries);

cmd.Connection.Open();

cmd.ExecuteNonQuery();

The problem is it throws a NullReferenceException from within the Oracle Driver, Specifically from Oracle.DataAccess.Types.OracleUdt.SetValue(OracleConnection con, IntPtr pUdt, Int32 attrIndex, Object value, Object statusArray).

Notes:

• The connection is tested with other procedure calls and it is working properly.


• I have made sure through the debugger that the array elements are not null and their "IsNull" property is set to false, and that their members are not null and each members "IsNull" property is set to false.


• 
  

  The UDT is defined as follows:

  
  
  

   CREATE OR REPLACE TYPE my_type AS OBJECT
  
   (
  
    id NUMBER;
  
    value NUMBER;
  
   )
  
  

  
  


• 
  

  The procedure takes a custom collection type defined as follows:

  
  
  

  CREATE OR REPLACE my_type_varray AS VARRAY(50) OF my_type
  
  

  
  



• The parameter's settings are the only accepted settings, I have tried creating a custom type for a collection but it generates an error saying "wrong number or types of arguments in call to MYPROCEDURE", these setting generate the NullReferenceException, which means it accepted the parameters and moved on to process them.




• For sake of simplicity I have omitted a lot of the code, and I have written it manually. But I will post it if I had to.

Additional info:

stacktrace:

Oracle.DataAccess.Types.OracleUdt.SetValue(OracleConnection con, IntPtr pUdt, Int32 attrIndex, Object value, Object statusArray)   

at Oracle.DataAccess.Types.OracleUdt.SetValue(OracleConnection con, IntPtr pUdt, Int32 attrIndex, Object value)   

at Oracle.DataAccess.Client.OracleParameter.SetUDTFromArray(OracleConnection conn, Object array, Int32 i)   

at Oracle.DataAccess.Client.OracleParameter.PreBind_Collection(OracleConnection conn)   

at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize, Boolean bIsFromEF, Boolean bIsSelectStmt)   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

I have created a simple test procedure called TEST, which takes a single instance of the UDT. The procedure is defined as follows:

FUNCTION test(obj in MY_TYPE) RETURN NUMBER IS

BEGIN

  RETURN obj.id*obj.value;

END;

The code for calling the procedure is :

OracleCommand cmd = DataBase.Connection.CreateCommand();

cmd.CommandText = "TEST";

cmd.CommandType = CommandType.StoredProcedure;

cmd.BindByName = true;



MY_TYPE obj = new MY_TYPE(2, 3);



OracleParameter pEntries = new OracleParameter();

Entries.ParameterName = "obj";

pEntries.Direction = ParameterDirection.Input;

pEntries.OracleDbType = OracleDbType.Object;

pEntries.UdtTypeName = "MY_TYPE";

pEntries.Value = obj;

cmd.Parameters.Add(pEntries);



// -- omitted some code for the return value parameter



cmd.Connection.Open();

cmd.ExecuteNonQuery();

The previous code worked properly and the result was 6.

As far as I know you cannot use VARRAY as parameter. The only supported collection type are Associative Arrays (Index-By Tables), e.g.

TYPE TArrayOfNumber IS TABLE OF NUMBER INDEX BY INTEGER;

So, you would have to pass two parameters, one array of id and one array for value. Then you can create the OBJECT type within PL/SQL

PROCEDURE MYPROCEDURE(IdList IN TArrayOfNumber, ValueList IN TArrayOfNumber ) IS



   my_type_list my_type_varray;

BEGIN



   FOR i IN IdList.FIRST..IdList.LAST LOOP

      my_type_list(i) := my_type(IdList(i), ValueList(i));

   END LOOP;



...



END;

and in C# like this:

var par1 = cmd.Parameters.Add("IdList ", OracleDbType.Int16, ParameterDirection.Input);

par1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

par1.Value = new int { 1, 2};

par1.Size = 2:





var par2 = cmd.Parameters.Add("ValueList ", OracleDbType.Int16, ParameterDirection.Input);

par2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

par2.Value = new int { 3, 4};

par2.Size = 2: