Using Large Objects in .NET
by Jason Price

Learn how to read and write to large objects (LOBs) using .NET, as well as how to read data via a BFILE.

Downloads for this article: · Sample Code · Oracle Database 10g · ODP.NET (version 10.1.0.2.0 or later, if required) · Microsoft .NET Framework and SDK

In this article, a sequel to a previous article on using database objects in .NET, you will learn how to use large objects in Visual Basic .NET (VB.NET) and Visual C# .NET (C#). Specifically, you will earn how to read and write to large objects (LOBs) using .NET. You will also learn how to read data via a BFILE, which you can think of as a pointer to a file. All the scripts and files referenced in this article are available here. This article assumes familiarity with C# and VB.NET programming in general.

This article is for developers and you should already be familiar with C# and VB.NET programming in general, and have a basic knowledge of LOBs. If you need an introduction to LOBs, you can read the Oracle LOB documentation or you can also read my book Oracle Database 10g SQL (McGraw-Hill/Osborne, 2004).

Before you can follow along with the examples shown in this article, you will need to install the following software:

• Windows NT 4.0, Windows 2000, Windows XP Professional, or Windows Server 2003
• Access to an installation of Oracle Database
• Oracle Client (version 10.1.0.2.0 or later)
• Oracle Net (version 10.1.0.2.0 or later)
• Oracle Data Providers for .NET (version 10.1.0.2.0 or later, if required)
• Microsoft .NET Framework (version 1.0 or later)
• Microsoft .NET Framework SDK (version 1.0 or later)

Note: If you are using a release prior to Oracle Database 10g (Oracle8i release 3 8.1.7 or later), you will need to download and install the Oracle Data Provider for .NET (ODP.NET) separately from the database.

The ODP.NET driver is tuned for maximum performance when accessing an Oracle database, and also supports the rich features of an Oracle database such as the BFILE, BLOB, CLOB, XMLType, and so on. If you are developing .NET applications running against and Oracle database, ODP.NET is your best data access solution for both features and performance.

Note: The ODP.NET drivers are tuned for maximum performance when accessing an Oracle database, and also support the rich features of an Oracle database such as the BFILE, BLOB, CLOB, XMLType, and so on. If you are developing .NET applications running against an Oracle database, ODP.NET is your best data access solution for both features and performance.

Database Schema Setup

First you need to set up your database schema, which will contain the tables used in this article. You must first create a user named lob_user and grant the required privileges to that user as follows. (You must first log into the database as a user with database administrator privileges to create a user and grant privileges):

CREATE USER lob_user IDENTIFIED BY lob_password;
GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY TO lob_user;

You will find the two previous statements and the others shown in this section to set up the store schema in the sample code file lob_db.sql.

The following statement creates a directory named SAMPLE_FILES_DIR that points to the C:\sample_files directory on the hard drive of the server; you must create the sample_files directory in the C: partition of your hard drive and copy the textContent.txt and binaryContent.doc files to C:\sample_files.

CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS 'C:\sample_files';

Note: The textContent.txt and binaryContent.doc files contain quotes from Shakespeare's play Macbeth. You will see the content from these files copied to the database shortly.

GRANT READ ON DIRECTORY SAMPLE_FILES_DIR TO PUBLIC;

The next statement connects as lob_user:

CONNECT lob_user/lob_password;

You will see the use of three tables in this article:

• clob_content: contains a CLOB column named clob_column. This column is used to store the character data contained in the textContent.txt file.
• blob_content: contains a BLOB column named blob_column. This column is used to store the binary data stored in the binaryContent.doc file.
• bfile_content: contains a BFILE column named bfile_column. This column is used to store pointers to the two external files.

CREATE TABLE clob_content (
  id          INTEGER PRIMARY KEY,
  clob_column CLOB NOT NULL
);

CREATE TABLE blob_content (
  id          INTEGER PRIMARY KEY,
  blob_column BLOB NOT NULL
);

CREATE TABLE bfile_content (
  id           INTEGER PRIMARY KEY,
  bfile_column BFILE NOT NULL
);

If you create the tables in a different schema to lob_user, you will need change the schema name in the sample programs that you will see later.

The next two statements add an empty CLOB and BLOB to the clob_content and blob_content tables:

INSERT INTO clob_content (
  id, clob_column
) VALUES (
  1, EMPTY_CLOB()
);

INSERT INTO blob_content (
  id, blob_column
) VALUES (
  1, EMPTY_BLOB()
);

The following PL/SQL statements load the text from the file textContent.txt into the clob_content table and load the binary from the file binaryContent.doc into the blob_content table:

