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

Rheumatism

I don't see the bind parameter for modification_notes being set up and you created master_id twice. Refer to the ORACLE_HOME\odp.net\samples directory for examples of LOBs and bind variables. – Supramolecular

That was a copy/paste error when writing the example, it's not in the actual code. Fixed it in the question, thanks. – Rheumatism

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; 

Consternate

My test confirms that the bind order does not matter if BindByName=true – Vinculum

Answer 2

Edit: My answer below applies for typical use of Clobs where the size is greater than 32k (what they were designed for). If you know you will always be binding less than 32k bytes, or 16k characters in the usual case of unicode you can bind as Varchar2 and free yourself from having to create a temporary lob.

--

Keep in mind that a LOB in an oracle column is really a LOB Locator, a pointer to the actual data. Before you can update a CLOB column with that Lob Locator, you need to create and populate a temporary CLOB first.

In the ODP.NET samples directory in your Oracle Home there should be a LOB directory, in there it looks like samples5.cs might be a good place to start. Here is a snippet from it:

  // Set the command
  OracleCommand cmd = new OracleCommand(
    "update multimedia_tab set story = :1 where thekey = 1");
  cmd.Connection = con;
  cmd.CommandType = CommandType.Text; 

  // Create an OracleClob object, specifying no caching and not a NCLOB
  OracleClob clob = new OracleClob(con, false, false);

  // Write data to the OracleClob object, clob, which is a temporary LOB
  string str = "this is a new story";
  clob.Write(str.ToCharArray(), 0, str.Length);

  // Bind a parameter with OracleDbType.Clob
  cmd.Parameters.Add("clobdata", 
                      OracleDbType.Clob, 
                      clob, 
                      ParameterDirection.Input);

  try 
  {
    // Execute command
    cmd.ExecuteNonQuery();

Supramolecular

Answer 3

See the accepted answer for the actual solution.

[Edit: Former suspected answer]:
After several days of testing and debugging I found the solution which was so far away from everything I considered:

Apparently, you need to bind all Clob fields first before binding anything else - even when using actual placeholders instead of using :1, :2 etc.

Changing the bind order (i.e. the order of the AddParameter calls) fixed it.

Rheumatism

This is definitely not true. The real cause of your problem was that ODP.NET defaults to bind by position and in your query the two clobs came first so they must be bound first in this one case. Had you set BindbyName to True, your code would work. See the other answer. – Supramolecular

@ChristianShay Thanks for pointing that out, I've accepted the other answer. – Rheumatism

Answer 4

Try This :

  string Query3 = "  DECLARE " +
                  "str varchar2(32767); " +
                  " BEGIN " +
                  " str := '" + base64ImageRepresentationLogo + "'; " +
                  "  update map_general_settings set value=str where  DESC_AR='LOGO_IMG' ; END; ";
                    command.CommandText = Query3;
                    command.ExecuteNonQuery();

Remittance