This topic describes the usage and semantics of LOBs that you need to be familiar with to use LOBs in your application. Various techniques for working with LOBs are covered.
Here, persistent LOBs assume that you are dealing with LOBs in tables that already exist whereas the task of creating tables with LOB columns is typically performed by your database administrator.
- You can use temporary LOBs to store transient data. The data is stored in a temporary tablespace rather than regular tablespace. You should free temporary LOBs after you no longer need them. If you do not, then space the LOB consumes in temporary tablespace will not be reclaimed.
- You can insert temporary LOBs into a table. When you do this, a permanent copy of the LOB is created and stored.
- You can create a temporary LOB with the static method createTemporary, defined in both the oracle.sql.BLOB and oracle.sql.CLOB classes. You free a temporary LOB with the freeTemporary method.
- You can test whether a LOB is temporary or not by calling the isTemporary() method. If the LOB was created by calling the createTemporary method, then the isTemporary() method returns true, else it returns false.
- You can free a temporary LOB by calling the freeTemporary method. Free any temporary LOBs before ending the session or call.
LOB Column States
The techniques which is used while accessing a cell in a LOB column differ depending on the state of the given cell. A cell in a LOB Column can be in any one of the following states:
- NULL: The table cell is created, but the cell holds no value or locator.
- Empty: A LOB instance having a locator exists in the cell, but it has no value in that condition the length of the LOB is zero.
- Populated: A LOB instance having both locator and value exists in the cell.
Locking a Row Containing a LOB
Reason for locking LOB is you can lock a row containing a LOB to prevent other database users from writing to the LOB during a transaction. While locking a row containing a LOB, specify the FOR UPDATE clause when you select the row, if the row is locked, other users cannot lock or update the LOB, until you end your transaction.
Opening and Closing LOBs
The LOB APIs include operations that enable you to explicitly open and close a LOB instance. You can easily open or close a persistent LOB instance of any these type: BLOB, CLOB, NCLOB, or BFILE. You can open a LOB to achieve one or both of the following results:
- Open the LOB in read-only mode: This ensures that the LOB cannot be changed in your session until you explicitly close the LOB. For example, you can open the LOB to ensure that the LOB is not changed by some other part of your program while you are using the LOB in a critical operation.
- Open the LOB in read/write mode—persistent CLOB, NCLOB, or BLOB instances only: Opening a LOB in read-write mode defers any index maintenance on the LOB column until you close the LOB. Opening a LOB in read-write mode is only useful if there is an extensible index on the LOB column and you do not want the database to perform index to be maintained every time you write to the LOB. This technique will increase the performance of your application if you are doing several write operations on the LOB while it is open.
- If you open a LOB, then you must close the LOB at some point later in your session. This is the only requirement to achieve an open LOB. While a LOB instance is open, you can perform as many operations as you want on the LOB—provided the operations are allowed in the given mode.
- void open (int mode)
- void close()
- boolean isOpen()
- You should wrap LOB operations inside an Open/Close call operation, otherwise, 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.
- When you wrap your LOB operations inside the Open/Close call operation, then triggers will not be fired for each LOB modification. Instead of that, the trigger on domain indexes will be fired at the Close call operations.
- When you open a LOB by calling the open or open(int) method. You may then read or write the LOB without any triggers being associated with that LOB firing. When you finish accessing the LOB, close the LOB by calling the close method. When you close the LOB, any triggers associated with the LOB will fire.
- You can check if a LOB is open or closed by calling the isOpen method. Whenever you open the LOB by calling the open(int) method, then the value of the argument must be either MODE_READONLY or MODE_READWRITE, as defined in the oracle.sql.BLOB and oracle.sql.CLOB classes. If you open the LOB with MODE_READONLY, then any attempt to write to the LOB will result in a SQL exception.