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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Apparel21
Posts: 15
Joined: Mon 01 Sep 2008 01:55

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

Post by Apparel21 » Wed 04 Jun 2014 04:02

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
Last edited by Apparel21 on Wed 04 Jun 2014 04:04, edited 1 time in total.

Pinturiccio
Devart Team
Posts: 2138
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Wed 04 Jun 2014 11:52

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

Pinturiccio
Devart Team
Posts: 2138
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Wed 11 Jun 2014 12:01

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.

Post Reply