Error Oracle Column / Value size

Question

I am using ODP.NET and Oracle 10g to transfer data from datatable to db table. The problem I am facing is trying to insert a value into the NUMBER (12.3) column. Value 100100100.55 - I get an error: Error in row '1' column '6' ORA-26093: input data column size (24) exceeds maximum input size (22)

But if I try 100100100.5, it works fine

This error message does not matter to me.

Can someone explain to me why? Thanks!

Also here is a snippet that throws this error:

OracleBulkCopy bc = new OracleBulkCopy(DBFacade.DbConnection); DataTable dt = new DataTable(); dt.Columns.Add(); dt.Rows.Add(100100100.11); bc.DestinationTableName = "tmp_import_bom"; bc.ColumnMappings.Add(0, "QTY"); bc.WriteToServer(dt); 

Answer 1

When you create a DataTable column similar to the one in your sample code, dt.Columns.Add() , it defaults to a row .

I had a similar problem, and I found that the problem is that the oversized copy of the oracle cannot handle converting strings to decimal or long if the string is longer than 11 .

To get a workaround, you need to make sure that the original data type is decimal or long, not a string. In your sample code, you can do this with dt.Columns.Add("QTY", typeof(decimal)) ;

More details:

From what I'm collecting, the error message actually tells us that the maximum line length for such a conversion is 11. Those (24) and (22) links in the message are the length of the input lines and the maximum allowed is x 2. My line was 26641778.595, length 12, including the decimal point. If I added a number, the message would change to (26), etc. Again, I also had the same problem with a plain old number as 123456789012, whose length is 12.

Answer 2

A NUMBER(12,3) should contain the values ​​you are trying without problems. And if you have exceeded the accuracy of the column of the number, most likely the error you get will be ORA-01438: value larger than specified precision allowed for this column .

It looks like the error might be due to another column in the table. It seems that only one column is indicated in your insert, but links to column '6' errors. What are the rest of the columns in the table?

Answer 3

The answer to this question is in the Oracle doument "OracleBulkCopy Throws ORA-26093 when pasted into the NUMBER column with precision (Doc ID 1382276.1)."

In short:

REASON: This is by design and expected behavior. The default type is System.String.

SOLUTION: specify the data type of the column

So, instead of:

 dt.Columns.Add(); 

should write:

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

Answer 4

This error is usually due to a metadata error, not a value error. Make sure that the data type you are using on the .Net side is consistent with the specification of the column number (12.3) on the ORACLE side. Try passing it as "double" (conversion to double in the application) and see if this fixes the problem.