5
Advanced Design Considerations

This chapter describes design considerations for more advanced application development issues.

This chapter contains these topics:

• LOB Buffering Subsystem
• Opening Persistent LOBs with the OPEN and CLOSE Interfaces
• Read Consistent Locators
• LOB Locators and Transaction Boundaries
• LOBs in the Object Cache
• Terabyte-Size LOB Support
• Interfaces Not Supporting LOBs Greater Than 4 Gigabytes

LOB Buffering Subsystem

The database provides a LOB buffering subsystem (LBS) for advanced OCI-based applications such as Data Cartridges, Web servers, and other client-based applications that need to buffer the contents of one or more LOBs in the client address space. The client-side memory requirement for the buffering subsystem during its maximum usage is 512KBytes. It is also the maximum amount that you can specify for a single read or write operation on a LOB that has been enabled for buffered access.

Advantages of LOB Buffering

The advantages of buffering, especially for client applications that perform a series of small reads and writes (often repeatedly) to specific regions of the LOB, are:

• Buffering enables deferred writes to the server. You can buffer up several writes in the LOB buffer in the client address space and eventually flush the buffer to the server. This reduces the number of network round-trips from your client application to the server, and hence, makes for better overall performance for LOB updates.
• Buffering reduces the overall number of LOB updates on the server, thereby reducing the number of LOB versions and amount of logging. This results in better overall LOB performance and disk space usage.

Guidelines for Using LOB Buffering

The following caveats apply to buffered LOB operations:

• Explicitly flush LOB buffer contents. The LOB buffering subsystem is not a cache. The contents of a LOB buffer are not always the same as the LOB value in the server. Unless you explicitly flush the contents of a LOB buffer, you will not see the results of your buffered writes reflected in the actual LOB on the server.
• Error recovery for buffered LOB operations is your responsibility. Owing to the deferred nature of the actual LOB update, error reporting for a particular buffered read or write operation is deferred until the next access to the server based LOB.
• LOB Buffering is Single User, Single Threaded. Transactions involving buffered LOB operations cannot migrate across user sessions -- the LBS is a single user, single threaded system.
• Maintain logical savepoints to rollback to. Oracle does not guarantee transactional support for buffered LOB operations. To ensure transactional semantics for buffered LOB updates, you must maintain logical savepoints in your application to rollback all the changes made to the buffered LOB in the event of an error. You should always wrap your buffered LOB updates within a logical savepoint (see "OCI Example of LOB Buffering").
• Ensure LOB is not updated by another bypassing transaction. In any given transaction, once you have begun updating a LOB using buffered writes, it is your responsibility to ensure that the same LOB is not updated through any other operation within the scope of the same transaction that bypasses the buffering subsystem.

  You could potentially do this by using an SQL statement to update the server-based LOB. Oracle cannot distinguish, and hence prevent, such an operation. This will seriously affect the correctness and integrity of your application.

• Updating buffer-enabled LOB locators. Buffered operations on a LOB are done through its locator, just as in the conventional case. A locator that is enabled for buffering will provide a consistent read version of the LOB, until you perform a write operation on the LOB through that locator. See also, "Read Consistent Locators".

  Once the locator becomes an updated locator by virtue of its being used for a buffered write, it will always provide access to the most up-to-date version of the LOB as seen through the buffering subsystem. Buffering also imposes an additional significance to this updated locator -- all further buffered writes to the LOB can be done only through this updated locator. Oracle will return an error if you attempt to write to the LOB through other locators enabled for buffering. See also, "Updating LOBs Through Updated Locators".

• Passing a buffer-enabled LOB locator an IN OUT or OUT parameter. You can pass an updated locator that was enabled for buffering as an IN parameter to a PL/SQL procedure. However, passing an IN OUT or an OUT parameter will produce an error, as will an attempt to return an updated locator.
• You cannot assign an updated locator that was enabled for buffering to another locator. There are a number of different ways that assignment of locators may occur -- through OCILobAssign(), through assignment of PL/SQL variables, through OCIObjectCopy() where the object contains the LOB attribute, and so on. Assigning a consistent read locator that was enabled for buffering to a locator that did not have buffering enabled, turns buffering on for the target locator. By the same token, assigning a locator that was not enabled for buffering to a locator that did have buffering enabled, turns buffering off for the target locator.

  Similarly, if you SELECT into a locator for which buffering was originally enabled, then the locator becomes overwritten with the new locator value, thereby turning buffering off.

• When two or more locators point to the same LOB do not enable both for buffering. If two or more different locators point to the same LOB, then it is your responsibility to make sure that you do not enable both the locators for buffering. Otherwise Oracle does not guarantee the contents of the LOB.
• Buffer-enable LOBs do not support appends that create zero-byte fillers or spaces. Appending to the LOB value using buffered write(s) is allowed, but only if the starting offset of these write(s) is exactly one byte (or character) past the end of the BLOB (or CLOB/NCLOB). In other words, the buffering subsystem does not support appends that involve creation of zero-byte fillers or spaces in the server based LOB.
• For CLOBs, Oracle requires the client side character set form for the locator bind variable be the same as that of the LOB in the server. This is usually the case in most OCI LOB programs. The exception is when the locator is SELECTed from a remote database, which may have a different character set form from the database which is currently being accessed by the OCI program. In such a case, an error is returned. If there is no character set form input by the user, then Oracle assumes it is SQLCS_IMPLICIT.

LOB Buffering Subsystem Usage

LOB Buffer Physical Structure

Each user session has the following structure:

