why I get OracleTruncateException with ODP.NET OracleDataAdapter but not with System.Data.OracleClient's adapter when DbDataAdapter.Update called?

Question

I do the following:

protected int CreateComponent(DbConnection cnctn, string tableName)
{
    int newId;

    DbCommand selectCmd = _provFactory.CreateCommand();
    selectCmd.Connection = cnctn;
    selectCmd.CommandText = string.Format(
            "SELECT * FROM {0} WHERE ID = (SELECT MAX(ID) FROM {0})", tableName);

    DbDataAdapter dataAdapter = _provFactory.CreateDataAdapter();
    dataAdapter.SelectCommand = selectCmd;

      ...
    // create Insert/Update/Delete commands with a builder for the data adapter
      ...

    dataAdapter.Fill(_dataSet, tableName);      

    newId = Convert.ToInt32(_dataSet.Tables[tableName].Rows[0]["id"]) + 1000000;

    DataRow newRow = _dataSet.Tables[tableName].NewRow();
    newRow.ItemArray = _dataSet.Tables[tableName].Rows[0].ItemArray;
    newRow["ID"] = newId;

    _dataSet.Tables[tableName].Rows.Add(newRow); 
}

This works perfectly for OleDb and System.Data.OracleClient. However with Oracle.DataAccess.Client's provider I get:

Oracle.DataAccess.Types.OracleTruncateException (16550) 

with text truncated result originating from:

at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors  
at System.Data.Common.DbDataAdapter.UpdatedRowStatus  
at System.Data.Common.DbDataAdapter.Update
at Oracle.DataAccess.Client.OracleDataAdapter.Update  
at System.Data.Common.DbDataAdapter.UpdateFromDataTable  
at System.Data.Common.DbDataAdapter.Update

The tables I get this are big tables that other contains 61 fields. The types of all fields are limited to:

VARCHAR2(different lenghts)
VARCHAR2(different lenghts) NOT NULL
FLOAT(126) NOT NULL     
NUMBER NOT NULL
DATE

Edit to prevent too many comments:

-I cannot change the datatype or anything in the database.

-In DataRow these FLOAT(126) columns have data type System.Decimal (like when using other providers)

-Unlike I stated before: ID is not primary key. It's unique index. Table does not have primary key (as Oracle definition) I have to admit that I thought that unique index is primary key which may sound preposterous for people familiar with Oracle. Anyway I make only Insert of 1 row. I haven't tried to handbuild Insert-command which I'll do in a bit. Command builders should handle tables without PK (http://msdn.microsoft.com/en-us/library/tf579hcz.aspx: "The SelectCommand must also return at least one primary key or unique column.")

-This works also with ODP.NET/Oracle.DataAccess.Client if:

• I give all the FLOAT(126)-columns value 0 before the method's last row. Even with giving value 1 or 2 to any raises same exception when DbDataAdapter.Update is called.

or

• I create the DbDataAdapter.Insertommand myself and there's only insert (like code above) when DbDataAdapter.Update is called. When I cmd build myself I give DbParameter.DbType = DbType.Double for FLOAT(126)-columns. If I build it myself all normal double values are accepted.

app.config:

<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
  <system.data>
  <DbProviderFactories>
    <add name="Oracle Data Provider for .NET"
            invariant="Oracle.DataAccess.Client"
            description="Oracle Data Provider for .NET"
            type="Oracle.DataAccess.Client.OracleClientFactory,
                  Oracle.DataAccess,
                  Version=2.112.1.0,
                  Culture=neutral,
                  PublicKeyToken=89b483f429c47342" />
  </DbProviderFactories>
  </system.data>

any ideas what is the reason and how i'm gonna make it work for all the 3 providers?

Thanks & Best Regards - Matti

Answer 1

First, I think you should report this to Oracle as a bug. The error happens even if the table is really small. It has nothing to do with indexes or primary keys, the error occurs even if the table has no index. If you set the value of ID to 0, the insert will run OK.

I managed to create a workaround, although it is not a good one it may be good enough for your case.

The workaround is to use the native Oracle client classes for the ODP.Net, so you would have to check if your app was configured for ODP or one of the others and choose your code accordingly.

A "ODP Only" version of your function could look like this:

    protected void CreateComponentODPOnly(Oracle.DataAccess.Client.OracleConnection cntn, string tableName)
    {
        int newId;

        System.Data.DataSet _dataSet = new DataSet();

        Oracle.DataAccess.Client.OracleCommand selectCmd = new Oracle.DataAccess.Client.OracleCommand();
        selectCmd.Connection = cntn;
        selectCmd.CommandText = string.Format(
                "SELECT * FROM {0} WHERE ID = (SELECT MAX(ID) FROM {0})", tableName);

        Oracle.DataAccess.Client.OracleDataAdapter dataAdapter = new Oracle.DataAccess.Client.OracleDataAdapter();
        Oracle.DataAccess.Client.OracleCommandBuilder cmdBuilder = new Oracle.DataAccess.Client.OracleCommandBuilder();
        dataAdapter.SelectCommand = selectCmd;
        cmdBuilder.DataAdapter = dataAdapter;

        dataAdapter.Fill(_dataSet, tableName);

        newId = Convert.ToInt32(_dataSet.Tables[tableName].Rows[0]["id"]) + 1000000;

        DataRow newRow = _dataSet.Tables[tableName].NewRow();
        newRow.ItemArray = _dataSet.Tables[tableName].Rows[0].ItemArray;
        newRow["ID"] = (Decimal)newId;

        _dataSet.Tables[tableName].Rows.Add(newRow);
        dataAdapter.InsertCommand = cmdBuilder.GetInsertCommand();
        dataAdapter.Update(_dataSet.Tables[tableName]);
    }