13 Overview of Supplied LOB APIs

Provide a LOB Locator Before Running the DBMS_LOB Routine

As described in more detail in the following, 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. See "Directory Objects" for more information.

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 will illustrate this in the following sections.

Guidelines for Offset and Amount Parameters in DBMS_LOB Operations

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.

  See Also:

  • "Loading a LOB with Data from a BFILE"

  • "Loading a BLOB with Data from a BFILE"

  • "Loading a CLOB or NCLOB with Data from a BFILE"

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

  "Reading Data from a LOB"

Determining Character Set ID

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. UTF16 does not work because it has no character set name. Use character set ID = 1000 for UTF16. Although UTF16 is not allowed as a database or national character set, the APIs in DBMS_LOB support it for database conversion purposes. DBMS_LOB.LOADCLOBFROMFILE and other procedures in DBMS_LOB take character set ID, not character set name, as an input.

PL/SQL Functions and Procedures for LOBs

PL/SQL functions and procedures that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are summarized in the following:

• To modify persistent LOB values, see Table 13-4

• To read or examine LOB values, see Table 13-5

• To create, free, or check on temporary LOBs, see Table 13-6

• For read-only functions on external LOBs (BFILEs), see Table 13-7

• To open or close a LOB, or check if LOB is open, see Table 13-8

• To perform archive management on SecureFiles, see "Using DBFS Links"

PL/SQL Functions and Procedures to Modify LOB Values

Here is a table of DBMS_LOB procedures:

PL/SQL Functions and Procedures for Introspection of LOBs

PL/SQL Operations on Temporary LOBs

PL/SQL Read-Only Functions and Procedures for BFILEs

PL/SQL Functions and Procedures to Open and Close Internal and External LOBs

These procedures are described in detail for specific LOB operations, such as, INSERT a row containing a LOB, in "Opening Persistent LOBs with the OPEN and CLOSE Interfaces".

Prefetching of LOB Data, Length, and Chunk Size

To improve OCI access of smaller LOBs, LOB data can be prefetched and cached while also fetching the locator. This applies to internal LOBs, temporary LOBs, and BFILEs.

Setting the CSID Parameter for OCI LOB APIs

If you want to read or write data in 2-byte Unicode format, then set the csid (character set ID) parameter in OCILobRead2() and OCILobWrite2() to OCI_UTF16ID. The csid parameter indicates the character set id for the buffer parameter. You can set the csid parameter to any character set ID. If the csid parameter is set, then it will override the NLS_LANG environment variable.

Fixed-Width and Varying-Width Character Set Rules for OCI

In OCI, for fixed-width client-side character sets, the following rules apply:

• CLOBs and NCLOBs: offset and amount parameters are always in characters

• BLOBs and BFILEs: 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:

  • CLOBs and NCLOBs: in characters

  • BLOBs and BFILEs: in bytes

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

  • When referring to a server-side LOB: in characters

  • When referring to a client-side buffer: in bytes

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

  • CLOBs and NCLOBs: in characters

  • BLOBs and BFILEs: in bytes

• OCILobRead2(): 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 buffer bufp.

• OCILobWrite2(): With client-side character set of varying-width, CLOBs and NCLOBs:

  • Input amount is in bytes. The input amount refers to the number of bytes of data in the input buffer bufp.

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

OCILobLoadFromFile2() Amount Parameter

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

OCILobRead2() Amount Parameter

To read to the end of a LOB using OCILobRead2(), you specify an amount equal to the value returned by OCILobGetStorageLimit(). See "Reading Data from a LOB" for more information.

OCILobLocator Pointer Assignment

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.

LOB Locators in Defines and Out-Bind Variables in OCI

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

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

• To modify persistent LOBs, see Table 13-9

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

• To create or free temporary LOB, or check if Temporary LOB exists, see Table 13-11

• For read only functions on external LOBs (BFILEs), see Table 13-12

• To operate on LOB locators, see Table 13-13

• For LOB buffering, see Table 13-14

• To open and close LOBs, see Table 13-15

OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

OCI Functions to Read or Examine Persistent LOB and External LOB (BFILE) Values

OCI Functions for Temporary LOBs

OCI Read-Only Functions for BFILEs

OCI LOB Locator Functions

OCI LOB-Buffering Functions

OCI Functions to Open and Close Internal and External LOBs

OCI LOB Examples

Further OCI examples are provided in:

• Chapter 21, " Using LOB APIs"

