13.1 Read-Consistent Locators

Oracle Database provides the same read consistency mechanisms for LOBs as for all other database reads and updates of scalar quantities.

Read consistency has some special applications to LOB locators that you must understand. The following sections discuss read consistency and include examples which should be looked at in relationship to each other.

• A Selected Locator Becomes a Read-Consistent Locator
  A read-consistent locator contains the snapshot environment as of the point in time of the SELECT operation.
• 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 UPDATE operation.
• Example of Updating LOBs Through Updated Locators
  Learn about updating LOBs through Locators in this section.
• Example of Updating a LOB Using SQL DML and DBMS_LOB
  Using the print_media table in the following example, a CLOB locator is created as clob_selected.
• Example of Using One Locator to Update the Same LOB Value
  You may avoid many pitfalls if you use only one locator to update a given LOB value. Learn about it in this section.
• Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable
  Learn about updating a LOB with a PL/SQL bind variable in this section.
• Example of Deleting a LOB Using Locator
  Learn about deleting a LOB with a PL/SQL bind variable in this section.
• Ensuring Read Consistency
  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.

See Also:

• Oracle Database Concepts for general information about read consistency

13.1.1 A Selected Locator Becomes a Read-Consistent Locator

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.

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

Example 13-1

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

13.1.3 Example of Updating LOBs Through Updated Locators

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.

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 using 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 a persistent LOB value, the modification is always made to the most current LOB value.

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

Note:

Once you have selected out a LOB locator by whatever means, you can read from the locator but not write into it.

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:

Oracle Database PL/SQL Language Reference

13.1.4 Example of Updating a LOB Using SQL DML and DBMS_LOB

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

13.1.5 Example of Using One Locator to Update the Same LOB Value

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

Note:

Avoid updating the same LOB with different locators.

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

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

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

13.1.7 Example of Deleting a LOB Using Locator

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

13.1.8 Ensuring Read Consistency

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

SCN (System Change Number) is a stamp that defines a version of the database at the time that a transaction is committed.

Perform the backup.

Run the next script:

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

Back up the archive logs generated by the database. At the minimum, archive logs between start SCN and end SCN (including both SCN points) must be backed up.

To restore to a point with no read inconsistency, restore to end SCN as your incomplete recovery point. If recovery is done to an SCN after end SCN, there can be read inconsistency in the NOLOGGING LOBs.

For SecureFiles, if a read inconsistency is found during media recovery, the database treats the inconsistent blocks as holes and fills BLOBs with 0's and CLOBs with fill characters.