5
Large Objects: Advanced Topics

This chapter contains the following sections:

• Introducing Large Objects: Advanced Topics
• Read Consistent Locators
• LOB Locators and Transaction Boundaries
• LOBs in the Object Cache
• LOB Buffering Subsystem
• Creating a Varray Containing References to LOBs
• LOBs in Partitioned Index-Organized Tables
• Restrictions for LOBs in Partitioned Index-Organized Tables

Note: Examples in this chapter are based on the multimedia schema and table Multimedia_tab described in Appendix B, "The Multimedia Schema".

Introducing Large Objects: Advanced Topics

The material in this chapter is a supplement and elaboration of the use cases described in the following chapters.You will probably find the topics discussed here to be more relevant once you have explored the use cases.

Read Consistent Locators

Oracle provides the same read consistency mechanisms for LOBs as for all other database reads and updates of scalar quantities. Refer to Oracle9i 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.

This has some complex implications. Let us say that you have created a read consistent locator (L1) by way of a SELECT operation. In reading the value of the internal 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, L1 does not see L2's 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.

Clearly you can utilize the existence of multiple locators to access different transformations of the LOB value. However, in taking this course, you must be careful to 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 Multimedia_tab, as defined in Appendix B, "The Multimedia Schema", and PL/SQL, three CLOBs 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 story is associated with the locator clob_selected.
• In the second operation (at t2), the value in story is associated with the locator clob_updated. Since there has been no change in the value of story 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 utilizes 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 Multimedia_tab VALUES (1, 'abcd', EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), 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 story INTO clob_selected 
     FROM Multimedia_tab 
     WHERE clip_id = 1; 

  -- At time t2: 
  SELECT story INTO clob_updated 
     FROM Multimedia_tab 
     WHERE clip_id = 1 
     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; 
/

Updating LOBs Via Updated Locators

When you update the value of the internal 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 allows 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 internal 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 Multimedia_tab as defined previously, a CLOB locator is created:

• clob_selected.

Note the following progressions in the following example PL/SQL (DBMS_LOB) code, from times t1 through t3:

• At the time of the first SELECT INTO (at t1), the value in story is associated with the locator clob_selected.
• In the second operation (at t2), the value in story is modified through the SQL UPDATE statement, bypassing 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 Multimedia_tab VALUES (1, 'abcd', EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), 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 story INTO clob_selected 
  FROM Multimedia_tab 
  WHERE clip_id = 1;
  
  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 Multimedia_tab SET story = empty_clob() 
      WHERE clip_id = 1; 
 -- 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 story INTO clob_selected FROM Multimedia_tab WHERE
       clip_id = 1; 
 -- 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

Using table Multimedia_tab as defined previously, two CLOBs are created as potential locators:

• clob_updated
• clob_copied

Note these progressions in the following example PL/SQL (DBMS_LOB) code at times t1 through t5:

• At the time of the first SELECT INTO (at t1), the value in story 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. The example demonstrates this with a series of DBMS_LOB.READ() calls.
• At this juncture (at t3), the program utilizes 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 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 Multimedia_tab VALUES (1,'abcd', EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), 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 story INTO clob_updated FROM Multimedia_tab 
      WHERE clip_id = 1 
      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 internal 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 L2's snapshot environment at the time of the operation is used.

Using the table Multimedia_tab as defined previously, three CLOBs are created as potential locators:

• clob_selected
• clob_updated
• clob_copied

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

• At the time of the first SELECT INTO (at t1), the value in story 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 utilizes 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, we insert the value associated with clob_copied (for example, without the new changes made by clob_updated). This is demonstrated by the subsequent DBMS_LOB.READ() of the value just inserted.

Example

INSERT INTO Multimedia_tab VALUES (1, 'abcd', EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), 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 story INTO clob_updated FROM Multimedia_tab 
      WHERE clip_id = 1 
      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 doesn't 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 Multimedia_tab VALUES (2, clob_copied, EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, NULL, NULL) 
    RETURNING story 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 Cannot Span Transactions

Modifying an internal LOB's 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. Further, the INSERT or UPDATE statement automatically starts a transaction and locks the row. Once this has occurred, the locator may not 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 may be used to read the LOB value unless you are in a serializable transaction.

Using table Multimedia_tab defined previously, a CLOB locator is created: clob_updated.

• At the time of the first SELECT INTO (at t1), the value in story is associated with the locator clob_updated.
• The second operation (at t2), utilizes the DBMS_LOB.WRITE() command 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 Multimedia_tab VALUES (1, 'abcd', EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), 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      story 
     INTO        clob_updated 
     FROM        Multimedia_tab 
     WHERE       clip_id = 1 
     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; 
/

LOB Locators and Transaction Boundaries

A basic description of LOB locators and their operations is given in Chapter 2, "Basic LOB Components".

This section discusses the use of LOB locators in transactions, and transaction IDs.

• Locators Contain Transaction IDs When....

  You Begin the Transaction, Then Select Locator. If you begin a transaction and then select a locator, 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, 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.

Transaction IDs: 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, you cannot write to the LOB outside of that particular transaction.
• Can Write Using Locator: If the locator does not contain a transaction ID, 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, you cannot read or write using that locator.
• Can Read, Not Write Using Locator With Non-Serializable Transactions: If the transaction is non-serializable, you can read, but not write outside of that transaction.

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

Non-Serializable Example: Selecting the Locator with No Current Transaction

Case 1:

Case 2:

Non-Serializable Example: Selecting the Locator within a Transaction

Case 3:

Case 4:

LOBs in the Object Cache

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

• Internal 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 an internal 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 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 alias and filename before reading from the file.

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.

LOB Buffering Subsystem

Oracle8i and Oracle9i provide 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's 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's buffer in the client's 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. Oracle8i and Oracle9i provide a simple buffering subsystem, and not a cache. To be specific, Oracle does not guarantee that the contents of a LOB's buffer are always in sync with the LOB value in the server. Unless you explicitly flush the contents of a LOB's 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 Via 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, 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, 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 we assume it is SQLCS_IMPLICIT.

LOB Buffering Usage Notes

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

Example of Using the LOB Buffering System (LBS)

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, Oracle8i and Oracle9i allocate one or more of the free pages in the page pool to the LOB's 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's 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's 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's buffer, Oracle allocates any available free page(s) from the page pool to the LOB's buffer. If there are no free pages available in the page pool, Oracle reallocates the pages as follows. It ages out the least recently used page among the unmodified pages in the LOB's buffer and reallocates it for the current operation.

If no such page is available in the LOB's buffer, 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 L1's buffer are dirty, with the remaining 2 containing unmodified data read in from the server. Assume similar conditions in L2's buffer. Now, for the next buffered operation on L1, Oracle will reallocate the least recently used page from the two unmodified pages in L1's buffer. Once all the 8 pages in L1's buffer are used up for LOB writes, Oracle can service two more operations on L1 by allocating the two unmodified pages from L2's 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, 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 LOB(s) 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's 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 wish to update the LOB in the server, 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.

This raises the question: What happens to the data in the LOB buffer? There are two possibilities. In the default mode, the flush operation retains the data in the pages that were modified. In this case, when you read or write to the same range of bytes no round-trip to the server is necessary. Note that flush 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.

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. Note that flush in this context 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 LBS 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/method in PL/SQL.

Note: 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/write using the locator,
• Flush the LOB,
• Disable the locator for buffering
• Return to the calling function/procedure/method in PL/SQL

Remember that Oracle never implicitly flushes the LOB.

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:

    OCILobRead(), OCILobWrite(), 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 -- OCILobCopy(), OCILobAppend(), OCILobErase(), OCILobGetLength(), OCILobTrim(), OCILobWriteAppend().

    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 pseudocode for an OCI program based on the Multimedia_tab schema illustrates the issues described in the preceding discussion.

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 etc.
  
   init_OCI();

   -- Establish a savepoint before start of LBS operations 
   exec_statement("savepoint lbs_savepoint");
  
   -- Initialize bind variable to BLOB columns from buffered 
   -- access: 
   exec_statement("select frame into lbs_loc1 from Multimedia_tab
       where clip_id = 12");
   exec_statement("select frame into lbs_loc2 from Multimedia_tab
       where clip_id = 12 for update");
   exec_statement("select frame into lbs_loc2 from Multimedia_tab
       where clip_id = 12 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;
   OCILobRead(.., 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's
     -- 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);
          OCILobWrite(.., 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's 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;
          OCILobRead(.., 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);
          OCILobWrite(.., 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);
          OCILobWrite(.., 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 etc.
          -- When done, flush LOB's 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's 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's buffer resources.
        exception_handler:
      handle_exception_reporting();
      exec_statement("rollback to savepoint lbs_savepoint");
}    

Creating a Varray Containing References to LOBs

LOBs, or rather references to LOBs, can also be created using VARRAYs. To create a VARRAY containing references to LOBs read the following:

Column, MAP_OBJ of type MAP_TYP, already exists in table Multimedia_tab. See Appendix B, "The Multimedia Schema" for a description of table Multimedia_tab. Column MAP_OBJ contains a BLOB column named DRAWING.

The syntax for creating the associated types and table Multimedia_tab is described in Chapter 10, "Internal Persistent LOBs", SQL: Create a Table Containing One or More LOB Columns, .

Creating a Varray Containing LOB References: Example

Suppose you need to store multiple map objects in each multimedia clip. To do that follow these steps:

1. Define a VARRAY of type REF MAP_TYP.

   For example:

   CREATE TYPE MAP_TYP_ARR AS
            VARRAY(10) OF REF MAP_TYP;
   

2. Define a column of the array type in Multimedia_tab.

   For example:

   CREATE TABLE MULTIMEDIA_TAB ( ......etc. [list all columns here]
                ... MAP_OBJ_ARR MAP_TYP_ARR)
                VARRAY MAP_OBJ_ARR STORE AS LOB MAP_OBJ_ARR_STORE;
   

LOBs in Partitioned Index-Organized Tables

Oracle9i introduces support for LOB, VARRAY columns stored as LOBs, and BFILEs in partitioned index-organized tables. The behavior of LOB columns in these tables is similar to that of LOB columns in conventional (heap-organized) partitioned tables, except for the following few minor differences:

• Tablespace Mapping. In the absence of a tablespace specification for a LOB column at the individual partition level or at the table level, the LOB's data and index segments for that partition are created in the tablespace in which the primary key index segment of the corresponding partition of the index-organized table is created. In other words, they are equi-partitioned and collocated with their corresponding primary key index segments.
• Inline vs Out-of-line. If the partitioned index-organized table is defined without an overflow segment, then a LOB column cannot be defined or altered to be created inline. This requirement is the same as that for a non-partitioned index-organized table.

LOB columns are supported only in range partitioned index-organized tables.

Example of LOB Columns in Partitioned Index-Organized Tables

In this section, we'll highlight the differences listed in the preceding for LOBs in partitioned index-organized tables with the Multimedia_Tab example described in Appendix B.

Assume that Multimedia-tab has been created as a range-partitioned index-organized table, as follows:

CREATE TABLE Multimedia_tab (
  CLIP_ID    INTEGER PRIMARY KEY,
  CLIP_DATE  DATE,
  STORY      CLOB,
  FLSUB      NCLOB,
  PHOTO      BFILE,
  FRAME      BLOB,
  SOUND      BLOB,
  ...
  )
  ORGANIZATION INDEX
    TABLESPACE TBS_IDX
  OVERFLOW
    TABLESPACE TBS_OVF
  LOB (FRAME, S0UND) STORE AS (TABLESPACE TBS_LOB)
  PARTITION BY RANGE (CLIP_DATE)
  (PARTITION Jan_Multimedia_tab VALUES LESS THAN (01-FEB-2000)
     LOB (STORY) STORE AS (TABLESPACE TBS_LOB),
   PARTITION Feb_Multimedia_tab VALUES LESS THAN (01-MAR-2000)
     LOB (FLSUB) STORE AS (TABLESPACE TBS_LOB
                           ENABLE STORAGE IN ROW)
  );

In the preceding example, the LOB columns FRAME and SOUND will be stored in the tablespace TBS_LOB across all the partitions.

• In the partition, Jan_Multimedia_tab, the column STORY is stored in TBS_LOB because of the partition-specific override, but the column FLSUB will be stored in TBS_IDX, that is, the tablespace of its primary key index segment.
• In the partition, Feb_Multimedia_tab, the column STORY is stored in TBS_IDX, and the column FLSUB will be stored in TBS_LOB by virtue of the partition-specific override.

  Note: Since Multimedia_tab was created with an overflow segment, the LOB columns are allowed to be stored inline. If that was not the case, the "ENABLE STORAGE IN ROW" clause for FLSUB in Feb_Multimedia_tab, would have generated an error.

The inheritance semantics for the rest of the LOB physical attributes are in line with LOBs in conventional tables.

Restrictions for LOBs in Partitioned Index-Organized Tables

Range Partitioned Index-Organized Table LOB Restrictions

Non-Supported Column Types

The following column types in Range partitioned index-organized table are not supported:

• VARRAY columns STORED AS both inline/out-of-line LOBs.
• Abstract Data Types (ADTs) with LOB attributes
• VARRAY (stored as LOB) of ADT (with LOB attributes).
• NESTED TABLEs with LOB columns.

Non-Supported Column Types in Object Range Partitioned Index-Organized Tables

The following column types in Object Range partitioned index-organized tables are not supported:

• ADTs with LOB attributes
• VARRAY columns STORED AS both inline/out-of-line LOBs.
• VARRAY (stored as LOB) of ADT (with LOB attributes).
• NESTED TABLEs with LOB columns.

Hash Partitioned Index-Organized Table LOB Restrictions

LOB columns are not supported in Hash partitioned index- organized tables.