Oraclebulkcopy Throws Error Ora-12899 With BINARY_DOUBLE or BINARY_FLOAT Column In The Table (Doc ID 1414050.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Data Provider for .NET - Version: 11.2.0.3 and later   [Release: 11.2 and later ]
Microsoft Windows (32-bit)

Symptoms

When trying to use OracleBulkCopy class from ODP.NET on a table with a
BINARY_DOUBLE or BINARY_FLOAT column in it. When they attempt to bulk load the following error occurs:

Unhandled Exception: Oracle.DataAccess.Client.OracleException:
Error in row '1' column '3'
ORA-12899: value too large for column COL3 (actual: 20, maximum: 11)
at Oracle.DataAccess.Client.OracleBulkCopy.PerformBulkCopy()
at Oracle.DataAccess.Client.OracleBulkCopy.WriteDataSourceToServer()
at Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table,
DataRowState rowState)
at Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table)
at ConsoleApplication2.Program.Main(String[] args) in
D:\Customer\3-4712098841\ConsoleApplication2\ConsoleApplication2\Program.cs:line 49

Note: If you change the BINARY_DOUBLE column to a varchar2(512) in the table.
The error does not occur.

Here is the code used to reproduce the problem:

1.Execute the following sql in sqlplus:

drop table test_temp_dest;
CREATE TABLE TEST_TEMP_DEST (
col1 VARCHAR2(512),
col2 TIMESTAMP,
col3 binary_double);

2. Create a c# application and post the following code in the .cs code file:
using System;
using System.Data;
using Oracle.DataAccess.Client;

namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
//create a datatable
DataTable dt = new DataTable();

DataColumn dc = new DataColumn();
dc.DataType = Type.GetType("System.Int32");
dc.ColumnName = "col1";
dt.Columns.Add(dc);

DataColumn dc2 = new DataColumn();
dc2.DataType = Type.GetType("System.Double");
dc2.ColumnName = "col2";
dt.Columns.Add(dc2);

DataColumn dc3 = new DataColumn();
dc3.DataType = Type.GetType("System.DateTime");
dc3.ColumnName = "col3";
dt.Columns.Add(dc3);

DataColumn dc4 = new DataColumn();
dc4.DataType = Type.GetType("System.String");
dc4.ColumnName = "col4";
dt.Columns.Add(dc4);

for (int i = 0; i < 10; i++)
{
DataRow dr = dt.NewRow();
dr["col1"] = i;
dr["col2"] = (double)1.2f;
dr["col3"] = System.DateTime.Now + new TimeSpan(i * 1000);
dr["col4"] = "foobar" + i.ToString();
dt.Rows.Add(dr);
}

string connStr = "Data Source=utf8;User ID=scott;
@ Password=tiger;Persist Security Info=True;";

using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connStr,
OracleBulkCopyOptions.Default))
{
bulkCopy.DestinationTableName = "testtstab";
bulkCopy.WriteToServer(dt);
}
}
}
}

3. Change the connstion string in this line to match your configuration:
string connStr = "Data Source=YOUR_CONNECTION_STRING;User ID=USERID; Password=PASSWORD;Persist Security Info=True;";

4. Compile and execute the code. You will see the following error occur:
Unhandled Exception: Oracle.DataAccess.Client.OracleException: Error in row
'1'
column '3'
ORA-12899: value too large for column COL3 (actual: 20, maximum: 11)
at Oracle.DataAccess.Client.OracleBulkCopy.PerformBulkCopy()
at Oracle.DataAccess.Client.OracleBulkCopy.WriteDataSourceToServer()
at Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table,
Dat
aRowState rowState)
at Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table)
at ConsoleApplication2.Program.Main(String[] args) in
D:\Customer\3-471209884
1\ConsoleApplication2\ConsoleApplication2\Program.cs:line 49

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms