Learn about the concepts that you should know before using the programmatic interfaces to work on LOBs, using the LOB locator.
8.1.1 Getting a LOB Locator
All LOB APIs need a valid LOB locator to be passed as an input. This section discusses various methods to populate LOB variables using a LOB locator.
- Persistent LOBs: First create a table with a LOB column, then
insert a value into the LOB column and select out the LOB locator. To modify
an existing LOB using a LOB locator, you must lock the row in the table in
order to prevent other database users from writing to the LOB during a
- Persistent LOBs for information on how to create a a table with a LOB column and populate it.
- Selecting a LOB into a LOB Variable for Read Operations for information on how to select a LOB locator for LOB read operations.
- Selecting a LOB into a LOB Variable for Write Operations for information on how to lock the row for LOB modify operations.
- Temporary LOBs: You can create a temporary LOB by using an API
DBMS_LOB.CREATETEMPORARYor by invoking a SQL or PL/SQL function that returns a temporary LOB.
See Also:Temporary LOBs
8.1.2 LOB Open and Close Operations
The LOB APIs include operations that enable you to explicitly open and close a LOB instance.
NCLOB. You open a LOB to achieve one or both of the following results:
Open the LOB in read-only mode
This ensures that the LOB (both the LOB locator and LOB value) 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. After you perform the operation, you can then close the LOB.
Open the LOB in read-write mode
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 a functional or domain index on the LOB column, and you do not want the database to perform index maintenance every time you write to the LOB. This technique can improve the performance of your application if you are doing several write operations on the LOB while it is open. Note that any index on the LOB column is not valid until you explicitly close the LOB.
If you do not explicitly open the LOB instance, then every modification to the LOB implicitly opens and closes the LOB instance. The database performs index maintenance for any functional and domain indexes on the LOB column on each implicit close of the LOB. This means that the indexes on the LOB are updated as soon as any modification to the LOB instance is made. These indexes are always valid and can be used at any time.
The open state of a LOB is associated with the LOB instance, not the LOB locator. The locator does not save any information indicating whether the LOB instance that it points to is open.
- Between DML statements that start a transaction, including
SELECT ... FOR UPDATEand
- Within an autonomous transaction block.
- Before the end of a session (when there is no transaction in progress in the session).
If you do not explicitly close the LOB instance, then it is implicitly closed at the end of the session and no index triggers are fired, which means that any indexes on the LOB column are not updated. In this situation, you must rebuild your indexes on the LOB column.
Committing a transaction on the open LOB instance causes an error. When this error occurs, the LOB instance is closed implicitly, any modifications to the LOB instance are saved, and the transaction is committed, but any indexes on the LOB column are not updated. In this situation, you must rebuild your indexes on the LOB column.
If you subsequently rollback the transaction, then the LOB instance is rolled back to its previous state, but the LOB instance is no longer explicitly open.
- Explicitly opening a LOB instance that has been explicitly open earlier.
- Explicitly closing a LOB instance that is has been explicitly closed earlier.
This occurs whether you access the LOB instance using the same locator or different locators.
8.1.3 Read and Write at Chunk Boundaries
To improve performance, you should perform LOB reads and writes using
offsets and amount that are a multiple of the value returned by
If it is appropriate for your application, then you should batch reads and writes until you have enough for an entire chunk instead of issuing several LOB read or write calls that operate on the same LOB chunk.
8.1.4 Prefetching LOB Data and Length
In most clients like JDBC, OCI and ODP.NET, the number of server round trips
can be reduced by prefetching part of the data and metadata (length and chunk size) along
with the LOB locator during the fetch. This applies to persistent LOBs, temporary LOBs, and
For small to medium sized LOBs, Oracle recommends setting the prefetch length such that about majority of your LOBs are smaller than the prefetch size.
LOB prefetch size can be set at the session level, and can be overwritten at the statement or the column level.
8.1.5 Determining Character Set ID
Some LOB APIs such as
OCILobWrite2() take in a character
set ID as an input. To determine the character set ID, you must know the character set
A user can select from the
which lists the names of the character sets that are valid as database and national
character sets. Then call the function
NLS_CHARSET_ID with the
desired character set name as the one string argument. The character set ID is
returned as an integer.
UTF16is not allowed as a database or national character set, LOB APIs support it for database conversion purposes. Use character
set ID = 1000for
UTF16, or in OCI, you can use
8.1.6 LOB APIs
Once a LOB variable is initialized with either a persistent or a temporary
LOB locator, subsequent read operations on the LOB can be performed using APIs such as the
DBMS_LOB package subprograms.
The operations supported on LOBs are divided into the following categories:
Table 8-1 Operations supported by LOB APIs
|Category||Operation||Example function/procedure in DBMS_LOB or OCILob|
|Sanity Checking||Check if the LOB variable has been initialized||
|Find out if the
|Open/Close||Open a LOB||
|Check is a LOB is open||
|Close the LOB||
|Read Operations||Get the length of the LOB||
|Get the LOB storage limit for the database configuration||
|Get the optimum read or write size||
|Read data from the LOB starting at the specified offset||
|Return part of the LOB value starting at the
specified offset using
|Return the matching position of a pattern in a LOB
|Modify Operations||Write data to the LOB at a specified offset||
|Write data to the end of the LOB||
|Erase part of a LOB, starting at a specified offset||
|Trim the LOB value to the specified shorter length||
|Operations involving multiple locators||Check whether the two LOB locators are the same||
|Compare all or part of the value of two LOBs||
|Append a LOB value to another LOB||
|Copy all or part of a LOB to another LOB||
|Assign LOB locator
|Operations Specific to SecureFiles||Returns options (deduplication, compression, encryption) for SecureFiles.||
|Sets LOB features (deduplication and compression) for SecureFiles||
|Gets the content string for a SecureFiles.||
|Sets the content string for a SecureFiles.||
|Delete the data from the LOB at the given offset for the given length||
|Insert the given data (< 32KBytes) into the LOB at the given offset||
|Move the given amount of bytes from the given offset to the new given offset||
|Replace the data at the given offset with the given data (< 32kBytes)||
DBMS_LOBpackage provides a rich set of operations on LOBs. If you are using a different programmatic interface, where some of these operations are not provided, then call the corresponding PL/SQL procedure or function in
Most of the code examples in the following sections use the
print_media table with the following structure:
Figure 8-1 print_media table