14 Working with LOBs and BFILEs

This chapter describes how you use JDBC and the oracle.sql.* classes to access and manipulate LOB and BFILE locators and data, covering the following topics:

Oracle Extensions for LOBs and BFILEs
Working with BLOBs and CLOBs
Shortcuts For Inserting and Retrieving CLOB Data
Working With Temporary LOBs
Using Open and Close With LOBs
Working with BFILEs

Notes:

At 10g Release 1 (10.1), the Oracle JDBC drivers support the JDBC 3.0 java.sql.Clob and java.sql.Blob interfaces. Certain Oracle extensions made in oracle.sql.CLOB and oracle.sql.BLOB before this release are no longer necessary and are deprecated. You should port your application to the standard JDBC 3.0 interface.
Before Release 10g (10.1), the maximum size of a LOB was 2³² bytes. That restriction is removed at this release; the maximum size is now limited to the size of available physical storage. The Java LOB API has not changed.

14.1 Oracle Extensions for LOBs and BFILEs

LOBs ("large objects") are stored in a way that optimizes space and provides efficient access. The JDBC drivers provide support for two types of LOBs: BLOBs (unstructured binary data) and CLOBs (character data). BLOB and CLOB data is accessed and referenced by using a locator, which is stored in the database table and points to the BLOB or CLOB data, which is outside the table.

BFILEs are large binary data objects stored in operating system files outside of database tablespaces. These files use reference semantics. They can also be located on tertiary storage devices such as hard disks, CD-ROMs, PhotoCDs and DVDs. As with BLOBs and CLOBs, a BFILE is accessed and referenced by a locator which is stored in the database table and points to the BFILE data.

To work with LOB data, you must first obtain a LOB locator. Then you can read or write LOB data and perform data manipulation. The following sections also describe how to create and populate a LOB column in a table.

The JDBC drivers support these oracle.sql.* classes for BLOBs, CLOBs, and BFILEs:

oracle.sql.BLOB
oracle.sql.CLOB
oracle.sql.BFILE

The oracle.sql.BLOB and CLOB classes implement the java.sql.Blob and Clob interfaces, respectively. By contrast, BFILE is an Oracle extension, without a corresponding java.sql interface.

Instances of these classes contain only the locators for these datatypes, not the data. After accessing the locators, you must perform some additional steps to access the data. These steps are described in "Reading and Writing BLOB and CLOB Data" and "Reading BFILE Data".

Note:

You cannot construct BLOB, CLOB, or BFILE objects in your JDBC application—you can only retrieve existing BLOBs, CLOBs, or BFILEs from the database or create them using the createTemporary() and empty_lob() methods.

14.2 Working with BLOBs and CLOBs

This section describes how to read and write data to and from binary large objects (BLOBs) and character large objects (CLOBs) in an Oracle database, using LOB locators.

For general information about LOBs and how to use them, see the Oracle Database Application Developer's Guide - Large Objects.

14.2.1 Getting and Passing BLOB and CLOB Locators

Standard as well as Oracle-specific getter and setter methods are available for retrieving or passing LOB locators from or to the database.

14.2.1.1 Retrieving BLOB and CLOB Locators

Given a standard JDBC result set (java.sql.ResultSet) or callable statement (java.sql.CallableStatement) that includes BLOB or CLOB locators, you can access the locators by using standard getter methods, as follows. All the standard and Oracle-specific getter methods discussed here take either an int column index or a String column name as input.

Under JDK 1.2.x and higher, you can use the standard getBlob() and getClob() methods, which return java.sql.Blob and Clob objects, respectively.

If you retrieve or cast the result set or callable statement to an OracleResultSet or OracleCallableStatement object, then you can use Oracle extensions as follows:

You can use getBLOB() and getCLOB(), which return oracle.sql.BLOB and CLOB objects, respectively.
You can also use the getOracleObject() method, which returns an oracle.sql.Datum object, and cast the output appropriately.

14.2.1.1.1 Example: Getting BLOB and CLOB Locators from a Result Set

Assume the database has a table called lob_table with a column for a BLOB locator, blob_col, and a column for a CLOB locator, clob_col. This example assumes that you have already created the Statement object, stmt.

First, select the LOB locators into a standard result set, then get the LOB data into appropriate Java classes:

// Select LOB locator into standard result set.
ResultSet rs =
   stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{
   // Get LOB locators into Java wrapper classes.
   java.sql.Blob blob = (java.sql.Blob)rs.getObject(1);
   java.sql.Clob clob = (java.sql.Clob)rs.getObject(2);
   (...process...)
}

The output is cast to java.sql.Blob and Clob. As an alternative, you can cast the output to oracle.sql.BLOB and CLOB to take advantage of extended functionality offered by the oracle.sql.* classes. For example, you can rewrite the above code to get the LOB locators as:

