When using the ODP.Net managed API, when using array binding to insert data into a column of type VARCHAR2(4000), and a string length of a row value in our array is greater than 1000 characters, the following exception is thrown:
ORA-01461: can bind a LONG value only for insert into a LONG column
string sql = "INSERT INTO STAGING(\"COLUMN1\") VALUES (:COLUMN1)";
using (OracleCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
cmd.ArrayBindCount = dt.Rows.Count;
var p = new OracleParameter { ParameterName = parameterName.ToUpper() };
p.OracleDbType = OracleDbType.Varchar2;
p.Value = dt.AsEnumerable().Select(c => (!c.IsNull(fieldName) ? c.Field<T>(fieldName) : default(T))).ToArray();
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
We currently define our parameters as:
p.OracleDbType = OracleDbType.Varchar2;
I tried to use this instead, but still run into the same issue:
p.OracleDbType = OracleDbType.Clob;
Also tried to set a size on the length of the Varchar2 as follows, but still have the same issue.
p.OracleDbType = OracleDbType.Varchar2;
p.Size = 4000;
Also tried this, with no luck:
string sql = "INSERT INTO STAGING(\"COLUMN1\") VALUES (CAST(:COLUMN1 AS VARCHAR2(4000))";
Any ideas?
This appears to be a similiar issue: https://community.oracle.com/thread/3649551
Update I suspected that maybe there was some sort of character set issue, which made the length longer than expected, so to rule this out, I reduced the column length of the table that we're trying to insert data into down to VARCHAR2(1000), assuming that this would make the maximum allowable character length to be 250 - this is not the case though. The maximum that is working before this exception is thrown is still 1000.
Update 2 I've found an oracle patch which may resolve this issue. I will try and get this patch and verify. https://support.oracle.com/epmos/faces/PatchDetail?patchId=20361140&requestId=18735492
Update 3 The oracle patch didn't fix the issue for me. I tried to iterate through all of the parameter bind statuses, but they all indicate a success.
catch (Exception ex)
{
foreach (OracleParameter p in cmd.Parameters)
{
foreach (var s in p.ArrayBindStatus)
{
if (s != OracleParameterStatus.Success)
{
}
}
}
}
Update 4 Seems that this is a bug in the Oracle Managed API, here's a sample class that can reproduce the issue.
namespace OracleBindError
{
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Linq;
class Program
{
static void Main(string[] args)
{
string testTable = "BIND_TEST_TABLE";
string connString = "[conn string here]";
string dropTable =
@"DECLARE pEXISTS NUMBER;
BEGIN
SELECT COUNT(*) INTO pEXISTS FROM USER_TABLES WHERE TABLE_NAME = '" + testTable + @"';
IF(pEXISTS > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE " + testTable + @"';
END IF;
EXECUTE IMMEDIATE 'CREATE TABLE " + testTable + @" (COLUMN1 VARCHAR2(4000), COLUMN2 VARCHAR2(4000))';
END;";
string[] greaterThanOneThousand = new string[] {
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkldfdffd",
};
string insertStatement = "INSERT INTO " + testTable + "(\"COLUMN1\",\"COLUMN2\") VALUES (:COLUMN1,:COLUMN2)";
using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
OracleCommand dropCmd = new OracleCommand(dropTable, conn);
dropCmd.ExecuteNonQuery();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = insertStatement;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
cmd.ArrayBindCount = greaterThanOneThousand.Length;
var p = new OracleParameter { ParameterName = "COLUMN1" };
p.OracleDbType = OracleDbType.Varchar2;
p.Value = greaterThanOneThousand.ToArray();
cmd.Parameters.Add(p);
var p2 = new OracleParameter { ParameterName = "COLUMN2" };
p2.OracleDbType = OracleDbType.Varchar2;
p2.Value = new string[] { null };
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
}
}
Found work-around If I change the OracleDbType from Varchar2 to NVarchar2 in my parameters it works.
var p = new OracleParameter { ParameterName = "COLUMN1" };
p.OracleDbType = OracleDbType.NVarchar2;
p.Value = greaterThanOneThousand.ToArray();
cmd.Parameters.Add(p);
var p2 = new OracleParameter { ParameterName = "COLUMN2" };
p2.OracleDbType = OracleDbType.Varchar2;
p2.Value = new string[] { " " };
cmd.Parameters.Add(p2);
Work-Around The work-around is to use NVARCHAR2 in the parameter on the .net side.
