Using C # for inserting clob data in Oracle

Source: Internet
Author: User
Tags oracleconnection

<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
Character data. It is, however, very difficult to store such a huge amount
Data in the table. What oracle actually stores in the table is just a pointer
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%"> 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.

Http://www.codeproject.com/useritems/C__and_Oracle.asp

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.