DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator representing a LOB that exists in the database tablespaces or external file system, before you call the routine.

• Persistent LOBs: First use SQL to define tables that contain LOB columns, and subsequently you can use SQL to initialize or populate the locators in these LOB columns.

• External LOBs: Define a DIRECTORY object that maps to a valid physical directory containing the external LOBs that you intend to access. These files must exist, and have READ permission for Oracle Server to process. If your operating system uses case-sensitive path names, then specify the directory in the correct case.

Once the LOBs are defined and created, you may then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value.

Examples provided with each DBMS_LOB routine illustrate this in the following sections.

The following guidelines apply to offset and amount parameters used in procedures in the DBMS_LOB PL/SQL package:

• For character data—in all formats, fixed-width and varying-width—the amount and offset parameters are in characters. This applies to operations on CLOB and NCLOB data types.

• For binary data, the offset and amount parameters are in bytes. This applies to operations on BLOB data types.

• When using the following procedures:

  • DBMS_LOB.LOADFROMFILE

  • DBMS_LOB.LOADBLOBFROMFILE

  • DBMS_LOB.LOADCLOBFROMFILE

  you cannot specify an amount parameter with a value larger than the size of the BFILE you are loading from. To load the entire BFILE with these procedures, you must specify either the exact size of the BFILE, or the maximum allowable storage limit.

• When using DBMS_LOB.READ, the amount parameter can be larger than the size of the data. The amount should be less than or equal to the size of the buffer. The buffer size is limited to 32K.

  See Also:

  • Loading a LOB with Data from a BFILE

  • About Loading a BLOB with Data from a BFILE

  • Loading a CLOB or NCLOB with Data from a BFILE

  • About Reading Data from a LOB

Here is a table of DBMS_LOB procedures:

When using OCILobLoadFromFile2() you cannot specify amount larger than the length of the BFILE. To load the entire BFILE, you can pass the value returned by OCILobGetStorageLimit().

To read to the end of a LOB using OCILobRead2(), you specify an amount equal to the value returned by OCILobGetStorageLimit().

Special care must be taken when assigning OCILobLocator pointers in an OCI program—using the "=" assignment operator. Pointer assignments create a shallow copy of the LOB. After the pointer assignment, the source and target LOBs point to the same copy of data.

These semantics are different from using LOB APIs, such as OCILobAssign() or OCILobLocatorAssign() to perform assignments. When the these APIs are used, the locators logically point to independent copies of data after assignment.

For temporary LOBs, before performing pointer assignments, you must ensure that any temporary LOB in the target LOB locator is freed by calling OCIFreeTemporary(). In contrast, when OCILobLocatorAssign() is used, the original temporary LOB in the target LOB locator variable, if any, is freed automatically before the assignment happens.

Before you reuse a LOB locator in a define or an out-bind variable in a SQL statement, you must free any temporary LOB in the existing LOB locator buffer using OCIFreeTemporary().

OCI functions that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:

• To modify persistent LOBs, see **INTERNAL XREF ERROR**

• To read or examine LOB values, see **INTERNAL XREF ERROR**

• To create or free temporary LOB, or check if Temporary LOB exists, see **INTERNAL XREF ERROR**

• For read only functions on external LOBs (BFILEs), see **INTERNAL XREF ERROR**

• To operate on LOB locators, see **INTERNAL XREF ERROR**

• To open and close LOBs, see **INTERNAL XREF ERROR**

Further OCI examples are provided in:

• Using LOB APIs

• LOB APIs for BFILE Operations

OCCI provides these classes that allow you to use different types of LOB instances as objects in your C++ application:

• Clob class to access and modify data stored in internal CLOBs and NCLOBs

• Blob class to access and modify data stored in internal BLOBs

• Bfile class to access and read data stored in external LOBs (BFILEs)

  See Also:

  Syntax information on these classes and details on OCCI in general is available in theOracle C++ Call Interface Programmer's Guide.

In OCCI, for fixed-width client-side character sets, these rules apply:

• Clob: offset and amount parameters are always in characters

• Blob: offset and amount parameters are always in bytes

• Bfile: offset and amount parameters are always in bytes

The following rules apply only to varying-width client-side character sets:

• Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:

  • Clob(): in characters

  • Blob(): in bytes

  • Bfile(): in bytes

• Amount parameter: The amount parameter is always as indicated:

  • Clob: in characters, when referring to a server-side LOB

  • Blob: in bytes, when referring to a client-side buffer

  • Bfile: in bytes, when referring to a client-side buffer

• length(): Regardless of whether the client-side character set is varying-width, the output length is as follows:

  • Clob.length(): in characters

  • Blob.length(): in bytes

  • Bfile.length(): in bytes

• Clob.read() and Blob.read(): With client-side character set of varying-width, CLOBs and NCLOBs:

  • Input amount is in characters. Input amount refers to the number of characters to read from the server-side CLOB or NCLOB.

  • Output amount is in bytes. Output amount indicates how many bytes were read into the OCCI buffer parameter, buffer.

