Author Topic: Working with BLOB and CLOB  (Read 5949 times)

Mike

  • Administrator
  • Hero Member
  • *****
  • Posts: 1947
    • View Profile
Working with BLOB and CLOB
« on: September 11, 2009, 08:57:56 AM »
Working with BLOB and CLOB

LOB basics
LOB stands for Large OBject, a stream of data stored in a database. Maximum capacity of a LOB is (4 gigabytes-1) bytes.In Oracle three kinds of LOB data type exist:

1) BLOB datatype stores unstructured binary large objects.   BLOB objects can be thought of as bitstreams with no character set semantics.

2) The CLOB datatype stores single-byte and multibyte character data. Both   fixed-width and variable-width character sets are supported, and both use the database character set.

3) The NCLOB datatype stores Unicode data.

dotConnect for Oracle supports all three datatypes.

You can retrieve values of LOB fields using OracleDataReader as well as other types like LONG and LONG RAW. The difference with usage of LOB data type becomes evident when you need to access these fields in DML and PL/SQL statements. For BLOB and CLOB data types only LOB locators (pointers to data) are stored in table columns; actual BLOB and CLOB data is stored in separate tablespace. This is the difference to the way that data of LONG or LONG RAW types is stored in database - tables hold their immediate values.

Another issue you should be aware of is temporary LOBs. This kind of object is not referenced by any table yet. It exists in current transaction only. You have to use temporary LOBs when inserting new data to a table. In dotConnect for Oracle, you can create temporary LOBs using OracleLob constructors. Once you create an OracleLob instance, the corresponding temporary LOB appears on the server and any data you insert into the object is sent immediately to server. An example of using temporary LOBs you will see later in the article.

Working with BLOB data
Binary data is generally represented as stream of bytes, or buffers. dotConnect for Oracle allows manipulating BLOB data in most convenient ways. You can work with OracleLob.Value property or use OracleLob.Read() and OracleLob.Write() methods to transfer data to and from the server. Both ways are presented in the samples below. Note that when OracleLob.LobType property is OracleDbType.Blob the OracleLob.Value is treated as an array of bytes (byte[]), whereas OracleDbType.Clob and OracleDbType.NClob representOracleLob.Value as string.

The next sample routines show how to upload a file from hard disk to server and download it back.To execute these routines you have to create a table that is described as follows:

Code: [Select]
CREATE TABLE PICTURES (
ID NUMBER(12),
PICNAME VARCHAR2(20),
PICTURE BLOB
)