// Get LOB locators into Java wrapper classes.
   oracle.sql.BLOB blob = (BLOB)rs.getObject(1);
   oracle.sql.CLOB clob = (CLOB)rs.getObject(2);
   (...process...)

14.2.1.1.2 Example: Getting a CLOB Locator from a Callable Statement

The callable statement methods for retrieving LOBs are identical to the result set methods.

For example, if you have an OracleCallableStatement ocs that calls a function func that has a CLOB output parameter, then set up the callable statement as in the following example.

This example registers OracleTypes.CLOB as the typecode of the output parameter.

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{? = call func()}");
ocs.registerOutParameter(1, OracleTypes.CLOB);
ocs.execute();
oracle.sql.CLOB clob = ocs.getCLOB(1);

14.2.1.2 Passing BLOB and CLOB Locators

Given a standard JDBC prepared statement (java.sql.PreparedStatement) or callable statement (java.sql.CallableStatement), you can use standard setter methods to pass LOB locators, as follows. All the standard and Oracle-specific setter methods discussed here take an int parameter index and the LOB locator as input. You use the standard setBlob() and setClob() methods, which take java.sql.Blob and Clob locators as input.

Note:

If you pass a BLOB to a PL/SQL procedure, the BLOB must be no bigger than 32K - 7. If you pass a BLOB that exceeds this limit, you will receive a SQLException.

Given an Oracle-specific OraclePreparedStatement or OracleCallableStatement, then you can use Oracle extensions as follows:

Use setBLOB() and setCLOB(), which take oracle.sql.BLOB and CLOB locators as input, respectively.
Use the setOracleObject() method, which simply specifies an oracle.sql.Datum input.

14.2.1.2.1 Example: Passing a BLOB Locator to a Prepared Statement

If you have an OraclePreparedStatement object ops and a BLOB named my_blob, then write the BLOB to the database as follows:

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                            ("INSERT INTO blob_table VALUES(?)"); 
ops.setBLOB(1, my_blob);
ops.execute();

14.2.1.2.2 Example: Passing a CLOB Locator to a Callable Statement

If you have an OracleCallableStatement object ocs and a CLOB named my_clob, then input the CLOB to the stored procedure proc as follows:

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{call proc(?))}");
ocs.setClob(1, my_clob);
ocs.execute();

14.2.2 Reading and Writing BLOB and CLOB Data

Once you have a LOB locator, you can use JDBC methods to read and write the LOB data. LOB data is materialized as a Java array or stream. However, unlike most Java streams, a locator representing the LOB data is stored in the table. Thus, you can access the LOB data at any time during the life of the connection.

To read and write the LOB data, use the methods in the oracle.sql.BLOB or oracle.sql.CLOB class, as appropriate. These classes provide functionality such as reading from the LOB into an input stream, writing from an output stream into a LOB, determining the length of a LOB, and closing a LOB.

Notes:

To write LOB data, the application must acquire a write lock on the LOB object. One way to accomplish this is through a SELECT FOR UPDATE. Also, disable auto-commit mode.

To read and write LOB data, you can use these methods:

To read from a BLOB, use the getBinaryStream() method of an oracle.sql.BLOB object to retrieve the entire BLOB as an input stream. This returns a java.io.InputStream object.

As with any InputStream object, use one of the overloaded read() methods to read the LOB data, and use the close() method when you finish.
To write to a BLOB, use the setBinaryStream() method of an oracle.sql.BLOB object to retrieve the BLOB as an output stream. This returns a java.io.OutputStream object to be written back to the BLOB.

As with any OutputStream object, use one of the overloaded write() methods to update the LOB data, and use the close() method when you finish.
To read from a CLOB, use the getAsciiStream() or getCharacterStream() method of an oracle.sql.CLOB object to retrieve the entire CLOB as an input stream. The getAsciiStream() method returns an ASCII input stream in a java.io.InputStream object. The getCharacterStream() method returns a Unicode input stream in a java.io.Reader object.

As with any InputStream or Reader object, use one of the overloaded read() methods to read the LOB data, and use the close() method when you finish.

You can also use the getSubString() method of oracle.sql.CLOB object to retrieve a subset of the CLOB as a character string of type java.lang.String.

To write to a CLOB, use the setAsciiStream() or setCharacterStream() method of an oracle.sql.CLOB object to retrieve the CLOB as an output stream to be written back to the CLOB. The setAsciiStream() method returns an ASCII output stream in a java.io.OutputStream object. The setCharacterStream() method returns a Unicode output stream in a java.io.Writer object.

As with any Stream or Writer object, use one of the overloaded write() methods to update the LOB data, and use the flush() and close() methods when you finish.

Notes:

The stream "write" methods described in this section write directly to the database when you write to the output stream. You do not need to execute an UPDATE to write the data. CLOBs and BLOBs are transaction controlled. After writing to either, you must commit the transaction for the changes to be permanent. BFILEs are not transaction controlled. Once you write to them the changes are permanent, even if the transaction is rolled back, unless the external file system does something else.
When writing to or reading from a CLOB, the JDBC drivers perform all character set conversions for you.

