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

From my experience ODP.NET has some strange behaviour and sometimes strange bugs depending on which version you use and which Oracle client you use etc. Sadly the `System.Data.OracleClient` is deprecated as of now... These thing lead me to use a commercial provider... rather happy, one bug so far (which was corrected immediately) and never looked back.... I don't know whether this is an option for you though... — Yahia, Nov 14 '11 at 14:49
yes it does it's "ID". i further tested it and fetched the maxid row like above but then changed the new row's values from 1st FLOAT(126) column to last column (NUMBERs and FLOAT(126)s get 0, VARCHAR2s get short nonsense string and DATEs get DateTime.Now) and there's no problem! when i leave the 1st float field as it was the exception is raised so it does not allow the values that are fetched from db!!! — char m, Nov 14 '11 at 20:24
I just noticed `FLOAT (126)` - this is certainly strange esp. in an Oracle DB... can you elaborate ? — Yahia, Nov 14 '11 at 20:43
try using `NUMBER` instead - it is much more "natural" for an Oracld DB and has a slightly higher precision (38 instead of 37.9). — Yahia, Nov 14 '11 at 20:51
i really can't tell anything more except that in the db double values are stored this way. I have no idea why. the db is mainly used by old C program. as I said other providers can handle this. when I check the DataRow's float columns in debugger their DataType have value Decimal like with other providers. somewhere there was also OraDbType which was 107. gotta check this. can't change the the type of columns. — char m, Nov 14 '11 at 20:56
if you can't change columns of type FLOAT(126) then I guess you are stuck as this looks like the various anomalies I had with ODP.NET in the past... — Yahia, Nov 14 '11 at 21:10
@lucas: sorry for late answer. didn't see your comment. i can't since it's job-related :( i think the mainpoints are: that it DOES NOT HAVE PRIMARY KEY. I'm sorry I didn't tell this before because for me primary key means unique index which ID is. another point already mentioned is that ALL FLOAT(126) fields are NOT NULL. all the type combinations are mentioned in the question. — char m, Nov 16 '11 at 14:19
Can you share a similar create script that has the same problem? — Lucas B, Nov 17 '11 at 19:36
(Update: Nevermind, just saw that it fails with 1 or 2, that happens for not reading carefully..) I think you're in the right track and the problem is that some of the values in the decimal fields don't fit the float(126) precision. A good second test could be truncating the values to it's integer portion before save. If you find this is the problem you'll probably have to handle it by rounding the values somehow to get a valid float(126) which probably is what the other drivers are doing anyway, assuming it's not a bug. — Fredy Treboux, Nov 17 '11 at 19:51
What version of ODP.Net are you using? This post names a bug that was fixed in version 10G: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13945 — GTG, Nov 17 '11 at 20:42
@GTG: I added app.config where the exact .dll version can be seen. Oracle11g. So this should be fixed. — char m, Nov 18 '11 at 07:10

score 1 · Accepted Answer · answered Nov 18 '11 at 08:50

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]);
    }

Thanks GTG! I also found a workaround myself that I edited to the question (building myself). Your workaround is good enough if it works! The code is already full of conditions checking the provider because parameters and schemas etc. are handled differently. I'll try to test it today and definitely gonna accept if it works. Thanks again! — char m, Nov 18 '11 at 10:15
thanks again! it works which is weird because data adapter and builders are instances of OracleDataAdapter and OracleCommandBuilder. any explanation? I'll test a bit more before accepting. — char m, Nov 18 '11 at 12:28
I did some more tests and found that the problem seems to be in the commandbuilder.GetInsertCommand(). My test was to use _provFactory.Create... for all the objects and to use the System.Data datatypes. This works fine, if you do it like this: dataAdapter.InsertCommand = ((Oracle.DataAccess.Client.OracleCommandBuilder)cmdBuilder).GetInsertCommand(); This seems strange - one would assume that this is a virtual function so this typecast should have no effect. — GTG, Nov 18 '11 at 12:50
strange indeed. anyway problem "solved". it's really strange that bug in this common operation is unnoticed (according my searches in web). maybe nobody really uses ODP.NET 'cause it's full of bugs. — char m, Nov 19 '11 at 15:31

Lucas B · Answer 2 · 2011-11-17T19:37:55.197

0

Have you tried adding a PK after you fill the table?

_dataSet.Tables[tableName].PrimaryKey = (new List<DataColumn>() { _dataSet.Tables[0].Columns["ID"] }).ToArray();

edited Nov 17 '11 at 19:37

answered Nov 15 '11 at 14:51

Lucas B

11,793
5
37
50

what is CurrentDataSet.Tables[0]? but if you mean trying to add ID column as PK I haven't. I try it. – char m Nov 16 '11 at 05:40
adding "ID" as PK had no effect :( – char m Nov 16 '11 at 08:27
sorry, that was meant to be _dataSet. I have updated my answer, though that appears you already figured as much and tried that. – Lucas B Nov 17 '11 at 19:37

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

2 Answers2

Linked