• Chapter 22, "LOB APIs for BFILE Operations"

See also Appendix B, "OCI Demonstration Programs" in Oracle Call Interface Programmer's Guide, for further OCI demonstration script listings.

Further Information About OCI

For further information and features of OCI, refer to the OTN Web site, http://www.oracle.com/technology/ for OCI features and frequently asked questions.

OCCI Classes for LOBs

OCCI provides the following 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 the Oracle C++ Call Interface Programmer's Guide.

Fixed-Width Character Set Rules

In OCCI, for fixed-width client-side character sets, the following 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

Varying-Width Character Set Rules

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

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

Offset and Amount Parameters for Other OCCI Operations

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.

Amount Parameter for OCCI LOB copy() Methods

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.

Amount Parameter for OCCI read() Operations

The read() method on an Clob, Blob, or Bfile object, reads data from a BFILE. You can pass one of the following 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.

Further Information About OCCI

OCCI Methods That Operate on BLOBs, BLOBs, NCLOBs, and BFILEs

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

• To modify persistent LOBs, see Table 13-16

• To read or examine LOB values, see Table 13-17

• For read only methods on external LOBs (BFILEs), see Table 13-18

• Other LOB OCCI methods are described in Table 13-19

• To open and close LOBs, see Table 13-20

OCCI Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

OCCI Methods to Read or Examine Persistent LOB and BFILE Values

OCCI Read-Only Methods for BFILEs

Other OCCI LOB Methods

OCCI Methods to Open and Close Internal and External LOBs

First Provide an Allocated Input Locator Pointer That Represents LOB

Unlike locators in PL/SQL, locators in Pro*C/C++ are mapped to locator pointers which are then used to refer to the LOB or BFILE value.

To successfully complete an embedded SQL LOB statement you must do the following:

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:

   • Chapter 19, "Operations Specific to Persistent and Temporary LOBs"

   • Chapter 21, " Using LOB APIs"

   • Chapter 22, "LOB APIs for BFILE Operations"

Pro*C/C++ Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

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

• To modify persistent LOBs, see Table 13-21

• To read or examine LOB values, see Table 13-22

• To create or free temporary LOB, or check if Temporary LOB exists, see Table 13-23

• To operate close and 'see if file exists' functions on BFILEs, see Table 13-24

• To operate on LOB locators, see Table 13-25

• For LOB buffering, see Table 13-26

• To open or close LOBs or BFILEs, see Table 13-27

Pro*C/C++ Embedded SQL Statements to Modify Persistent LOB Values

Pro*C/C++ Embedded SQL Statements for Introspection of LOBs

Pro*C/C++ Embedded SQL Statements for Temporary LOBs

Pro*C/C++ Embedded SQL Statements for BFILEs

Pro*C/C++ Embedded SQL Statements for LOB Locators

Pro*C/C++ Embedded SQL Statements for LOB Buffering

Pro*C/C++ Embedded SQL Statements to Open and Close LOBs

First Provide an Allocated Input Locator Pointer That Represents LOB

Unlike locators in PL/SQL, locators in Pro*COBOL are mapped to locator pointers which are then used to refer to the LOB or BFILE value. For the successful completion of an embedded SQL LOB statement you must perform the following:

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:

   • Chapter 19, "Operations Specific to Persistent and Temporary LOBs"

   • Chapter 21, " Using LOB APIs"

   • Chapter 22, "LOB APIs for BFILE Operations"

Where the Pro*COBOL interface does not supply the required functionality, you can call OCI using C. Such an example is not provided here because such programs are operating system dependent.

Pro*COBOL Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

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

• To modify persistent LOBs, see Table 13-28

• To read or examine internal and external LOB values, see Table 13-29

• To create or free temporary LOB, or check LOB locator, see Table 13-30

• To operate close and 'see if file exists' functions on BFILEs, see Table 13-31

• To operate on LOB locators, see Table 13-32

• For LOB buffering, see Table 13-33

• To open or close persistent LOBs or BFILEs, see Table 13-34

Pro*COBOL Embedded SQL Statements to Modify Persistent LOB Values

Pro*COBOL Embedded SQL Statements for Introspection of LOBs

Pro*COBOL Embedded SQL Statements for Temporary LOBs

Pro*COBOL Embedded SQL Statements for BFILEs

Pro*COBOL Embedded SQL Statements for LOB Locators

Pro*COBOL Embedded SQL Statements for LOB Buffering