• Fixed page pool of 16 pages, shared by all LOBs accessed in buffering mode from that session.
• Each page has a fixed size of up to 32K bytes (not characters) where page size = n x CHUNKSIZE ~= 32K.

A LOB buffer consists of one or more of these pages, up to a maximum of 16 in each session. The maximum amount that you ought to specify for any given buffered read or write operation is 512K bytes, remembering that under different circumstances the maximum amount you may read/write could be smaller.

LOB Buffering Subsystem Usage Scenario

Consider that a LOB is divided into fixed-size, logical regions. Each page is mapped to one of these fixed size regions, and is in essence, their in-memory copy. Depending on the input offset and amount specified for a read or write operation, the database allocates one or more of the free pages in the page pool to the LOB buffer. A free page is one that has not been read or written by a buffered read or write operation.

For example, assuming a page size of 32KBytes:

• For an input offset of 1000 and a specified read/write amount of 30000, Oracle reads the first 32K byte region of the LOB into a page in the LOB buffer.
• For an input offset of 33000 and a read/write amount of 30000, the second 32K region of the LOB is read into a page.
• For an input offset of 1000, and a read/write amount of 35000, the LOB buffer will contain two pages -- the first mapped to the region 1 -- 32K, and the second to the region 32K+1 -- 64K of the LOB.

This mapping between a page and the LOB region is temporary until Oracle maps another region to the page. When you attempt to access a region of the LOB that is not already available in full in the LOB buffer, Oracle allocates any available free page(s) from the page pool to the LOB buffer. If there are no free pages available in the page pool, then Oracle reallocates the pages as follows. It ages out the least recently used page among the unmodified pages in the LOB buffer and reallocates it for the current operation.

If no such page is available in the LOB buffer, then it ages out the least recently used page among the unmodified pages of other buffered LOBs in the same session. Again, if no such page is available, then it implies that all the pages in the page pool are modified, and either the currently accessed LOB, or one of the other LOBs, need to be flushed. Oracle notifies this condition to the user as an error. Oracle never flushes and reallocates a modified page implicitly. You can either flush them explicitly, or discard them by disabling buffering on the LOB.

To illustrate the preceding discussion, consider two LOBs being accessed in buffered mode -- L1 and L2, each with buffers of size 8 pages. Assume that 6 of the 8 pages in the L1 buffer are dirty, with the remaining 2 containing unmodified data read in from the server. Assume similar conditions in the L2 buffer. Now, for the next buffered operation on L1, Oracle will reallocate the least recently used page from the two unmodified pages in the L1 buffer. Once all the 8 pages in the L1 buffer are used up for LOB writes, Oracle can service two more operations on L1 by allocating the two unmodified pages from the L2 buffer using the least recently used policy. But for any further buffered operations on L1 or L2, Oracle returns an error.

If all the buffers are dirty and you attempt another read from or write to a buffered LOB, then you will receive the following error:

 Error 22280: no more buffers available for operation

There are two possible causes:

1. All buffers in the buffer pool have been used up by previous operations.

   In this case, flush the LOBs through the locator that is being used to update the LOB.

2. You are trying to flush a LOB without any previous buffered update operations.

   In this case, write to the LOB through a locator enabled for buffering before attempting to flush buffers.

Flushing the LOB Buffer

The term flush refers to a set of processes. Writing data to the LOB in the buffer through the locator transforms the locator into an updated locator. Once you have updated the LOB data in the buffer through the updated locator, a flush call will

• Write the dirty pages in the LOB buffer to the server-based LOB, thereby updating the LOB value,
• Reset the updated locator to be a read consistent locator, and
• Free the flushed buffers or turn the status of the buffer pages back from dirty to unmodified.

After the flush, the locator becomes a read consistent locator and can be assigned to another locator (L2 := L1).

For instance, suppose you have two locators, L1 and L2. Let us say that they are both read consistent locators and consistent with the state of the LOB data in the server. If you then update the LOB by writing to the buffer, L1 becomes an updated locator. L1 and L2 now refer to different versions of the LOB value. If you want to update the LOB in the server, then you must use L1 to retain the read consistent state captured in L2. The flush operation writes a new snapshot environment into the locator used for the flush. The important point to remember is that you must use the updated locator (L1), when you flush the LOB buffer. Trying to flush a read consistent locator will generate an error.

The technique you use to flush the LOB buffer determines whether data in the buffer is cleared and has performance implications as follows:

• In the default mode, data is retained in the pages that were modified when the flush operation occurs. In this case, when you read or write to the same range of bytes, no round-trip to the server is necessary. Note that flushing the buffer, in this context, does not clear the data in the buffer. It also does not return the memory occupied by the flushed buffer to the client address space.

  Note: Unmodified pages may now be aged out if necessary.

• In the second case, you set the flag parameter in OCILobFlushBuffer() to OCI_LOB_BUFFER_FREE to free the buffer pages, and so return the memory to the client address space. Flushing the buffer using this technique updates the LOB value on the server, returns a read consistent locator, and frees the buffer pages.

Flushing the Updated LOB

It is very important to note that you must flush a LOB that has been updated through the LOB buffering subsystem in the following situations:

• Before committing the transaction
• Before migrating from the current transaction to another
• Before disabling buffering operations on a LOB
• Before returning from an external callout execution into the calling function, procedure, or method in PL/SQL

  Note that when the external callout is called from a PL/SQL block and the locator is passed as a parameter, all buffering operations, including the enable call, should be made within the callout itself. In other words, adhere to the following sequence:

  • Call the external callout
  • Enable the locator for buffering
  • Read or write using the locator
  • Flush the LOB
  • Disable the locator for buffering
  • Return to the calling function, procedure, or method in PL/SQL

  Remember that the database never implicitly flushes the LOB buffer.

