Using Java (JDBC) to Work with LOBs

Changing Internal LOBs with 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 LOBs and External LOBs (BFILEs) with Java

JDBC interface allows you to use Java to read both internal 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

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.

Referencing the LOBs

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 Syntax References and Further Information

Syntax

See: Oracle8i JDBC Developer's Guide and Reference for detailed documentation, including parameters, parameter types, return values, and example code. Oracle8i SQLJ Developer's Guide and Reference http://www.oracle.com/java/codesamples/jdbc/demos/oci8/object-samples/fileexample.txt http://www.oracle.com/java/codesamples/jdbc/demos/oci8/object-samples/lobexample.txt http://www.oracle.com/java/codesamples/jdbc/demos/oci8/object-samples/plsql_fileexample.txt http://www.oracle.com/java/codesamples/jdbc/demos/oci8/object-samples/plsql_lobexample.txt

Further Information

Refer to the following web site for further information about JDBC:

http://www.oracle.com/products

JDBC Methods for Operating on LOBs

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

• BLOBs:

To modify BLOB values, see Table 3-36

• To read or examine BLOB values, see Table 3-37

• For BLOB buffering, see Table 3-38

• CLOBs:
  • To modify CLOBs, see Table 3-39

  • To read or examine CLOB values, see Table 3-40

  • For CLOB buffering, see Table 3-41

• BFILEs:
  • To read or examine BFILEs, see Table 3-42

  • For BFILE buffering, see Table 3-43

JDBC oracle.sql.BLOB Methods To Modify BLOB Values:

Table 3-36 JDBC oracle.sql.BLOB Methods To Modify BLOB Values

Method	Description
int putBytes(long, byte[])	Inserts the byte array into the LOB, starting at the given offset

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

Table 3-37 JDBC oracle.sql.BLOB Methods Tor Read or Examine BLOB Values

Method	Description
byte[] getBytes(long, int)	Gets the contents of the LOB as an array of bytes, given an offset
long position(byte[],long)	Finds the given byte array within the LOB, given an offset
long position(Blob,long)	Finds the given BLOB within the LOB
public boolean equals(java.lang.Object)	Compares this LOB with another . Compares the LOB locators.
public long length()	Returns the length of the LOB
public int getChunkSize()	Returns the ChunkSize of the LOB

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

Table 3-38 JDBC oracle.sql.BLOB Methods and Properties for BLOB-Buffering

Method	Description
public java.io.InputStream getBinaryStream())	Streams the LOB as a binary stream
public java.io.OutputStream getBinaryOutputStream()	Writes to LOB as a binary stream

JDBC oracle.sql.CLOB Methods To Modify CLOB Values

Table 3-39 JDBC oracle.sql.CLOB Methods To Modify CLOB Values

Method	Description
int putString(long, java.lang.String)	Inserts the string into the LOB, starting at the given offset
int putChars(long, char[])	Inserts the character array into the LOB, starting at the given offset

JDBC oracle.sql.CLOB Methods To Read or Examine CLOB Values

Table 3-40 JDBC oracle.sql.CLOB Methods To Read or Examine CLOB Values

Method	Description
java.lang.String getSubString(long, int)	Returns a substring of the LOB as a string
int getChars(long, int, char[])	Reads a subset of the LOB into a character array
long position(java.lang.String, long)	Finds the given String within the LOB, given an offset
long position(oracle.jdbc2.Clob, long)	Finds the given CLOB within the LOB, given an offset
boolean equals(java.lang.Object)	Compares this LOB with another
long length()	Returns the length of the LOB
int getChunkSize()	Returns the ChunkSize of the LOB

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

Table 3-41 JDBC oracle.sql.CLOB Methods and Properties for CLOB-Buffering

Method	Description
java.io.InputStream getAsciiStream()	Reads the LOB as an ASCII stream
java.io.OutputStream getAsciiOutputStream()	Writes to the LOB from an ASCII stream
java.io.Reader getCharacterStream()	Reads the LOB as a character stream
java.io.Writer getCharacterOutputStream()	Writes to LOB from a character stream

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

Table 3-42 JDBC oracle.sql.BFILE Methods To Read or Examine External LOB (BFILE) Values

Method	Description
byte[] getBytes(long, int)	Gets the contents of the BFILE as an array of bytes, given an offset
int getBytes(long, int, byte[])	Reads a subset of the BFILE into a byte array
long position(oracle.sql.BFILE, long)	Finds the first appearance of the given BFILE contents within the LOB, from the given offset
long position(byte[], long)	Finds the first appearance of the given byte array within the BFILE, from the given offset
boolean equals(java.lang.Object)	Compares this BFILE with another. Compares locator bytes.
long length()	Returns the length of the BFILE
boolean fileExists()	Checks if the operating system (OS) file referenced by this BFILE exists
public void openFile()	Opens the OS file referenced by this BFILE
public void closeFile()	Closes the OS file referenced by this BFILE
public boolean isFileOpen()	Checks if this BFILE is already open
public java.lang.String getDirAlias()	Gets the directory alias for this BFILE
public java.lang.String getName()	Gets the file name referenced by this BFILE

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

Table 3-43 JDBC oracle.sql.BFILE Methods and Properties for BFILE-Buffering

Method	Description
public java.io.InputStream getBinaryStream()	Reads the BFILE as a binary stream

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

OracleBlob and OracleClob were Oracle specific functions used inJDBC 8.0.x drivers to access LOB data. In 8.1.x and future releases, 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 :

"Dumping lobs java.sql.SQLException: ORA-03115: unsupported network datatype or 
representation etc."

See release 8.1.5 Oracle8i JDBC Developer's Guide and Reference for a description of these non-supported functions and alternative and improved JDBC methods.

For further ideas on working with LOBs with Java, refer to the LOB Example sample shipped with Oracle8i or get a LOB examplet from www.oracle.com/java/jdbc.