As Published In
Oracle Magazine
November/December 2005

DEVELOPER: ODP.NET


Serving Winning LOBs

By Mark A. Williams Oracle ACE

New features in ODP.NET make it easier than ever to handle LOBs.

There are different ways to access data from Oracle databases in .NET applications, but in terms of features and performance, the Oracle Data Provider for .NET (ODP .NET) is your best choice for connecting .NET applications with Oracle Database.

This issue's ODP.NET column focuses on how to work with ODP.NET 10g Release 10.2 and Large Object (LOB) data in Oracle Database 10g Release 2.

ODP.NET LOB Support

There are two basic types of LOB data—binary and character—and both are accessible via ODP.NET. LOB data types are quite flexible and excel at handling small amounts of data as well as extremely large amounts. While the exact maximum size of a LOB depends on your database configuration, it measures in the almost unbelievable 8 to 128 terabyte range. At the other end of the scale, LOB data can be as small as a single byte or character, though using a traditional data type such as a VARCHAR2 to store smaller amounts of character data may make more sense. Binary data is stored as a BLOB column in a table or as a BFILE that resides in a file system outside the database. Character data is stored using the database character set in a CLOB column or the National character set in a NCLOB column.

ODP.NET makes it easy to access and work with LOB data. All LOB objects within ODP.NET inherit from the Stream class provided by the .NET framework and are connected objects. LOB data types are supported by ODP.NET via the OracleBlob , OracleBFile , and OracleClob classes. The OracleClob class represents both CLOB and NCLOB data types. The IsNCLOB property distinguishes which type of data is contained in the CLOB object.

Data that's stored as a LOB is read from the database via an instance of the OracleDataReader class. The OracleDataReader class lets you work with LOB data as an object or as an array of scalar data. To work with LOB data as an object, you invoke a typed accessor such as GetOracleBlob , GetOracleBFile , or GetOracleClob . If you want to work with LOB data as an array, you use either the GetBytes method (for binary data) or the GetChars method (for character data) to read a specific number of bytes or characters at a time and populate the array. In this column I'll focus on working with LOB data stored in the database (rather than stored externally in a BFILE).

The InitialLOBFetchSize Property. The InitialLOBFetchSize property is set on an OracleCommand object. An OracleDataReader object then inherits the value for this property from its related OracleCommand object. The amount of LOB data that's fetched from the database during a read operation on a LOB object is determined by the InitialLOBFetchSize property. When working with character data (CLOB and NCLOB), this value specifies the number of characters to read. When working with binary data (BLOB), this value specifies the number of bytes to read. The behavior and functionality of this property has changed with Oracle Database 10g Release 2.

Prior to this release, the legal values for the InitialLOBFetchSize property were either 0 or an integer between 1 and 32,767 (32KB), inclusive. Beginning with Oracle Database 10g Release 2, the legal values for this property are -1, 0, or an integer between 1 and 2,147,483,647 (2GB), inclusive. As you can see, the maximum value has dramatically increased. This property controls both how data from a LOB may be retrieved, as well has how much data is retrieved. You'll find the complete list of methods available when using various values for this property in the Oracle Data Provider for .NET Developer's Guide.

InitialLOBFetchSize is 0. As with previous releases of ODP.NET, the default value of the InitialLOBFetchSize property is 0. When it's set to 0, all of the typed accessors for a LOB object are enabled and may be called by the client application. This provides a high degree of flexibility: You can work with the LOB as an object by invoking the GetOracleBlob or GetOracleClob typed accessor methods, or you can invoke the GetBytes or GetChars methods to work with the LOB as an array.

InitialLOBFetchSize is > 0. With the InitialLOBFetchSize property set to an integer between 1 and 2,147,483,647 (2GB), ODP.NET reads and caches this number of bytes or characters when the read method is invoked on the OracleDataReader object. If the total size of the LOB is less than this number, the entire LOB data will be read. Thanks to a new performance enhancement in Oracle Database 10g Release 2, the complete LOB data is returned when a typed accessor method is invoked and InitialLOBFetchSize is greater than 0.

In addition to the increased maximum value for the InitialLOBFetchSize property, the use of the typed accessor methods has changed. You can now invoke the GetOracleBlob and GetOracleClob typed accessors when the value of the InitialLOBFetchSize parameter is greater than 0. (In previous releases of Oracle Database, you had to include a primary key, a ROWID, or a unique column in the select list when InitialLOBFetchSize was greater than 0.)