Using Buffer-Enabled Locators

Note that there are several cases in which you can use buffer-enabled locators and others in which you cannot.

• When it is OK to Use Buffer-Enabled Locators:
  • OCI -- A locator that is enabled for buffering can only be used with the following OCI APIs:

    OCILobRead2(), OCILobWrite2(), OCILobAssign(), OCILobIsEqual(), OCILobLocatorIsInit(), OCILobCharSetId(), OCILobCharSetForm()

• When it is Not OK to Use Buffer-Enabled Locators: The following OCI APIs will return errors if used with a locator enabled for buffering:
  • OCI -- OCILobCopy2(), OCILobAppend(), OCILobErase2(), OCILobGetLength2(), OCILobTrim2(), OCILobWriteAppend2()

    These APIs will also return errors when used with a locator which has not been enabled for buffering, but the LOB that the locator represents is already being accessed in buffered mode through some other locator.

  • PL/SQL (DBMS_LOB) -- An error is returned from DBMS_LOB APIs if the input lob locator has buffering enabled.
  • As in the case of all other locators, buffer-enabled locators cannot span transactions.

Saving Locator State to Avoid a Reselect

Suppose you want to save the current state of the LOB before further writing to the LOB buffer. In performing updates while using LOB buffering, writing to an existing buffer does not make a round-trip to the server, and so does not refresh the snapshot environment in the locator. This would not be the case if you were updating the LOB directly without using LOB buffering. In that case, every update would involve a round-trip to the server, and so would refresh the snapshot in the locator.

Therefore to save the state of a LOB that has been written through the LOB buffer, follow these steps:

1. Flush the LOB, thereby updating the LOB and the snapshot environment in the locator (L1). At this point, the state of the locator (L1) and the LOB are the same.
2. Assign the locator (L1) used for flushing and updating to another locator (L2). At this point, the states of the two locators (L1 and L2), as well as the LOB are all identical.

L2 now becomes a read consistent locator with which you are able to access the changes made through L1 up until the time of the flush, but not after. This assignment avoids incurring a round-trip to the server to reselect the locator into L2.

OCI Example of LOB Buffering

The following OCI example is based on the PM schema included with Oracle Database Sample Schemas.

