How can I call a plsql procedure with a plsql table as a parameter?

[Apparel21]

I have a pl/sql package with a procedure declared like this:

Code: Select all

CREATE OR REPLACE PACKAGE TableExample IS
  TYPE DictType IS TABLE OF CLOB INDEX BY VARCHAR2(256);
  PROCEDURE Test(sInputParameters IN DictType);
END TableExample;

My c# code is this:

Code: Select all

using (var connection = new OracleConnection())
{
    connection.ConnectionString = "User ID=user;Password=pwd;Data Source=dev";
    connection.Open();
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "TableExample.Test";
        cmd.Parameters.Clear();
        cmd.Prepare();
        ....
    }
}

When the cmd.Prepare() is called, Devart gives this error:
Type "OracleDbType.Clob" cannot be used in PL/SQL table parameter.
Stack trace looks like this:
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0)
at Devart.Data.Oracle.OracleParameter.set_ArrayLength(Int32 value)
at Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)
at Devart.Data.Oracle.OracleCommand.CreateStoredProcSql(String procName)
at Devart.Common.DbCommandBase.CreateSql()
at Devart.Common.DbCommandBase.get_Sql()
at Devart.Data.Oracle.OracleCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.Prepare()

Devart.Data.Oracle version is 8.2.90.0
Oracle Database 11g Release 11.2.0.3.0

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

This is the designed behaviour. CLOB type cannot be used in PL/SQL Associative Arrays when working with Oracle database via external libraries. For example, the ODP.NET provider does not allow using CLOB type with PL/SQL Associative Arrays too. We will add the list of the types allowed in PL/SQL Associative Arrays to our documentation.