ORA-01483: invalid length for DATE or NUMBER bind variable

ORA-01483: invalid length for DATE or NUMBER bind variable

Postby davidxu » 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));


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 < table.Rows.Count; i++)
                    {
                        values[i] = table.Rows[i][dc];
                    }

                    // Create command
                    if (dc.DataType == typeof(Int32))
                    {
                        command.Parameters.Add(columnName, Devart.Data.Oracle.OracleDbType.Integer);
                        command.Parameters[columnName].Value = Array.ConvertAll(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);
            }
        }

    }
}
davidxu
 
Posts: 3
Joined: Wed 27 Jul 2011 00:41

Postby Shalex » Fri 02 Dec 2011 15:04

Thank you for your report. We will post here when the problem is fixed.
As a workaround, please either switch to the OCI mode (via Oracle client) or switch order of columns in the data table.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Thu 08 Dec 2011 14:20

The bug with NVarChar parameters of OracleCommand in Direct mode is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Thu 22 Dec 2011 17:07

New build of dotConnect for Oracle 6.60.268 is available for download now!
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://www.devart.com/forums/viewtopic.php?t=22977 .
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle