Using C# for Inserting CLOB Data in Oracle

Introduction

Even the latest versions of Oracle databases have a limitation on the size of strings that they can handle when storing and retrieving data from tables. Currently, this limitation is 4,000 characters. Prior to version 8.1, that was 2,000. While this limitation does not cause any problems for storing short strings and words, it becomes a serious obstacle when a programmer needs to store large volumes of text in one record.

Oracle provides a special column data type called Character Large Object (CLOB) that allows storage up to 4 megabytes of character data. It is, however, very difficult to store such a huge amount of data in the table. What Oracle actually stores in the table is just a pointer to the place in the data store where the actual data is stored. This technique is the root of a more complicated procedure that's needed first to store data in the CLOB column, and then to store other 'primitive' data types. It is still possible to pass a string to a SQL INSERT statement of insert to a column that has CLOB data type, but only strings no longer than 4,000 characters can be stored in this way.

In order to store large amounts of text, a procedure consisting of several steps is required. Below is a class sample of how this can be done:

using System;

namespace InsertingCLOB
{
    public class clsOracle
    {     
        private System.Data.OracleClient.OracleConnection connOracle;
        private System.Data.OracleClient.OracleDataReader rstOracle;
        private System.Data.OracleClient.OracleCommand sqlCommandOracle;
        private System.Data.OracleClient.OracleTransaction txn; 
        private System.Data.OracleClient.OracleLob clob;    
        
        public clsOracle()    
        {
            string p_conn_db= "Data Source=" + OracleIP + ";User ID=" + 
                              OracleUserName + ";PASSWORD=" + OraclePassword + ";";
            connOracle = new System.Data.OracleClient.OracleConnection(p_conn_db);
            connOracle.Open();
        }   

        public void InsertRecord(string SQLStatement)
        { 
            if (SQLStatement.Length>0)
            {
                if(connOracle.State.ToString().Equals("Open"))
                { 
                    sqlCommandOracle = 
                      new System.Data.OracleClient.OracleCommand(SQLStatement,connOracle);
                    sqlCommandOracle.ExecuteScalar(); 
                }
            }
        }

        public void InsertCLOB(string SQLStatement, string str)    
        {
            if (SQLStatement.Length>0)
            {
                if(connOracle.State.ToString().Equals("Open"))
                {                        
                    byte[] newvalue = System.Text.Encoding.Unicode.GetBytes(str);
                    sqlCommandOracle= 
                      new System.Data.OracleClient.OracleCommand(SQLStatement,connOracle);
                    rstOracle = sqlCommandOracle.ExecuteReader();
                    rstOracle.Read();
                    txn = connOracle.BeginTransaction();
                    clob = rstOracle.GetOracleLob(0);
                    clob.Write(newvalue, 0, newvalue.Length);
                    txn.Commit();
                }
            }
        }
        public void CloseDatabase()
        {
            connOracle.Close();    
            connOracle.Dispose(); 
        }   
    }
}

Include this class named clsOracle.cls in your project. Now, create a button 'Save' to call this class with the code shown below:

private void btnSave_Click(object sender, System.EventArgs e)
{
    clsOracle db=new clsOracle();   

    // example for primary key    
    string field_id = "1";         
    // insert 2 characters for addresing    
    string field_temp = "XX";
    
    string sql = "Insert into table_nm values('" + field_id + 
                 "', '" + field_temp + "')"; 
    db.InsertRecord(sql); 

    sql="select news_text from table_nm +
    "WHERE field_nm'" + field_id + "' FOR UPDATE";
    
    db.InsertCLOB(sql, txtCLOBData.Text.ToString()); 
    db.CloseDatabase();
}

Regarding the characteristics of CLOB addressing, we need to insert a record first to get it updated. Then, the task is completed...

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)