How can I update data in CLOB fields using a >> prepared query << with ODP (Oracle.DataAccess)?

Question

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

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.

Query:

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

Error:

ORA-00932: inconsistent datatypes: expected - got CLOB

Further Inormation:

Parameters are assigned as follows:

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

I have tried the following things:

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

How to insert CLOB field in Oracle using C#

Is it possible to do this through a prepared query?

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

---

Input data:

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

Code:
Disclaimer: I typed the following example by hand, there might be mistakes which are not in the actual code

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

Answer 1

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;