How to work with BLOB and CLOB data using dotConnect for Oracle

LOB (Large OBjects) is a set of data types for storing large amounts of unstructured or semi-structured data. In Oracle there are several different kinds of LOBs:

• BLOB (Binary Large Object) datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. They are often used for storing multimedia, like images, audio, and video.
• The CLOB (Character Large Object) datatype stores textual data in the database character set. Both fixed-width and variable-width character sets are supported. This data type is suitable for storing documents or large strings or schema-less XML documents.
• The NCLOB datatype stores Unicode data. This data type can be used for storing documents and texts in National Character Set.

Old versions of Oracle use LONG and LONGRAW datatypes. These datatypes are considered outdated and Oracle recommends using BLOB datatype instead.

There is also the BFILE data type that is used to store binary data as usual files in a file system, outside of Oracle database, however, we don't cover working with them in this article. This article contains information on how to read and write BLOB and CLOB data (including NCLOB), using the OracleLob class of dotConnect for Oracle, and this class is not used with the BFILE data. To work with Oracle BFILEs, you need to use the OracleBFile class.

 

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 LOB using OracleLob constructors. Once you create an OracleLob instance 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 (Binary Large Object) data

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

The next sample routines show how to upload a file from hard disk to server and download it back. This example demonstrates how to read BLOB data in Oracle using the OracleLob.Read method, how to write BLOB data to the database, and how to get size of BLOB in Oracle via OracleLob.Length property. For this example, you have to create a table that is described as follows:

CREATE TABLE Pictures (
  ID NUMBER(12),
  PicName VARCHAR2(20),
  Picture BLOB
)