Pro*COBOL Embedded SQL Statements for Opening and Closing LOBs and BFILEs

OO4O Syntax Reference

The OO4O syntax reference and further information is viewed from the OO4O online help. Oracle Objects for OLE (OO4O), is a Windows-based product included with the database.

OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators

These interfaces encapsulate LOB locators, so you do not deal directly with locators, but instead, can use methods and properties provided to perform operations and get state information.

Example of OraBlob and OraBfile

The following example shows usage of OraBlob and OraBfile.

Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob, OraMyBfile as OraBFile

OraConnection.BeginTrans
set OraDyn = OraDb.CreateDynaset("select * from print_media order by product_id", ORADYN_DEFAULT)
set OraSound1 = OraDyn.Fields("Sound").value
set OraSoundClone = OraSound1

OraParameters.Add "id", 1,ORAPARAM_INPUT
OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT
OraParameters("mybfile").ServerType = ORATYPE_BFILE

OraDatabase.ExecuteSQL ("begin  GetBFile(:id, :mybfile ") end")

Set OraMyBFile = OraParameters("mybfile").value
'Go to Next row
OraDyn.MoveNext

OraDyn.Edit
'Lets update OraSound1 data with that from the BFILE
OraSound1.CopyFromBFile  OraMyBFile
OraDyn.Update

OraDyn.MoveNext
'Go to Next row
OraDyn.Edit
'Lets update OraSound1 by appending with LOB data from 1st row represented by 
'OraSoundClone
OraSound1.Append  OraSoundClone
OraDyn.Update

OraConnection.CommitTrans

In the preceding example:

OraSound1 — represents the locator for the current row in the dynaset OraSoundClone — represents the locator for the 1st row.

A change in the current row (say a OraDyn.MoveNext) means the following:

OraSound1 — will represent the locator for the 2nd row.

OraSoundClone — will represent the locator in the 1st row. OraSoundClone only refers the locator for the 1st row irrespective of any OraDyn row navigation).

OraMyBFile — refers to the locator obtained from an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure, either by doing an OraDatabase.ExecuteSQL.

OO4O Methods and Properties to Access Data Stored in LOBs

Oracle Objects for OLE (OO4O) includes methods and properties that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs.

The following OO4O methods and properties operate on BLOBs, CLOBs, NCLOBs, and BFILEs:

• To modify persistent LOBs, see Table 13-35

• To read or examine internal and external LOB values, see Table 13-36

• To open and close BFILEs, see Table 13-37

• For LOB buffering, see Table 13-38

• Properties such as to see if LOB is NULL, or to get or set polling amount, see Table 13-39

• For read-only BFILE methods, see Table 13-40

• For BFILE properties, see Table 13-41

OO4O Methods to Modify BLOB, CLOB, and NCLOB Values

OO4O Methods to Read or Examine Internal and External LOB Values

OO4O Methods to Open and Close External LOBs (BFILEs)

OO4O Methods for Persistent LOB Buffering

OO4O Properties for Operating on LOBs

OO4O Read-Only Methods for External Lobs (BFILEs)

OO4O Properties for Operating on External LOBs (BFILEs)

Modifying Internal Persistent LOBs Using Java

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle, or end of a persistent LOB in Java by means of the JDBC API using the classes:

• oracle.sql.BLOB

• oracle.sql.CLOB

These classes implement java.sql.Blob and java.sql.Clob interfaces according to the JDBC 3.0 specification, which has methods for LOB modification. They also include legacy Oracle proprietary methods for LOB modification. These legacy methods are marked as deprecated and will be removed in a future release.

Starting in Oracle release 11.1 the minimum supported version of the JDK is JDK5. JDK6 will also be supported when it is released by Sun Microsystems, Inc. To use JDK5, place ojdbc5.jar in your CLASSPATH. To use JDK6, place ojdbc6.jar in your CLASSPATH. ojdbc5.jar supports the JDBC 3.0 specification and ojdbc6.jar supports the JDBC4.0 specification which is new with JDK6.

Oracle recommends that you discontinue use of the deprecated proprietary APIs described in Table 7-42. They will be removed in a future release.

Reading Internal Persistent LOBs and External LOBs (BFILEs) With Java

With JDBC you can use Java to read both internal persistent LOBs and external LOBs (BFILEs).

Calling DBMS_LOB Package from Java (JDBC)

Any LOB functionality not provided by these classes can be accessed by a call to the PL/SQL DBMS_LOB package. This technique is used repeatedly in the examples throughout this manual.