14.2.2.1 Example: Reading BLOB Data

Use the setBinaryStream() method of the oracle.sql.BLOB class to read BLOB data. The setBinaryStream() method reads the BLOB data into a binary stream.

The following example uses the setBinaryStream() method to read BLOB data into a byte stream and then reads the byte stream into a byte array (returning the number of bytes read, as well).

// Read BLOB data from BLOB locator.
InputStream byte_stream = my_blob.setBinaryStream(1L);
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
...

14.2.2.2 Example: Reading CLOB Data

The following example uses the setCharacterStream() method to read CLOB data into a Unicode character stream. It then reads the character stream into a character array (returning the number of characters read, as well).

// Read CLOB data from CLOB locator into Reader char stream.
Reader char_stream = my_clob.setCharacterStream(1L);
char [] char_array = new char [10];
int chars_read = char_stream.read (char_array, 0, 10);
...

The next example uses the setAsciiStream() method of the oracle.sql.CLOB class to read CLOB data into an ASCII character stream. It then reads the ASCII stream into a byte array (returning the number of bytes read, as well).

// Read CLOB data from CLOB locator into Input ASCII character stream
Inputstream asciiChar_stream = my_clob.setAsciiStream(1L); 
byte[] asciiChar_array = new byte[10]; 
int asciiChar_read = asciiChar_stream.read(asciiChar_array,0,10);

14.2.2.3 Example: Writing BLOB Data

Use the setBinaryOutputStream() method of an oracle.sql.BLOB object to write BLOB data.

The following example reads a vector of data into a byte array, then uses the setBinaryOutputStream() method to write an array of character data to a BLOB.

java.io.OutputStream outstream;

// read data into a byte array 
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};

// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).setBinaryOutputStream(1L);
outstream.write(data);
...

14.2.2.4 Example: Writing CLOB Data

Use the setCharacterStream() method or the setAsciiStream() method to write data to a CLOB. The setCharacterStream() method returns a Unicode output stream; the setAsciiStream() method returns an ASCII output stream.

The following example reads a vector of data into a character array, then uses the setCharacterStream() method to write the array of character data to a CLOB. The setCharacterStream() method returns a java.io.Writer instance in an oracle.sql.CLOB object, not a java.sql.Clob object.

java.io.Writer writer;

// read data into a character array
char[] data = {'0','1','2','3','4','5','6','7','8','9'};

// write the array of character data to a CLOB 
writer = ((CLOB)my_clob).setCharacterStream();
writer.write(data);
writer.flush();
writer.close();
...

The next example reads a vector of data into a byte array, then uses the setAsciiStream() method to write the array of ASCII data to a CLOB. Because setAsciiStream() returns an ASCII output stream, you must cast the output to a oracle.sql.CLOB datatype.

java.io.OutputStream out;

// read data into a byte array
byte[] data = {'0','1','2','3','4','5','6','7','8','9'};

// write the array of ascii data to a CLOB 
out = ((CLOB)clob).setAsciiStream();
out.write(data);
out.flush();
out.close();

14.2.3 Creating and Populating a BLOB or CLOB Column

Create and populate a BLOB or CLOB column in a table by using SQL statements.

Note:

You cannot construct a new BLOB or CLOB locator in your application with a Java new statement. You must create the locator through a SQL operation, and then select it into your application or with the createTemporary() or empty_lob() methods.

Create a BLOB or CLOB column in a table with the SQL CREATE TABLE statement, then populate the LOB. This includes creating the LOB entry in the table, obtaining the LOB locator, creating a file handler for the data (if you are reading the data from a file), and then copying the data into the LOB.

14.2.3.1 Creating a BLOB or CLOB Column in a New Table

To create a BLOB or CLOB column in a new table, execute the SQL CREATE TABLE statement. The following example code creates a BLOB column in a new table. This example assumes that you have already created your Connection object conn and Statement object stmt:

String cmd = "CREATE TABLE my_blob_table (x varchar2 (30), c blob)";
stmt.execute (cmd);

In this example, the VARCHAR2 column designates a row number, such as 1 or 2, and the BLOB column stores the locator of the BLOB data.

14.2.3.2 Populating a BLOB or CLOB Column in a New Table

This example demonstrates how to populate a BLOB or CLOB column by reading data from a stream. These steps assume that you have already created your Connection object conn and Statement object stmt. The table my_blob_table is the table that was created in the previous section.

The following example writes the GIF file john.gif to a BLOB.

Begin by using SQL statements to create the BLOB entry in the table. Use the empty_blob syntax to create the BLOB locator.
```
stmt.execute ("INSERT INTO my_blob_table VALUES ('row1', empty_blob())");
```

Get the BLOB locator from the table.

