1. SecureFiles and Large Objects Developer's Guide
  2. Locator Interface for LOBs
  3. Before You Begin

9.1 Before You Begin

Learn about the concepts that you should know before using the programmatic interfaces to work on LOBs, using the LOB locator.

  • 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.
  • LOB Open and Close Operations
    The LOB APIs include operations that enable you to explicitly open and close a LOB instance.
  • 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 GETCHUNKSIZE function.
  • 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 BFILEs.
  • Determining Character Set ID
    Some LOB APIs such as DBMS_LOB.LOADCLOBFROMFILE, OCILobRead2() and OCILobWrite2() take in a character set ID as an input. To determine the character set ID, you must know the character set name.
  • 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.

Parent topic: Locator Interface for LOBs

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

All LOB APIs need a valid LOB locator to be passed as an input. Use one of the following methods to populate a LOB variable in your application with 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 transaction.

    See Also:

  • Temporary LOBs: You can create a temporary LOB by using an API like DBMS_LOB.CREATETEMPORARY or by invoking a SQL or PL/SQL function that returns a temporary LOB.

    See Also:

    Temporary LOBs

Parent topic: Before You Begin

9.1.2 LOB Open and Close Operations

The LOB APIs include operations that enable you to explicitly open and close a LOB instance.

You can open and close a persistent or temporary LOB instance of any type: BLOB, CLOB or 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.

You must close any LOB instance that you explicitly open in the following places:
  • Between DML statements that start a transaction, including SELECT ... FOR UPDATE and COMMIT.
  • 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.

Keep track of the open or closed state of LOBs that you explicitly open. The following actions cause an error:
  • 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.

Parent topic: Before You Begin

9.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 GETCHUNKSIZE function.

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.

Parent topic: Before You Begin

9.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 BFILEs.

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.

Parent topic: Before You Begin

9.1.5 Determining Character Set ID

Some LOB APIs such as DBMS_LOB.LOADCLOBFROMFILE, OCILobRead2() and OCILobWrite2() take in a character set ID as an input. To determine the character set ID, you must know the character set name.

A user can select from the V$NLS_VALID_VALUES view, 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.

Although UTF16 is not allowed as a database or national character set, LOB APIs support it for database conversion purposes. Use character set ID = 1000 for UTF16, or in OCI, you can use OCI_UTF16ID.

See Also:

Parent topic: Before You Begin

9.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 9-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 OCILobLocatorIsInit
Find out if the BLOB or CLOB locator is a SecureFile ISSECUREFILE
Open/Close Open a LOB OPEN
Check is a LOB is open ISOPEN
Close the LOB CLOSE
Read Operations Get the length of the LOB GETLENGTH
Get the LOB storage limit for the database configuration GET_STORAGE_LIMIT
Get the optimum read or write size GETCHUNKSIZE
Read data from the LOB starting at the specified offset READ
Return part of the LOB value starting at the specified offset using SUBSTR SUBSTR
Return the matching position of a pattern in a LOB using INSTR INSTR
Modify Operations Write data to the LOB at a specified offset WRITE
Write data to the end of the LOB WRITEAPPEND
Erase part of a LOB, starting at a specified offset ERASE
Trim the LOB value to the specified shorter length TRIM
Operations involving multiple locators Check whether the two LOB locators are the same OCILobIsEqual
Compare all or part of the value of two LOBs COMPARE
Append a LOB value to another LOB APPEND
Copy all or part of a LOB to another LOB COPY
Assign LOB locator src to LOB locator dst dst:=src, OCILobLocatorAssign
Converts a BLOB to a CLOB or a CLOB to a BLOB CONVERTTOBLOB, CONVERTTOCLOB
Load BFILE data into a LOB LOADCLOBFROMFILE, LOADBLOBFROMFILE
Operations Specific to SecureFiles Returns options (deduplication, compression, encryption) for SecureFiles. GETOPTIONS
Sets LOB features (deduplication and compression) for SecureFiles SETOPTIONS
Gets the content string for a SecureFiles. GETCONTENTTYPE
Sets the content string for a SecureFiles. SETCONTENTTYPE
Delete the data from the LOB at the given offset for the given length FRAGMENT_DELETE
Insert the given data (< 32KBytes) into the LOB at the given offset FRAGMENT_INSERT
Move the given amount of bytes from the given offset to the new given offset FRAGMENT_MOVE
Replace the data at the given offset with the given data (< 32kBytes) FRAGMENT_REPLACE

See Also:

Note:

The DBMS_LOB package 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 DBMS_LOB package.

Most of the code examples in the following sections use the print_media table with the following structure:

Figure 9-1 print_media table

print_media table

Parent topic: Before You Begin