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.
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.
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.
■ The implementation of the data access API uses direct native calls in the JDBC OCI and server-side internal drivers, thereby providing better performance. You can use the same API on the LOB classes in all Oracle JDBC drivers.
■ In the case of the JDBC Thin driver only, the implementation of the data access API uses the PL/SQL DBMS_LOB package internally. You never have to use DBMS_LOB directly. This is in contrast to the 8.0.x drivers. For more information on the DBMS_LOB package, see theOracle9i Supplied PL/SQL Packages Reference.
■ To write to a BLOB, use the getBinaryOutputStream() 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 getAsciiOutputStream() or
getCharacterOutputStream() method of an oracle.sql.CLOB object to retrieve the CLOB as an output stream to be written back to the CLOB. The getAsciiOutputStream() method returns an ASCII output stream in a java.io.OutputStream object. The getCharacterOutputStream() method returns a Unicode output stream in a java.io.Writer object.
As with any OutputStream 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.
Working with BLOBs and CLOBs
Example: Reading BLOB Data Use the getBinaryStream() method of the
oracle.sql.BLOB class to read BLOB data. The getBinaryStream() method reads the BLOB data into a binary stream.
The following example uses the getBinaryStream() 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.getBinaryStream();
byte [] byte_array = new byte [10];
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.
Important: The JDBC 2.0 specification states that
PreparedStatement methods setBinaryStream() and setObject() can be used to input a stream value as a BLOB, and that the PreparedStatement methods setAsciiStream(), setUnicodeStream(), setCharacterStream(), and
setObject() can be used to input a stream value as a CLOB. This bypasses the LOB locator, going directly to the LOB data itself.
In the implementation of the Oracle JDBC drivers, this functionality is supported only for a configuration using an 8.1.6 and higher database and 8.1.6 and higher JDBC OCI driver. Do not use this functionality for any other configuration, as data corruption may result.
int bytes_read = byte_stream.read(byte_array);
...
Example: Reading CLOB Data The following example uses the
getCharacterStream() 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.getCharacterStream();
char [] char_array = new char [10];
int chars_read = char_stream.read (char_array, 0, 10);
...
The next example uses the getAsciiStream() 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.getAsciiStream();
byte[] asciiChar_array = new byte[10];
int asciiChar_read = asciiChar_stream.read(asciiChar_array,0,10);
Example: Writing BLOB Data Use the getBinaryOutputStream() 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 getBinaryOutputStream() 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).getBinaryOutputStream();
outstream.write(data);
...
Example: Writing CLOB Data Use the getCharacterOutputStream() method or the getAsciiOutputStream() method to write data to a CLOB. The
getCharacterOutputStream() method returns a Unicode output stream; the getAsciiOutputStream() method returns an ASCII output stream.
Working with BLOBs and CLOBs
The following example reads a vector of data into a character array, then uses the getCharacterOutputStream() method to write the array of character data to a CLOB. The getCharacterOutputStream() 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).getCharacterOutputStream();
writer.write(data);
writer.flush();
writer.close();
...
The next example reads a vector of data into a byte array, then uses the
getAsciiOutputStream() method to write the array of ASCII data to a CLOB.
Because getAsciiOutputStream() 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).getAsciiOutputStream();
out.write(data);
out.flush();
out.close();