OraBLOB and OraCLOB Objects

Description

The OraBlob and OraClob interfaces in OO4O provide methods for performing operations on large objects in the database of data types BLOB, CLOB, and NCLOB. In this help file BLOB, CLOB, and NCLOB datatypes are also referred to as LOB datatypes.

OO4O supports the creation of temporary BLOBs or CLOBs which can be manipulated and then bound into SQL statements or PL/SQL blocks, or copied into permanent LOBs.

Remarks

LOB data is accessed using Read and the CopyToFile methods.

LOB data is modified using Write, Append, Erase, Trim, Copy , CopyFromFile, and CopyFromBFile methods. Before modifying the content of a LOB column in a row, a row lock must be obtained. If the LOB column is a field of an OraDynaset, then the lock is obtained by invoking the Edit method.

None of the LOB operations are allowed on NULL LOBs. To avoid errors, this may be detected through the IsNull property. To perform write operations on a LOB that is NULL, the LOB column must first be initialized with an 'Empty' value. Also, to insert a new row having a LOB column, the LOB column must first be initialized with an 'Empty' value. To initialize with an 'Empty' value, set the Value property of the OraField/OraParameter object to the keyword 'Empty' and commit the change to the database. Also, the newly updated Empty Lob must be reselected from the database before it can be used. This is done automatically in the case of OraDynaset: If a LOB field in an OraDynaset is set to 'Empty' and the Update method called, OO4O will automatically re-select the Empty LOB into the dynaset making it available for use in subsequent write operations.

There are two modes of operation for Read/Write operations for LOBs.

Multiple piece read/write operations

In this mode, the total amount of data to be read/written is more than the size of the buffer for an individual Read/Write operation. Rather than make a complete round-trip for each operation, the pieces are streamed. To begin the multiple piece operation, the PollingAmount property is first set to the total amount of data to be read/written. Also, the Offset property is set at this time to specify the initial offset for the first piece Read/Write operation. The offset is automatically incremented after the first read/write and may not again be changed until the multiple piece operation has completed. The Status property must be checked for the success of each piecewise operation and the operation must continue until all pieces are read or written (it may not be aborted). To start another multiple piece wise Read/Write operation on the same LOB, PollingAmount has to be re-set to the desired amount. See Example: Multiple piece-wise Read of a LOB.

2. Single piece read/write operation

In this mode , the reading and writing of data occurs in one operation. This mode is enabled when PollingAmount property is set to 0. See Example: Single piece Read of a LOB.

The Offset property in both modes of operation is 1-based.

By design, LOBs cannot span transactions started by SELECT .. FOR UPDATE, INSERT, and UPDATE statements. Selecting or modifying LOB values using these SQL statements makes LOBs invalid outside the current transaction. In Oracle Objects for OLE, transactions can be started and end in following ways.

Dynaset Edit/Update method

The Edit method executes SELECT FOR UPDATE to lock the row and start the transaction. The Update method ends the transaction. If the LOB column value is modifed between the Edit/Update pair, OO4O reselect the value of LOB column after the Update call. This is transparent to the user. Note that OO4O does not reselect the LOB value if LOB is attribute of Oracle objects instance or element of Oracle collection. Also if the transaction is started by OraSession/OraDatabase or OraServer object and LOB data is modified between Edit and Update method, OO4O does not re-selects the LOB value from the database. So LOBs are invalid after committing transactions initiated by OraSession/OraDatabase or OraServer objects. See Example: Dynasets Containing LOBs and Transactions.

2. Executing INSERT/UPDATE statement through ExecuteSQL or CreateSQL method.

An INSERT/UPDATE statement starts the transaction and the transaction is implicitly ended by Oracle objects for OLE (auto-commit). If a statement has a LOB output bind parameter, as in the case of RETURNING .. INTO clause, then it will become invalid after the ExecuteSQL or CreateSQL method. In order to avoid this, user must execute these statement between the BeginTrans/CommitTrans pair of OraSession, OraServer or OraDatabase objects. See Example: INSERT/UPDATE with LOBs and Transactions.

For more information about LOB operations and about LOB performance issues, see Using Large Objects (LOBs).

For a detailed description of Oracle LOBs, see the Oracle Database Application Developer's Guide - Large Objects (LOBs).