ORA-01486: size of array element is too large

ORA-01486: size of array element is too large

Postby martinjw » Thu 12 Jun 2014 11:39

Using array binding with specific elements causes an Oracle error.

The first two elements are NULL, then a VARCHAR over 1800 characters.

Every other combination works:
    If the first two elements are a VARCHAR, then NULL, it works.
    If the first two elements are NULL and a VARCHAR of 1799, it works.
    Two NULLs, or two VARCHARs, everything else works!

Note the same code used to work with an old Devart v6 dll. We upgraded to v8 (8.4.171.0) and encountered the error.

To reproduce:
1. Connect to an Oracle database.
2. Execute the Oracle package body script.
Code: Select all
CREATE OR REPLACE PACKAGE "DOWN"."TST_DEVART" IS
   TYPE p_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
 
  PROCEDURE pp (inp in p_type);
END TST_DEVART;

/
create or replace
PACKAGE BODY TST_DEVART IS
    PROCEDURE pp (inp in p_type) IS
      BEGIN
        FOR i IN 1..inp.count LOOP
          dbms_output.put_line(inp(i));
        END LOOP;
      END pp;
END TST_DEVART;


3. Create a new Visual Studio solution with a C# console project.
4. Add a reference to the Devart.Data and Devart.Data.Oracle assemblies to the console project.
5. Use the following:

Code: Select all
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Devart.Data.Oracle;

namespace TestDevart
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new OracleConnection())
            {
                conn.Direct = true;
                conn.UserId = "scott";
                conn.Password = "password";
                conn.Port = 1521;
                conn.Sid = "IXXXX";
                conn.Server = "ixxxx.dgfh.hjh";
                conn.Open();

                using (var cmd = conn.CreateCommand("TST_DEVART.PP"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    var values = new List<string>();
                    values.Add(null);
                    values.Add(new string('A', 2000));

                    //  reverse them and it works!

                    var param = new OracleParameter("inp", OracleDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = values.ToArray();
                    param.ArrayLength = 2;
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}


6. Modify the connection information of the Oracle database in Program.cs.
7. Run the console project.

Exepected result:

Console project terminates without error.

Actual result:

An OracleException is thrown with the following message:
ORA-01486: size of array element is too large
martinjw
 
Posts: 3
Joined: Mon 31 Mar 2014 17:53

Re: ORA-01486: size of array element is too large

Postby Pinturiccio » Fri 13 Jun 2014 12:27

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44

Re: ORA-01486: size of array element is too large

Postby martinjw » Fri 08 Aug 2014 07:18

Any news on this? Is there any ETA for a fix?
martinjw
 
Posts: 3
Joined: Mon 31 Mar 2014 17:53

Re: ORA-01486: size of array element is too large

Postby thakkarmayank » Fri 08 Aug 2014 14:51

Hi,
We are also having the same issue. Any updates on this? We need a fix immediately.

~Mayank
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: ORA-01486: size of array element is too large

Postby Pinturiccio » Wed 13 Aug 2014 10:53

We have fixed the bug with parameters of type array of strings, containing strings longer than 1800 characters, when connected in the Direct mode to a unicode server with the Unicode connection string parameter set to false. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44

Re: ORA-01486: size of array element is too large

Postby Pinturiccio » Thu 14 Aug 2014 15:18

New build of dotConnect for Oracle 8.4.225 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=30176
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44

Re: ORA-01486: size of array element is too large

Postby thakkarmayank » Wed 03 Sep 2014 15:36

Pinturiccio,
We upgraded to the new version 8.4.225 and tried our application. We get the same error again.

We downgraded to version 6.80.350.0 and it works fine. (all other parameters being the same).

We are using ExecuteNonQuery() method. The sql statement, tied to the method, is an anonymous SQL block, which accepts 35 parameters, each of type array and length 72. The anonymous block then processes the arrays (iterates over the array length) and inserts the records. (Our own implementation of executeArray(), working with an anonymous block). The anonymous block contains error handling at every row level, but that code is not even hit.

The watch window shows the following error:

Code: Select all
   
ORA-01486: size of array element is too large
at Devart.Data.Oracle.dn.d()
   at Devart.Data.Oracle.bq.f()
   at Devart.Data.Oracle.bq.a()
   at Devart.Data.Oracle.h.a(Int32 A_0, b8 A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
   at Davita.VillageHealth.CkcConnect.SyncProvider.Oracle.OracleSyncProvider.SaveGenericData(Connection connection, SyncAction action, GenericData genericData)



This error shows up consistently in the new version. We are using Direct mode.

I can not post the code here because of its sensitive nature. Is there a way I can send you the code more privately?

We have numerous apps using Devart dotConnect for Oracle. Since one of the apps is failing, we are not able to upgrade any of the apps. This is affecting our release cycles.

Please look into the matter and let us know asap.

PS: I have a ongoing valid subscription. Can I get expedited support on this issue?

~Mayank
Last edited by thakkarmayank on Thu 04 Sep 2014 18:34, edited 1 time in total.
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: ORA-01486: size of array element is too large

Postby Pinturiccio » Thu 04 Sep 2014 15:57

We could not reproduce the issue with dotConnect for Oracle 8.4.225 and the sample you provided in the first post.

thakkarmayank wrote:The sql statement, tied to the method, is an anonymous SQL block, which accepts 35 parameters, each of type array and length 72...

The application differs from the one you described in the first post, and probably the reasons of the issue are different for the sample in the first post and for the sample, described in the last post.

thakkarmayank wrote:I can not post the code here because of its sensitive nature. Is there a way I can send you the code more privately?

You can send us the sample via our contact form
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44

Re: ORA-01486: size of array element is too large

Postby thakkarmayank » Tue 16 Sep 2014 20:23

After much debugging (and trying many combinations), we found that if a parameter whose OracleDbType is set to OracleDbType.Varchar, is bound to an array containing a integer, the above issue can be reproduced. (This can be reproduced only with one record too.)
Please note that we are using arraybinding.

This issue is not present in the older version of Devart dotConnect. It seems like the driver was much more "accommodating" before (converted integer to string on its own) but has become more strict now. Did the implementation change? Was it documented somewhere?

If this is in error, when can we expect a fix for this?
~Mayank
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: ORA-01486: size of array element is too large

Postby Pinturiccio » Wed 17 Sep 2014 12:44

Please create and send us a small test project with the corresponding DDL/DML scripts for reproducing the issue. Please also describe the steps for reproducing the issue consistently.
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle