12 Advanced Design Considerations

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, "Example of 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

Here are some details of the LOB buffering subsystem:

Error 22280: no more buffers available for operation

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 pseudocode example is based on the PM schema included with the 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, &amount, 0, offset, 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, &amount, 0, offset, 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, &amount, 0, offset, 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, &amount, 0, offset, 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, amount, 0, offset,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");
}

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.

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.

Example of 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.

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 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.

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.

INSERT INTO Print_media VALUES (3247, 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 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;
/

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.

INSERT INTO PRINT_MEDIA VALUES (2049, 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 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.

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;
/

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.

First Scenario:

1. Select the locator with no current transaction. At this point, the locator does not contain a transaction id.

2. Begin the transaction.

3. Use the locator to read data from the LOB.

4. Commit or rollback the transaction.

5. Use the locator to read data from the LOB.

6. Begin a transaction. The locator does not contain a transaction id.

7. Use the locator to write data to the LOB. This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id.

Second Scenario:

1. Select the locator with no current transaction. At this point, the locator does not contain a transaction id.

2. Begin the transaction. The locator does not contain a transaction id.

3. Use the locator to read data from the LOB. The locator does not contain a transaction id.

4. Use the locator to write data to the LOB. This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id. You can continue to read from or write to the LOB.

5. Commit or rollback the transaction. The locator continues to contain the transaction id.

6. Use the locator to read data from the LOB. This is a valid operation.

7. Begin a transaction. The locator already contains the previous transaction id.

8. Use the locator to write data to the LOB. This write operation will fail because the locator does not contain the transaction id that matches the current transaction.

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 1:

1. Select the locator within a transaction. At this point, the locator contains the transaction id.

2. Begin the transaction. The locator contains the previous transaction id.

3. Use the locator to read data from the LOB. This operation is valid even though the transaction id in the locator does not match the current transaction.

   See Also:

   "Read-Consistent Locators" for more information about using the locator to read LOB data.

4. Use the locator to write data to the LOB. This operation fails because the transaction id in the locator does not match the current transaction.

Scenario 2:

1. Begin a transaction.

2. Select the locator. The locator contains the transaction id because it was selected within a transaction.

3. Use the locator to read from or write to the LOB. These operations are valid.

4. Commit or rollback the transaction. The locator continues to contain the transaction id.

5. Use the locator to read data from the LOB. This operation is valid even though there is a transaction id in the locator and the transaction was previously committed or rolled back.

6. Use the locator to write data to the LOB. This operation fails because the transaction id in the locator is for a transaction that was previously committed or rolled back.

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;
/

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.

Oracle Database lets you create tablespaces with block sizes different from the database block size, and the maximum size of a LOB depends on the size of the tablespace blocks. CHUNK is a parameter of LOB storage whose value is controlled by the block size of the tablespace in which the LOB is stored.

When you create a LOB column, you can specify a value for CHUNK, which is the number of bytes to be allocated for LOB manipulation. The value must be a multiple of the tablespace block size, or Oracle Database rounds up to the next multiple. (If the tablespace block size is the same as the database block size, then CHUNK is also a multiple of the database block size.)

The maximum allowable storage limit for your configuration depends on the tablespace block size setting, and is calculated as (4 gigabytes - 1) times the value obtained from DBMS_LOB.GETCHUNKSIZE or OCILobGetChunkSize(). This value, in number of bytes for BLOBs or number of characters for CLOBs, is actually less than the size of the CHUNK parameter due to internal storage overhead. With the current allowable range for the tablespace block size from 2K to 32K, the storage limit ranges from 8 terabytes to 128 terabytes.

For example, suppose your database block size is 32K bytes and you create a tablespace with a nonstandard block size of 8K. Further suppose that you create a table with a LOB column and specify a CHUNK size of 16K (which is a multiple of the 8K tablespace block size). Then the maximum size of a LOB in this column is (4 gigabytes - 1) * 16K.

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 the LOB APIs included in the Oracle JDBC classes to access terabyte-size LOBs.

Using Terabyte-Size LOBs with the DBMS_LOB Package

You can access terabyte-size LOBs with all APIs in the DBMS_LOB PL/SQL package. Use DBMS_LOB.GETCHUNKSIZE to obtain the value to be used in reading and writing LOBs. The number of bytes stored in a chunk is actually less than the size of the CHUNK parameter due to internal storage overhead. The DBMS_LOB.GET_STORAGE_LIMIT function returns the storage limit for your database configuration. This is the maximum allowable size for LOBs. BLOBs are sized in bytes, while CLOBs and NCLOBs are sized in characters.

Using Terabyte-Size LOBs with OCI

The Oracle Call Interface API provides a set of functions for operations on LOBs of all sizes. OCILobGetChunkSize() returns the value, in bytes for BLOBs, or in characters for CLOBs, to be used in reading and writing LOBs. For varying-width character sets, the value is the number of Unicode characters that fit. The number of bytes stored in a chunk is actually less than the size of the CHUNK parameter due to internal storage overhead. The function OCILobGetStorageLimit() returns the maximum allowable size, in bytes, of internal LOBs in the current database installation. If streaming mode is used, where the whole LOB is read, there is no need to get the chunk size.

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.