|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Modeling and Design, 8 of 21
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.
LOBwill implicitly open and close the
LOBthereby firing any triggers on a domain index. Note that in this case, any domain indexes on the
LOBwill become updated as soon as
LOBmodifications are made. Therefore, domain
LOBindexes are always valid and may be used at any time.
LOBmodification. Instead, the trigger on domain indexes will be fired at the
CLOSEcall. 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
LOBwill not be valid in-between the
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.
At transaction rollback, the openness of all open LOBs still open for that transaction are discarded. Discarding the openness means the following for LOBs:
A 'transaction' where an open
LOB value must be closed meets one of the following:
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.
It is also an error to open/close the same
LOB twice either with different locators or with the same locator.
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;
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;