OCI_BLOB_buffering_program()
{
   int            amount;
   int            offset;
   OCILobLocator  lbs_loc1, lbs_loc2, lbs_loc3;
   void          *buffer;
   int            bufl;

   -- Standard OCI initialization operations - logging on to
   -- server, creating and initializing bind variables...

   init_OCI();

   -- Establish a savepoint before start of LOB buffering subsystem
operations
   exec_statement("savepoint lbs_savepoint");

   -- Initialize bind variable to BLOB columns from buffered
   -- access:
   exec_statement("select ad_composite into lbs_loc1 from Print_media
       where ad_id = 12001");
   exec_statement("select ad_composite into lbs_loc2 from Print_media
       where ad_id = 12001 for update");
   exec_statement("select ad_composite into lbs_loc2 from Print_media
       where ad_id = 12001 for update");

   -- Enable locators for buffered mode access to LOB:
   OCILobEnableBuffering(lbs_loc1);
   OCILobEnableBuffering(lbs_loc2);
   OCILobEnableBuffering(lbs_loc3);

   -- Read 4K bytes through lbs_loc1 starting from offset 1:
   amount = 4096; offset = 1; bufl = 4096;
   OCILobRead2(.., lbs_loc1, offset, &amount, buffer, bufl,
      ..);
      if (exception)
          goto exception_handler;
          -- This will read the first 32K bytes of the LOB from
          -- the server into a page (call it page_A) in the LOB
          -- client-side buffer.
          -- lbs_loc1 is a read consistent locator.

          -- Write 4K of the LOB throgh lbs_loc2 starting from
          -- offset 1:
          amount = 4096; offset = 1; bufl = 4096;
          buffer = populate_buffer(4096);
          OCILobWrite2(.., lbs_loc2, offset, amount, buffer,
              bufl, ..);

      if (exception)
          goto exception_handler;
          -- This will read the first 32K bytes of the LOB from
          -- the server into a new page (call it page_B) in the
          -- LOB buffer, and modify the contents of this page
          -- with input buffer contents.
          -- lbs_loc2 is an updated locator.

          -- Read 20K bytes through lbs_loc1 starting from
          -- offset 10K
          amount = 20480; offset = 10240;
          OCILobRead2(.., lbs_loc1, offset, &amount, buffer,
              bufl, ..);

      if (exception)
        goto exception_handler;
          -- Read directly from page_A into the user buffer.
          -- There is no round-trip to the server because the
          -- data is already in the client-side buffer.

          -- Write 20K bytes through lbs_loc2 starting from offset
          -- 10K
          amount = 20480; offset = 10240; bufl = 20480;
          buffer = populate_buffer(20480);
          OCILobWrite2(.., lbs_loc2, offset, amount, buffer,
               bufl, ..);

      if (exception)
          goto exception_handler;
          -- The contents of the user buffer will now be written
          -- into page_B without involving a round-trip to the
          -- server.  This avoids making a new LOB version on the
          -- server and writing redo to the log.

          -- The following write through lbs_loc3 will also
          -- result in an error:
          amount = 20000; offset = 1000; bufl = 20000;
          buffer = populate_buffer(20000);
          OCILobWrite2(.., lbs_loc3, offset, amount, buffer,
               bufl, ..);

      if (exception)
          goto exception_handler;
          -- No two locators can be used to update a buffered LOB
          -- through the buffering subsystem

      -- The following update through lbs_loc3 will also
      -- result in an error
      OCILobFileCopy(.., lbs_loc3, lbs_loc2, ..);

      if (exception)
          goto exception_handler;
          -- Locators enabled for buffering cannot be used with
          -- operations like Append, Copy, Trim and so on
          -- When done, flush the LOB buffer to the server:
      OCILobFlushBuffer(.., lbs_loc2, OCI_LOB_BUFFER_NOFREE);

      if (exception)
         goto exception_handler;
         -- This flushes all the modified pages in the LOB buffer,
         -- and resets lbs_loc2 from updated to read consistent
         -- locator. The modified pages remain in the buffer
         -- without freeing memory.  These pages can be aged
         -- out if necessary.

      -- Disable locators for buffered mode access to LOB */
      OCILobDisableBuffering(lbs_loc1);
      OCILobDisableBuffering(lbs_loc2);
      OCILobDisableBuffering(lbs_loc3);

      if (exception)
         goto exception_handler;
         -- This disables the three locators for buffered access,
         -- and frees up the LOB buffer resources.
        exception_handler:
      handle_exception_reporting();
      exec_statement("rollback to savepoint lbs_savepoint");
}

Opening Persistent LOBs with the OPEN and CLOSE Interfaces

The OPEN and CLOSE interfaces enable you to explicitly open a persistent LOB instance. When you open a LOB instance with the OPEN interface, the instance remains open until you explicitly close the LOB using the CLOSE interface. The ISOPEN interface enables you to determine whether a persistent LOB is already open.

Note that the open state of a LOB is associated with the LOB instance, not the LOB locator. The locator does not save any information indicating whether the LOB instance that it points to is open.

Index Performance Benefits of Explicitly Opening a LOB

Explicitly opening a LOB instance can benefit performance of a persistent LOB in an indexed column.

If you do not explicitly open the LOB instance, then every modification to the LOB implicitly opens and closes the LOB instance. Any triggers on a domain index are fired each time the LOB is closed. Note that in this case, any domain indexes on the LOB are updated as soon as any modification to the LOB instance is made; the domain index is always valid and can be used at any time.

When you explicitly open a LOB instance, index triggers do not fire until you explicitly close the LOB. Using this technique can increase performance on index columns by eliminating unneeded indexing events until you explicitly close the LOB. Note that any index on the LOB column is not valid until you explicitly close the LOB.

Working with Explicitly Open LOB Instances

If you explicitly open a LOB instance, then you must close the LOB before you commit the transaction.

Committing a transaction on the open LOB instance will cause an error. When this error occurs, the LOB instance is closed implicitly, any modifications to the LOB instance are saved, and the transaction is committed, but any indexes on the LOB column are not updated. In this situation, you must rebuild your indexes on the LOB column.

If you subsequently rollback the transaction, then the LOB instance is rolled back to its previous state, but the LOB instance is no longer explicitly open.

You must close any LOB instance that you explicitly open:

• Between DML statements that start a transaction, including SELECT ... FOR UPDATE and COMMIT
• Within an autonomous transaction block
• Before the end of a session (when there is no transaction involved)
  If you do not explicitly close the LOB instance, then it is implicitly closed at the end of the session and no index triggers are fired.

Keep track of the open or closed state of LOBs that you explicitly open. The following will cause an error:

• Explicitly opening a LOB instance that is already explicitly open.
• Explicitly closing a LOB instance that is already explicitly closed.

This occurs whether you access the LOB instance using the same locator or different locators.

Read Consistent Locators

Oracle Database provides the same read consistency mechanisms for LOBs as for all other database reads and updates of scalar quantities. Refer to Oracle Database Concepts for general information about read consistency. Read consistency has some special applications to LOB locators that you must understand. These applications are described in the following sections.

A Selected Locator Becomes a Read Consistent Locator

A selected locator, regardless of the existence of the FOR UPDATE clause, becomes a read consistent locator, and remains a read consistent locator until the LOB value is updated through that locator. A read consistent locator contains the snapshot environment as of the point in time of the SELECT operation.

This has some complex implications. Suppose you have created a read consistent locator (L1) by way of a SELECT operation. In reading the value of the persistent LOB through L1, note the following:

• The LOB is read as of the point in time of the SELECT statement even if the SELECT statement includes a FOR UPDATE.
• If the LOB value is updated through a different locator (L2) in the same transaction, then L1 does not see the L2 updates.
• L1 will not see committed updates made to the LOB through another transaction.
• If the read consistent locator L1 is copied to another locator L2 (for example, by a PL/SQL assignment of two locator variables -- L2:= L1), then L2 becomes a read consistent locator along with L1 and any data read is read as of the point in time of the SELECT for L1.

You can use the existence of multiple locators to access different transformations of the LOB value. However, in doing so, you must keep track of the different values accessed by different locators.

Updating LOBs and Read-Consistency

Read consistent locators provide the same LOB value regardless of when the SELECT occurs.

The following example demonstrates the relationship between read-consistency and updating in a simple example. Using the PRINT_MEDIA table and PL/SQL, three CLOB instances are created as potential locators:

• clob_selected
• clob_update
• clob_copied

Observe these progressions in the code, from times t1 through t6:

• At the time of the first SELECT INTO (at t1), the value in ad_sourcetext is associated with the locator clob_selected.
• In the second operation (at t2), the value in ad_sourcetext is associated with the locator clob_updated. Because there has been no change in the value of ad_sourcetext between t1 and t2, both clob_selected and clob_updated are read consistent locators that effectively have the same value even though they reflect snapshots taken at different moments in time.
• The third operation (at t3) copies the value in clob_selected to clob_copied. At this juncture, all three locators see the same value. The example demonstrates this with a series of DBMS_LOB.READ() calls.
• At time t4, the program uses DBMS_LOB.WRITE() to alter the value in clob_updated, and a DBMS_LOB.READ() reveals a new value.
• However, a DBMS_LOB.READ() of the value through clob_selected (at t5) reveals that it is a read consistent locator, continuing to refer to the same value as of the time of its SELECT.
• Likewise, a DBMS_LOB.READ() of the value through clob_copied (at t6) reveals that it is a read consistent locator, continuing to refer to the same value as clob_selected.

Example

INSERT INTO PRINT_MEDIA VALUES (2056, 20010, EMPTY_BLOB(), 
    'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL);

COMMIT;

DECLARE
  num_var           INTEGER;
  clob_selected     CLOB;
  read_amount       INTEGER;
  read_offset       INTEGER;
  buffer            VARCHAR2(20);

BEGIN

  -- At time t1:
  SELECT ad_sourcetext INTO clob_selected
  FROM Print_media
  WHERE ad_id = 20010;

  read_amount := 10;
  read_offset := 1;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer);
  dbms_output.put_line('clob_selected value: ' || buffer);
  -- Produces the output 'abcd'

  -- At time t2:
  UPDATE Print_media SET ad_sourcetext = empty_clob()
      WHERE ad_id = 20010;
  -- although the most current current LOB value is now empty,
  -- clob_selected still sees the LOB value as of the point
  -- in time of the SELECT

  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset,
     buffer);
  dbms_output.put_line('clob_selected value: ' || buffer);
  -- Produces the output 'abcd'

  -- At time t3:
  SELECT ad_sourcetext INTO clob_selected FROM Print_media WHERE
       ad_id = 20010;
  -- the SELECT allows clob_selected to see the most current
  -- LOB value

  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer);
  -- ERROR: ORA-01403: no data found
END;
/

Updating LOBs Through Updated Locators

When you update the value of the persistent LOB through the LOB locator (L1), L1 (that is, the locator itself) is updated to contain the current snapshot environment as of the point in time after the operation was completed on the LOB value through locator L1. L1 is then termed an updated locator. This operation enables you to see your own changes to the LOB value on the next read through the same locator, L1.

Any committed updates made by a different transaction are seen by L1 only if your transaction is a read-committed transaction and if you use L1 to update the LOB value after the other transaction committed.

Updating the value of the persistent LOB through any of the available methods, such as OCI LOB APIs or PL/SQL DBMS_LOB package, updates the LOB value and then reselects the locator that refers to the new LOB value.

Note that updating the LOB value through SQL is merely an UPDATE statement. It is up to you to do the reselect of the LOB locator or use the RETURNING clause in the UPDATE statement so that the locator can see the changes made by the UPDATE statement. Unless you reselect the LOB locator or use the RETURNING clause, you may think you are reading the latest value when this is not the case. For this reason you should avoid mixing SQL DML with OCI and DBMS_LOB piecewise operations.

Example of Updating a LOB Using SQL DML and DBMS_LOB

Using table PRINT_MEDIA in the following example, a CLOB locator is created as clob_selected. Note the following progressions in the example, from times t1 through t3:

• At the time of the first SELECT INTO (at t1), the value in ad_sourcetext is associated with the locator clob_selected.
• In the second operation (at t2), the value in ad_sourcetext is modified through the SQL UPDATE statement, without affecting the clob_selected locator. The locator still sees the value of the LOB as of the point in time of the original SELECT. In other words, the locator does not see the update made using the SQL UPDATE statement. This is illustrated by the subsequent DBMS_LOB.READ() call.
• The third operation (at t3) re-selects the LOB value into the locator clob_selected. The locator is thus updated with the latest snapshot environment which allows the locator to see the change made by the previous SQL UPDATE statement. Therefore, in the next DBMS_LOB.READ(), an error is returned because the LOB value is empty, that is, it does not contain any data.

Example

INSERT INTO PRINT_MEDIA VALUES (2056, 20020, EMPTY_BLOB(), 
    'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL);

COMMIT;

DECLARE
  num_var           INTEGER;
  clob_selected     CLOB;
  clob_updated      CLOB;
  clob_copied       CLOB;
  read_amount       INTEGER;
  read_offset       INTEGER;
  write_amount      INTEGER;
  write_offset      INTEGER;
  buffer            VARCHAR2(20);

BEGIN
  -- At time t1:
  SELECT ad_sourcetext INTO clob_selected
     FROM Print_media
     WHERE ad_id = 20020;

  -- At time t2:
  SELECT ad_sourcetext INTO clob_updated
     FROM Print_media
     WHERE ad_id = 20020
     FOR UPDATE;

  -- At time t3:
  clob_copied := clob_selected;
  -- After the assignment, both the clob_copied and the
  -- clob_selected have the same snapshot as of the point in time
  -- of the SELECT into clob_selected

  -- Reading from the clob_selected and the clob_copied will
  -- return the same LOB value. clob_updated also sees the same
  -- LOB value as of its select:
  read_amount := 10;
  read_offset := 1;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer);
  dbms_output.put_line('clob_selected value: ' || buffer);
  -- Produces the output 'abcd'

  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'

  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcd'

  -- At time t4:
  write_amount := 3;
  write_offset := 5;
  buffer := 'efg';
  dbms_lob.write(clob_updated, write_amount, write_offset,
       buffer);

  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcdefg'

  -- At time t5:
  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer);
  dbms_output.put_line('clob_selected value: ' || buffer);
  -- Produces the output 'abcd'

  -- At time t6:
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'
END;
/

