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

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

Postby 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.
Apparel21
 
Posts: 15
Joined: Mon 01 Sep 2008 01:55

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

Postby 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: 1996
Joined: Wed 02 Nov 2011 09:44

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

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1996
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle