Problem with Array Parameters for Procedure Calls

Problem with Array Parameters for Procedure Calls

Postby Matti-Koopa » 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);
Matti-Koopa
 
Posts: 2
Joined: Fri 06 Feb 2015 09:21

Re: Problem with Array Parameters for Procedure Calls

Postby Pinturiccio » 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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleDbType.html
http://www.devart.com/dotconnect/oracle/docs/?Varray.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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleTable.html
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44

Re: Problem with Array Parameters for Procedure Calls

Postby Matti-Koopa » Thu 12 Feb 2015 15:20

This works. Thank you!
Matti-Koopa
 
Posts: 2
Joined: Fri 06 Feb 2015 09:21


Return to dotConnect for Oracle