LOB Prefetching to Improve Performance

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. The SELECT parse, execution, and fetch occurs in one round trip. For large LOBs (larger than five times the prefetch size) less improvement is seen.

To configure the prefetch size, a connection property, oracle.jdbc.defaultLobPrefetchSize, defined as a constant in oracle.jdbc.OracleConnection can be used. Values can be -1 to disable prefetching, 0 to enable prefetching for metadata only, or any value greater than 0 which represents the number of bytes for BLOBs and characters for CLOBs, to be prefetched along with the locator during fetch operations.

You can change the prefetch size for a particular statement by using a method defined in oracle.jdbc.OracleStatement:

void setLobPrefetchSize(int size) throws SQLException;

The statement level setting overrides the setting at the connection level. This setting can also be overriden at the column level through the extended defineColumnType method, where the size represents the number of bytes (or characters for CLOB) to prefetch. The possible values are the same as for the connection property. The type must be set to OracleTypes.CLOB for a CLOB column and OracleTypes.BLOB for a BLOB column. This method throws SQLException if the value is less than -1. To complement the statement there is in oracle.jdbc.OracleStatement:

int getLobPrefetchSize();

Zero-Copy Input/Output for SecureFiles to Improve Performance

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 SecureFile or not, use the method

public boolean isSecureFile() throws SQLException

If it is a SecureFile, TRUE is returned.

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

oracle.net.useZeroCopyIO

Referencing LOBs Using Java (JDBC)

You can get a reference to any of the preceding LOBs in the following two ways:

• As a column of an OracleResultSet

• As an OUT type PL/SQL parameter from an OraclePreparedStatement

JDBC Syntax References and Further Information

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

JDBC Methods for Operating on LOBs

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

• BLOBs:

  • To modify BLOB values, see Table 13-44

  • To read or examine BLOB values, see Table 13-45

  • For BLOB buffering, see Table 13-46

  • Temporary BLOBs: Creating, checking if BLOB is open, and freeing. See Table 13-54

  • Opening, closing, and checking if BLOB is open, see Table 13-54

  • Truncating BLOBs, see Table 13-57

  • BLOB streaming API, see Table 13-59

• CLOBs:

  • To read or examine CLOB values, see Table 13-48

  • For CLOB buffering, see Table 13-49

  • To modify CLOBs, see Table 13-59

• Temporary CLOBs:

  • Opening, closing, and checking if CLOB is open, see Table 13-55

  • Truncating CLOBs, see Table 13-58

  • CLOB streaming API, see Table 13-60

• BFILEs:

  • To read or examine BFILEs, see Table 13-50

  • For BFILE buffering, see Table 13-51

  • Opening, closing, and checking if BFILE is open, see Table 13-56

  • BFILE streaming API, see Table 13-61

JDBC oracle.sql.BLOB Methods to Modify BLOB Values

JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values

JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering

JDBC oracle.sql.CLOB Methods to Modify CLOB Values

JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Value

JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering

JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values

JDBC oracle.sql.BFILE Methods and Properties for BFILE Buffering

JDBC Temporary LOB APIs

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()

JDBC: Opening and Closing LOBs

oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 13-53 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().

JDBC: Opening and Closing BLOBs

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

/** 
 * Open a BLOB in the indicated mode. Valid modes include MODE_READONLY,
 * and MODE_READWRITE. It is an error to open the same LOB twice. 
 */ 
public void open (int mode) throws SQLException

public static final int MODE_READONLY 
public static final int MODE_READWRITE 

BLOB blob = ... 
blob.open (BLOB.MODE_READWRITE);

/** 
 * Check whether the BLOB is opened. 
 * @return true if the LOB is opened. 
 */ 
 public boolean isOpen () throws SQLException

BLOB blob = ... 
// See if the BLOB is opened 
boolean isOpen = blob.isOpen ();

/** 
  * Close a previously opened BLOB. 
  */ 
public void close () throws SQLException

BLOB blob = ... 
// close the BLOB 
blob.close ();

JDBC: Opening and Closing CLOBs

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

/** 
 * Open a CLOB in the indicated mode. Valid modes include MODE_READONLY,
 * and MODE_READWRITE. It is an error to open the same LOB twice. 
 */ 
public void open (int mode) throws SQLException

public static final int MODE_READONLY 
public static final int MODE_READWRITE 

