Release 1 (9.0.1)
Part Number A88879-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
LOB Support in Different Programmatic Environments, 10 of 11
You can perform the following tasks on LOBs with Java (JDBC):
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:
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.
With JDBC you can use Java to read both internal persistent LOBs and external LOBs (BFILEs).
BLOB and CLOB data types.
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.
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.
You can get a reference to any of the above LOBs in the following two ways:
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.
For further JDBC syntax and information about using JDBC with LOBs:
|
See:
|
The following JDBC methods operate on BLOBs, CLOBs, and BFILEs:
| Method | Description |
|---|---|
|
int putBytes(long, by |
Inserts the byte array into the |
| Method | Description |
|---|---|
|
public java.io.InputStream getBinaryStream()) |
Streams the |
|
public java.io.OutputStream getBinaryOutputStream() |
Writes to |
| Method | Description |
|---|---|
|
public java.io.InputStream getBinaryStream() |
Reads the BFILE as a binary stream |
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:
"Dumping lobs java.sql.SQLException: ORA-03115: unsupported network datatype or representation etc."
See Oracle9i 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 examples shipped with Oracle9i or get a LOB examplet from http://www.oracle.com/java/jdbc.
Oracle9i JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace prior releases' workaround of using the following procedures from the DBMS_LOB PL/SQL package:
DBMS_LOB.createTemporary()
DBMS_LOB.isTemporary()
DBMS_LOB.freeTemporary()
Table 3-50 JDBC: Temporary BLOB APIs
oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-51 lists the new Oracle extension APIs in oracle.sql.CLOB for accessing temporary CLOBs.
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().
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.
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);
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:
/** * Check whether the BLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
BLOB blob = ... // See if the BLOB is opened boolean isOpen = blob.isOpen ();
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 ();
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.
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);
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:
/** * Check whether the CLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
CLOB clob = ... // See if the CLOB is opened boolean isOpen = clob.isOpen ();
To close a CLOB, the JDBC application can use the close method defined in oracle.sql.CLOB. The close API is defined as follows:
/** * Close a previously opened CLOB. */ public void close () throws SQLException
The usage example is:
CLOB clob = ... // close the CLOB clob.close ();
oracle.sql.BFILE class wraps the database BFILE object. Table 3-54 lists the new Oracle extension APIs in oracle.sql.BFILE for opening and closing 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 ();
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:
/** * Check whether the BFILE is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
BFILE bfile = ... // See if the BFILE is opened boolean isOpen = bfile.isOpen ();
To close a BFILE, your JDBC application can use the CLOSE method defined in oracle.sql.BFILE. The CLOSE API is defined as follows:
/** * Close a previously opened BFILE. */ public void close () throws SQLException
The usage example is --
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", "tiger"); // It's 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 (); } }
Oracle9i JDBC drivers contain APIs to trim internal LOBs. These APIs replace previous techniques that used DBMS_LOB.trim().
oracle.sql.blob class is Oracle JDBC driver's implementation of the standard JDBC java.sql.blob interface. Table 3-55 lists the new Oracle extension API in oracle.sql.blob that trims BLOBs.
| Methods | Description |
|---|---|
|
public void trim(long newlen) throws SQLException |
Trims the BLOB |
The trim API is defined as follows:
/** * Trim the value of the BLOB to the length you specify in the newlen parameter. * @param newlen the new length of the BLOB. */ public void trim (long newlen) throws SQLException
The newlen parameter specifies the new length of the BLOB.
oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-56 lists the new Oracle extension API in oracle.sql.CLOB that trims CLOBs.
| Methods | Description |
|---|---|
|
public void trim(long newlen) throws SQLException |
Trims the CLOB |
The trim API is defined as follows:
/** * Trim the value of the CLOB to the length you specify in the newlen parameter. * @param newlen the new length of the CLOB. */ public void trim (long newlen) throws SQLException
The newlen parameter specifies the new length of the CLOB.
Oracle9i JDBC drivers contains the new LOB streaming APIs to read from/write to a LOB at the requested position from a Java stream. In prior releases, LOB streaming APIs did not specify the offset.
oracle.sql.BLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Blob interface. Table 3-57 lists the new Oracle extension APIs in oracle.sql.BLOB that manipulate the BLOB content from the requested position.
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 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
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.
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 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
oracle.sql.bfile class wraps the database BFILEs. Table 3-59 lists the new Oracle extension APIs in oracle.sql.bfile that reads BFILE content from the requested position.
| Methods | Description |
|---|---|
|
public java.io.InputStream getBinaryStream(long pos) throws SQLException |
Reads from the BFILE as a stream |
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", "tiger"); // It's 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.getCharacterOutputStream(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.getBinaryOutputStream(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(); } }
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 a 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:
JDBC applications cannot read or write to empty LOBs created from the above APIs. An ORA-17098 "Invalid empty lob operation" results if your application attempts to read/write to an empty LOB.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|