I'm trying pass a custom array to the stored procedure. Unfortunately while the code works for integer arrays, varchar arrays etc with similar code I am unable succesfully pass CLOB Array because of an exception that I'm getting.
Am I doing something wrong or CLOB / BLOB etc arrays are not supported?
I'm using Devart.Data.Oracle version 9.8.838.0 (latest nugget package)
Exception is thrown in the following line of ClobyArray:
Code: Select all
oracleArray.Value = data;
Code: Select all
System.InvalidCastException : Unable to cast object of type 'System.String' to type ' '.
at .(Object , OracleAttribute , IntPtr )
at . (Object , Int32 )
at .(Object )
at .Add(Object )
at Devart.Data.Oracle.NativeOracleArray.set_Value(Array value)
at OracleDevArt.ClobArray.ToOracleArray(OracleConnection con) in C:\dev\repos\OracleTesting\OracleDevArt\DevArtProvider.cs:line 392
at Devart.Data.Oracle.OracleParameter.(OracleDbType , Object , Object , Byte[] , Hashtable , Int32 , Int32 , Int32 , Int32 , Int32 , Boolean , OracleConnection , ParameterDirection , OracleType , , Boolean& )
at Devart.Data.Oracle.OracleParameter.( & , Boolean , OracleConnection , Byte[] , Hashtable , , Boolean& , Int32 )
at Devart.Data.Oracle.OracleCommand.( , Int32 , OracleParameterCollection , , Boolean& )
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at OracleDevArt.DevArtProvider.PassClobArray(String[] data)
Code: Select all
CREATE OR REPLACE TYPE CLOB_ARRAY IS VARRAY(4000) OF CLOB
Code: Select all
PROCEDURE PASSCLOBARRAY(cArray IN TESTSCHEMA.CLOB_ARRAY,
iResult OUT NUMBER) IS
BEGIN
iResult := cArray.Count;
END;
Code: Select all
public int? PassClobArray(string[] data)
{
using (var connection = new OracleConnection(connectionString))
{
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.PassParametersByName = true;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TESTSCHEMA.ORACLETESTING.PASSCLOBARRAY";
var iResult = new OracleParameter("iResult", OracleDbType.Number, ParameterDirection.Output);
cmd.Parameters.Add(iResult);
var clobarray = new ClobArray(data);
var parameter = new OracleParameter("cArray", OracleDbType.Array, clobarray, ParameterDirection.Input)
{
ObjectTypeName = "TESTSCHEMA.CLOB_ARRAY"
};
cmd.Parameters.Add(parameter);
cmd.ExecuteNonQuery();
return GetInteger(iResult);
}
}
}
Custom Type:
Code: Select all
public class ClobArray : ICustomOracleArray
{
public const string TYPE = "TESTSCHEMA.CLOB_ARRAY";
private readonly string[] data;
public ClobArray(string[] data)
{
this.data = data;
}
public void FromOracleArray(NativeOracleArray oraArray)
{
throw new NotImplementedException();
}
public NativeOracleArray ToOracleArray(OracleConnection con)
{
NativeOracleArray oracleArray = new NativeOracleArray(TYPE, con);
oracleArray.Value = data;
return oracleArray;
}
}
Code: Select all
System.InvalidCastException : Unable to cast object of type 'System.Byte[]' to type ' '.
at .(Object , OracleAttribute , IntPtr )
at . (Object , Int32 )
at .(Object )
at .Add(Object )
at Devart.Data.Oracle.NativeOracleArray.Add(Object value)