DECLARE
  my_clob  CLOB;
  my_blob  BLOB;
  my_bfile BFILE;
BEGIN
  -- load the CLOB
  my_bfile := BFILENAME('SAMPLE_FILES_DIR', 'textContent.txt');
  SELECT clob_column
  INTO my_clob
  FROM clob_content
  WHERE id = 1 FOR UPDATE;
  DBMS_LOB.FILEOPEN(my_bfile, dbms_lob.file_readonly);
  DBMS_LOB.LOADFROMFILE(my_clob, my_bfile, DBMS_LOB.GETLENGTH(my_bfile), 1, 1);

  -- load the BLOB
  my_bfile := BFILENAME('SAMPLE_FILES_DIR', 'binaryContent.doc');
  SELECT blob_column
  INTO my_blob
  FROM blob_content
  WHERE id = 1 FOR UPDATE;
  DBMS_LOB.FILEOPEN(my_bfile, dbms_lob.file_readonly);
  DBMS_LOB.LOADFROMFILE(my_blob, my_bfile, DBMS_LOB.GETLENGTH(my_bfile), 1, 1);

  DBMS_LOB.FILECLOSEALL();
  COMMIT;
END;
/

The next statement makes the BFILE in the bfile_content table point to the textContent.txt file located in the SAMPLE_FILES_DIR directory:

INSERT INTO bfile_content (
  id,
  bfile_column
) VALUES (
  1,
  BFILENAME('SAMPLE_FILES_DIR', 'textContent.txt')
);

Reading Data from a LOB Using C# and VB.NET

There are two ways to retrieve a LOB:

1. Defer LOB retrieval using LOB locators: ODP.NET retrieves a pointer to the LOB on the database server. No actual LOB data is retrieved until the application begins reading the LOB. For a CLOB, you use an object of the Oracle.DataAccess.Types.OracleClob class to store the locator that you read using the GetOracleClob() method of an OracleDataReader object, and then use the Read() method to access the data stored in the OracleClob. For a BLOB, you use an Oracle.DataAccess.Types.OracleBlob object and use the GetOracleBlob() method.
2. Retrieve all or much of the LOB data immediately: ODP.NET will retrieve LOB data for all LOBs as soon as the SELECT statement executes. For a CLOB, you read the data using the GetString(), GetChars(), GetValue(), or GetOracleString() methods of an OracleDataReader. For a BLOB, you use the GetBytes(), GetValue(), GetValues(), or GetOracleBinary() methods.

All the LOBs in the SELECT statement execution are affected by this parameter. For example, if you set InitialLOBFetchSize to 5K and 10 LOBs will be retrieved in your SELECT statement execution, then the first 5K of each of the 10 LOBs will be retrieved in one database round trip. Currently, the maximum setting in release 10.1.0.2.0 of ODP.NET for InitialLOBFetchSize is 32KB. Oracle will increase this maximum size to 2GB in a future release.

Note: If you change InitialLOBFetchSize from 0, then you can only use the accessors in method 2 mentioned earlier to read from a CLOB—although Oracle plans to remove that restriction. In a future ODP.NET release, you will be able to use both methods to retrieve LOBs.

If the amount of data in all the LOBs you are selecting is not large and you are selecting many LOBs, then you will likely see better performance retrieving LOB data immediately by changing InitialLOBFetchSize from its default 0 value. If you use InitialLOBFetchSize, then you should set it to a value slightly larger than the size of 80% of the LOBs selected. For example, if 80% of the LOBs in the rows are 1KB or less, then you should set InitialLOBFetchSize to 1KB. You should experiment with your setup to find the optimum setting for InitialLOBFetchSize since your results will depend on things like network performance, latency, size of data, and so on.

Immediate LOB retrieval occurs when you change InitialLOBFetchSize from its default value of 0. Deferred LOB retrieval occurs when you leave InitialLOBFetchSize in its default value of 0. The following table provides some principles to consider when deciding between using deferred LOB retrieval and immediate LOB retrieval.

Use Deferred LOB Retrieval When...	Use Immediate LOB Retrieval When...
Network bandwidth between the client and database server is scarce	Network bandwidth is abundant
You do not need immediate access to most of your LOB data immediately, but rather can retrieve it over time	You want to read almost all the LOB data as soon as it is selected
You are performing an update, insert, or delete and don't plan to read the LOB	N/A

Using Deferred LOB Retrieval to Read Data from a LOB

1. ClobExample1.cs, which is a C# program that reads from clob_content.clob_column
2. ClobExample1.vb, which is the same as ClobExample1.cs but written in VB.NET
3. BlobExample1.cs, which is a C# program that reads from blob_content.blob_column
4. BlobExample1.vb, which is the same as BlobExample1.cs but written in VB.NET.

