CLOB for string and vice versa [closed]

Reading% of CLOB is not the same as for other columns, since it does not have a "primitive" type - char, int, datetime, etc.

  

First understand the following: CLOB is not a table column.

In the design of the table, a CLOB column is even created in the same way as the others, but physically it is another hidden table, where your records are "one-to-one" with the main table. This is due to the complexity of the metadata of a CLOB.

And this goes for BLOB columns as well.

  

Important: Try to never SELECT * on a table with column CLOB

As a good practice, just bring the CLOB column when convenient, making simple SELECT COLUNA_CLOB FROM TABELA WHERE ID = @ID . This is basically to avoid Oracle's processing-intensive when trying to bind the main table to the internal CLOB table and create a fetch readable by its SQL clause.

  

Now: How to read a CLOB for string?

When I worked with C # and Oracle - around 2003-2005 - we had to make a script to do the download of the data. Today is the same, but now Oracle encapsulate this routine in your .NET Provider.

After a quick search, I found the new syntax in that answer .

// sendo "i" o índice da sua coluna CLOB 
var texto = reader.IsDBNull(i) 
    : string.Empty
    ? (string) reader.GetOracleClob(i).Value

See GetOracleLob(int)

  

Finally: How to record / update a CLOB column?

In Oracle's own documentation find the method to do what you need. See here about GetOracleClobForUpdate .

Follow their example:

/* Database Setup, if you have not done so yet.
connect scott/tiger@oracle 
CREATE TABLE empInfo (
empno NUMBER(4) PRIMARY KEY,
empName VARCHAR2(20) NOT NULL,
hiredate DATE,
salary NUMBER(7,2),
jobDescription Clob,
byteCodes BLOB
);

Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(1,'KING','SOFTWARE ENGR', '5657');
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(2,'SCOTT','MANAGER', '5960');
commit;

*/
// C#

using System;
using System.Data;
using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types;

class GetOracleClobForUpdateSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();

    // Get the job description for empno = 1
    string cmdStr = "SELECT JOBDESCRIPTION, EMPNO FROM EMPINFO where EMPNO = 1";
    OracleCommand cmd = new OracleCommand(cmdStr, con);

    // Since we are going to update the OracleClob object, we will
    //  have to create a transaction
    OracleTransaction txn = con.BeginTransaction();

    // Get the reader
    OracleDataReader reader = cmd.ExecuteReader();

    // Declare the variables to retrieve the data in EmpInfo
    OracleClob jobDescClob;

    // Read the first row
    reader.Read();

    if (!reader.IsDBNull(0))
    {
      jobDescClob = reader.GetOracleClobForUpdate(0);

      // Close the reader
      reader.Close();

      // Update the job description Clob object
      char[] jobDesc = "-SALES".ToCharArray();
      jobDescClob.Append(jobDesc, 0, jobDesc.Length);

      // Now commit the transaction
      txn.Commit();
      Console.WriteLine("Clob Column successfully updated");
    }
    else
      reader.Close();

    // Close the connection
    con.Close();
  }
}