Read clob and BLOB fields in Oracle

If data is written to the clob field as a string in Oracle, the default length is 4000, and the maximum storage space is 4 GB.

Writing a string directly to the clob field can contain a maximum of 4000 bytes. To write a large character data, you can write the character data in the form of a binary stream.

Write clob (BLOB code ):

Oracleconnection conn = new oracleconnection (getconfiguserconnectionstring ());
Conn. open ();
Oracletransaction TRAN = conn. begintransaction ();
String id = string. empty;
Oraclecommand command = NULL;
Try
{
Command = new oraclecommand ();
Command. Connection = conn;
Double scale = 0;
If (Ct. Scale. indexof (":") =-1)
{
Scale = basicoperate. getdouble (Ct. Scale, true );
}
Else
{
Scale = basicoperate. getdouble (Ct. Scale. substring (Ct. Scale. indexof (":") + 1), true );
}

// An empty empty_clob () [empty_blob ()] is first saved during insertion.
Command. commandtext = string. Format (@ "insert into chart (chartname, charttype, source, scale, producttime ,"
+ "Xml_definition) values ('{0}', '{1}', '{2}', {3}, '{4}', empty_clob ())"
+ "Returning chartid into: RID ",
Ct. chartname, CT. charttype, CT. Source, scale, CT. producttime, CT. xmldefinition );
Command. Parameters. Add ("RID", oracledbtype. varchar2, 32). Direction = parameterdirection. output;
Command. executenonquery ();
Id = command. Parameters ["RID"]. value. tostring ();

// Read the clob field and edit it.
Command. commandtext = string. Format ("select xml_definition from chart where chartid = '{0}' for update", ID );
Using (oracledatareader reader = command. executereader ())
{
While (reader. Read ())
{
Oracleclob clob = reader. getoracleclob (0); // Read Binary Pairs
Clob. Erase (); // clear the data.
Clob. Position = 0;
Clob. beginchunkwrite (); // start writing
Int buffer size = 100;
Int retval = 0;
Byte [] BTS = new byte [buffersize];

// Serialize the string into a binary stream
Memorystream stream = new memorystream ();
Binaryformatter formatter = new binaryformatter ();
Formatter. serialize (stream, CT. xmldefinition );

// Write the binary stream to the clob character
Stream. Seek (0, seekorigin. Begin );
Retval = stream. Read (BTS, 0, buffersize );
While (retval = buffersize)
{
Clob. Write (BTS, 0, buffersize );
Retval = stream. Read (BTS, 0, buffersize );
}
Clob. Write (BTS 0,100 );
Clob. endchunkwrite (); // end the write
Clob. Flush (); // refresh
Clob. Close (); // close
}
Reader. Close ();
}
Tran. Commit ();
}
Catch (exception ex)
{
Tran. rollback ();
// Throw new exception (ex. Message );
}
Finally
{
If (conn. State = connectionstate. open)
{
Conn. Close ();
}
}

 

 

Read the clob (BLOB) field:

 

...

Using (oracledatareader reader = command. executereader ())
{
While (reader. Read ())
{
Oracleclob clob = reader. getoracleclob (0); // Read Binary Fields
Clob. Position = 0; // point to the Starting Point
Byte [] TT = new byte [clob. Length];
Clob. Read (TT, 0, (INT) clob. Length); // read clob as binary data.
Memorystream MS = new memorystream (TT );
Binaryformatter BB = new binaryformatter ();
Object oo = BB. deserialize (MS); // deserialization retrieves character data

}}