CLOB clob = ... 
clob.open (CLOB.MODE_READWRITE);

/** 
  * Check whether the CLOB is opened. 
  * @return true if the LOB is opened. 
  */ 
public boolean isOpen () throws SQLException

CLOB clob = ... 
 // See if the CLOB is opened 
 boolean isOpen = clob.isOpen ();

/** 
* Close a previously opened CLOB. 
*/ 
public void close () throws SQLException

CLOB clob = ... 
// close the CLOB 
clob.close ();

JDBC: Opening and Closing BFILEs

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

/** 
 * Open a external LOB in the read-only mode. It is an error 
 * to open the same LOB twice. 
 */ 
public void open () throws SQLException 

/** 
 * Open a external LOB in the indicated mode. Valid modes include 
 * MODE_READONLY only. It is an error to open the same 
 * LOB twice. 
 */ 
public void open (int mode) throws SQLException

public static final int MODE_READONLY 

BFILE bfile = ... 
bfile.open ();

/** 
 * Check whether the BFILE is opened. 
 * @return true if the LOB is opened. 
 */ 
public boolean isOpen () throws SQLException

BFILE bfile = ... 
// See if the BFILE is opened 
boolean isOpen = bfile.isOpen ();

/** 
 * Close a previously opened BFILE. 
*/ 
public void close () throws SQLException

BFILE bfile = ... 
// close the BFILE 
bfile.close ();

/* 
 * This sample shows how to open/close BLOB and CLOB. 
 */ 

// You need to import the java.sql package to use JDBC 
import java.sql.*; 

// You need to import the oracle.sql package to use oracle.sql.BLOB 
import oracle.sql.*; 

class OpenCloseLob 
{ 
  public static void main (String args []) 
       throws SQLException 
  { 
    // Load the Oracle JDBC driver 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 

    String url = "jdbc:oracle:oci8:@"; 
    try { 
      String url1 = System.getProperty("JDBC_URL"); 
      if (url1 != null) 
        url = url1; 
    } catch (Exception e) { 
      // If there is any security exception, ignore it 
      // and use the default 
    } 

    // Connect to the database 
    Connection conn = 
      DriverManager.getConnection (url, "scott", "password"); 
    // It is faster when auto commit is off 
    conn.setAutoCommit (false); 

    // Create a Statement 
    Statement stmt = conn.createStatement (); 

    try 
    { 
      stmt.execute ("drop table basic_lob_table"); 
    } 
    catch (SQLException e) 
    { 
      // An exception could be raised here if the table did not exist already. 
    } 

// Create a table containing a BLOB and a CLOB 
stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c clob)"); 

// Populate the table 
stmt.execute (
    "insert into basic_lob_table values"
    + " ('one', '010101010101010101010101010101', 'onetwothreefour')"); 

    // Select the lobs 
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Open the lobs 
      System.out.println ("Open the lobs"); 
      blob.open (BLOB.MODE_READWRITE); 
      clob.open (CLOB.MODE_READWRITE); 

      // Check if the lobs are opened 
      System.out.println ("blob.isOpen()="+blob.isOpen()); 
      System.out.println ("clob.isOpen()="+clob.isOpen()); 

      // Close the lobs 
      System.out.println ("Close the lobs"); 
      blob.close (); 
      clob.close (); 

      // Check if the lobs are opened 
      System.out.println ("blob.isOpen()="+blob.isOpen()); 
      System.out.println ("clob.isOpen()="+clob.isOpen()); 
    } 

    // Close the ResultSet 
    rset.close (); 

    // Close the Statement 
    stmt.close (); 

    // Close the connection 
    conn.close (); 
  } 
} 

Truncating LOBs Using JDBC

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

/** 
*Truncate the value of the BLOB to the length you specify in the newlen parameter. 
 * @param newlen the new length of the BLOB. 
 */ 
public void truncate (long newlen) throws SQLException

/** 
*Truncate the value of the CLOB to the length you specify in the newlen parameter.
 * @param newlen the new length of the CLOB. 
 */ 
public void truncate (long newlen) throws SQLException

JDBC BLOB Streaming APIs

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 13-59 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

JDBC CLOB Streaming APIs

The oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 13-60 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

BFILE Streaming APIs

oracle.sql.BFILE class wraps the database BFILEs. Table 13-61 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

/* 
 * This sample shows how to read/write BLOB and CLOB as streams. 
 */ 

import java.io.*; 

