22 Using LOB APIs

This chapter describes APIs that perform operations on BLOB, CLOB, and NCLOB data types. The operations given in this chapter can be used with either persistent or temporary LOB instances. Note that operations in this chapter do not apply to BFILEs. APIs covered in this chapter are listed in Table 22-1.

The following information is given for each operation described in this chapter:

Preconditions describe dependencies that must be met and conditions that must exist before calling each operation.

Usage Notes provide implementation guidelines such as information specific to a given programmatic environment or data type.

Syntax refers you to the syntax reference documentation for each supported programmatic environment.

Examples describe any setup tasks necessary to run the examples given. Demonstration files listed are available in subdirectories under $ORACLE_HOME/rdbms/demo/lobs/ named plsql, oci, vb, and java. The driver program lobdemo.sql is in /plsql and the driver program lobdemo.c is in /oci.

Supported Environments

Table 22-1, "Environments Supported for LOB APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, OCCI, COBOL, Pro*C/C++, COM, and JDBC.

Appending One LOB to Another

Before you can append one LOB to another, the following conditions must be met:

Two LOB instances must exist.

Both instances must be of the same type, for example both BLOB or both CLOB types.

You can pass any combination of persistent or temporary LOB instances to this operation.

Usage Notes

Persistent LOBs: You must lock the row you are selecting the LOB from prior to updating a LOB value if you are using the PL/SQL DBMS_LOB Package or OCI. While the SQL INSERT and UPDATE statements implicitly lock the row, locking the row can be done explicitly using the SQL SELECTFORUPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs. For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".

Syntax

See the following syntax references for each programmatic environment:

As an alternative to this operation, you can use SQL*Loader to load persistent LOBs with data directly from a file in the file system. See "Using SQL*Loader to Load LOBs" for more information.

Preconditions

Before you can load a LOB with data from a BFILE, the following conditions must be met:

The BFILE must exist.

The target LOB instance must exist.

Usage Notes

Note the following issues regarding this operation.

Use LOADCLOBFROMFILE When Loading Character Data

When you use the DBMS_LOB.LOADFROMFILE procedure to load a CLOB or NCLOB instance, you are loading the LOB with binary data from the BFILE and no implicit character set conversion is performed. For this reason, using the DBMS_LOB.LOADCLOBFROMFILE procedure is recommended when loading character data, see Loading a CLOB or NCLOB with Data from a BFILE for more information.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to functions listed in Table 22-2 must be one of the following:

An amount less than or equal to the actual size (in bytes) of the BFILE you are loading.

The maximum allowable LOB size (in bytes). Passing this value, loads the entire BFILE. You can use this technique to load the entire BFILE without determining the size of the BFILE before loading. To get the maximum allowable LOB size, use the technique described in Table 22-2.

Table 22-2 Maximum LOB Size for Load from File Operations

Environment

Function

To pass maximum LOB size, get value of:

DBMS_LOB

DBMS_LOB.LOADBLOBFROMFILE

DBMS_LOB.LOBMAXSIZE

DBMS_LOB

DBMS_LOB.LOADCLOBFROMFILE

DBMS_LOB.LOBMAXSIZE

OCI

OCILobLoadFromFile2()

(For LOBs of any size.)

UB8MAXVAL

OCI

OCILobLoadFromFile()

(For LOBs less than 4 gigabytes in size.)

UB4MAXVAL

Syntax

See the following syntax references for details on using this operation in each programmatic environment:

As an alternative to this operation, you can use SQL*Loader to load persistent LOBs with data directly from a file in the file system. See "Using SQL*Loader to Load LOBs" for more information.

Preconditions

The following conditions must be met before calling this procedure:

The target BLOB instance must exist.

The source BFILE must exist.

You must open the BFILE. (After calling this procedure, you must close the BFILE at some point.)

Usage Notes

Note the following with respect to this operation:

New Offsets Returned

Using DBMS_LOB.LOADBLOBFROMFILE to load binary data into a BLOB achieves the same result as using DBMS_LOB.LOADFROMFILE, but also returns the new offsets of BLOB.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to the DBMS_LOB.LOADBLOBFROMFILE function must be one of the following:

An amount less than or equal to the actual size (in bytes) of the BFILE you are loading.

The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE. Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.

As an alternative to this operation, you can use SQL*Loader to load persistent LOBs with data directly from a file in the file system. See "Using SQL*Loader to Load LOBs" for more information.

Preconditions

The following conditions must be met before calling this procedure:

The target CLOB or NCLOB instance must exist.

The source BFILE must exist.

You must open the BFILE. (After calling this procedure, you must close the BFILE at some point.)

Usage Notes

You can specify the character set id of the BFILE when calling this procedure. Doing so, ensures that the character set is properly converted from the BFILE data character set to the destination CLOB or NCLOB character set.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to the DBMS_LOB.LOADCLOBFROMFILE function must be one of the following:

An amount less than or equal to the actual size (in characters) of the BFILE data you are loading.

The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE

Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.

Java (JDBC): Checking If a LOB Is Open

Here is how to check a BLOB or a CLOB.

Checking If a CLOB Is Open