• Clob.write() and Blob.write(): With client-side character set of varying-width, CLOBs and NCLOBs:

  • Input amount is in bytes. Input amount refers to the number of bytes of data in the OCCI input buffer, buffer.

  • Output amount is in characters. Output amount refers to the number of characters written into the server-side CLOB or NCLOB.

For all other OCCI LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOBs and NCLOBs. These include the following:

• Clob.copy()

• Clob.erase()

• Clob.trim()

• For LoadFromFile functionality, overloaded Clob.copy()

All these operations refer to the amount of LOB data on the server.

The copy() method on Clob and Blob enables you to load data from a BFILE. You can pass one of the following values for the amount parameter to this method:

• An amount smaller than the size of the BFILE to load a portion of the data

• An amount equal to the size of the BFILE to load all of the data

• The UB8MAXVAL constant to load all of the BFILE data

You cannot specify an amount larger than the length of the BFILE.

The read() method on an Clob, Blob, or Bfile object, reads data from a BFILE. You can pass one of these values for the amount parameter to specify the amount of data to read:

• An amount smaller than the size of the BFILE to load a portion of the data

• An amount equal to the size of the BFILE to load all of the data

• 0 (zero) to read until the end of the BFILE in streaming mode

You cannot specify an amount larger than the length of the BFILE.

OCCI methods that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:

• To modify persistent LOBs, see Table 10-15

• To read or examine LOB values, see Table 10-16

• For read only methods on external LOBs (BFILEs), see Table 10-17

• Other LOB OCCI methods are described in Table 10-18

• To open and close LOBs, see Table 10-19

1. Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.
2. SELECT a LOB locator into a LOB locator pointer variable.
3. Use this variable in the embedded SQL LOB statement to access and manipulate the LOB value.

   See Also:

   APIs for supported LOB operations are described in detail in:

   • Operations Specific to Persistent and Temporary LOBs

   • Using LOB APIs

   • LOB APIs for BFILE Operations

Pro*C/C++ statements that operate on BLOBs, CLOBs, and NCLOBs are listed in the following tables:

• To modify persistent LOBs, see **INTERNAL XREF ERROR**

• To read or examine LOB values, see **INTERNAL XREF ERROR**

• To create or free temporary LOB, or check if Temporary LOB exists, see **INTERNAL XREF ERROR**

• To operate close and 'see if file exists' functions on BFILEs, see **INTERNAL XREF ERROR**

• To operate on LOB locators, see **INTERNAL XREF ERROR**

• To open or close LOBs or BFILEs, see **INTERNAL XREF ERROR**

1. Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.
2. SELECT a LOB locator into a LOB locator pointer variable
3. Use this variable in an embedded SQL LOB statement to access and manipulate the LOB value.

   See Also:

   APIs for supported LOB operations are described in detail in:

   • Operations Specific to Persistent and Temporary LOBs

   • Using LOB APIs

   • LOB APIs for BFILE Operations

The following Pro*COBOL statements operate on BLOBs, CLOBs, NCLOBs, and BFILEs:

• To modify persistent LOBs, see **INTERNAL XREF ERROR**

• To read or examine internal and external LOB values, see **INTERNAL XREF ERROR**

• To create or free temporary LOB, or check LOB locator, see **INTERNAL XREF ERROR**

• To operate close and 'see if file exists' functions on BFILEs, see **INTERNAL XREF ERROR**

• To operate on LOB locators, see **INTERNAL XREF ERROR**

• To open or close persistent LOBs or BFILEs, see **INTERNAL XREF ERROR**

To improve the performance of SecureFiles, there is a Zero-copy Input/Output protocol on the server that is only available to network clients that support the new Net NS Data transfer protocol.

To determine if a LOB is a SecureFiles or not, use the method

public boolean isSecureFile() throws SQLException

If it is a SecureFiles, TRUE is returned.

Use this thin connection property to disable (by setting to FALSE) the Zero-copy Input/Output protocol:

oracle.net.useZeroCopyIO

For further JDBC syntax and information about using JDBC with LOBs:

The following JDBC methods operate on BLOBs, CLOBs, and BFILEs:

• BLOBs:

  • To modify BLOB values, see **INTERNAL XREF ERROR**

  • To read or examine BLOB values, see **INTERNAL XREF ERROR**

  • For streaming BLOB data, see Table 10-34

  • Temporary BLOBs: Creating, checking if BLOB is open, and freeing. See **INTERNAL XREF ERROR**

  • Opening, closing, and checking if BLOB is open, see **INTERNAL XREF ERROR**

  • Truncating BLOBs, see **INTERNAL XREF ERROR**

  • BLOB streaming API, see **INTERNAL XREF ERROR**

• CLOBs:

  • To read or examine CLOB values, see **INTERNAL XREF ERROR**

  • For streaming CLOB data, see Table 10-37

  • To modify CLOBs, see **INTERNAL XREF ERROR**

