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 s, for general information about read consistency. However, read consistency has some special applications to LOB locators that need to be understood.

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

Example of an Update Using Read Consistent Locators

Read Consistent Locators Provide Same LOB Value Regardless of When the SELECT Occurs

The following code demonstrates the relationship between read-consistency and updating in a simple example. Using Multimedia_tab, as defined in Chapter 8, "Sample Application", 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; 
/

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

Note: The snapshot environment in the locator is not updated if the locator is used to merely read the LOB value. It is only updated when you modify the LOB value through the locator via the PL/SQL DBMS_LOB package or the OCI LOB APIs.

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.

Note: When you update an internal LOB's value, the modification is always made to the most current LOB value.

Updating the value of the internal LOB through any of the available methods, such as via OCI LOB APIs or the PL/SQL DBMS_LOB package, can be thought of as updating the LOB value and then reselecting 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.

See Also: PL/SQL User's Guide and Reference.

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 via 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 (i.e., 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

Note: Avoid updating the same LOB with different locators! You will avoid many pitfalls if you use only 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 (i.e. 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 via 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.

See Also: "LOB Locators and Transaction Boundaries", for more information about the relationship between LOBs and transaction boundaries.

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