Example of Using One Locator to Update the Same LOB Value

In the following example, using table PRINT_MEDIA, two CLOBs are created as potential locators:

• clob_updated
• clob_copied

Note these progressions in the example at times t1 through t5:

• At the time of the first SELECT INTO (at t1), the value in ad_sourcetext is associated with the locator clob_updated.
• The second operation (at time t2) copies the value in clob_updated to clob_copied. At this time, both locators see the same value. The example demonstrates this with a series of DBMS_LOB.READ() calls.
• At time t3, the program uses DBMS_LOB.WRITE() to alter the value in clob_updated, and a DBMS_LOB.READ() reveals a new value.
• However, a DBMS_LOB.READ() of the value through clob_copied (at time t4) reveals that it still sees the value of the LOB as of the point in time of the assignment from clob_updated (at t2).
• It is not until clob_updated is assigned to clob_copied (t5) that clob_copied sees the modification made by clob_updated.

Example

INSERT INTO PRINT_MEDIA VALUES (2056, 20030, EMPTY_BLOB(), 
    'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL);

COMMIT;

DECLARE
  num_var          INTEGER;
  clob_updated     CLOB;
  clob_copied      CLOB;
  read_amount      INTEGER;
  read_offset      INTEGER;
  write_amount     INTEGER;
  write_offset     INTEGER;
  buffer           VARCHAR2(20);
BEGIN

-- At time t1:
  SELECT ad_sourcetext INTO clob_updated FROM PRINT_MEDIA
      WHERE ad_id = 20030
      FOR UPDATE;

  -- At time t2:
  clob_copied := clob_updated;
  -- after the assign, clob_copied and clob_updated see the same
  -- LOB value

  read_amount := 10;
  read_offset := 1;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcd'

  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'

  -- At time t3:
  write_amount := 3;
  write_offset := 5;
  buffer := 'efg';
  dbms_lob.write(clob_updated, write_amount, write_offset,
        buffer);

  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcdefg'


  -- At time t4:
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'


  -- At time t5:
  clob_copied := clob_updated;

  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcdefg'
END;
/

Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable

When a LOB locator is used as the source to update another persistent LOB (as in a SQL INSERT or UPDATE statement, the DBMS_LOB.COPY() routine, and so on), the snapshot environment in the source LOB locator determines the LOB value that is used as the source. If the source locator (for example L1) is a read consistent locator, then the LOB value as of the point in time of the SELECT of L1 is used. If the source locator (for example L2) is an updated locator, then the LOB value associated with the L2 snapshot environment at the time of the operation is used.

In the following example, using the table PRINT_MEDIA, three CLOBs are created as potential locators:

• clob_selected
• clob_updated
• clob_copied

Note these progressions in the example at times t1 through t5:

• At the time of the first SELECT INTO (at t1), the value in ad_sourcetext is associated with the locator clob_updated.
• The second operation (at t2) copies the value in clob_updated to clob_copied. At this juncture, both locators see the same value.
• Then (at t3), the program uses DBMS_LOB.WRITE() to alter the value in clob_updated, and a DBMS_LOB.READ() reveals a new value.
• However, a DBMS_LOB.READ of the value through clob_copied (at t4) reveals that clob_copied does not see the change made by clob_updated.
• Therefore (at t5), when clob_copied is used as the source for the value of the INSERT statement, the value associated with clob_copied (for example, without the new changes made by clob_updated) is inserted. This is demonstrated by the subsequent DBMS_LOB.READ() of the value just inserted.

Example

INSERT INTO PRINT_MEDIA VALUES (2056, 20020, EMPTY_BLOB(), 
    'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL);

COMMIT;

DECLARE
  num_var           INTEGER;
  clob_selected     CLOB;
  clob_updated      CLOB;
  clob_copied       CLOB;
  read_amount       INTEGER;
  read_offset       INTEGER;
  write_amount      INTEGER;
  write_offset      INTEGER;
  buffer            VARCHAR2(20);
BEGIN

  -- At time t1:
  SELECT ad_sourcetext INTO clob_updated FROM PRINT_MEDIA
      WHERE ad_id = 20020
      FOR UPDATE;

  read_amount := 10;
  read_offset := 1;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcd'

  -- At time t2:
  clob_copied := clob_updated;


  -- At time t3:
  write_amount := 3;
  write_offset := 5;
  buffer := 'efg';
  dbms_lob.write(clob_updated, write_amount, write_offset,
       buffer);

  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_updated value: ' || buffer);
  -- Produces the output 'abcdefg'
  -- note that clob_copied does not see the write made before
  -- clob_updated


  -- At time t4:
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
  dbms_output.put_line('clob_copied value: ' || buffer);
  -- Produces the output 'abcd'

  -- At time t5:
  -- the insert uses clob_copied view of the LOB value which does
  -- not include clob_updated changes
  INSERT INTO PRINT_MEDIA VALUES (2056, 20022, EMPTY_BLOB(), 
    clob_copied, EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL)
    RETURNING ad_sourcetext INTO clob_selected;

  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer);
  dbms_output.put_line('clob_selected value: ' || buffer);
  -- Produces the output 'abcd'
END;
/

LOB Locators and Transaction Boundaries

This section discusses the use of LOB locators in transactions and transaction IDs. A basic description of LOB locators and their operations is given in "LOB Locators and BFILE Locators".

Note the following regarding LOB locators and transactions:

• Locators contain transaction IDs when:

  You Begin the Transaction, Then Select Locator.
  If you begin a transaction and subsequently select a locator, then the locator contains the transaction ID. Note that you can implicitly be in a transaction without explicitly beginning one. For example, SELECT... FOR UPDATE implicitly begins a transaction. In such a case, the locator will contain a transaction ID.

• Locators Do Not Contain Transaction IDs When...
  • You are Outside the Transaction, Then Select Locator. By contrast, if you select a locator outside of a transaction, then the locator does not contain a transaction ID.
  • Locators Do Not Contain Transaction IDs When Selected Prior to DML Statement Execution. A transaction ID will not be assigned until the first DML statement executes. Therefore, locators that are selected prior to such a DML statement will not contain a transaction ID.

Reading and Writing to a LOB Using Locators

You can always read the LOB data using the locator irrespective of whether the locator contains a transaction ID.

• Cannot Write Using Locator: If the locator contains a transaction ID, then you cannot write to the LOB outside of that particular transaction.
• Can Write Using Locator: If the locator does not contain a transaction ID, then you can write to the LOB after beginning a transaction either explicitly or implicitly.
• Cannot Read or Write Using Locator With Serializable Transactions: If the locator contains a transaction ID of an older transaction, and the current transaction is serializable, then you cannot read or write using that locator.
• Can Read, Not Write Using Locator With Non-Serializable Transactions: If the transaction is non-serializable, then you can read, but not write outside of that transaction.

The following examples show the relationship between locators and non-serializable transactions

Selecting the Locator Outside of the Transaction Boundary

The following scenarios describe techniques for using locators in non-serializable transactions when the locator is selected outside of a transaction.

Scenario:

Scenario:

Selecting the Locator Within a Transaction Boundary

The following scenarios describe techniques for using locators in non-serializable transactions when the locator is selected within a transaction.

Scenario:

Scenario:

LOB Locators Cannot Span Transactions

Modifying a persistent LOB value through the LOB locator using DBMS_LOB, OCI, or SQL INSERT or UPDATE statements changes the locator from a read consistent locator to an updated locator. The INSERT or UPDATE statement automatically starts a transaction and locks the row. Once this has occurred, the locator cannot be used outside the current transaction to modify the LOB value. In other words, LOB locators that are used to write data cannot span transactions. However, the locator can be used to read the LOB value unless you are in a serializable transaction.

In the following example, a CLOB locator is created: clob_updated

• At the time of the first SELECT INTO (at t1), the value in ad_sourcetext is associated with the locator clob_updated.
• The second operation (at t2), uses the DBMS_LOB.WRITE() function to alter the value in clob_updated, and a DBMS_LOB.READ() reveals a new value.
• The commit statement (at t3) ends the current transaction.
• Therefore (at t4), the subsequent DBMS_LOB.WRITE() operation fails because the clob_updated locator refers to a different (already committed) transaction. This is noted by the error returned. You must re-select the LOB locator before using it in further DBMS_LOB (and OCI) modify operations.

Example of Locator Not Spanning a Transaction

INSERT INTO PRINT_MEDIA VALUES (2056, 20010, EMPTY_BLOB(), 
    'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL);

COMMIT;

DECLARE
  num_var          INTEGER;
  clob_updated     CLOB;
  read_amount      INTEGER;
  read_offset      INTEGER;
  write_amount     INTEGER;
  write_offset     INTEGER;
  buffer           VARCHAR2(20);

BEGIN
          -- At time t1:
     SELECT      ad_sourcetext
     INTO        clob_updated
     FROM        PRINT_MEDIA
     WHERE       ad_id = 20010
     FOR UPDATE;
     read_amount := 10;
     read_offset := 1;
     dbms_lob.read(clob_updated, read_amount, read_offset,
          buffer);
     dbms_output.put_line('clob_updated value: ' || buffer);
     -- This produces the output 'abcd'

     -- At time t2:
     write_amount := 3;
     write_offset := 5;
     buffer := 'efg';
     dbms_lob.write(clob_updated, write_amount, write_offset,
          buffer);
     read_amount := 10;
     dbms_lob.read(clob_updated, read_amount, read_offset,
         buffer);
     dbms_output.put_line('clob_updated value: ' || buffer);
     -- This produces the output 'abcdefg'

    -- At time t3:
    COMMIT;

     -- At time t4:
    dbms_lob.write(clob_updated , write_amount, write_offset,
         buffer);
    -- ERROR: ORA-22990: LOB locators cannot span transactions
END;
/

LOBs in the Object Cache

Consider these object cache issues for internal and external LOB attributes:

• Persistent LOB attributes: Creating an object in object cache, sets the LOB attribute to empty.
  
  When you create an object in the object cache that contains a persistent LOB attribute, the LOB attribute is implicitly set to empty. You may not use this empty LOB locator to write data to the LOB. You must first flush the object, thereby inserting a row into the table and creating an empty LOB -- that is, a LOB with 0 length. Once the object is refreshed in the object cache (use OCI_PIN_LATEST), the real LOB locator is read into the attribute, and you can then call the OCI LOB API to write data to the LOB.
• External LOB (BFILE) attributes: Creating an object in object cache, sets the BFILE attribute to NULL.
  
  When creating an object with an external LOB (BFILE) attribute, the BFILE is set to NULL. It must be updated with a valid directory object name and filename before reading from the BFILE.

When you copy one object to another in the object cache with a LOB locator attribute, only the LOB locator is copied. This means that the LOB attribute in these two different objects contain exactly the same locator which refers to one and the same LOB value. Only when the target object is flushed is a separate, physical copy of the LOB value made, which is distinct from the source LOB value.

Therefore, in cases where you want to modify the LOB that was the target of the copy, you must flush the target object, refresh the target object, and then write to the LOB through the locator attribute.

Terabyte-Size LOB Support

Terabyte-size LOBs--LOBs up to a maximum size of 8 to 128 terabytes depending on your database block size--are supported by the following APIs:

• Java using JDBC (Java Database Connectivity)
• PL/SQL using the DBMS_LOB Package
• C using OCI (Oracle Call Interface)

Note that other LOB APIs support LOBs up to a maximum size of 4 gigabytes. See "Interfaces Not Supporting LOBs Greater Than 4 Gigabytes".

The sections that follow, discuss APIs provided to support terabyte-size LOBs in each of these environments.

Maximum Storage Limit for Terabyte-Size LOBs

In supported environments, you can create and manipulate LOBs that are up to the maximum storage size limit for your database configuration. The maximum allowable storage limit for your configuration depends on the database block size setting, the value of the DB_BLOCK_SIZE initialization parameter, and is calculated as (4 gigabytes - 1) times the value of the DB_BLOCK_SIZE parameter. With the current allowable range for the database block size from 2k to 32k, the storage limit ranges from 8 terabytes to 128 terabytes.

This storage limit applies to all LOB types in environments that support terabyte-size LOBs; however, note that CLOB and NCLOB types are sized in characters while the BLOB type is sized in bytes.

Using Terabyte-Size LOBs with JDBC

You can use terabyte-size LOBs with all LOB APIs included in Oracle JDBC classes.

Using Terabyte-Size LOBs with the DBMS_LOB Package

You can use terabyte-size LOBs with all APIs in the DBMS_LOB PL/SQL package. The DBMS_LOB.GET_STORAGE_LIMIT function returns the storage limit for your database configuration.

Using Terabyte-Size LOBs with OCI

The Oracle Call Interface API provides a set of functions specifically for operations on terabyte-size LOBs.

Interfaces Not Supporting LOBs Greater Than 4 Gigabytes

You can create and use LOB instances up to 4 gigabytes in size--"gigabyte LOBs"-- in the following programmatic environments:

• COBOL using the Pro*COBOL precompiler
• C/C++ using the Pro*C/C++ precompiler
• Visual Basic using OO4O (Oracle Objects for OLE)

Guidelines for Creating Gigabyte LOBs

To create gigabyte LOBs in supported environments, use the following guidelines to make use of all available space in the tablespace for LOB storage:

• Single Datafile Size Restrictions: There are restrictions on the size of a single datafile for each operating system. For example, Solaris 2.5 only allows operating system files of up to 2 gigabytes. Hence, add more datafiles to the tablespace when the LOB grows larger than the maximum allowed file size of the operating system on which your Oracle Database runs.
• Set PCT INCREASE Parameter to Zero: PCTINCREASE parameter in the LOB storage clause specifies the percent growth of the new extent size. When a LOB is being filled up piece by piece in a tablespace, numerous new extents get created in the process. If the extent sizes keep increasing by the default value of 50 percent every time, then extents will become unmanageable and eventually will waste unnecessary space in the tablespace. Therefore, the PCTINCREASE parameter should be set to zero or a small value.
• Set MAXEXTENTS to a Suitable Value or UNLIMITED: The MAXEXTENTS parameter limits the number of extents allowed for the LOB column. A large number of extents are created incrementally as the LOB size grows. Therefore, the parameter should be set to a value that is large enough to hold all the LOBs for the column. Alternatively, you could set it to UNLIMITED.
• Use a Large Extent Size: For every new extent created, Oracle generates undo information for the header and other meta data for the extent. If the number of extents is large, then the rollback segment can be saturated. To get around this, choose a large extent size, say 100 megabytes, to reduce the frequency of extent creation, or commit the transaction more often to reuse the space in the rollback segment.

Creating a Tablespace and Table to Store Gigabyte LOBs

The following example illustrates how to create a tablespace and table to store gigabyte LOBs.

CREATE TABLESPACE lobtbs1 datafile '/your/own/data/directory/lobtbs_1.dat'
size 2000M reuse online nologging default storage (maxextents unlimited);
ALTER TABLESPACE lobtbs1 add datafile
'/your/own/data/directory/lobtbs_2.dat'  size 2000M reuse;

CREATE TABLE PRINT_MEDIA_BACKUP
  (PRODUCT_ID NUMBER(6), 
   AD_ID NUMBER(6), 
   AD_COMPOSITE BLOB, 
   AD_SOURCETEXT CLOB, 
   AD_FINALTEXT CLOB, 
   AD_FLTEXTN NCLOB, 
   AD_TEXTDOCS_NTAB TEXTDOC_TAB, 
   AD_PHOTO BLOB, 
   AD_GRAPHIC BLOB, 
   AD_HEADER ADHEADER_TYP)
   NESTED TABLE AD_TEXTDOCS_NTAB STORE AS TEXTDOCS_NESTEDTAB5 
   LOB(AD_SOURCETEXT) store as (tablespace lobtbs1 chunk 32768 pctversion 0 
                                NOCACHE NOLOGGING
                                storage(initial 100M next 100M maxextents 
                                unlimited pctincrease 0));

Note the following with respect to this example:

• The storage clause in this example is specified in the CREATE TABLESPACE statement.
• You can specify the storage clause in the CREATE TABLE statement as an alternative.
• The storage clause is not allowed in the CREATE TEMPORARY TABLESPACE statement.
• Setting the PCTINCREASE parameter to 0 is recommended for gigabyte LOBs. For small, or medium size lobs, the default PCTINCREASE value of 50 is recommended as it reduces the number of extent allocations.