如何使用>>更新CLOB字段中的数据.准备好的查询<<与ODP(Oracle.DataAccess)? [英] How can I update data in CLOB fields using a >> prepared query << with ODP (Oracle.DataAccess)?

问题描述

我正在尝试执行一个准备好的sql查询，该查询将更新Oracle 10g数据库(10.2.0.1)中的CLOB字段.

I'm trying to execute a prepared sql query which updates CLOB fields in an Oracle 10g database (10.2.0.1).

如果我从SQL Developer内部执行以下查询并提供占位符的值，则没有问题.但是，如果我通过OracleCommand(Oracle.DataAccess.dll，版本1.102.0.1(我认为)，. NET Framework 3.5)执行它， 我收到以下错误消息.请注意，由于我们需要批量插入，因此我们没有使用默认的oracle客户端.不幸的是，给定的ODP版本和.NET Framework版本是一个硬性要求，我们可能不会对此进行更改.

If I execute the following query from inside SQL Developer and supply the values for the placeholders, there is no prblem. If I however execute it through an OracleCommand (Oracle.DataAccess.dll, version 1.102.0.1 (I think), .NET Framework 3.5), I get the error message below. Note that we are not using the default oracle client as we require bulk insertion. The given ODP version and .NET Framework version are unfortunately a hard requirement and we may not change that.

查询:

UPDATE master_table
SET    description = :description,
       modification_notes = :modification_notes
WHERE  master_id = :master_id;

错误:

ORA-00932:数据类型不一致:预期-获得CLOB

ORA-00932: inconsistent datatypes: expected - got CLOB

更多信息:

参数分配如下:

var param_description = new OracleParameter(":description", OracleDbType.Clob);
param_description.Value = "Test";

我尝试了以下操作:

• 将to_clob()插入到SQL查询中
• 将Oracle.DataAccess.Types.OracleClob对象分配给该参数.

• insert to_clob() into the SQL query
• assign a Oracle.DataAccess.Types.OracleClob object to the parameter.

我还找到了以下描述，但我确实希望能够保留准备好的查询.

I have also found the following description, but I would really want to be able to keep the prepared query.

如何插入CLOB Oracle中使用C#

是否可以通过准备好的查询来做到这一点?

Is it possible to do this through a prepared query?

我已经附上了一个产生错误的完整示例. DESCRIPTION和MODIFICATION_NOTES是数据库中类型为CLOB的两列.

I've attached a complete example which produces the error. DESCRIPTION and MODIFICATION_NOTES are two columns of type CLOB in the database.

输入数据:

• 连接:OracleConnection到数据库
• master_id:要过滤的主键

• connection: OracleConnection to the database
• master_id: primary key to filter for

代码:
免责声明:我手动输入了以下示例，可能存在实际代码中没有的错误

var query = "UPDATE master_table " + 
            "SET description = :description " + 
            "    modification_notes = :modification_notes " +
            "WHERE master_id = :master_id";

var param_master_id = new OracleParameter(":master_id", OracleDbType.Int64);
param_master_id.Value = master_id;

var param_description = new OracleParameter(":description", OracleDbType.Clob);
param_description.Value = "Test1";

var param_master_id = new OracleParameter(":modification_notes", OracleDbType.Clob);
param_description.Value = "Test2";

IDbCommand command = new OracleCommand(query, connection);
command.parameters.Add(param_master_id);
command.parameters.Add(param_description);
command.parameters.Add(param_modification_notes);

command.ExecuteNonQuery(); // this line throws an exception

推荐答案

如果要按名称绑定，则需要将其设置为true.默认情况是按添加的参数顺序进行绑定.

You need to set this to true if you want to bind by name. Default is bind by the order of the parameter added.

cmd.BindByName = true; 

这篇关于如何使用>>更新CLOB字段中的数据.准备好的查询<<与ODP(Oracle.DataAccess)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！