Oracle 8i Application Developer's Guide - Large Objects (LOBs)
Release 8.1.5
A68004-01

Library
Product
Contents
Index

Prev Next

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

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, the LOB is read as of the point in time of the SELECT statement even if the SELECT statement includes a FOR UPDATE. Further, if the LOB value is updated through a different locator (L2) in the same transaction, L1 does not see L2's updates. In addition, L1 will not see committed updates made to the LOB through another transaction.

Furthermore, 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. The following code demonstrates the relationship between read-consistency and updating in a simple example.

Using Multimedia_tab as defined previously and PL/SQL, three CLOBs are created as potential locators: clob_selected, clob_updated and clob_copied.

Example of a Read Consistent Locator

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 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 the 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 (see the PL/SQL User's Guide and Reference) 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.

Using the Multimedia_tab as defined previously, a CLOB locator is created: clob_selected.

Example of Repercussions of Mixing SQL DML with DBMS_LOB 

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

Note:

We advise that you 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 the table Multimedia_tab as defined previously, two CLOBs are created as potential locators: clob_updated and clob_copied.

Example of an Updated LOB Locator 
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; 
/

LOB Bind Variables

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

Example of Updating a LOB with a PL/SQL Variable 
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.

For more information about the relationship between LOBs and transaction boundaries see:

 

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

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

LOBs in the Object Cache

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.

When creating an object with a 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.

See Also:

"Example of a Read Consistent Locator" for a description of what version of the LOB value will be seen by each object if a write is performed through one of the locators.  

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

Oracle8 provides a LOB buffering subsystem (LBS) for advanced OCI based applications such as DataCartridges, 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 512K bytes. 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:

Considerations in the Use of LOB Buffering

The following caveats hold for buffered LOB operations:

LOB Buffering Operations

The Physical Structure of the LOB Buffer

Each user session has a fixed page pool of 16 pages, which are to be shared by all LOBs accessed in buffering mode from that session. Each page has a fixed size of up to 32K bytes (not characters) -- to be precise, pagesize = n x CHUNKSIZE ~= 32K. A LOB's buffer consists of one or more of these pages, up to a maximum of 16 per 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.

Using the LOB Buffering System

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, Oracle8 allocates 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 32K, for an input offset of 1000 and a specified read/write amount of 30000, Oracle8 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 Oracle8 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, Oracle8 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, Oracle8 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 dirty (i.e. they have been modified), and either the currently accessed LOB, or one of the other LOBs, need to be flushed. Oracle8 notifies this condition to the user as an error. Oracle8 never flushes and reallocates a dirty page implicitly -- you can either flush them explicitly, or discard them by disabling buffering on the LOB.

To illustrate the above 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 contain unmodified data read in from the server. Assume similar conditions in L2's buffer. Now, for the next buffered operation on L1, Oracle8 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, Oracle8 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, Oracle8 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

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

Note:

Unmodified pages may now be aged out if necessary.  

In the second case, you set the flag parameter in OCILobFlushBuffer() to OCI_LOB_BUFFER_FREE to free the buffer pages, and so return the memory to the client address space. 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:

Using Locators Enabled for Buffering

Note that there are several cases in which you can use buffer-enabled locators and others in which you cannot.

Saving Locator State so as 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 roundtrip 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 roundtrip to the server, and so would refresh the snapshot in the locator. In order to save the state of a LOB that has been written through the LOB buffer, you therefore need to

  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 roundtrip to the server to reselect the locator into L2.

Example of LOB Buffering

The following pseudocode for an OCI program based on the Multimedia_tab schema briefly explains the concepts listed above. 

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");
}

User Guidelines for Best Performance Practices

Working with Varying-Width Character Data

In using the OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another. However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.

LOBs in Index Organized Tables

Index Organized Tables now support internal and external LOB columns. The SQL DDL, DML and piecewise operations on LOBs in index organized tables exhibit the same behavior as that observed in conventional tables. The only exception is the default behavior of LOBs during creation. The main differences are:

Consider the following example: 

CREATE TABLE iotlob_tab (c1 INTEGER primary key, c2 BLOB, c3 CLOB, c4 
VARCHAR2(20)) 
  ORGANIZATION INDEX 
    TABLESPACE iot_ts 
    PCTFREE 10 PCTUSED 10 INITRANS 1 MAXTRANS 1 STORAGE (INITIAL 4K) 
    PCTTHRESHOLD 50 INCLUDING c2 
  OVERFLOW 
    TABLESPACE ioto_ts 
    PCTFREE 10 PCTUSED 10 INITRANS 1 MAXTRANS 1 STORAGE (INITIAL 8K) LOB (c2) 
    STORE AS lobseg (TABLESPACE lob_ts DISABLE STORAGE IN ROW  
                     CHUNK 1 PCTVERSION 1 CACHE STORAGE (INITIAL 2m) 
                     INDEX LOBIDX_C1 (TABLESPACE lobidx_ts STORAGE (INITIAL 
                                                                    4K)));

Executing these statements will result in the creation of an index organized table iotlob_tab with the following elements:

Other LOB features, such as BFILEs and varying character width LOBs, are also supported in index organized tables, and their usage is the same as conventional tables.

Note:

Support for LOBs in partitioned index organized tables will be provided in a future release.  


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index