Using C# for Inserting CLOB data in Oracle

Introduction

Inserting CLOB data in Oracle

Even the latest versions of Oracle's database 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 an SQL INSERT statement of inserting to column that has CLOB data type, but this way only strings no longer than 4,000 characters can be stored.

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

Then, please include that class into your project named clsOracle.cls
After all, creating button 'SAVE' to call this class with code showed 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 characteristic of CLOB addresing, we need to insert a record first 
to get it updated.

Then, task completed...