BLOB blob;
cmd = "SELECT * FROM my_blob_table WHERE X='row1'";
ResultSet rset = stmt.executeQuery(cmd);
rset.next();
BLOB blob = ((OracleResultSet)rset).getBLOB(2);

Declare a file handler for the john.gif file, then print the length of the file. This value will be used later to ensure that the entire file is read into the BLOB. Next, create a FileInputStream object to read the contents of the GIF file, and an OutputStream object to retrieve the BLOB as a stream.
```
File binaryFile = new File("john.gif");
System.out.println("john.gif length = " + binaryFile.length());
FileInputStream instream = new FileInputStream(binaryFile);
OutputStream outstream = blob.setBinaryStream(1L);
```
Call getBufferSize() to retrieve the ideal buffer size (according to calculations by the JDBC driver) to use in writing to the BLOB, then create the buffer byte array.
```
int size = blob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;
```
Use the read() method to read the GIF file to the byte array buffer, then use the write() method to write it to the BLOB. When you finish, close the input and output streams.
```
while ((length = instream.read(buffer)) != -1)
   outstream.write(buffer, 0, length);
instream.close();
outstream.close();
```

Once your data is in the BLOB or CLOB, you can manipulate the data. This is described in the next section, "Accessing and Manipulating BLOB and CLOB Data".

14.2.4 Accessing and Manipulating BLOB and CLOB Data

Once you have your BLOB or CLOB locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you first must select their locators from a result set or from a callable statement. "Getting and Passing BLOB and CLOB Locators" describes these techniques in detail.

After you select the locators, you can retrieve the BLOB or CLOB data. You will usually want to cast the result set to the OracleResultSet datatype so that you can retrieve the data in oracle.sql.* format. After retrieving the BLOB or CLOB data, you can manipulate it however you want.

This example is a continuation of the example in the previous section. It uses the SQL SELECT statement to select the BLOB locator from the table my_blob_table into a result set. The result of the data manipulation is to print the length of the BLOB in bytes.

// Select the blob - what we are really doing here
// is getting the blob locator into a result set 
BLOB blob;
cmd = "SELECT *  FROM my_blob_table";
ResultSet rset = stmt.executeQuery (cmd);

// Get the blob data - cast to OracleResult set to 
// retrieve the data in oracle.sql format 
String index = ((OracleResultSet)rset).getString(1);
blob = ((OracleResultSet)rset).getBLOB(2);

// get the length of the blob
int length = blob.length();

// print the length of the blob
System.out.println("blob length" + length);

// read the blob into a byte array 
// then print the blob from the array
byte bytes[] = blob.getBytes(1, length);
blob.printBytes(bytes, length);

14.2.5 Additional BLOB and CLOB Features

In addition to what has already been discussed in this chapter, the oracle.sql.BLOB and oracle.sql.CLOB classes have a number of methods for further functionality.

Note:

The oracle.sql.CLOB class supports all the character sets that the Oracle data server supports for CLOB types.

14.2.5.1 Additional BLOB Methods

The oracle.sql.BLOB class includes the following methods:

close(): Closes the BLOB associated with the locator. (See "Using Open and Close With LOBs" for more information.)
freeTemporary(): Frees the storage used by a temporary BLOB. (See "Working With Temporary LOBs" for more information.)
setBinaryStream(long): Returns the BLOB data for this Blob instance as a stream of bytes beginning at the position in the BLOB specified in the argument.
getBufferSize(): Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing BLOB data. This value is a multiple of the chunk size (see getChunkSize() below) and is close to 32K.
getBytes(): Reads from the BLOB data, starting at a specified point, into a supplied buffer.
getChunkSize(): Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the BLOB value. Part of each chunk stores system-related information, and the rest stores LOB data. Performance is enhanced if read and write requests use some multiple of the chunk size.
isOpen(): Returns true if the BLOB was opened by calling the open() method; otherwise, it returns false. (See "Using Open and Close With LOBs" for more information.)
isTemporary(): Returns true if the BLOB is a temporary BLOB. (See "Working With Temporary LOBs" for more information.)
length(): Returns the length of the BLOB in bytes.
open(): Opens the BLOB associated with the locator. (See "Using Open and Close With LOBs" for more information.)
open(int): Opens the BLOB associated with the locator in the mode specified by the argument. (See "Using Open and Close With LOBs" for more information.)
position(): Determines the byte position in the BLOB where a given pattern begins.
setBytes(): Writes BLOB data, starting at a specified point, from a supplied buffer.
truncate(long): Trims the value of the BLOB to the length specified by the argument.

14.2.5.2 Additional CLOB Methods

The oracle.sql.CLOB class includes the following methods:

