OPEN, CLOSE, and ISOPEN Interfaces for Internal LOBs

The OPEN, CLOSE, and ISOPEN interfaces let you open and close an internal LOB and test whether an internal LOB is already open.

It is not mandatory that you wrap all LOB operations inside the OPEN/CLOSE APIs. The addition of this feature does not impact already-existing applications that write to LOBs without first opening them, since these calls did not exist in 8.0.

Note: Openness is associated with the LOB, not the locator. The locator does not save any information as to whether the LOB to which it refers is open.

Wrap LOB Operations Inside an OPEN / CLOSE Call

• If you do not wrap LOB operations inside an OPEN/CLOSE call operation: Each modification to the LOB will implicitly open and close the LOB thereby firing any triggers on a domain index. Note that in this case, any domain indexes on the LOB will become updated as soon as LOB modifications are made. Therefore, domain LOB indexes are always valid and may be used at any time.

• If you wrap your LOB operations inside the OPEN/CLOSE operation: Triggers will not be fired for each LOB modification. Instead, the trigger on domain indexes will be fired at the CLOSE call. For example, you might design your application so that domain indexes are not be updated until you call CLOSE. However, this means that any domain indexes on the LOB will not be valid in-between the OPEN/CLOSE calls.

Close All Opened LOBs Before Committing the Transaction

It is an error to commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed but but the domain and functional indexes are not updated. If this happens, please rebuild your functional and domain indexes on the LOB column.

Note: Changes to the LOB are not discarded if the COMMIT returns an error.

At transaction rollback, the openness of all open LOBs still open for that transaction are discarded. Discarding the openness means the following for LOBs:

• LOBs will not be closed

• Triggers on domain indexes will not be fired

What is a 'Transaction' Where an Open LOB Value is Closed?

A 'transaction' where an open LOB value must be closed meets one of the following:

• Between 'DML statements that start a transaction (including SELECT ... FOR UPDATE)' and COMMIT

• Within an autonomous transaction block

A LOB opened when there is no transaction, must be closed before the end of the session. If there are still open LOBs at the end of the session, the openness will be discarded and no triggers on domain indexes will be fired.

Do Not Open or Close Same LOB Twice!

It is also an error to open/close the same LOB twice either with different locators or with the same locator.

Example 1: Correct Use of OPEN/CLOSE Calls to LOBs in a Transaction

This example shows the correct use of open and close calls to LOBs inside and outside a transaction.

DECLARE 
   Lob_loc1 CLOB; 
   Lob_loc2 CLOB;
   Buffer   VARCHAR2(32767); 
   Amount   BINARY_INTEGER := 32767; 
   Position INTEGER := 1; 
BEGIN 
   /* Select a LOB: */ 
   SELECT Story INTO Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1; 

   /* The following statement opens the LOB outside of a transaction 
      so it must be closed before the session ends: */ 
   DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY); 
   /* The following statement begins a transaction. Note that Lob_loc1 and 
      Lob_loc2 point to the same LOB:  */ 
   SELECT Story INTO Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 1 for update;
   /* The following LOB open operation is allowed since this lob has  
      not been opened in this transaction:  */ 
   DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READWRITE); 
   /* Fill the buffer with data to write to the LOB */
   buffer := 'A good story';
   Amount := 12;
   /* Write the buffer to the LOB: */ 
   DBMS_LOB.WRITE(Lob_loc2, Amount, Position, Buffer); 
   /* Closing the LOB is mandatory if you have opened it: */ 
   DBMS_LOB.CLOSE(Lob_loc2); 
   /* The COMMIT ends the transaction. It is allowed because all LOBs 
      opened in the transaction were closed. */ 
   COMMIT; 
   /* The the following statement closes the LOB that was opened  
       before the transaction started: */ 
   DBMS_LOB.CLOSE(Lob_loc1); 
END; 

Example 2: Incorrect Use of OPEN/CLOSE Calls to a LOB in a Transaction

This example the incorrect use of OPEN and CLOSE calls to a LOB and illustrates how committing a transaction which has open LOBs returns an error.

DECLARE
   Lob_loc CLOB;
BEGIN
   /* Note that the FOR UPDATE clause starts a transaction: */
   SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 for update;
   DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
   /* COMMIT returns an error because there is still an open LOB associated
   with this transaction: */
   COMMIT;
END;