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

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.

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:

  • 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 like DBMS_LOB.CREATETEMPORARY or 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.

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.

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

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

8.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:

• OCIUnicodeToCharSet() for information on the OCIUnicodeToCharSet() function and details on OCI syntax in general.
• Overview of Globalization Support for detailed information about implementing applications in different languages.

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	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:

• Temporary LOBs
• BFILEs
• Comparing the LOB Interfaces

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 8-1 print_media table