The first step in ClobExample1.cs reads the row from the clob_content table:

myOracleCommand.CommandText =
  "SELECT id, clob_column " +
  "FROM clob_content " +
  "WHERE id = 1";
OracleDataReader myOracleDataReader =
  myOracleCommand.ExecuteReader();
myOracleDataReader.Read();

In ClobExample1.vb the VB.NET is

myOracleCommand.CommandText = _
  "SELECT id, clob_column " & _
  "FROM clob_content " & _
  "WHERE id = 1"
Dim myOracleDataReader As _
  OracleDataReader = myOracleCommand.ExecuteReader()
myOracleDataReader.Read()

The first step in BlobExample1.cs reads the row from the blob_content table:

myOracleCommand.CommandText =
  "SELECT id, blob_column " +
  "FROM blob_content " +
  "WHERE id = 1";
OracleDataReader myOracleDataReader =
  myOracleCommand.ExecuteReader();
myOracleDataReader.Read();

In BlobExample1.vb the VB.NET is

myOracleCommand.CommandText = _
  "SELECT id, blob_column " & _
  "FROM blob_content " & _
  "WHERE id = 1"
Dim myOracleDataReader As _
  OracleDataReader = myOracleCommand.ExecuteReader()
myOracleDataReader.Read()

Step 2

The second step in ClobExample1.cs copies the LOB locator to an OracleClob object. You use the method to get the locator:

OracleClob myOracleClob = myOracleDataReader.GetOracleClob(1);

In ClobExample1.vb the VB.NET is

Dim myOracleClob As _
  OracleClob = myOracleDataReader.GetOracleClob(1)

The second step in BlobExample1.cs copies the LOB locator to an OracleBlob object. You use the myOracleDataReader.GetOracleBlob() method to get the locator:

OracleBlob myOracleBlob = myOracleDataReader.GetOracleBlob(1);

In BlobExample1.vb the VB.NET is

Dim myOracleBlob As _
  OracleBlob = myOracleDataReader.GetOracleBlob(1)

Step 3

The third step in ClobExample1.cs is to get the CLOB data using the Read() method of the OracleClob object. There are two versions of the Read() method:

int Read(byte [] byteArray, int offset, int count)
int Read(char [] charArray, int offset, int count)

• The returned int is the number of bytes or characters read
• byteArray and charArray are the arrays to read the data into
• offset is the position in the array to which to write the data
• count is the number of bytes or characters to read.

char [] charArray = new char[50];
int numCharsRead; 
while ((numCharsRead = myOracleClob.Read(charArray, 0, 50)) > 0)
{
  Console.WriteLine("numCharsRead = " + numCharsRead);
  string clobData = new string(charArray, 0, numCharsRead);
  Console.WriteLine("clobData = " + clobData);
}

In ClobExample1.vb (VB.NET):

Dim charArray(50) As char
Dim numCharsRead As Integer
numCharsRead = myOracleClob.Read(charArray, 0, 50)
Do While (numCharsRead > 0)
  Console.WriteLine("numCharsRead = " & numCharsRead)
  Dim clobData As New string(charArray, 0, numCharsRead)
  Console.WriteLine("clobData = " & clobData)
  numCharsRead = myOracleClob.Read(charArray, 0, 50)
Loop

The third step in BlobExample1.cs uses the Read() method of the OracleBlob object. There is only one version of the Read() method:

int Read(byte [] byteArray, int offset, int count)

In the following C# code from BlobExample1.cs you will notice the characters are read from the BLOB into an array of bytes named byteArray. The sample code will read the BLOB 50 bytes at a time for each database round trip until the entire BLOB is read.

byte [] byteArray = new byte[50];
Console.WriteLine("byteArray.Length = " + byteArray.Length);
int numBytesRead;
while ((numBytesRead = myOracleBlob.Read(byteArray, 0, 50)) > 0)
{
  Console.WriteLine("numBytesRead = " + numBytesRead);
}

In BlobExample1.vb the VB.NET is

Dim byteArray(50) As byte
Dim numBytesRead As Integer
numBytesRead = _
  myOracleBlob.Read(byteArray, 0, 50)
Do While (numBytesRead > 0)
  Console.WriteLine("numBytesRead = " & numBytesRead)
  numBytesRead = _
    myOracleBlob.Read(byteArray, 0, 50)
Loop

Using Immediate LOB Retrieval to Read Data from a LOB

• ClobExample2.cs, a C# program that reads from clob_content.clob_column
• ClobExample2.vb, same as above but written in VB.NET
• BlobExample2.cs, a C# program that reads from blob_content.blob_column
• BlobExample2.vb, same as above but written in VB.NET.

