Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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 Oracle8i LOBs and how to use them, see the Oracle8i Application Developer's Guide--Large Objects (LOBs).

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

#### 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, you can use the standard `getBlob()` and `getClob()` methods, which return `java.sql.Blob` and `Clob` objects, respectively.

- Under JDK 1.1.x, there is no standard BLOB or CLOB functionality, but you can use the generic `getObject()` method, which returns `java.lang.Object`, and cast the output as desired.

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:

- Under either JDK 1.2.x or JDK 1.1.x, you can use `getBLOB()` and `getCLOB()`, which return `oracle.sql.BLOB` and `CLOB` objects, respectively.

- Under either JDK 1.2.x or JDK 1.1.x, you can also use the `getOracleObject()` method, which returns an `oracle.sql.Datum` object, and cast the output appropriately.

- Under JDK 1.1.x, you also have the option of using the Oracle extensions `getBlob()` and `getClob()`, which return `oracle.jdbc2.Blob` and `Clob` objects, respectively. (These `Blob` and `Clob` interfaces mimic the standard interfaces available in JDK 1.2.x.)

  Note: If using `getObject()` or `getOracleObject()`, then remember to cast the output, as necessary. For more information, see "Casting Your get Method Return Values".

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

##### 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);

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

- Under JDK 1.2.x, you can use the standard `setBlob()` and `setClob()` methods, which take `java.sql.Blob` and `Clob` locators as input.

- Under JDK 1.1.x, there is no standard BLOB or CLOB functionality, but you can use the generic `setObject()` method, which simply specifies a `java.lang.Object` input.

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

- Under either JDK 1.2.x or JDK 1.1.x, you can use `setBLOB()` and `setCLOB()`, which take `oracle.sql.BLOB` and `CLOB` locators as input, respectively.

- Under either JDK 1.2.x or JDK 1.1.x, you can also use the `setOracleObject()` method, which simply specifies an `oracle.sql.Datum` input.

- Under JDK 1.1.x, you also have the option of using the Oracle extensions `setBlob()` and `setClob()`, which take `oracle.jdbc2.Blob` and `Clob` locators as input, respectively. (These `Blob` and `Clob` interfaces mimic the standard interfaces available in JDK 1.2.x.)

##### 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();

##### 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();

### 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. 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 the Oracle8i Supplied PL/SQL Packages Reference.

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

  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`/`COMMIT` to write the data. 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.

##### 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];
    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.

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();

### 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 create a new `BLOB` or `CLOB` locator in your application, such as with a Java `new` statement. You must create the locator through a SQL operation, and then select it into your application.

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.

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

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

1. 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())");

2. Get the BLOB locator from the table.

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

3. 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.getBinaryOutputStream();

4. 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;

5. 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".

### 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);
    printBytes(bytes, length);

### Additional BLOB and CLOB Features

In addition to what has already been discussed in this chapter, the `oracle.sql.BLOB` and `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.

#### Additional BLOB Methods

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

- `getBinaryOutputStream()`: Returns a `java.io.OutputStream` to write data to the BLOB as a stream.

- `getBinaryStream()`: Returns the BLOB data for this `Blob` instance as a stream of bytes.

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

- `length()`: Returns the length of the BLOB in bytes.

- `position()`: Determines the byte position in the BLOB where a given pattern begins.

- `putBytes()`: Writes BLOB data, starting at a specified point, from a supplied buffer.

#### Additional CLOB Methods

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

- `getAsciiOutputStream()`: Returns a `java.io.OutputStream` to write data to the CLOB as a stream.

- `getAsciiStream()`: Returns the CLOB value designated by the `Clob` object as a stream of ASCII bytes.

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

- `getCharacterOutputStream()`: Returns a `java.io.Writer` to write data to the CLOB as a stream.

- `getCharacterStream()`: Returns the CLOB data as a stream of `Unicode` characters.

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

- `length()`: Returns the length of the CLOB in characters.

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

- `putString()`: Writes a string to a specified point in the CLOB data.

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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index