close(): Closes the CLOB associated with the locator. (See "Using Open and Close With LOBs" for more information.)
freeTemporary(): Frees the storage used by a temporary CLOB. (See "Working With Temporary LOBs" for more information.)
setAsciiStream(long): Returns a java.io.OutputStream object to write data to the CLOB as a stream. The data is written beginning at the position in the CLOB specified by the argument.
getAsciiStream(): Returns the CLOB value designated by the Clob object as a stream of ASCII bytes.
getAsciiStream(long): Returns the CLOB value designated by the CLOB object as a stream of ASCII bytes, beginning at the position in the CLOB specified by the argument.
getBufferSize(): Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing CLOB data. This value is a multiple of the chunk size (see getChunkSize() below) and is close to 32K.
setCharacterStream(long): Returns a java.io.Writer object to write data to the CLOB as a stream. The data is written beginning at the position in the CLOB specified by the argument.
getCharacterStream(): Returns the CLOB data as a stream of Unicode characters.
getCharacterStream(long): Returns the CLOB data as a stream of Unicode characters beginning at the position in the CLOB specified by the argument.
getChars(): Retrieves characters from a specified point in the CLOB data into a character array.
getChunkSize(): Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the CLOB value. Part of each chunk stores system-related information and the rest stores LOB data. Performance is enhanced if you make read and write requests using some multiple of the chunk size.
isOpen(): Returns true if the CLOB was opened by calling the open() method; otherwise, it returns false. (See "Using Open and Close With LOBs" for more information.)
isTemporary(): Returns true if and only if the CLOB is a temporary CLOB. (See "Working With Temporary LOBs" for more information.)
length(): Returns the length of the CLOB in characters.
open(): Opens the CLOB associated with the locator. (See "Using Open and Close With LOBs" for more information.)
open(int): Opens the CLOB associated with the locator in the mode specified by the argument. (See "Using Open and Close With LOBs" for more information.)
position(): Determines the character position in the CLOB at which a given substring begins.
putChars(): Writes characters from a character array to a specified point in the CLOB data.
getSubString(): Retrieves a substring from a specified point in the CLOB data.
setString(long pos, String str): Writes a string to a specified point in the CLOB data.
truncate(long): Trims the value of the CLOB to the length specified by the argument.

14.2.5.3 Creating Empty LOBs

Before writing data to an internal LOB, you must make sure the LOB column/attribute is not null: it must contain a locator. You can accomplish this by initializing the internal LOB as an empty LOB in an INSERT or UPDATE statement, using the empty_lob() method defined in the oracle.sql.BLOB and oracle.sql.CLOB classes:

public static BLOB empty_lob() throws SQLException
public static CLOB empty_lob() throws SQLException

A JDBC driver creates an empty LOB instance without making database round trips. You can use empty LOBs in the following:

setXXX() methods of the OraclePreparedStatement class
updateXXX() methods of updatable result sets
attributes of STRUCT objects

elements of ARRAY objects

Note:

Because an empty_lob() method creates a special marker that does not contain a locator, a JDBC application cannot read or write to it. The JDBC driver throws the exception ORA-17098 Invalid empty LOB operation if a JDBC application attempts to read or write to an empty LOB before it is stored in the database.

14.3 Shortcuts For Inserting and Retrieving CLOB Data

You often use a CLOB column to store character data which may be larger than the size permitted by a VARCHAR column, if and only it is known that the actual data stored is within the limits which can be bound with setString().

CREATE TABLE MY_CLOB_TAB( C CLOB )
PreparedStatement pstmt = conn.prepareStatement( 
  "INSERT INTO MY_CLOB_TAB VALUES ( ? )" );
pstmt.setString( 1, "a string that is less than 32765 bytes" );pstmt.execute();

The string data is transferred to the database and automatically transformed into a CLOB which is inserted into the column.

In 10g Release 1 (10.1) an additional method, pstmt(), is added to OraclePreparedStatement.

OraclePreparedStatement pstmt = (OraclePreparedStatement)(conn.pstmt(    "INSERT INTO MY_CLOB_TAB VALUES ( ? )" ));pstmt.setStringForClob( 1, "any Java string" );pstmt.execute();

In addition, there is a connection property SetBigStringUseClob. Setting this property forces PreparedStatement.setString() method to use setStringForClob() if the data is larger than 32765 bytes. Please note that using this method with VARCHAR and LONG columns may cause large data to be truncated silently, or cause other errors differing from the normal behavior of setString().

You can use getString() to read a CLOB column.

For both of these operations, the only limit on the size of the string is the limit imposed by Java Language itself, which is that the length must be a positive Java int. Note, however, that if the data is extremely large it is may not be wise to handle it this way. Please read the information provided by your Java Virtual Machine vendor about the impact of very large data elements on memory management, and consider using the stream interfaces instead.

CLOB and BLOB data may also be read and written using the same streaming mechanism as for LONG and LONG RAW data. To read, use defineColumnType(nn, Types.LONGVARCHAR) or defineColumnType( nn, Types.LONGVARBINARY) on the column; this produces a direct stream on the data as if it were a LONG or LONG RAW column. For input in a PreparedStatement, you may use setBinaryStream(), setCharacterStream(), or setAsciiStream() for a parameter which is a BLOB or CLOB. These methods will use the stream interface to create a LOB in the database from the data from the stream. Both of these techniques reduce database round trips and may result in improved performance in some cases. See the Javadoc on stream data for the significant restrictions which apply.