• Temporary CLOBs:

  • Opening, closing, and checking if CLOB is open, see **INTERNAL XREF ERROR**

  • Truncating CLOBs, see **INTERNAL XREF ERROR**

  • CLOB streaming API, see **INTERNAL XREF ERROR**

• BFILEs:

  • To read or examine BFILEs, see **INTERNAL XREF ERROR**

  • For streaming BFILE data, see Table 10-39

  • Opening, closing, and checking if BFILE is open, see **INTERNAL XREF ERROR**

  • BFILE streaming API, see **INTERNAL XREF ERROR**

Oracle Database JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace workarounds that use the following procedures from the DBMS_LOB PL/SQL package in prior releases:

• DBMS_LOB.createTemporary()

• DBMS_LOB.isTemporary()

• DBMS_LOB.freeTemporary()

oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 10-41 lists the Oracle extension APIs in oracle.sql.CLOB for accessing temporary CLOBs.

Oracle Database JDBC drivers contain APIs to explicitly open and close LOBs. These APIs replace previous techniques that use DBMS_LOB.open() and DBMS_LOB.close().

oracle.sql.BLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Blob interface. Table 10-42 lists the Oracle extension APIs in oracle.sql.BLOB that open and close BLOBs.

Class oracle.sql.CLOB is the Oracle JDBC driver implementation of the standard JDBC java.sql.Clob interface. Table 10-43 lists the Oracle extension APIs in oracle.sql.CLOB to open and close CLOBs.

oracle.sql.BFILE class wraps the database BFILE object. Table 10-44 lists the Oracle extension APIs in oracle.sql.BFILE for opening and closing BFILEs.

Oracle Database JDBC drivers contain APIs to truncate persistent LOBs. These APIs replace previous techniques that used DBMS_LOB.trim().

The JDBC interface provided with the database includes LOB streaming APIs that enable you to read from or write to a LOB at the requested position from a Java stream.

The oracle.sql.BLOB class implements the standard JDBC java.sql.Blob interface. Table 10-47 lists BLOB Streaming APIs.

These APIs are defined as follows:

/** 
 * Write to the BLOB from a stream at the requested position. 
 * 
 * @param pos is the position data to be put. 
 * @return a output stream to write data to the BLOB 
 */ 
public java.io.OutputStream setBinaryStream(long pos) throws SQLException

/** 
 * Read from the BLOB as a stream at the requested position. 
 * 
 * @param pos is the position data to be read. 
 * @return a output stream to write data to the BLOB 
 */ 
public java.io.InputStream getBinaryStream(long pos) throws SQLException

The oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 10-48 lists the CLOB streaming APIs.

These APIs are defined as follows:

/** 
  * Write to the CLOB from a stream at the requested position. 
  * @param pos is the position data to be put. 
  * @return a output stream to write data to the CLOB 
  */ 
public java.io.OutputStream setAsciiStream(long pos) throws SQLException 

/** 

     * Write to the CLOB from a stream at the requested position. 
     * @param pos is the position data to be put. 
     * @return a output stream to write data to the CLOB 
     */ 
  public java.io.Writer setCharacterStream(long pos) throws SQLException 

    /** 
     * Read from the CLOB as a stream at the requested position. 
     * @param pos is the position data to be put. 
     * @return a output stream to write data to the CLOB 
     */ 
  public java.io.InputStream getAsciiStream(long pos) throws SQLException 

   /** 
    * Read from the CLOB as a stream at the requested position. 
    * @param pos is the position data to be put. 
    * @return a output stream to write data to the CLOB 
    */ 
   public java.io.Reader getCharacterStream(long pos) throws SQLException

oracle.sql.BFILE class wraps the database BFILEs. Table 10-49 lists the Oracle extension APIs in oracle.sql.BFILE that reads BFILE content from the requested position.

These APIs are defined as follows:

/** 
 * Read from the BLOB as a stream at the requested position. 
 * 
 * @param pos is the position data to be read. 
 * @return a output stream to write data to the BLOB 
 */ 
public java.io.InputStream getBinaryStream(long pos) throws SQLException

An empty BLOB can be created from the following API from oracle.sql.BLOB:

public static BLOB empty_lob () throws SQLException 

Similarly, the following API from oracle.sql.CLOB creates an empty CLOB:

public static CLOB empty_lob () throws SQLException 

Empty LOB instances are created by JDBC drivers without making database round trips. Empty LOBs can be used in the following cases:

• set APIs of PreparedStatement

• update APIs of updatable result set

• attribute value of STRUCTs

• element value of ARRAYs

  Note:

  Empty LOBs are special marker LOBs but not real LOB values.

JDBC applications cannot read or write to empty LOBs created from the preceding APIs. An ORA-17098 "Invalid empty lob operation" results if your application attempts to read/write to an empty LOB.