ODP.NET InsertCommand erroneously returns ORA-01401: inserted value too large for column

Question

I've got a C# function that is constructing a parameterized insert query using ODP.NET that for some reason keeps returning the "ORA-01401: inserted value too large for column" exception.

I've thoroughly checked the length of the string variable in question and it falls well beneath the maximum size of the database column (70 chars long in a 1024 chars long Varchar2 column). What's stranger is that if I insert the value as a string rather than a parameter variable, the insertion works just fine. Here's the code (parameterized):

connectionString = GetConnectionString();
conn = new OracleConnection(connectionString);
OracleDataAdapter oda = new OracleDataAdapter();
conn.Open();
insertStatement = "INSERT INTO DOCS (ID, PATH, PAGES, USERID,SUFFIX, MASK) ";
insertStatement += "VALUES (:id, :itemUrl, 1, 'SHAREPOINT\\system',0,'000') ";
oda.InsertCommand = new OracleCommand(insertStatement, conn);
oda.InsertCommand.Parameters.Add(":id", docList[0].taskerID.ToString());
oda.InsertCommand.Parameters.Add(":itemUrl", itemUrl);
count += oda.InsertCommand.ExecuteNonQuery();

I'm thinking that maybe there's something I'm missing here with ODP.NET or there is actually a bug with ODP.NET that I'm encountering. I'm using version 9.2.0.7 of Oracle.DataAccess on my server. Any ideas?

Answer 1

I got this to work with just a slight modification of your code, but I did not receive the same exception as you.

The only changes I did was to add "INTO" into the INSERT statement as well as double quoting the paramater names in the add collection items.

---

create table DOCS (id varchar2(70) , path varchar2(70) , pages number, userid varchar2(70) , suffix varchar2(70), mask varchar2(70));

---

OracleDataAdapter oda = new OracleDataAdapter();
insertStatement = "INSERT INTO DOCS (ID, PATH, PAGES, USERID,SUFFIX, MASK) ";
insertStatement += "VALUES (:id, :itemUrl, 1, 'SHAREPOINT\\system',0,'000') ";
conn.Open();
oda.InsertCommand =  new OracleCommand(insertStatement, conn);
oda.InsertCommand.Parameters.Add(":id", "test1");
oda.InsertCommand.Parameters.Add(":itemUrl", "test2");
count += oda.InsertCommand.ExecuteNonQuery();

I think you may have something else going on that is not apparent in your sample.

I know that I have received your error (many many times) when I have the parameters in a cardinal order that differs from expected param collection.

This is caused by ODP binding by position by default AND NOT BINDING BY NAME (like the old Ms oraClient did).

You can either verify that the parameters are indeed in the correct position or simply:

oda.InsertCommand.BindByName = true ;

hth