[C#]

static void UploadBlob(OracleConnection connection)
{
  // Open file on disk
  FileStream stream = new FileStream(@"D:\Tmp\test.bmp", FileMode.Open, FileAccess.Read);
  BinaryReader reader = new BinaryReader(stream);

  try
  {
    connection.Open();
    
    // Create temporary BLOB
    OracleLob lob = new OracleLob(connection,OracleDbType.Blob);
    int streamLength = (int)stream.Length;
  
    // Transfer data to server
    lob.Write(reader.ReadBytes(streamLength), 0, streamLength);
  
    // Perform INSERT
    OracleCommand command = new OracleCommand(
      "INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", connection);
    OracleParameter param = command.Parameters.Add("Pictures", OracleDbType.Blob);
    param.OracleValue = lob;
  
    Console.WriteLine("{0} rows affected.", command.ExecuteNonQuery());
  }
  finally
  {
    connection.Close();
    reader.Close();
    stream.Close();
  }
}

static void DownloadBlob(OracleConnection connection)
{
  OracleCommand command = new OracleCommand("SELECT * FROM Pictures", connection);
  connection.Open();
  OracleDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.Default);
  try
  {
    while (reader.Read())
    {
      // Obtain OracleLob directly from OracleDataReader
      OracleLob lob = reader.GetOracleLob(reader.GetOrdinal("Picture"));
      if (!lob.IsNull)
      {
        string fileName = reader.GetString(reader.GetOrdinal("PicName"));

        // Create file on disk
        FileStream stream = new FileStream(@"D:\Tmp\" + fileName + ".bmp", FileMode.Create);

        // Use buffer to transfer data
        byte[] buffer = new byte[lob.Length];

        // Read data from database
        lob.Read(buffer,0,(int)lob.Length);

        // Write data to file
        stream.Write(buffer,0,(int)lob.Length);
        stream.Close();
        Console.WriteLine(fileName + " downloaded.");
      }
    }
  }
  finally
  {
    reader.Close();
    connection.Close();
  }
}

[Visual Basic]

Public Sub UploadBlob(ByVal connection As OracleConnection)
  ' Open file on disk
  Dim stream As FileStream = New FileStream("D:\Tmp\test.bmp", FileMode.Open, FileAccess.Read)
  Dim reader As BinaryReader = New BinaryReader(stream)
  Try
    connection.Open()

    ' Create temporary BLOB
    Dim lob As OracleLob = New OracleLob(connection, OracleDbType.Blob)
    Dim streamLength As Int32 = stream.Length

    ' Transfer data to server
    lob.Write(reader.ReadBytes(streamLength), 0, streamLength)

    ' Perform INSERT
    Dim command As OracleCommand = New OracleCommand(
      "INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", connection)
    Dim param As OracleParameter = command.Parameters.Add("Pictures", OracleDbType.Blob)
    param.OracleValue = lob
    Console.WriteLine("{0} rows affected.", command.ExecuteNonQuery());

  Finally
    connection.Close()
    reader.Close()
    stream.Close()
  End Try

End Sub

Public Sub DownloadBlob(ByVal connection As OracleConnection)
  Dim command As New OracleCommand("SELECT * FROM Pictures", connection)
  connection.Open()

  ' Obtain OracleLob directly from OracleDataReader
  Dim reader As OracleDataReader = & _
      command.ExecuteReader(System.Data.CommandBehavior.Default)
  Try
    While reader.Read()
      Dim lob As OracleLob = reader.GetOracleLob(reader.GetOrdinal("Picture"))
      If Not lob.IsNull Then
        Dim fileName As String = reader.GetString(reader.GetOrdinal("PicName"))

        ' Create file on disk
        Dim stream As FileStream = New FileStream("D:\Tmp\" + fileName + ".bmp", FileMode.Create)

        ' Use buffer to transfer data
        Dim buffer As Byte() = New Byte(lob.Length - 1) {}

        ' Read data from database
        lob.Read(buffer, 0, CInt(lob.Length))

        ' Write data to file
        stream.Write(buffer, 0, CInt(lob.Length))
        stream.Close()
        Console.WriteLine(fileName & " downloaded.")
      End If
    End While
  Finally
    reader.Close()
    connection.Close()
  End Try
End Sub

Working with CLOB (Character Large Object) data

Working with CLOB (Character Large Object) 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.

CREATE TABLE ClobTable (
  Id NUMBER,
  Name VARCHAR2(30),
  Value CLOB
)

However, with dotConnect for Oracle you probably won't need to dive deeply in LOB data specifics or details of OracleLob class if you need to perform simple tasks like fetch a record set. The usual GetValue method of OracleDataReader returns an array of bytes for a BLOB column and a string for CLOB one. In addition you can use OracleDataReader.GetChars and OracleDataReader.GetBytes methods to get pieces of LOB value.

The following example shows how to read CLOB data in Oracle:

[C#]

...
OracleCommand oraCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection);
OracleDataReader reader = oraCommand.ExecuteReader();
while (reader.Read())
  Console.WriteLine(reader.GetValue("Value"));
...

[Visual Basic]

...
Dim oraCommand As OracleCommand = new OracleCommand( _
    "SELECT * FROM ClobTable", oraConnection)
Dim reader As OracleDataReader = oraCommand.ExecuteReader()
while (reader.Read())
  Console.WriteLine(reader.GetValue("Value"))
end while
...

You can do the same thing using OracleLob object directly.

[C#]

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

[Visual Basic]

...
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("Value")
  Console.WriteLine(clob.Value)
end while
...

As we see first code sample 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,

UPDATE ClobTable
SET
  Name = :Name,
  Value = EMPTY_CLOB()
WHERE
  Id = :Id
RETURNING
  Value
INTO
  :Value

Here is the sample code:

[C#]

...
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.Output;
oraCommand.ExecuteNonQuery();
...

[Visual Basic]

...
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.Output
oraCommand.ExecuteNonQuery()
...

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

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;

Having previous procedure declaration we may execute it as this:

[C#]

...
OracleCommand oraCommand = new OracleCommand("ClobTableUpdate", oraConnection);
oraCommand.CommandType = CommandType.StoredProcedure;
OracleCommandBuilder.DeriveParameters(oraCommand);
oraCommand.Parameters["p_Id"].Value = id;
oraCommand.Parameters["p_Name"].Value = name;
oraCommand.ExecuteNonQuery();
...

[Visual Basic]

...
Dim oraCommand As OracleCommand = new OracleCommand("ClobTableUpdate", oraConnection)
oraCommand.CommandType = CommandType.StoredProcedure
OracleCommandBuilder.DeriveParameters(oraCommand)
oraCommand.Parameters("p_Id").Value = id
oraCommand.Parameters("p_Name").Value = name
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 Direction is ParameterDirection.Output it reads data.

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

Conclusion

This article describes Oracle LOB functionality, allowing storing large binary or text values in the database, and demonstrates different ways of working with Oracle LOB on .NET Framework, using dotConnect for Oracle - an ADO.NET provider from Devart.

You may also be interested in:

• Working with Cursors
• Working with PL/SQL Tables
• Working with Array Binding
• Working with Objects
• Working with VARRAY Type

Back to list