Step 1

The first step sets the InitialLOBFetchSize to 1,000 bytes. In ClobExample2.cs and BlobExample2.cs the C# is

myOracleCommand.InitialLOBFetchSize = 1000;

Notice that you set the InitialLOBFetchSize on the OracleCommand object. In ClobExample2.vb and BlobExample2.vb the VB.NET is

myOracleCommand.InitialLOBFetchSize = 1000

Step 2

The second step is identical to Step 1 shown for the deferred LOB retrieval sample earlier.

Step 3

The third step in ClobExample2.cs gets the data from the CLOB using the GetString() method and displays the data:

String clobData = myOracleDataReader.GetString(1);
Console.WriteLine("clobData = " + clobData);

In ClobExample2.vb the VB.NET is

Dim clobData As string = myOracleDataReader.GetString(1)
Console.WriteLine("clobData = " & clobData)

The third step in BlobExample2.cs gets the data from the BLOB using the GetBytes() method and displays the number of bytes read:

byte [] byteArray = new byte[1000];
long numBytesRead = myOracleDataReader.GetBytes(1, (long) 0, byteArray, 0, 1000);
Console.WriteLine("numBytesRead = " + numBytesRead);

In BlobExample2.vb the VB.NET is

Dim byteArray(1000) As byte
Dim numBytesRead As long = _
  myOracleDataReader.GetBytes(1, 0, byteArray, 0, 1000)
Console.WriteLine("numBytesRead = " & numBytesRead)

Writing to a LOB Using C# and VB.NET

There are two ways to write to a LOB when using an OracleDataReader object:

• For a CLOB, call the GetOracleClobForUpdate() method of the OracleDataReader. For a BLOB, you call the GetOracleBlobForUpdate() method.
• Add a FOR UPDATE clause to the SELECT statement, and then call the GetOracleClob() or GetOracleBlob() methods to get the CLOB or BLOB.

Using Method 1 to Write to a CLOB

I will walk you through the main steps in two example programs that use Method 1 to write to the CLOB in clob_content.clob_column. The two programs are as follows:

• ClobExample3.cs, a C# program that writes to clob_content.clob_column
• ClobExample3.vb, the same as above but written in VB.

Step 1

All updates to LOBs must be performed within the context of an OracleTransaction object. In ClobExample3.cs the C# is

OracleTransaction myOracleTransaction = myOracleConnection.BeginTransaction();

In ClobExample3.vb the VB.NET is

Dim myOracleTransaction As OracleTransaction = _
  myOracleConnection.BeginTransaction()

Step 2

The second step is to read the row, which is the same as Step 1 shown in the earlier section entitled "Using Deferred LOB Retrieval to Read Data from a LOB."

Step 3

The third step is to get the LOB locator using the GetOracleClobForUpdate() method of the OracleDataReader. In ClobExample3.cs the C# is

OracleClob myOracleClob = myOracleDataReader.GetOracleClobForUpdate(1);

In ClobExample3.vb the VB.NET is

Dim myOracleClob As _
  OracleClob = myOracleDataReader.GetOracleClobForUpdate(1)

Step 4

The fourth step is to write to the CLOB using the Write() method of the OracleClob object. There are two versions of the Write() method:

Write(byte [] byteArray, int offset, int count)
Write(char [] charArray, int offset, int count)

where:

• byteArray and charArray are the arrays containing the data to write to the CLOB
• offset is the position in the CLOB to which to write the data
• count is the number of bytes or characters to write.

myOracleClob.Erase();
string text = "It is the east, and Juliet is the Sun";
char [] charArray = text.ToCharArray();
myOracleClob.Write(charArray, 0, charArray.Length);
Console.WriteLine("myOracleClob.Value = " + myOracleClob.Value);

In ClobExample3.vb the VB.NET is

myOracleClob.Erase()
Dim text As string = "It is the east, and Juliet is the Sun"
Dim charArray() As char = text.ToCharArray()
myOracleClob.Write(charArray, 0, charArray.Length)
Console.WriteLine("myOracleClob.Value = " & myOracleClob.Value)

Step 5

The fifth step is to commit the transaction to permanently store the new text in the database. In ClobExample3.cs the C# is

myOracleTransaction.Commit();

In ClobExample3.vb the VB.NET is

myOracleTransaction.Commit()

Using Method 2 to Write to a CLOB

Now let's walk through the main steps in two example programs that use method 2 to write to the CLOB in clob_content.clob_column. The two programs are as follows:

• ClobExample4.cs, a C# program that writes to clob_content.clob
• ClobExample4.vb, the same as above but written in VB.NET.

