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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
ieichens
Posts: 8
Joined: Wed 16 Jan 2008 08:08

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

Post by ieichens » Tue 12 May 2009 15:18

Given this table:

Code: Select all

create table test(
pk integer primary key,
col1 varchar2(64),
col2 clob,
col3 integer,
col4 number(12, 2));
and the following code:

Code: Select all

DataTable t = new DataTable();
t.Columns.Add("pk", typeof(int));
t.Columns.Add("col1", typeof(string));
t.Columns.Add("col2", typeof(string));
t.Columns.Add("col3", typeof(int));
t.Columns.Add("col4", typeof(decimal));
DataRow r = t.NewRow();
r["pk"] = 1234;
r["col2"] = "Text";
t.Rows.Add(r);

using (OracleConnection con = new OracleConnection())
{
    con.Server = "localhost";
    con.Sid = "...";
    con.UserId = "...";
    con.Password = "...";
    con.Direct = true;

    con.Open();

    OracleCommand cmd = new OracleCommand("delete from test", con);
    cmd.ExecuteNonQuery();

    OracleDataAdapter a = new OracleDataAdapter();
    a.InsertCommand = new OracleCommand("insert into test(pk, col1, col2, col3, col4) values(:pk, :col1, :col2, :col3, :col4)", con);
    a.InsertCommand.Parameters.Add("pk", OracleDbType.Integer, 0, "pk");
    a.InsertCommand.Parameters.Add("col1", OracleDbType.NVarChar, 0, "col1");
    a.InsertCommand.Parameters.Add("col2", OracleDbType.Clob, 0, "col2");
    a.InsertCommand.Parameters.Add("col3", OracleDbType.Integer, 0, "col3");
    a.InsertCommand.Parameters.Add("col4", OracleDbType.Number, 0, "col4");
    a.Update(t);

    con.Close();
}
i get the error message: ORA-01483: invalid length for DATE or NUMBER bind variable
Server Version is 11.1.0.6.0. dotConnect Version is 5.20.29.0

If i change parameter col1 in the insert command from NVarChar to VarChar it works.
If i then change the Unicode property in the connection to true it does not.
If i change Direct to false it works.

Is this a bug or am i doing something wrong?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 13 May 2009 13:45

Please change the third parameter's value from 0 to 64 of the overload you use when adding the col1 parameter:

Code: Select all

a.InsertCommand.Parameters.Add("col1", OracleDbType.NVarChar, 64, "col1");
Does it help?

ieichens
Posts: 8
Joined: Wed 16 Jan 2008 08:08

Post by ieichens » Thu 14 May 2009 02:00

Yes it does help, but i didn't have this 0-value in my real application. Instead i used a value larger than 2000 which also leads to this error. This behavior is different from version 4.75 which i used before. Anyway playing with the right sizes it seems to solve it.
Thank's a lot.

Post Reply