Error thrown on casting string to CLOB in Array

[bpx03]

Hello,

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;

Thanks for any advices.

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)

Schema Type

Code: Select all

CREATE OR REPLACE TYPE CLOB_ARRAY IS VARRAY(4000) OF CLOB

An example stored procedure that uses it:

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;
        }
    }
  

edit: A similar exception is throw for BLOB Array type where a byte[] is attempted to be set

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)

[Shalex]

Thank you for your report. We have reproduced and are investigating the issue. We will notify you about the result.

[bpx03]

Thank you Shalex,

Is there any update on this issue?

Regards

[Shalex]

The investigation is in progress. As soon as we have any results, we will notify you.

[bpx03]

Hello, could you please let me know if any progress has been made against this issue?

I wonder if that is something that can be fixed in some near future or it is something which never will be fixed.

Thanks

[Shalex]

LOB / BLOB arrays are not supported.
Unfortunately, we cannot find a way to solve or work around the problem. There is no timeframe for the fix at the moment.