myOracleCommand.CommandText =
  "SELECT id, clob_column " +
  "FROM clob_content " +
  "WHERE id = 1 FOR UPDATE";
OracleDataReader myOracleDataReader =
  myOracleCommand.ExecuteReader();
myOracleDataReader.Read();

In ClobExample4.vb the VB.NET is

myOracleCommand.CommandText = _
  "SELECT id, clob_column " & _
  "FROM clob_content " & _
  "WHERE id = 1 FOR UPDATE"
Dim myOracleDataReader As _
  OracleDataReader = myOracleCommand.ExecuteReader()
myOracleDataReader.Read()

Step 3

OracleClob myOracleClob = myOracleDataReader.GetOracleClob(1);

In ClobExample4.vb the VB.NET is

Dim myOracleClob As OracleClob = _
  myOracleDataReader.GetOracleClob(1)

Here's the difference between this step and the one shown in the previous method: whereas here I call GetOracleClob(), previously I called GetOracleClobForUpdate(). Because the FOR UPDATE clause is used in step 2, I don't have to use GetOracleClobForUpdate()—I can simply call GetOracleClob() and start writing to the CLOB. Once you have the CLOB locator, writing to the CLOB is exactly the same as before in method 1.

Reading from a BFILE Using C# and VB.NET

A BFILE stores a pointer to a file on the file system, which you can access via that pointer. I will walk you through the main steps in two example programs that read the text in the file textContent.txt that is pointed to by the BFILE in bfile_content.bfile_column. The two programs are as follows:

• BfileExample1.cs, a C# program
• BfileExample1.vb, the VB.NET version of the above.

myOracleCommand.CommandText =
  "SELECT id, bfile_column " +
  "FROM bfile_content " +
  "WHERE id = 1";
OracleDataReader myOracleDataReader =
  myOracleCommand.ExecuteReader();
myOracleDataReader.Read();

In BfileExample1.vb the VB.NET is

myOracleCommand.CommandText = _
  "SELECT id, bfile_column " & _
  "FROM bfile_content " & _
  "WHERE id = 1"
Dim myOracleDataReader As OracleDataReader = _
  myOracleCommand.ExecuteReader()
myOracleDataReader.Read()

Step 2

OracleBFile myOracleBFile = myOracleDataReader.GetOracleBFile(1);
Console.WriteLine("myOracleBFile.DirectoryName = " +
  myOracleBFile.DirectoryName);
Console.WriteLine("myOracleBFile.FileName = " +
  myOracleBFile.FileName);
Console.WriteLine("myOracleBFile.FileExists = " +
  myOracleBFile.FileExists);

In BfileExample1.vb the VB.NET is

Dim myOracleBFile As OracleBFile = _
  myOracleDataReader.GetOracleBFile(1)
Console.WriteLine("myOracleBFile.DirectoryName = " & _
  myOracleBFile.DirectoryName)
Console.WriteLine("myOracleBFile.FileName = " & _
  myOracleBFile.FileName)
Console.WriteLine("myOracleBFile.FileExists = " & _
  myOracleBFile.FileExists)

Step 3

The third step in BfileExample1.cs opens the BFILE:

myOracleBFile.OpenFile();

In BfileExample1.vb the VB.NET is identical to the above.

Step 4

byte [] byteArray = new byte[1000];
int offset = 0;
int numBytesRead;
while ((numBytesRead = myOracleBFile.Read(byteArray, offset, 1000-offset)) > 0)
{
  Console.WriteLine("numBytesRead = " + numBytesRead);
  String text =
    System.Text.Encoding.ASCII.GetString(byteArray, 0, numBytesRead);
  Console.WriteLine("text = " + text);
  offset += numBytesRead;
}

In BfileExample1.vb the VB.NET is

Dim byteArray(1000) As byte
Dim offset As Integer = 0
Dim numBytesRead As Integer
numBytesRead = myOracleBFile.Read(byteArray, offset, 1000-offset)
Do While (numBytesRead > 0)
  Console.WriteLine("numBytesRead = " & numBytesRead)
  Dim text As string = _
    System.Text.Encoding.ASCII.GetString(byteArray, 0, numBytesRead)
  Console.WriteLine("text = " & text)
  offset += numBytesRead
  numBytesRead = myOracleBFile.Read(byteArray, offset, 1000-offset)
Loop                              

Step 5

The fifth step in BfileExample1.cs closes the BFILE using the Close() method:

myOracleBFile.Close();                   

In BfileExample1.vb the VB.NET is identical to the above.

In this article you explored how to access large objects from C# and VB.NET. You also learned how to read data via a BFILE.