Click here to Skip to main content
14,580,573 members

Using C# for Inserting CLOB Data in Oracle

Rate this:
2.57 (13 votes)
Please Sign up or sign in to vote.
2.57 (13 votes)
3 Aug 2007CPOL
Inserting CLOB data into an Oracle table using C#.

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...

License

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

Share

About the Author

ace
Web Developer
Singapore Singapore
No Biography provided

Comments and Discussions

 
QuestionThis worked for me even to overcome the 32k limit Pin
Erick Dee4-Dec-14 6:43
MemberErick Dee4-Dec-14 6:43 
QuestionCONNECTION ERROR on code line : clob.Write(newvalue, 0, newvalue.Length); Pin
Muhammad Saeed Ahmad2-Feb-14 23:06
MemberMuhammad Saeed Ahmad2-Feb-14 23:06 
GeneralFlexible Datatype Pin
Ajay Kale New11-Nov-10 0:26
MemberAjay Kale New11-Nov-10 0:26 
GeneralMy vote of 1 Pin
Anton Kratenok16-Oct-09 1:12
MemberAnton Kratenok16-Oct-09 1:12 
GeneralACE please test your code before you post it. Pin
John T. Emile28-May-08 9:33
MemberJohn T. Emile28-May-08 9:33 
QuestionWhy so difficult? Pin
tuinstoel7-Jan-08 8:07
Membertuinstoel7-Jan-08 8:07 
AnswerRe: Why so difficult? [modified] Pin
hungud29-Oct-08 23:41
Memberhungud29-Oct-08 23:41 
GeneralNull column Pin
StephanieLim15-Dec-07 19:41
MemberStephanieLim15-Dec-07 19:41 
QuestionCan you not just use OracleType.Clob Pin
shaneritchie9-Aug-07 13:33
Membershaneritchie9-Aug-07 13:33 
AnswerRe: Can you not just use OracleType.Clob Pin
ace11-Aug-07 18:35
Memberace11-Aug-07 18:35 
AnswerRe: Can you not just use OracleType.Clob Pin
Daniel A.15-Aug-07 23:52
MemberDaniel A.15-Aug-07 23:52 
GeneralThanks a lot! ....Oracle.DataAccess version Pin
Nicola Costantini3-Aug-07 0:14
MemberNicola Costantini3-Aug-07 0:14 
GeneralRe: Thanks a lot! ....Oracle.DataAccess version Pin
ace3-Aug-07 0:29
Memberace3-Aug-07 0:29 
GeneralInvalid Operation. The Connection is closed Pin
Venkat Raman30-Jul-07 16:27
MemberVenkat Raman30-Jul-07 16:27 
GeneralRe: Invalid Operation. The Connection is closed Pin
ace30-Jul-07 17:16
Memberace30-Jul-07 17:16 
GeneralRe: Invalid Operation. The Connection is closed Pin
bkeller8115-May-08 10:54
Memberbkeller8115-May-08 10:54 
AnswerRe: Invalid Operation. The Connection is closed Pin
asousaaraujo24-Feb-10 13:19
Memberasousaaraujo24-Feb-10 13:19 
QuestionWith stored procedure? Pin
yena_yena11-Jan-07 20:47
Memberyena_yena11-Jan-07 20:47 
AnswerRe: With stored procedure? Pin
ace14-Jan-07 15:29
Memberace14-Jan-07 15:29 
GeneralRe: With stored procedure? Pin
tuinstoel7-Jan-08 7:32
Membertuinstoel7-Jan-08 7:32 
QuestionWhere is INSERT? Pin
Abi Bellamkonda18-Apr-06 18:43
MemberAbi Bellamkonda18-Apr-06 18:43 
AnswerRe: Where is INSERT? Pin
ace19-Apr-06 16:18
Memberace19-Apr-06 16:18 
GeneralRe: Where is INSERT? Pin
Abi Bellamkonda19-Apr-06 17:11
MemberAbi Bellamkonda19-Apr-06 17:11 
GeneralRe: Where is INSERT? Pin
ace23-Apr-06 22:58
Memberace23-Apr-06 22:58 
GeneralSome improvements... Pin
hleuze11-Apr-06 21:31
Memberhleuze11-Apr-06 21:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 3 Apr 2006

Stats

219.1K views
34 bookmarked