14.4 Working With Temporary LOBs

You can use temporary LOBs to store transient data. The data is stored in temporary table space rather than regular table space. You should free temporary LOBs after you no longer need them. If you do not, the space the LOB consumes in temporary table space will not be reclaimed

You can insert temporary LOBs into a table. When you do this, a permanent copy of the LOB is created and stored. This is an alternative to the procedure described in "Creating and Populating a BLOB or CLOB Column". Inserting a temporary LOB may be preferable for some situations; remember that the data is initially stored in the temporary table space on the server and then moved into permanent storage.

You create a temporary LOB with the static method createTemporary(Connection, boolean, int). This method is defined in both the oracle.sql.BLOB and oracle.sql.CLOB classes. You free a temporary LOB with the freeTemporary() method.

public static BLOB createTemporary(Connection conn, boolean isCached, int duration);
public static CLOB createTemporary(Connection conn, boolean isCached, int duration);

The duration must be either DURATION_SESSION or DURATION_CALL as defined in the oracle.sql.BLOB or oracle.sql.CLOB class. In client applications DURATION_SESSION is appropriate. In Java stored procedures you can use either DURATION_SESSION or DURATION_CALL, which ever is appropriate.

You can test whether a LOB is temporary by calling the isTemporary() method. If the LOB was created by calling the createTemporary()method, the isTemporary() method returns true; otherwise, it returns false.

You can free a temporary LOB by calling the freeTemporary() method. Free any temporary LOBs before ending the session or call. Otherwise, the storage used by the temporary LOB will not be reclaimed.

Note:

Failure to free a temporary LOB will result in the storage used by that LOB being unavailable. Frequent failure to free temporary LOBs will result in filling up temporary table space with unavailable LOB storage.

14.4.1 Creating Temporary NCLOBs

You create temporary NCLOBs using a variant of the createTemporary() method.

The syntax is:

CLOB.createTemporary (Connection conn, boolean cache, int duration,   short form);

The form argument specifies whether the created LOB is a CLOB or an NCLOB. If form equals oracle.jdbc.OraclePreparedStatement.FORM_NCHAR, then the method creates an NCLOB; if form equals oracle.jdbc.OraclePreparedStatement.FORM_CHAR, the method creates a CLOB.

14.5 Using Open and Close With LOBs

You do not have to open and close your LOBs. You might choose to open and close them for performance reasons.

If you do not wrap LOB operations inside an Open/Close call operation: Each modification to the LOB will implicitly open and close the LOB thereby firing any triggers on an domain index. Note that in this case, any domain indexes on the LOB will become updated as soon as LOB modifications are made. Therefore, domain LOB indexes are always valid and may be used at any time.

If you wrap your LOB operations inside the Open/Close operation, triggers will not be fired for each LOB modification. Instead, the trigger on domain indexes will be fired at the Close call. For example, you might design your application so that domain indexes are not be updated until you call the close() method. However, this means that any domain indexes on the LOB will not be valid in-between the Open/Close calls.

You open a LOB by calling the open() or open(int) method. You may then read and write the LOB without any triggers associated with that LOB firing. When you are done accessing the LOB, close the LOB by calling the close() method. When you close the LOB, any triggers associated with the LOB will fire. You can see if a LOB is open or closed by calling the isOpen() method. If you open the LOB by calling the open(int) method, the value of the argument must be either MODE_READONLY or MODE_READWRITE, as defined in the oracle.sql.BLOB and oracle.sql.CLOB classes. If you open the LOB with MODE_READONLY, any attempt to write to the LOB will result in a SQL exception.

Note:

An error occurs if you commit the transaction before closing all opened LOBs that were opened by the transaction. The openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed but the triggers for domain indexing are not fixed.

14.6 Working with BFILEs

This section describes how to read and write data to and from external binary files (BFILEs), using file locators.

14.6.1 Getting and Passing BFILE Locators

Getter and setter methods are available for retrieving or passing BFILE locators from or to the database.

14.6.1.1 Retrieving BFILE Locators

Given a standard JDBC result set or callable statement object that includes BFILE locators, you can access the locators by using the standard result set getObject() method. This method returns an oracle.sql.BFILE object.

You can also access the locators by casting your result set to OracleResultSet or your callable statement to OracleCallableStatement and using the getOracleObject() or getBFILE() method.

Notes:

In the OracleResultSet and OracleCallableStatement classes, getBFILE() and getBfile() both return oracle.sql.BFILE. There is no java.sql interface for BFILEs.
If using getObject() or getOracleObject(), remember to cast the output, as necessary. For more information, see "Datatypes For Returned Objects from getObject and getXXX".

14.6.1.1.1 Example: Getting a BFILE locator from a Result Set

