CLOB for string and vice versa

Question

I’m developing a web server where I have to store images in String of Base64 exchanged for desktop, mobile and website to the database of the Oracle, but I’ve done a lot of research and seen some alternatives that are:

store in string even as if it were a text, but I have to convert to CLOB because it is extremely large, exceeding 4000 bytes which is not recommended.

Or turn it back into an image BLOB, store and vice versa.

Which alternative is better? Someone there has example methods?

If anyone has any new alternatives help me too.

Thank you :)

Answer 1

Read CLOB is not in the same way as in other columns, because this does not have a "primitive" type - char, int, datetime, etc.

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

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

And that all goes for BLOB columns too.

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

As good practice, only bring the CLOB column when it is convenient, making the simple SELECT COLUNA_CLOB FROM TABELA WHERE ID = @ID. This, basically, to avoid Processing-Intensive Oracle when trying to connect 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 the years 2003-2005 - we had to make a script to do the download of the data. Today the same occurs, but now Oracle encapsulates this routine in its Provider . NET.

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

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

See here the documentation of GetOracleLob(int)

Finally: How to save/update a CLOB column?

In the Oracle documentation itself 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();
  }
}