To see if a CLOB is open, your JDBC application can use the isOpen method defined in oracle.sql.CLOB. The return Boolean value indicates whether the CLOB has been previously opened or not. The isOpen method is defined as follows:

Checking If a BLOB Is Open

To see if a BLOB is open, your JDBC application can use the isOpen method defined in oracle.sql.BLOB. The return Boolean value indicates whether the BLOB has been previously opened or not. The isOpen method is defined as follows:

Displaying LOB Data

This section describes APIs that allow you to read LOB data. You can use this operation to read LOB data into a buffer. This is useful if your application requires displaying large amounts of LOB data or streaming data operations.

Usage Notes

Note the following when using these APIs.

Streaming Mechanism

The most efficient way to read large amounts of LOB data is to use OCILobRead2() with the streaming mechanism enabled.

Amount Parameter

The value you pass for the amount parameter is restricted for the APIs described in Table 22-3.

Table 22-3 Maximum LOB Size for Amount Parameter

Environment

Function

Value of amount parameter is limited to:

DBMS_LOB

DBMS_LOB.READ

The size of the buffer, 32Kbytes.

OCI

OCILobRead()

(For LOBs less than 4 gigabytes in size.)

UB4MAXVAL

Specifying this amount reads the entire file.

OCI

OCILobRead2()

(For LOBs of any size.)

UB8MAXVAL

Specifying this amount reads the entire file.

Syntax

Use the following syntax references for each programmatic environment:

Reading Data from a LOB

This section describes how to read data from LOBs using OCILobRead2().

Usage Notes

Note the following when using this operation.

Streaming Read in OCI

The most efficient way to read large amounts of LOB data is to use OCILobRead2() with the streaming mechanism enabled using polling or callback. To do so, specify the starting point of the read using the offset parameter as follows:

When using polling mode, be sure to look at the value of the byte_amt parameter after each OCILobRead2() call to see how many bytes were read into the buffer because the buffer may not be entirely full.