InitialLOBFetchSize is -1. Beginning with Oracle Database 10g Release 2, you can set the InitialLOBFetchSize property to a value of -1. When the property is set to this value, the total amount of LOB data in a column is returned during the read operation. When you use this value, you don't have to include a primary key, ROWID, or unique column in the statement's select list. However, you can't work with the LOB as an object in this case, because the GetOracleBlob and GetOracleClob typed accessors aren't available when InitialLOBFetchSize is -1. You must use another method of retrieving the LOB data, such as the GetBytes or GetChars method.

Strategy

Thanks to new features and fewer constraints, it's now easier and more powerful to work with LOB data and ODP.NET using Oracle Database 10g Release 2. As a developer you no longer must choose between flexibility and performance by using GetOracleBlob and GetOracleClob instead of setting the InitialLOBFetchSize property. You're now free to enjoy increased performance by setting the InitialLOBFetchSize property, as well as enjoy the benefits of working with LOB data as an object.

When developing a new application, you have choices to make. Making the best choices for your application involves using the available features in a way that makes sense for your needs. There are always boundary conditions and exemptions from any guideline; however, I've found that setting the InitialLOBFetchSize property appropriately and taking advantage of the new performance features and enhancements in Oracle Database 10g Release 2 results in a more flexible and better-performing application.

Next Steps


DOWNLOAD
sample application for this column
ODP.NET 10g
Oracle Developer Tools for Visual Studio .NET


When determining the appropriate value for the InitialLOBFetchSize property, I typically ask myself two questions: 

  • Do I know the size of the LOB data at design time?

  • Is the LOB data uniform in size or does it vary widely?

If I don't know the size of the LOB data at design time, I simply allow the InitialLOBFetchSize property to assume its default value of 0. If I do know the size of the LOB data, I ask myself the second question. If the size of the LOB is mostly uniform, meaning that each LOB is approximately the same size, I set the value of the InitialLOBFetchSize property to a value that includes at least 80 percent of the database rows. If the LOB data is highly uniform, meaning that each LOB is the exact same size, I set the value of this property to that size. On the other hand, if the size of the LOB data varies greatly, I again allow the InitialLOBFetchSize property to default to 0. Of course, each application is different; only you can truly determine if your data is uniform or diverse in size and what the appropriate setting for this property should be.

Code Listing 1: Retrieve and display an image stored as a BLOB 

private void btnDisplay_Click(object sender, System.EventArgs e)
{
  // create and open connection
  // change for your environment
  string connStr = "User Id=pm; Password=pm; Data Source=ora10gR2; Pooling=false";
  OracleConnection con = new OracleConnection(connStr);
  con.Open();

  // statement to get a blob
  string sql = "select ad_composite from print_media where product_id=3106 and ad_id=13001";
  
  // create command object
  // InitialLOBFetchSize defaults to 0
  OracleCommand cmd = new OracleCommand(sql, con);

  // create a datareader
  OracleDataReader dr = cmd.ExecuteReader();

  // read the single row result
  dr.Read();

  // use typed accessor to retrieve the blob
  OracleBlob blob = dr.GetOracleBlob(0);

  // create a memory stream from the blob
  MemoryStream ms = new MemoryStream(blob.Value);
    
  // set the image property equal to a bitmap
  // created from the memory stream
  pictureBox1.Image = new Bitmap(ms);
}


Implementation

As with earlier releases, Oracle Database 10g Release 2 ships with a set of sample schemas. The PM sample schema contains a table called print_media that you can use to test LOB data access. The sample code in Listing 1 illustrates a basic method of retrieving an image stored in the BLOB column called ad_composite . This simple Windows application project contains a button called btnDisplay and a picture box called pictureBox1 . Because I don't know the size of the data stored in the ad_composite column, I've allowed the InitialLOBFetchSize property to default to a value of 0. Running the code in Listing 1 shows the result in Figure 1.

 

figure 1
Figure 1



Mark A. Williams (mawilliams@cheshamdbs.com) is an Oracle ACE, Oracle Certified Professional DBA, and the author of Pro .NET Oracle Programming (Apress, 2004). Williams focuses on solutions for Oracle on Windows and contributes to the Oracle Data Provider for .NET forums on the Oracle Technology Network.


Send us your comments