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!
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;
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();
}
}
}
}
}
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