<H2>Introduction
Inserting CLOB data in Oracle</H2>
<P dir=ltr style="MARGIN-RIGHT: 0px">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.
</P>
<P dir=ltr style="MARGIN-RIGHT: 0px">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. </P>In order to store large amount of text, a procedure
consisting of several steps is required. Below is the class sample how this can
be done:
<DIV class=precollapse id=premain0 style="WIDTH: 100%"><IMG id=preimg0
style="CURSOR: hand" height=9 src="http://www.codeproject.com/images/minus.gif"
width=9 preid="0"> Collapse</DIV><PRE id=pre0 style="MARGIN-TOP: 0px">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();
}
}
}</PRE>
<P>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.