[C#]

Code: [Select]
static void UploadBlob(OracleConnection myConnection)
{
  //Open file on disk
  FileStream fs = new FileStream("D:\\Tmp\\test.bmp", FileMode.Open, FileAccess.Read);
  BinaryReader r = new BinaryReader(fs);
  myConnection.Open();
  //Create temporary BLOB
  OracleLob myLob = new OracleLob(myConnection,OracleDbType.Blob);
  int streamLength = (int)fs.Length;
  //Transfer data to server
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
  //Perform INSERT
  OracleCommand myCommand = new OracleCommand(
   "INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection);
  OracleParameter myParam = myCommand.Parameters.Add("Pictures", OracleDbType.Blob);
  myParam.OracleValue = myLob;
  try
  {
    Console.WriteLine(myCommand.ExecuteNonQuery() + " rows affected.");
  }
  finally
  {
    myConnection.Close();
    r.Close();
    fs.Close();
  }
}

static void DownloadBlob(OracleConnection myConnection)
{
  OracleCommand myCommand = new OracleCommand("SELECT * FROM Pictures", myConnection);
  myConnection.Open();
  OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
  try
  {
    while (myReader.Read())
    {
//Obtain OracleLob directly from OracleDataReader
OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("Picture"));
if (!myLob.IsNull)
{
   string FN = myReader.GetString(myReader.GetOrdinal("PicName"));
   //Create file on disk
   FileStream fs = new FileStream("D:\\Tmp\\" + FN + ".bmp", FileMode.Create);
   //Use buffer to transfer data
   byte[] b = new byte[myLob.Length];
   //Read data from database
   myLob.Read(b,0,(int)myLob.Length);
   //Write data to file
   fs.Write(b,0,(int)myLob.Length);
   fs.Close();
   Console.WriteLine(FN + " downloaded.");
}
    }
  }
  finally
  {
    myReader.Close();
    myConnection.Close();
  }
}


[Visual Basic]

Code: [Select]
Public Sub UploadBlob(ByVal myConnection As OracleConnection)
'Open file on disk
Dim fs As FileStream = New FileStream("D:\Tmp\test.bmp", FileMode.Open, FileAccess.Read)
Dim r As BinaryReader = New BinaryReader(fs)
myConnection.Open()
'Create temporary BLOB
Dim myLob As OracleLob = New OracleLob(myConnection, OracleDbType.Blob)
Dim streamLength As Int32 = fs.Length
'Transfer data to server
myLob.Write(r.ReadBytes(streamLength), 0, streamLength)
'Perform INSERT
Dim myCommand As OracleCommand = New OracleCommand(
"INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection)
Dim myParam As OracleParameter = myCommand.Parameters.Add("Pictures", OracleDbType.Blob)
myParam.OracleValue = myLob
Try
Console.WriteLine(myCommand.ExecuteNonQuery() & " rows affected.")
Finally
myConnection.Close()
r.Close()
fs.Close()
End Try
End Sub

Public Sub DownloadBlob(ByVal myConnection As OracleConnection)
Dim myCommand As New OracleCommand("SELECT * FROM Pictures", myConnection)
myConnection.Open()
Dim myReader As OracleDataReader = & _
  myCommand.ExecuteReader(System.Data.CommandBehavior.Default)
Try
While myReader.Read()
  Dim myLob As OracleLob = myReader.GetOracleLob(myReader.GetOrdinal("Picture"))
  If Not myLob.IsNull Then
   Dim FN As String = myReader.GetString(myReader.GetOrdinal("PicName"))
   Dim fs As FileStream = New FileStream("D:\Tmp\" + FN + ".bmp", FileMode.Create)
   Dim w As BinaryWriter = New BinaryWriter(fs)
   w.Write(myLob.Value)
   w.Close()
   fs.Close()
   Console.WriteLine(String.Concat(FN, " downloaded."))
  End If
End While
Finally
myReader.Close()
myConnection.Close()
End Try
End Sub

Working with CLOB data
Working with CLOB data generally is same as working with BLOB data. The difference is in representation of Value property. For CLOB and NCLOB data types when you read from OracleLob.Value property, you get strings. The data is transparently decoded so you do not have to take care about its character set. If you need raw data however you can use streaming capabilities of OracleLob, that is OracleLob.Read and OracleLob.Write methods. Note that since OracleLob.Value is a read-only property, you can write data to LOB only with OracleLob.Write method, and no encoding is performed.

For example, consider this table definition.

Code: [Select]
CREATE TABLE ClobTable (
Id NUMBER,
Name VARCHAR2(30),
Value CLOB
)

If you need to perform simple tasks like fetch record set in most cases you do not needto know about OracleLob. When calling OracleDataReader.GetValue on fetch you get array of bytesfor BLOB column and string for CLOB one. In addition you can use OracleDataReader.GetChars,OracleDataReader.GetBytes to get pieces of LOB value.

For example,

[C#]

Code: [Select]
OracleCommand oraCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection);
OracleDataReader reader = oraCommand.ExecuteReader();
while (reader.Read())
  Console.WriteLine(reader.GetValue(2));


[Visual Basic]

Code: [Select]
Dim oraCommand As OracleCommand = new OracleCommand( _
"SELECT * FROM ClobTable", oraConnection)
Dim reader As OracleDataReader = oraCommand.ExecuteReader()
while (reader.Read())
Console.WriteLine(reader.GetValue(2))
end while

You can do the same thing using OracleLob object directly.

[C#]

Code: [Select]
OracleCommand oraCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection);
OracleDataReader reader = oraCommand.ExecuteReader();
OracleLob clob;
while (reader.Read()) {
clob = reader.GetOracleLob(2);
Console.WriteLine(clob.Value);
}



[Visual Basic]

Code: [Select]
Dim oraCommand As OracleCommand = new OracleCommand( _
"SELECT * FROM ClobTable", oraConnection)
Dim reader OracleDataReader = oraCommand.ExecuteReader()
Dim clob As OracleLob
while (reader.Read())
clob = reader.GetOracleLob(2)
Console.WriteLine(clob.Value)
end while

As we see first code cut was shorter. So, usually, you need to access OracleLob directly in more complicated cases (e.g using temporary LOBs, advanced streaming operations).

Inserting LOBs on Oracle server 8.0.5 and older
Temporary LOBs are not supported in Oracle servers version 8.0.5 and older,so you have to create and initialize LOB locator for Value parameter.To initialize LOB locator you must use EMPTY_BLOB or EMPTY_CLOB Oracle function.To return initialized locator in the same statement use RETURNING clause.

For example,

Code: [Select]
UPDATE ClobTable
SET
Name = :Name,
Value = EMPTY_CLOB()
WHERE
Id = :Id
RETURNING
Value
INTO
:Value


Here is the sample code:

[C#]

Code: [Select]
string sql = "UPDATE ClobTable SET Name = :Name, Value = EMPTY_CLOB() +
   WHERE Id = :Id RETURNING Value INTO :Value";
OracleCommand oraCommand = new OracleCommand(sql, oraConnection);
oraCommand.Parameters.Add("Id", 1);
oraCommand.Parameters.Add("Name", "First");
OracleParameter param = oraCommand.Parameters.Add("Value", OracleDbType.Clob);
param.Direction = ParameterDirection.Input;
param.Value = "The CLOB test value.";
oraCommand.ExecuteNonQuery();


[Visual Basic]
Code: [Select]
Dim sql As string = "UPDATE ClobTable SET Name = :Name, Value = EMPTY_CLOB() & _
    WHERE Id = :Id RETURNING Value INTO :Value"
Dim oraCommand As OracleCommand = new OracleCommand(sql, oraConnection)
oraCommand.Parameters.Add("Id", 1)
oraCommand.Parameters.Add("Name", "First")
Dim param As OracleParameter = oraCommand.Parameters.Add("Value", OracleDbType.Clob)
param.Direction = ParameterDirection.Input
param.Value = "The CLOB test value."
oraCommand.ExecuteNonQuery()


dotConnect for Oracle writes LOB data to Oracle and then returns initialized Value field in :Value parameter. Stored procedures allow you to automate initialization process of LOB values as follows.

Code: [Select]
CREATE OR REPLACE
PROCEDURE ClobTableUpdate (p_Id NUMBER, p_Name VARCHAR2,
   p_Value OUT CLOB)
is
begin
UPDATE ClobTable
SET
Name = p_Name,
Value = EMPTY_CLOB()
WHERE
Id = p_Id
RETURNING
Value
INTO
p_Value;
end;


Note that p_Value parameter is declared as OUT. At the same time set Direction property for parameter of Lob data type to ParameterDirection.Input and assign it with required data prior to actual call to stored procedure. Having previous procedure declaration we may execute it as this:

[C#]

Code: [Select]
OracleCommand oraCommand = new OracleCommand("ClobTableUpdate", oraConnection);
oraCommand.CommandType = CommandType.StoredProcedure;
oraCommand.CreateParameters();
oraCommand.Parameters["p_Id"].Value = id;
oraCommand.Parameters["p_Name"].Value = name;
oraCommand.Parameters["p_Value"].Direction = ParameterDirection.Input;
oraCommand.Parameters["p_Value"].Value = "The CLOB test value.";
oraCommand.ExecuteNonQuery();


[Visual Basic]

Code: [Select]
Dim oraCommand As OracleCommand = new OracleCommand("ClobTableUpdate", oraConnection)
oraCommand.CommandType = CommandType.StoredProcedure
oraCommand.CreateParameters()
oraCommand.Parameters("p_Id").Value = id
oraCommand.Parameters("p_Name").Value = name
oraCommand.Parameters("p_Value").Direction = ParameterDirection.Input
oraCommand.Parameters("p_Value").Value = "The CLOB test value."
oraCommand.ExecuteNonQuery()

It is important to use Direction property of OracleParameter in LOB operations.If Direction is ParameterDirection.Input OracleCommand writes data to a server, if Directionis ParameterDirection.Output it reads data.

You can also use OracleDbType.Long and OracleDbType.LongRaw data types with LOB parametersto write ordinary DML statements. In this case Oracle automatically converts LONG andLONG ROW values to CLOB or BLOB data.


monudyer

  • Newbie
  • *
  • Posts: 1
    • View Profile
Re: Working with BLOB and CLOB
« Reply #1 on: October 27, 2009, 10:28:46 AM »
Hi,
 
Hope it will find you in best of youe health.
 
I found that this subject is the proper solution for my rnd, I'm using vb6 and oracle10g db,
 
my project is unable to compile the syntax like
 
Dim oraCommand As OracleCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection)
 
can you tell me what are the "References" and "Component" I have to include in my project in order to compile that syntax,
 
I'm struggling for fetching records from oracle db.
 
if u can halp me, I will shall be obliged.
 
Regards
Monu, Dubai

Robert Hamer

  • Newbie
  • *
  • Posts: 4
    • View Profile
Re: Working with BLOB and CLOB
« Reply #2 on: November 26, 2009, 06:35:23 AM »
On the server side, you look for the name and value pair. It also depends on whether this is a POST or GET operation. GET,the name value pairs are visible in a URL, POST the name value pairs are sent directly to the server.

BLOBS are for data that haven't a character structure, just like documents created in Office, or images.
CLOBS are just the opposite, great for information that is based on characters, like an XML file, or a long, long, long string.
When you try to retrieve the information from the database CLOBS can be readed directly into a variable, and you can"view" the content pretty easy. For BLOBS the things get complicated,because the whole content most be readed/writed at once, and to view the content you must interpret what it is, say if it's a Excel document you most readed asan Excel book, and so on for each kind of information you store in the column.

« Last Edit: November 26, 2009, 07:29:37 AM by Mike »