When using callbacks, the lenp parameter, which is input to the callback, indicates how many bytes are filled in the buffer. Be sure to check the lenp parameter during your callback processing because the entire buffer may not be filled with data (see the Oracle Call Interface Programmer's Guide.)

Chunk Size

A chunk is one or more Oracle blocks. You can specify the chunk size for the BasicFiles LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE. In OCI, use OCILobGetChunkSize(). For SecureFiless, CHUNK is an advisory size and is provided for backward compatibility purposes.

To improve performance, you may run write requests using a multiple of the value returned by one of these functions. The reason for this is that you are using the same unit that the Oracle database uses when reading data from disk. If it is appropriate for your application, then you should batch reads until you have enough for an entire chunk instead of issuing several LOB read calls that operate on the same LOB chunk.

Syntax

Use the following syntax references for each programmatic environment:

LOB array APIs can be used to read/write LOB data in multiple pieces. This can be done by using polling method or a callback function.Here data is read/written in multiple pieces sequentially for the array of locators. For polling, the API would return to the application after reading/writing each piece with the array_iter parameter (OUT) indicating the index of the locator for which data is read/written. With a callback, the function is called after reading/writing each piece with array_iter as IN parameter.

Note that:

It is possible to read/write data for a few of the locators in one piece and read/write data for other locators in multiple pieces. Data is read/written in one piece for locators which have sufficient buffer lengths to accommodate the whole data to be read/written.

Your application can use different amount value and buffer lengths for each locator.

Your application can pass zero as the amount value for one or more locators indicating pure streaming for those locators. In the case of reading, LOB data is read to the end for those locators. For writing, data is written until OCI_LAST_PIECE is specified for those locators.

LOB Array Read in Polling Mode

The following example reads 10Kbytes of data for each of 10 locators with 1Kbyte buffer size. Each locator needs 10 pieces to read the complete data. OCILobArrayRead() must be called 100 (10*10) times to fetch all the data.First we call OCILobArrayRead() with OCI_FIRST_PIECE as piece parameter. This call returns the first 1K piece for the first locator.Next OCILobArrayRead() is called in a loop until the application finishes reading all the pieces for the locators and returns OCI_SUCCESS. In this example it loops 99 times returning the pieces for the locators sequentially.

The following example reads 10Kbytes of data for each of 10 locators with 1Kbyte buffer size. Each locator needs 10 pieces to read all the data. The callback function is called 100 (10*10) times to return the pieces sequentially.

Copying All or Part of One LOB to Another LOB

This section describes how to copy all or part of a LOB to another LOB. These APIs copy an amount of data you specify from a source LOB to a destination LOB.

Usage Notes

Note the following issues when using this API.

Specifying Amount of Data to Copy

The value you pass for the amount parameter to the DBMS_LOB.COPY function must be one of the following:

An amount less than or equal to the actual size of the data you are loading.

The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE.Passing this value causes the function to read the entire LOB. This is a useful technique for reading the entire LOB without introspecting the size of the LOB.

Note that for character data, the amount is specified in characters, while for binary data, the amount is specified in bytes.

Locking the Row Prior to Updating

If you plan to update a LOB value, then you must lock the row containing the LOB prior to updating. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECTFORUPDATE statement in SQL and PL/SQL programs, or by using an OCIpin or lock function in OCI programs.

For OCI, the buffer can be written to the LOB in a single piece with this call; alternatively, it can be rendered piecewise using callbacks or a standard polling method.

Writing Piecewise: When to Use Callbacks or Polling

If the value of the piece parameter is OCI_FIRST_PIECE, then data must be provided through callbacks or polling.

If a callback function is defined in the cbfp parameter, then this callback function is called to get the next piece after a piece is written to the pipe. Each piece is written from bufp.

If no callback function is defined, then OCILobWriteAppend2() returns the OCI_NEED_DATA error code. The application must call OCILobWriteAppend2() again to write more pieces of the LOB. In this mode, the buffer pointer and the length can be different in each call if the pieces are of different sizes and from different locations. A piece value of OCI_LAST_PIECE terminates the piecewise write.

Locking the Row Prior to Updating Prior to updating a LOB value using the PL/SQL DBMS_LOB package or the OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of an SQL SELECTFORUPDATE statement in SQL and PL/SQL programs, or by using an OCIpin or lock function in OCI programs.

The most efficient way to write large amounts of LOB data is to use OCILobWrite2() with the streaming mechanism enabled, and using polling or a callback. If you know how much data is written to the LOB, then specify that amount when calling OCILobWrite2(). This ensures that LOB data on the disk is contiguous. Apart from being spatially efficient, the contiguous structure of the LOB data makes reads and writes in subsequent operations faster.

Chunk Size

A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE. In OCI, use OCILobGetChunkSize().

Use a Multiple of the Returned Value to Improve Write Performance

To improve performance, run write requests using a multiple of the value returned by one of these functions. The reason for this is that the LOB chunk is versioned for every write operation. If all writes are done on a chunk basis, then no extra or excess versioning is incurred or duplicated. If it is appropriate for your application, then you should batch writes until you have enough for an entire chunk instead of issuing several LOB write calls that operate on the same LOB chunk.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB Package or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECTFORUPDATE statement in SQL and PL/SQL programs, or by using an OCIpin or lock function in OCI programs.

When you are passing a hexadecimal string to DBMS_LOB.WRITE() to write data to a BLOB, use the following guidelines:

The amount parameter should be <= the buffer length parameter

The length of the buffer should be ((amount*2) - 1). This guideline exists because the two characters of the string are seen as one hexadecimal character (and an implicit hexadecimal-to-raw conversion takes place), that is, every two bytes of the string are converted to one raw byte.

The following example writes 10Kbytes of data for each of 10 locators with a 1K buffer size. OCILobArrayWrite() has to be called 100 (10 times 10) times to write all the data. The function is used in a similar manner to OCILobWrite2().

The following example writes 10Kbytes of data for each of 10 locators with a 1K buffer size. A total of 100 pieces must be written (10 pieces for each locator). The first piece is provided by the OCILobArrayWrite() call. The callback function is called 99 times to get the data for subsequent pieces to be written.

Prior to updating a LOB value using the PL/SQL DBMS_LOB Package, or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of:

Prior to updating a LOB value using the PL/SQL DBMS_LOB Package or OCI, you must lock the row containing the LOB. While INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SELECTFORUPDATE statement in SQL and PL/SQL programs, or by using the OCIpin or lock function in OCI programs.

Java (JDBC): Determining Whether a BLOB Is Temporary

To see if a BLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current BLOB object is temporary, or pass the BLOB object to the static isTemporary method to determine whether the specified BLOB object is temporary. These two methods are defined inlistempb.java.

This JDBC API replaces previous work-arounds that use DBMS_LOB.isTemporary().

To determine whether a CLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current CLOB object is temporary, or pass the CLOB object to the static isTemporary method. These two methods are defined in listempc.java.

Converting a BLOB to a CLOB

You can convert a BLOB instance to a CLOB using the PL/SQL procedure DBMS_LOB.CONVERTTOCLOB. This technique is convenient if you have character data stored in binary format that you want to store in a CLOB. You specify the character set of the binary data when calling this procedure. See Oracle Database PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.

Converting a CLOB to a BLOB

You can convert a CLOB instance to a BLOB instance using the PL/SQL procedure DBMS_LOB.CONVERTTOBLOB. This technique is a convenient way to convert character data to binary data using LOB APIs. See Oracle Database PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.

Ensuring Read Consistency

This script can be used to ensure that hot backups can be taken of tables that have NOLOGGING or FILESYSTEM_LIKE_LOGGING LOBs and have a known recovery point with no read inconsistencies:

Back up the archive logs generated by the database. At the minimum, archive logs between start SCN and end SCN (including both SCN points) must be backed up.

To restore to a point with no read inconsistency, restore to end SCN as your incomplete recovery point. If recovery is done to an SCN after end SCN, there can be read inconsistency in the NOLOGGING LOBs.

For SecureFiless, if a read inconsistency is found during media recovery, the database treats the inconsistent blocks as holes and fills BLOBs with 0's and CLOBs with fill characters.