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

Question

I am trying to execute a prepared sql query that updates fields CLOBin an Oracle 10g database (10.2.0.1).

If I run the following query from the inside SQL Developerand set the values ​​for placeholders, there is no prblem. If, however, I executed it through OracleCommand(Oracle.DataAccess.dll, version 1.102.0.1 (I think), .NET Framework 3.5), I get an error message below. Please note that we do not use the oracle client by default, since we need bulk insertion. This version of ODP and the version of the .NET Framework, unfortunately, are a strict requirement, and we cannot change this.

Query:

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

Error:

ORA-00932: : - CLOB

:

:

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

:

• to_clob() SQL
• Oracle.DataAccess.Types.OracleClob .

, .

CLOB Oracle #

?

, . DESCRIPTION MODIFICATION_NOTES - CLOB .

---

:

• : OracleConnection
• master_id:

:
: , ,

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

true, . .

cmd.BindByName = true; 

Answer 2

: Clobs, 32 . ( ). , 32 . 16 . , Varchar2 .

-

, LOB oracle - LOB Locator, . CLOB Lob Locator, CLOB.

ODP.NET Oracle Home LOB, samples5.cs . :

  // 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();

Answer 3

. .

[: ]:
<p > , , :

-, Clob , - - :1, :2 ..

Changing the binding order (i.e. the order of calls AddParameter) fixed it. Strike>

Answer 4

Try the following:

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