Changing Internal Persistent LOBs Using Java

You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of an internal LOB in Java by means of the JDBC API via the objects:

oracle.sql.BLOB

oracle.sql.CLOB

These objects also implement java.sql.Blob and java.sql.Clob interfaces according to the JDBC 2.0 specification. With this implementation, an oracle.sql.BLOB can be used wherever a java.sql.Blob is expected and an oracle.sql.CLOB can be used wherever a java.sql.Clob is expected.

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

BLOB, CLOB, and BFILE Classes

BLOB and CLOB Classes. In JDBC theses classes provide methods for performing operations on large objects in the database including BLOB and CLOB data types.

BFILE Class. In JDBC this class provides methods for performing operations on BFILE data in the database.

The BLOB, CLOB, and BFILE classes encapsulate LOB locators, so you do not deal with locators but instead use methods and properties provided to perform operations and get state information.

Calling DBMS_LOB Package From Java (JDBC)

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

Referencing LOBs Using Java (JDBC)

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

As a column of an OracleResultSet

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

Using OracleResultSet: BLOB and CLOB Objects Retrieved Represent LOB Locators of Current Row

When BLOB and CLOB objects are retrieved as a part of an OracleResultSet, these objects represent LOB locators of the currently selected row.

If the current row changes due to a move operation, for example, rset.next(), the retrieved locator still refers to the original LOB row.

To retrieve the locator for the most current row, you must call getXXXX() on the OracleResultSet each time a move operation is made, where XXXX is a BLOB, CLOB or BFILE.

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

JDBC: OracleBlob and OracleClob Do Not Work in Oracle8i 8.1.x and Higher Releases

OracleBlob and OracleClob were Oracle specific functions used in JDBC 8.0.x drivers to access LOB data. In Oracle8i Releases 8.1.x and higher, OracleBlob and OracleClob are deprecated.

If you use OracleBlob or OracleClob to access LOB data, you will receive the following typical error message, for example, when attempting to manipulate LOBs with Oracle8i Release 8.1.5 JDBC Thin Driver:

Oracle9i 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's implementation of standard JDBC java.sql.Blob interface. Table 3-52 lists the Oracle extension APIs in oracle.sql.BLOB that open and close BLOBs. These are new for this release.

Table 3-52 JDBC: Opening and Closing BLOBs

Methods

Description

public void open(int mode) throws SQLException

Opens the BLOB

public boolean isOpen() throws SQLException

Sees if the BLOB is open

public void close() throws SQLException

Closes the BLOB

Opening the BLOB

To open a BLOB, your JDBC application can use the open method as defined in oracle.sql.BLOB class as follows:

/**
* 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

Possible values of the mode parameter are:

public static final int MODE_READONLY
public static final int MODE_READWRITE

Each call to open opens the BLOB. For example:

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

Checking If the BLOB is Opened

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

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

Closing the BLOB

To close a BLOB, your JDBC application can use the close method defined in oracle.sql.BLOB. The close API is defined as follows:

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

The usage example is:

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

JDBC: Opening and Closing CLOBs

Class, oracle.sql.clob, is the Oracle JDBC driver's implementation of the standard JDBC java.sql.clob interface. Table 3-53 lists the new Oracle extension APIs in oracle.sql.clob to open and close CLOBs.

Table 3-53 JDBC: Opening and Closing CLOBs

Methods

Description

public void open(int mode) throws SQLException

Open the CLOB

public boolean isOpen() throws SQLExceptio

See if the CLOB is opened

public void close() throws SQLException

Close the CLOB

Opening the CLOB

To open a CLOB, your JDBC application can use the open method defined in oracle.sql.CLOB class as follows:

/**
* 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

The possible values of the mode parameter are:

public static final int MODE_READONLY
public static final int MODE_READWRITE

Each call to open opens the CLOB. For example,

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

Checking if the CLOB is Open

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

Opening BFILEs

To open a BFILE, your JDBC application can use the OPEN method defined in oracle.sql.BFILE class as follows:

/**
* Open a external LOB in the readonly 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

The only possible value of the mode parameter is:

public static final int MODE_READONLY

Each call to open opens the BFILE. For example,

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

Checking if the BFILE is Open

To see if a BFILE is opened, your JDBC application can use the ISOPEN method defined in oracle.sql.BFILE. The return boolean value indicates whether the BFILE has been previously opened or not. The ISOPEN method is 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 getBinaryOutputStream(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

New CLOB Streaming APIs

oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-58 lists the new Oracle extension APIs in oracle.sql.CLOB that manipulate the CLOB content from the requested position.

/**
* 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 getAsciiOutputStream(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 getCharacterOutputStream(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

Table 3-59 JDBC: New BFILE Streaming APIs

/**
* 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 BFILE Streaming Example (NewStreamLob.java)

Note:

Some of the Java code strings (in quotes) in the example should appear on one line, but instead, they wrap to the next lines. For example, the stmt.execute lines. Be aware of this if you are using this code and ensure that the strings appear on one line.