ORA-01483: invalid length for DATE or NUMBER bind variable
Posted: Wed 30 Nov 2011 16:28
This exception seems to be a new issue introduced in v6.50. I did not have this issue in v6.30.
Here are steps to reproduce it.
1. Create a Test table with 2 columns, IntId and StringField.
Create table Test
(
IntId int,
StringField nvarchar(255)
)
2. Run the following test code. It fails with exception "the ORA-01483: invalid length for DATE or NUMBER bind variable". Switching order of the columns below fix the exception. This issue does not exist in 6.30.
// Switching order of columns in the data table fix the exception.
dataTable.Columns.Add("StringField", typeof(String));
dataTable.Columns.Add("IntId", typeof(Int32));
Here are steps to reproduce it.
1. Create a Test table with 2 columns, IntId and StringField.
Create table Test
(
IntId int,
StringField nvarchar(255)
)
2. Run the following test code. It fails with exception "the ORA-01483: invalid length for DATE or NUMBER bind variable". Switching order of the columns below fix the exception. This issue does not exist in 6.30.
// Switching order of columns in the data table fix the exception.
dataTable.Columns.Add("StringField", typeof(String));
dataTable.Columns.Add("IntId", typeof(Int32));
Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Devart.Data.Oracle;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Common;
namespace DevartReportingDBTester
{
class Program
{
static void Main(string[] args)
{
try
{
TestBulkInsertion();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
static DataTable CreateTable()
{
DataTable dataTable = new DataTable("Test");
// Switching order of columns in the data table fix the exception.
dataTable.Columns.Add("StringField", typeof(String));
dataTable.Columns.Add("IntId", typeof(Int32));
return dataTable;
}
static void AddDataRow(DataTable dataTable)
{
DataRow dataRow = dataTable.NewRow();
dataRow["StringField"] = "Test string";
dataRow["IntId"] = 1;
dataTable.Rows.Add(dataRow);
}
static void TestBulkInsertion()
{
using (OracleConnection conn = new OracleConnection())
{
try
{
conn.ConnectionString = "Unicode=true;Direct=true;server=xxx;port=1521;SID=xxx;uid=xxx;pwd=xxx;";
conn.Open();
DataTable table = CreateTable();
AddDataRow(table);
BulkInsertDevart(conn, table);
}
catch (Exception ex)
{
Console.WriteLine("Error message: {0}", ex.Message + ex.StackTrace);
}
}
}
private static void BulkInsertDevart(DbConnection connection, DataTable table)
{
// Create Insert SQL
System.Text.StringBuilder sbValues = new System.Text.StringBuilder();
System.Text.StringBuilder sbFields = new System.Text.StringBuilder();
foreach (DataColumn dc in table.Columns)
{
string columnName = dc.ColumnName;
if (table.Columns.IndexOf(dc) != 0)
{
sbValues.Append(", ");
sbFields.Append(", ");
}
sbValues.AppendFormat(":{0}", columnName);
sbFields.AppendFormat("{0}", columnName);
}
string sql = String.Format("INSERT INTO {0}({1}) VALUES({2})", table.TableName, sbFields.ToString(), sbValues.ToString());
Console.WriteLine("SQL command: {0}", sql);
Devart.Data.Oracle.OracleConnection oracleConnection = connection as Devart.Data.Oracle.OracleConnection;
using (Devart.Data.Oracle.OracleCommand command = oracleConnection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.CommandTimeout = 30000;
foreach (DataColumn dc in table.Columns)
{
string columnName = dc.ColumnName;
// Get data array for Insertion
object[] values = new object[table.Rows.Count];
for (int i = 0; i (values, new Converter(delegate(object input)
{
if (input == null || input == DBNull.Value)
return 0;
else
return (Int32)input;
}));
}
else if (dc.DataType == typeof(String))
{
command.Parameters.Add(columnName, Devart.Data.Oracle.OracleDbType.NVarChar);
command.Parameters[columnName].Value = Array.ConvertAll(values, new Converter(delegate(object input)
{
string value = (string)input;
return value == String.Empty ? " " : value;
}));
}
command.Parameters[columnName].Direction = ParameterDirection.Input;
}
command.ExecuteArray(table.Rows.Count);
}
}
}
}