// You need to import the java.sql package to use JDBC 
import java.sql.*; 

// You need to import the oracle.sql package to use oracle.sql.BLOB 
import oracle.sql.*; 

class NewStreamLob 
{ 
  public static void main (String args [])  throws Exception 
  { 
    // Load the Oracle JDBC driver 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 

    String url = "jdbc:oracle:oci8:@"; 
    try { 
      String url1 = System.getProperty("JDBC_URL"); 
      if (url1 != null) 
        url = url1; 
    } catch (Exception e) { 
      // If there is any security exception, ignore it 
      // and use the default 
    } 

    // Connect to the database 
    Connection conn = 
      DriverManager.getConnection (url, "scott", "password"); 
    // It is faster when auto commit is off 
    conn.setAutoCommit (false); 

    // Create a Statement 
    Statement stmt = conn.createStatement (); 

    try 
    { 
      stmt.execute ("drop table basic_lob_table"); 
    } 
    catch (SQLException e) 
    { 
      // An exception could be raised here if the table did not exist already. 
    } 

    // Create a table containing a BLOB and a CLOB 
    stmt.execute (
         "create table basic_lob_table"  
         + "(x varchar2 (30), b blob, c clob)"); 

    // Populate the table 
    stmt.execute (
         "insert into basic_lob_table values"
         + "('one', '010101010101010101010101010101', 'onetwothreefour')"); 
  
    System.out.println ("Dumping lobs"); 

    // Select the lobs 
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Print the lob contents 
      dumpBlob (conn, blob, 1); 
      dumpClob (conn, clob, 1); 

      // Change the lob contents 
      fillClob (conn, clob, 11, 50); 
      fillBlob (conn, blob, 11, 50); 
    } 
    rset.close (); 

    System.out.println ("Dumping lobs again"); 

    rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Print the lobs contents 
      dumpBlob (conn, blob, 11); 
      dumpClob (conn, clob, 11); 
    } 
    // Close all resources 
    rset.close(); 
    stmt.close(); 
    conn.close(); 
  } 

  // Utility function to dump Clob contents 
  static void dumpClob (Connection conn, CLOB clob, long offset) 
    throws Exception 
  { 
    // get character stream to retrieve clob data 
    Reader instream = clob.getCharacterStream(offset); 

    // create temporary buffer for read 
    char[] buffer = new char[10]; 

    // length of characters read 
    int length = 0; 

    // fetch data 
    while ((length = instream.read(buffer)) != -1) 
    { 
      System.out.print("Read " + length + " chars: "); 

      for (int i=0; i<length; i++) 
        System.out.print(buffer[i]); 
      System.out.println(); 
    } 

    // Close input stream 
    instream.close(); 
  } 

  // Utility function to dump Blob contents 
  static void dumpBlob (Connection conn, BLOB blob, long offset) 
    throws Exception 
  { 
    // Get binary output stream to retrieve blob data 
    InputStream instream = blob.getBinaryStream(offset); 
    // Create temporary buffer for read 
    byte[] buffer = new byte[10]; 
    // length of bytes read 
    int length = 0; 
    // Fetch data 
    while ((length = instream.read(buffer)) != -1) 
    { 
      System.out.print("Read " + length + " bytes: "); 

      for (int i=0; i<length; i++) 
        System.out.print(buffer[i]+" "); 
      System.out.println(); 
    } 

    // Close input stream 
    instream.close(); 
  } 

  // Utility function to put data in a Clob 
  static void fillClob (Connection conn, CLOB clob, long offset, long length) 
    throws Exception 
  { 
    Writer outstream = clob.setCharacterStream(offset); 

    int i = 0; 
    int chunk = 10; 

    while (i < length) 
    { 
      outstream.write("aaaaaaaaaa", 0, chunk); 

      i += chunk; 
      if (length - i < chunk) 
         chunk = (int) length - i; 
    } 
    outstream.close(); 
  } 

  // Utility function to put data in a Blob 
  static void fillBlob (Connection conn, BLOB blob, long offset, long length) 
    throws Exception 
  { 
    OutputStream outstream = blob.setBinaryStream(offset); 

    int i = 0; 
    int chunk = 10; 

    byte [] data = { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 }; 

    while (i < length) 
    { 
      outstream.write(data, 0, chunk); 

      i += chunk; 
      if (length - i < chunk) 
         chunk = (int) length - i; 
    } 
    outstream.close(); 
  } 
} 

JDBC and Empty LOBs

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.