A read-consistent locator contains the snapshot environment as of the point in time of the SELECT operation.

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.

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

Read-consistent locators provide the same LOB value regardless of when the SELECT occurs. The following example demonstrates the relationship between read-consistency and UPDATE operation.

Using the print_media table and PL/SQL, three CLOB instances are created as potential locators: clob_selected, clob_update, and 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 does
  -- 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;
/

Learn about updating LOBs through Locators in this section.

When you update the value of the persistent LOB through the LOB locator (L1), L1 is updated to contain the current snapshot environment.

This snapshot is as of the 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.

Using the print_media table 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;
/

You may avoid many pitfalls if you use only one locator to update a given LOB value. Learn about it in this section.

In the following example, using table print_media, two CLOBs are created as potential locators: clob_updated and 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;
/

Learn about updating a LOB with a PL/SQL bind variable in this section.

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, three CLOBs are created as potential locators: clob_selected, clob_updated, and 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;
/

Learn about deleting a LOB with a PL/SQL bind variable in this section.

The following example illustrates that LOB content through a locator selected at a given point of time is available even though the LOB is deleted in the same transaction.

In the following example, using table print_media, two CLOBs are created as potential locators:clob_selected and clob_copied.

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

• At the time of the first SELECT INTO (at t1), the value inad_sourcetext for ad_id value 20020 is associated with the locator clob_selected. The value in ad_sourcetext for ad_id value 20021 is associated with the locator clob_copied.
• The second operation (at t2) deletes the row with ad_id value 20020. However, a DBMS_LOB.READ() of the value through clob_selected (at t1) reveals that it is a read-consistent locator, continuing to refer to the same value as of the time of its SELECT.
• The third operation (at t3), copies the LOB data read through clob_selected into the LOB clob_copied. DBMS_LOB.READ() of the value through clob_selected and clob_copied are now the same and refer to the same value as of the time of SELECT of clob_selected.

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

INSERT INTO PRINT_MEDIA VALUES (2057, 20021, EMPTY_BLOB(),
    'cdef', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL);

DECLARE
  clob_selected CLOB;
  clob_copied CLOB;
  buffer VARCHAR2(20);
  read_amount INTEGER := 20;
  read_offset INTEGER := 1;

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

  SELECT ad_sourcetext INTO clob_copied 
      FROM PRINT_MEDIA
      WHERE ad_id = 20021
      FOR UPDATE;

  dbms_lob.read(clob_selected, read_amount, read_offset,buffer);
  dbms_output.put_line(buffer);
  -- Produces the output 'abcd'

  dbms_lob.read(clob_copied, read_amount, read_offset,buffer);
  dbms_output.put_line(buffer);
  -- Produces the output 'cdef'

  -- At time t2: Delete the CLOB associated with clob_selected
  DELETE FROM PRINT_MEDIA WHERE ad_id = 20020;

  dbms_lob.read(clob_selected, read_amount, read_offset,buffer);
  dbms_output.put_line(buffer);
  -- Produces the output 'abcd'

  -- At time t3:
  -- Copy using clob_selected
  dbms_lob.copy(clob_copied, clob_selected, 4000, 1, 1);
  dbms_lob.read(clob_copied, read_amount, read_offset,buffer);
  dbms_output.put_line(buffer);
  -- Produces the output 'abcd'

END;
/

This script in this section can be used to ensure that hot backups can be taken of tables that have NOLOGGING or FILESYSTEM_LIKE_LOGGING LOBs and have a known recovery point without read inconsistencies.

ALTER DATABASE FORCE LOGGING;
SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;  --Start SCN

ALTER SYSTEM CHECKPOINT GLOBAL;
SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;  --End SCN
ALTER DATABASE NO FORCE LOGGING;