LOB Support

ODP.NET provides an easy and optimal way to access and manipulate large datatypes. Oracle Database supports large character and large binary datatypes.

Large Character Datatypes

CLOB - Character data can store up to 4 gigabytes (4 GB).
NCLOB - Unicode National character set data can store up to 4 gigabytes.

Large Binary Datatypes

BLOB - Unstructured binary data can store up to 4 gigabytes.
BFILE - Binary data stored in external file can store up to 4 gigabytes.

Note:
LONG and LONG RAW datatypes are made available for backward compatibility in Oracle9i, but should not be used in new applications.

ODP.NET provides three objects for LOBs for manipulating LOB data: OracleBFile, OracleBlob, and OracleClob.

Table 3-13 shows the proper ODP.NET class to use for a particular Oracle LOB type.

Table 3-13 ODP.NET LOB Objects

Oracle LOB Type	ODP.NET LOB object
`BFILE`	`OracleBFile` object
`BLOB`	`OracleBlob` object
`CLOB`	`OracleClob` object
`NCLOB`	`OracleClob` object

The ODP.NET LOB objects can be obtained by calling the proper typed accessor on the OracleDataReader or as an output parameter on a command execution with the proper bind type.

All ODP.NET LOB objects inherit from the .NET Stream class to provide generic Stream operations. The LOB data (except for BFILEs) can be updated using the ODP.NET LOB objects by using methods such as Write. Data is not cached in the LOB objects when read and write operations are carried out. Therefore, each Read or Write request incurs a server round-trip. The OracleClob overloads the Read method, providing two ways to read data from a CLOB. The Read method that takes a byte[] as the buffer populates it with CLOB data as Unicode byte array. The Read method that takes a char[] as the buffer populates it with Unicode characters.

Extensions can also be found on the OracleBFile object. An OracleBFile object must be explicitly opened using the OpenFile method before any data can be read from it. To close a previously opened BFILE, use the CloseFile method.

Every ODP.NET LOB object is a connected object and requires a connection during its lifetime. If the connection associated with a LOB object is closed, then the LOB object is not usable and should be disposed.

If an ODP.NET LOB object is obtained from an OracleDataReader through a typed accessor, then its Connection property is set with a reference to the same OracleConnection object used by the OracleDataReader. If a LOB object is obtained as an output parameter, then its Connection property is set with a reference to the same OracleConnection property used by the OracleCommand. If a LOB object is obtained by invoking an ODP.NET LOB object constructor to create a temporary LOB, the Connection property is set with a reference to the OracleConnection object provided in the constructor.

The ODP.NET LOB object Connection property is read-only and cannot be changed during its lifetime. In addition, the ODP.NET LOB types object can only be used within the context of the same OracleConnection referenced by the ODP.NET LOB object. For example, the ODP.NET LOB object's Connection must reference the same connection as the OracleCommand if the ODP.NET LOB object is a parameter of the OracleCommand. If that is not the case, ODP.NET raises an exception when the command is executed.

See Also:

Oracle Database Application Developer's Guide - Large Objects for complete information about Oracle9i LOBs and how to use them

Updating LOBs Using a DataSet

BFILE and BLOB data are stored in the DataSet as byte arrays while CLOB and NCLOB data are stored as strings. In a similar manner to other types, an OracleDataAdapter object can be used to fill and update LOB data changes along with the use of the OracleCommandBuilder for auto-generating SQL.

Note that an Oracle LOB column can store up to 4 GB of data. When the LOB data is fetched into the DataSet, the actual amount of LOB data the DataSet can hold for a LOB column is limited to the maximum size of a .NET string type, which is 2 GB. Therefore, when fetching LOB data that is greater than 2 GB, ODP.NET LOB objects must be used to avoid any data loss.

Updating LOBs Using OracleCommand and OracleParameter

To update LOB columns, LOB data can be bound as a parameter for SQL statements, anonymous PL/SQL blocks, or stored procedures. The parameter value can be set as a NET Framework type, ODP.NET type, or as an ODP.NET LOB object type. For example, when inserting a .NET string data into a LOB column in a Oracle9i database, that parameter can be bound as OracleDbType.Varchar2. For a parameter whose value is set to an OracleClob object, the parameter should be bound as OracleDbType.Clob.

Updating LOBs Using ODP.NET LOB Objects

Oracle BFILEs are not updatable and hence OracleBFile objects do not allow updates to BFILE columns.

Two requirements must be met to update LOB data using ODP.NET LOB objects.

A transaction must be started before a LOB column is selected.

The transaction must be started using the BeginTransaction method on the OracleCommand before the command execution so that the lock can be released when OracleTransaction Commit or Rollback is invoked.
The row in which the LOB column resides must be locked; on a row by row basis or as part of an entire result set.
1. Locking the entire result
  
  Add the FOR UPDATE clause to the end of the SELECT statement. After execution of the command, the entire result set is locked.
2. Locking the row - There are two options:
  - Invoke one of OracleDataReader's typed accessors (GetOracleClobForUpdate or GetOracleBlobForUpdate) on the OracleDataReader to obtain an ODP.NET LOB object while also locking the current row.
    
    This approach requires a primary key, unique column(s), or a ROWID in the result set because the OracleDataReader must uniquely identify the row to re-select it for locking.
  - Execute an INSERT or an UPDATE statement that returns a LOB in the RETURNING clause.

Temporary LOBs

Temporary LOBs can be instantiated for BLOBs, CLOBs, and NCLOBs. To instantiate an ODP.NET LOB object that represents a temporary LOB, the OracleClob or the OracleBlob constructor can be used.

Temporary ODP.NET LOB objects can be used for the following purposes:

To initialize and populate a LOB column with empty or non-empty LOB data.
To pass a LOB type as an input parameter to a SQL statement, anonymous PL/SQL blocks, or stored procedure.
To act as the source or the destination of data transfer between two LOB objects as in the CopyTo operation.

Note:
Temporary LOBs are not transaction aware. Commits and rollbacks do not affect the data referenced by a temporary LOB.