Assume that the database has a table called bfile_table with a single column for the BFILE locator bfile_col. This example assumes that you have already created your Statement object stmt.

Select the BFILE locator into a standard result set. If you cast the result set to an OracleResultSet, you can use getBFILE() to get the BFILE locator:

// Select the BFILE locator into a result set
ResultSet rs = stmt.executeQuery("SELECT bfile_col FROM bfile_table"); 
while (rs.next()) 
{
   oracle.sql.BFILE my_bfile = ((OracleResultSet)rs).getBFILE(1); 
}

Note that as an alternative, you can use getObject() to return the BFILE locator. In this case, because getObject() returns a java.lang.Object, cast the results to BFILE. For example:

oracle.sql.BFILE my_bfile = (BFILE)rs.getObject(1);

14.6.1.1.2 Example: Getting a BFILE Locator from a Callable Statement

Assume you have an OracleCallableStatement object ocs that calls a function func that has a BFILE output parameter. The following code example sets up the callable statement, registers the output parameter as OracleTypes.BFILE, executes the statement, and retrieves the BFILE locator:

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{? = call func()}");
ocs.registerOutParameter(1, OracleTypes.BFILE);
ocs.execute();
oracle.sql.BFILE bfile = ocs.getBFILE(1);

14.6.1.2 Passing BFILE Locators

To pass a BFILE locator to a prepared statement or callable statement (to update a BFILE locator, for example), you can do one of the following:

Use the standard setObject() method.

or:

Cast the statement to OraclePreparedStatement or OracleCallableStatement, and use the setOracleObject() or setBFILE() method.

These methods take the parameter index and an oracle.sql.BFILE object as input.

14.6.1.2.1 Example: Passing a BFILE Locator to a Prepared Statement

Assume you want to insert a BFILE locator into a table, and you have an OraclePreparedStatement object ops to insert data into a table. The first column is a string (to designate a row number), the second column is a BFILE, and you have a valid oracle.sql.BFILE object (bfile). Write the BFILE to the database as follows:

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                            ("INSERT INTO my_bfile_table VALUES (?,?)");
ops.setString(1,"one");
ops.setBFILE(2, bfile);
ops.execute();

14.6.1.2.2 Example: Passing a BFILE Locator to a Callable Statement

Passing a BFILE locator to a callable statement is similar to passing it to a prepared statement. In this case, the BFILE locator is passed to the myGetFileLength() procedure, which returns the BFILE length as a numeric value.

OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall
                               ("begin ? := myGetFileLength (?); end;");
try
{
   cstmt.registerOutParameter (1, Types.NUMERIC);
   cstmt.setBFILE (2, bfile);
   cstmt.execute ();
   return cstmt.getLong (1);
}

14.6.2 Reading BFILE Data

To read BFILE data, you must first get the BFILE locator. You can get the locator from either a callable statement or a result set. "Getting and Passing BFILE Locators" describes this.

Once you obtain the locator, you can invoke a number of methods on the BFILE without opening it. For example, you can use the oracle.sql.BFILE methods fileExists() and isFileOpen() to determine whether the BFILE exists and if it is open. If you want to read and manipulate the data, however, you must open and close the BFILE, as follows:

Use the openFile() method of the oracle.sql.BFILE class to open a BFILE.
When you are done, use the closeFile() method of the BFILE class.

BFILE data is materialized as a Java stream. To read from a BFILE, use the getBinaryStream() method of an oracle.sql.BFILE object to retrieve the entire file as an input stream. This returns a java.io.InputStream object.

As with any InputStream object, use one of the overloaded read() methods to read the file data, and use the close() method when you finish.

Notes:

BFILEs are read-only. You cannot insert data or otherwise write to a BFILE.
You cannot use JDBC to create a new BFILE. They are created only externally.

14.6.2.1 Example: Reading BFILE Data

The following example uses the getBinaryStream() method of an oracle.sql.BFILE object to read BFILE data into a byte stream and then read the byte stream into a byte array. The example assumes that the BFILE has already been opened.

// Read BFILE data from a BFILE locator
Inputstream in = bfile.getBinaryStream();
byte[] byte_array = new byte{10};
int byte_read = in.read(byte_array);

14.6.3 Creating and Populating a BFILE Column

This section discusses how to create a BFILE column in a table with SQL operations and specify the location where the BFILE resides. The examples below assume that you have already created your Connection object conn and Statement object stmt.

14.6.3.1 Creating a BFILE Column in a New Table

To work with BFILE data, create a BFILE column in a table, and specify the location of the BFILE. To specify the location of the BFILE, use the SQL CREATE DIRECTORY...AS statement to specify an alias for the directory where the BFILE resides. Then execute the statement. In this example, the directory alias is test_dir, and the BFILE resides in the /home/work directory.

String cmd;
cmd = "CREATE DIRECTORY test_dir AS '/home/work'";
stmt.execute (cmd);

