Devart Forums

Posted: **Fri 06 Feb 2015 09:49**

We recently updated dotConnect for Oracle from a very old version to 8.4.191.
Now procedure calls with arrays for parameters don't work any more.

I made a minimal example to show the problem.

I have a procedure in a package that looks like this:

Code: Select all

PROCEDURE ADV_P_TEST (vaIN IN VARCHAR2_10_ARRAY) IS  
  BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM DUAL WHERE 0 = 1';
END ADV_P_TEST;

The input type VARCHAR2_10_ARRAY is defined in userschema DEZADMIN like this:

Code: Select all

create or replace type VARCHAR2_10_ARRAY as table of VARCHAR2(10);

My call from .NET looks like this:

Code: Select all

OracleParameter op = new OracleParameter("VAIN", Devart.Data.Oracle.OracleDbType.Array);
op.Direction = ParameterDirection.Input;
op.Value = new string[] { "TEST1", "TEST2" };
op.ObjectTypeName = "DEZADMIN.VARCHAR2_10_ARRAY";
<OracleCommand>.Parameters.Add(op);

This worked in the old version. But in the new one I get the following exception on <OracleDataAdapter>.Fill(<DataSet>):

Code: Select all

A first chance exception of type 'System.IndexOutOfRangeException' occurred in Devart.Data.Oracle.dll
Additional information: Index was outside the bounds of the array.

After a search I tried a different approach with setting the OracleDbType to VarChar and setting an ArrayLength like this:

Code: Select all

OracleParameter op = new OracleParameter("VAIN", Devart.Data.Oracle.OracleDbType.VarChar);
op.Direction = ParameterDirection.Input;
op.Value = new string[] { "TEST1", "TEST2" };
op.ObjectTypeName = "DEZADMIN.VARCHAR2_10_ARRAY";
op.ArrayLength = 2;
<OracleCommand>.Parameters.Add(op);

But this results in a different exception:

Code: Select all

A first chance exception of type 'Devart.Data.Oracle.OracleException' occurred in Devart.Data.Oracle.dll
Additional information: ORA-06550: Zeile 2, Spalte 3:
PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'ADV_P_TEST'
ORA-06550: Zeile 2, Spalte 3:
PL/SQL: Statement ignored

Can anyone tell me how to solve this issue?

Other array types I am using are as follows:

Code: Select all

create or replace type INT_ARRAY as table of INT;
create or replace type NUMBER_10_3_ARRAY as table of NUMBER(10,3);

Posted: **Wed 11 Feb 2015 16:16**

OracleDbType.Array is used for working with the VARRAY type. For more information, refer to
http://www.devart.com/dotconnect/oracle ... bType.html
http://www.devart.com/dotconnect/oracle ... array.html

In your case it's better to use OracleDbType.Table. You can use the following code:

Code: Select all

OracleParameter op = new OracleParameter("vaIN", Devart.Data.Oracle.OracleDbType.Table);
op.Direction = ParameterDirection.Input;
op.ObjectTypeName = "DEZADMIN.VARCHAR2_10_ARRAY";
OracleTable table = new OracleTable(OracleType.GetObjectType("DEZADMIN.VARCHAR2_10_ARRAY", conn));
table.Add("TEST1");
table.Add("TEST2");
op.Value = table;
<OracleCommand>.Parameters.Add(op);

For more information, refer to http://www.devart.com/dotconnect/oracle ... Table.html

Posted: **Thu 12 Feb 2015 15:20**

This works. Thank you!

Devart Forums

Problem with Array Parameters for Procedure Calls

Problem with Array Parameters for Procedure Calls

Re: Problem with Array Parameters for Procedure Calls

Re: Problem with Array Parameters for Procedure Calls