为什么我得到OracleTruncateException与ODP.NET OracleDataAdapter但与System.Data.OracleClient的的适配器时DbDataAdapter.Update叫什么名字？ [英] why I get OracleTruncateException with ODP.NET OracleDataAdapter but not with System.Data.OracleClient's adapter when DbDataAdapter.Update called?

问题描述

我做到以下几点：

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

这完全适用的OleDb和System.Data.OracleClient的。然而，随着Oracle.DataAccess.Client的供应商，我得到：

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

我得到这个表是其他包含61个领域的大表。所有字段的类型仅限于：

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

编辑以prevent过多评论：

Edit to prevent too many comments:

- 我不能更改数据库中的数据类型或任何东西。

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

- 在这些的DataRow FLOAT（126）列有数据System.Decimal类型（使用其他供应商时等）

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

-Unlike我前面所说的：ID不是主键。这是唯一索引。表没有主键（如Oracle定义）我必须承认，我认为唯一索引是一个听起来preposterous为人们熟悉Oracle主键。无论如何，我只作将1排。我还没有试过handbuild插入命令我将做一点。命令生成器应该处理表，而PK（http://msdn.microsoft.com/en-us/library/tf579hcz.aspx：SelectCommand还必须至少返回一个主键或唯一列）

-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.")

- 此也与ODP.NET/Oracle.DataAccess.Client如果：

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

之前，该方法的最后一个
• 在我给所有的FLOAT（126）-columns值0 行。即使有任何放弃值1或2引发相同的异常时， DbDataAdapter.Update被调用。

• 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.

或

• 在我创建DbDataAdapter.Insertommand自己，这里只有 插入（如code以上）时，DbDataAdapter.Update被调用。当我CMD打造自己，我给DbParameter.DbType = DbType.Double浮法（126）-columns。如果我建立它自己一切正常的值被接受。

• 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：

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>

任何想法的原因是什么，以及如何我要使它成为所有3提供商合作？

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

感谢和放大器;顺祝商祺 - 马蒂·

Thanks & Best Regards - Matti

推荐答案

首先，我想你应该向甲骨文报告这个bug。错误发生，即使该表是非常小的。它无关，与索引或主键，出现错误，即使表有没有索引。如果ID的值设置为0，插入将运行行

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.

解决方法是使用本地Oracle客户端类的ODP.Net，所以你必须检查，如果你的应用程序被配置为ODP或其他人之一，并选择你的code相应。

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.

一个ODP只有版本的功能可能看起来是这样的：

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

这篇关于为什么我得到OracleTruncateException与ODP.NET OracleDataAdapter但与System.Data.OracleClient的的适配器时DbDataAdapter.Update叫什么名字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！