Use the SQL CREATE TABLE statement to create a table containing a BFILE column, then execute the statement. In this example, the name of the table is my_bfile_table.

// Create a table containing a BFILE field 
cmd = "CREATE TABLE my_bfile_table (x varchar2 (30), b bfile)";
stmt.execute (cmd);

In this example, the VARCHAR2 column designates a row number, and the BFILE column stores the locator of the BFILE data.

14.6.3.2 Populating a BFILE Column

Use the SQL INSERT INTO...VALUES statement to populate the VARCHAR2 and BFILE fields, then execute the statement. The BFILE column is populated with the locator to the BFILE data. To populate the BFILE column, use the bfilename function to specify the directory alias and the name of the BFILE file.

cmd ="INSERT INTO my_bfile_table VALUES ('one', bfilename(test_dir, 
                                         'file1.data'))";
stmt.execute (cmd);
cmd ="INSERT INTO my_bfile_table VALUES ('two', bfilename(test_dir,  
                                         'jdbcTest.data'))";
stmt.execute (cmd);

In this example, the name of the directory alias is test_dir. The locator of the BFILE file1.data is loaded into the BFILE column on row one, and the locator of the BFILE jdbcTest.data is loaded into the bfile column on row two.

As an alternative, you might want to create the row for the row number and BFILE locator now, but wait until later to insert the locator. In this case, insert the row number into the table, and null as a place holder for the BFILE locator.

cmd ="INSERT INTO my_bfile_table VALUES ('three', null)";
stmt.execute(cmd);

Here, three is inserted into the row number column, and null is inserted as the place holder. Later in your program, insert the BFILE locator into the table by using a prepared statement.

First get a valid BFILE locator into the bfile object:

rs = stmt.executeQuery("SELECT b FROM my_bfile_table WHERE x='two'");
rs.next();
oracle.sql.BFILE bfile = ((OracleResultSet)rs).getBFILE(1);

Then, create your prepared statement. Note that because this example uses the setBFILE() method to identify the BFILE, the prepared statement must be cast to an OraclePreparedStatement:

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                      (UPDATE my_bfile_table SET b=? WHERE x = 'three');
ops.setBFILE(1, bfile);
ops.execute();

Now row two and row three contain the same BFILE.

Once you have the BFILE locators available in a table, you can access and manipulate the BFILE data. The next section, "Accessing and Manipulating BFILE Data", describes this.

14.6.4 Accessing and Manipulating BFILE Data

Once you have the BFILE locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you must first select its locator from a result set or a callable statement.

The following code continues the example from "Populating a BFILE Column", getting the locator of the BFILE from row two of a table into a result set. The result set is cast to an OracleResultSet so that oracle.sql.* methods can be used on it. Several of the methods applied to the BFILE, such as getDirAlias() and getName(), do not require you to open the BFILE. Methods that manipulate the BFILE data, such as reading, getting the length, and displaying, do require you to open the BFILE.

When you finish manipulating the BFILE data, you must close the BFILE.

// select the bfile locator
cmd = "SELECT *  FROM my_bfile_table WHERE x = 'two'";
rset = stmt.executeQuery (cmd);

if (rset.next ())
    BFILE bfile = ((OracleResultSet)rset).getBFILE (2);

// for these methods, you do not have to open the bfile
println("getDirAlias() = " + bfile.getDirAlias());
println("getName() = " + bfile.getName());
println("fileExists() = " + bfile.fileExists());
println("isFileOpen() = " + bfile.isFileOpen());

// now open the bfile to get the data
bfile.openFile();

// get the BFILE data as a binary stream
InputStream in = bfile.getBinaryStream();
int length ;

// read the bfile data in 6-byte chunks
byte[] buf = new byte[6];

while ((length = in.read(buf)) != -1)
{
    // append and display the bfile data in 6-byte chunks 
   StringBuffer sb = new StringBuffer(length);
   for (int i=0; i<length; i++)
      sb.append( (char)buf[i] );
   System.out.println(sb.toString());
}

// we are done working with the input stream. Close it.   
in.close();

// we are done working with the BFILE. Close it.  
bfile.closeFile();

14.6.5 Additional BFILE Features

In addition to the features already discussed in this chapter, the oracle.sql.BFILE class has a number of methods for further functionality, including the following:

openFile(): Opens the external file for read-only access.
closeFile(): Closes the external file.
getBinaryStream(): Returns the contents of the external file as a stream of bytes.
getBinaryStream(long): Returns the contents of the external file as a stream of bytes beginning at the position in the external file specified by the argument.
getBytes(): Reads from the external file, starting at a specified point, into a supplied buffer.
getName(): Gets the name of the external file.
getDirAlias(): Gets the directory alias of the external file.
length(): Returns the length of the BFILE in bytes.
position(): Determines the byte position at which the given byte pattern begins.
isFileOpen(): Determines whether the BFILE is open (for read-only access).