14 Working with LOBs and BFILEs

Getting and Passing BLOB, CLOB, and NCLOB Locators

Standard as well as Oracle-specific getter and setter methods are available for retrieving or passing LOB locators from or to the database. This section covers the following topics:

• Retrieving BLOB, CLOB, and NCLOB Locators

• Passing BLOB, CLOB and NCLOB Locators

// Select LOB locator into standard result set.
ResultSet rs =
   stmt.executeQuery ("SELECT blob_col, clob_col, nclob_col FROM lob_table");
while (rs.next())
{
   // Get LOB locators into Java container classes.
   java.sql.Blob blob = (java.sql.Blob)rs.getObject(1);
   java.sql.Clob clob = (java.sql.Clob)rs.getObject(2);
   java.sql.NClob nclob = (java.sql.NClob)rs.getObject(3);
   (...process...)
}

// Get LOB locators into Java container classes.
   oracle.sql.BLOB blob = (BLOB)rs.getObject(1);
   oracle.sql.CLOB clob = (CLOB)rs.getObject(2);
   oracle.sql.NCLOB nclob = (NCLOB)rs.getObject(3);
   (...process...)

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{? = call func()}");
ocs.registerOutParameter(1, OracleTypes.CLOB);
ocs.execute();
oracle.sql.CLOB clob = ocs.getCLOB(1);

public void setBlob(int index, Blob value);
public void setClob(int index, Clob value);
public void setNClob(int index, NClob value);

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                            ("INSERT INTO blob_table VALUES(?)"); 
ops.setBLOB(1, my_blob);
ops.execute();

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{call proc(?))}");
ocs.setClob(1, my_clob);
ocs.execute();

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{call proc(?))}");
ocs.setNClob(1, my_nclob);
ocs.execute();

Reading and Writing BLOB, CLOB and NCLOB 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. Unlike LONG and LONG RAW data, 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 java.sql.BLOB, java.sql.CLOB, and java.sql.NCLOB 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 java.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 setBinaryStream method of an java.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 java.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 java.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 setAsciiStream or setCharacterStream method of an java.sql.CLOB object to retrieve the CLOB as an output stream to be written back to the CLOB. The setAsciiStream method returns an ASCII output stream in a java.io.OutputStream object. The setCharacterStream method returns a Unicode output stream in a java.io.Writer object.

  As with any Stream 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.

• To read from an NCLOB, use the getAsciiStream or getCharacterStream method of an java.sql.NCLOB object to retrieve the entire NCLOB 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 java.sql.NCLOB object to retrieve a subset of the NCLOB as a character string of type java.lang.String.

• To write to an NCLOB, use the setAsciiStream or setCharacterStream method of an oracle.sql.NCLOB object to retrieve the NCLOB as an output stream to be written back to the NCLOB. The setAsciiStream method returns an ASCII output stream in a java.io.OutputStream object. The setCharacterStream method returns a Unicode output stream in a java.io.Writer object.

  As with any Stream 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 run an UPDATE to write the data. However, you need to call close or flush to ensure all changes are written. 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 or an NCLOB, the JDBC drivers perform all character set conversions for you.

  • When reading a LOB using any of the getXXX methods described in the section, the returned stream fetches blocks of data from the database needed. The entire LOB is not fetched all at once, which makes it practical to read very large LOBs.

Example: Reading BLOB Data

Use the getBinaryStream method of the oracle.sql.BLOB class to read BLOB data. The getBinaryStream method provides access to the BLOB data through a binary stream.

The following example uses the getBinaryStream method to read BLOB data through 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(1L);
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(1L);
char [] char_array = new char [10];
int chars_read = char_stream.read (char_array, 0, 10);
...

Example: Reading NCLOB Data

The following example uses the getCharacterStream method to read NCLOB 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 NCLOB data from NCLOB locator into Reader char stream.
Reader char_stream = my_nclob.getCharacterStream(1L);
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.NCLOB class to read NCLOB data through an ASCII character stream. It then reads the ASCII stream into a byte array, returning the number of bytes read, as well.

// Read NCLOB data from NCLOB locator into Input ASCII character stream
Inputstream asciiChar_stream = my_nclob.getAsciiStream(1L); 
byte[] asciiChar_array = new byte[10]; 
int asciiChar_read = asciiChar_stream.read(asciiChar_array,0,10);

Example: Writing BLOB Data

Use the setBinaryOutputStream 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 setBinaryOutputStream 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).setBinaryOutputStream(1L);
outstream.write(data);
...

Example: Writing CLOB Data

Use the setCharacterStream method or the setAsciiStream method to write data to a CLOB. The setCharacterStream method returns a Unicode output stream. The setAsciiStream method returns an ASCII output stream.

The following example reads a vector of data into a character array, then uses the setCharacterStream method to write the array of character data to a CLOB.

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).setCharacterStream();
writer.write(data);
writer.flush();
writer.close();
...

The next example reads a vector of data into a byte array, then uses the setAsciiStream method to write the array of ASCII data to a CLOB.

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.setAsciiStream();
out.write(data);
out.flush();
out.close();

Example: Writing NCLOB Data

Use the setCharacterStream method or the setAsciiStream method to write data to an NCLOB. The setCharacterStream method returns a Unicode output stream. The setAsciiStream method returns an ASCII output stream.

The following example reads a vector of data into a character array, then uses the setCharacterStream method to write the array of character data to an NCLOB.

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 an NClob
writer = ((NCLOB)my_nclob).setCharacterStream();
writer.write(data);
writer.flush();
writer.close();
...

The next example reads a vector of data into a byte array, then uses the setAsciiStream method to write the array of ASCII data to an NCLOB.

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 NClob 
out = nclob.setAsciiStream();
out.write(data);
out.flush();
out.close();

Creating and Populating a BLOB, CLOB or NCLOB Column

Create and populate aBLOB, CLOB, or NCLOB column in a table by using SQL statements.

Create a BLOB, CLOB, or NCLOB 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, and then copying the data into the LOB.

Creating a BLOB, CLOB, or NCLOB Column in a New Table

To create a BLOB, CLOB, or NCLOB column in a new table, run 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, CLOB, or NCLOB Column in a New Table

This example demonstrates how to populate a BLOB, CLOB, or NCLOB 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 john.gif file to a BLOB:

1. Begin by using SQL statements to create the BLOB entry in the table. Use the empty_blob function 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' FOR UPDATE";
   ResultSet rset = stmt.executeQuery(cmd);
   rset.next();
   BLOB blob = ((OracleResultSet)rset).getBLOB(2);
   

   Note:

   You must disable auto-commit mode.

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

4. Call getBufferSize to retrieve the ideal buffer size 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 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 and commit the changes.

   while ((length = instream.read(buffer)) != -1)
      outstream.write(buffer, 0, length);
   instream.close();
   outstream.close();
   conn.commit();
   

Once your data is in the BLOB, CLOB, or NCLOB, you can manipulate the data.

Accessing and Manipulating BLOB, CLOB, or NCLOB Data

Once you have your BLOB, CLOB, or NCLOB 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.

After you select the locators, you can retrieve the BLOB, CLOB, or NCLOB data. After retrieving the BLOB, CLOB, or NCLOB 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);
blob.printBytes(bytes, length);

Getting and Passing BFILE Locators

Getter and setter methods are available for retrieving or passing BFILE locators from or to the database.

Retrieving BFILE Locators

Given a standard JDBC result set or callable statement object that includes BFILE locators, you can access the locators by using the standard result set getObject method. This method returns an oracle.sql.BFILE object.

You can also access the locators by casting your result set to OracleResultSet or your callable statement to OracleCallableStatement and using the getOracleObject or getBFILE method.

Example: Getting a BFILE locator from a Result Set

Assume that the database has a table called bfile_table with a single column for the BFILE locator bfile_col. This example assumes that you have already created your Statement object stmt.

Select the BFILE locator into a standard result set. If you cast the result set to OracleResultSet, then you can use getBFILE to get the BFILE locator, as follows:

// Select the BFILE locator into a result set
ResultSet rs = stmt.executeQuery("SELECT bfile_col FROM bfile_table"); 
while (rs.next()) 
{
   oracle.sql.BFILE my_bfile = ((OracleResultSet)rs).getBFILE(1); 
}

Note that as an alternative, you can use getObject to return the BFILE locator. In this case, because getObject returns a java.lang.Object, cast the results to BFILE. For example:

oracle.sql.BFILE my_bfile = (BFILE)rs.getObject(1); 

Example: Getting a BFILE Locator from a Callable Statement

Assume you have an OracleCallableStatement object ocs that calls a function func that has a BFILE output parameter. The following code example sets up the callable statement, registers the output parameter as OracleTypes.BFILE, runs the statement, and retrieves the BFILE locator:

OracleCallableStatement ocs = 
   (OracleCallableStatement)conn.prepareCall("{? = call func()}");
ocs.registerOutParameter(1, OracleTypes.BFILE);
ocs.execute();
oracle.sql.BFILE bfile = ocs.getBFILE(1);

Passing BFILE Locators

To pass a BFILE locator to a prepared statement or callable statement, you can do one of the following:

• Use the standard setObject method.

• Cast the statement to OraclePreparedStatement or OracleCallableStatement, and use the setOracleObject or setBFILE method.

These methods take the parameter index and an oracle.sql.BFILE object as input.

Example: Passing a BFILE Locator to a Prepared Statement

Assume you want to insert a BFILE locator into a table, and you have an OraclePreparedStatement object ops to insert data into a table. The first column is a string, the second column is a BFILE, and you have a valid oracle.sql.BFILE object, bfile. Write the BFILE to the database, as follows:

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                            ("INSERT INTO my_bfile_table VALUES (?,?)");
ops.setString(1,"one");
ops.setBFILE(2, bfile);
ops.execute();

Example: Passing a BFILE Locator to a Callable Statement

Passing a BFILE locator to a callable statement is similar to passing it to a prepared statement. In this case, the BFILE locator is passed to the myGetFileLength procedure, which returns the BFILE length as a numeric value.

OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall
                               ("begin ? := myGetFileLength (?); end;");
try
{
   cstmt.registerOutParameter (1, Types.NUMERIC);
   cstmt.setBFILE (2, bfile);
   cstmt.execute ();
   return cstmt.getLong (1);
}

Reading BFILE Data

To read BFILE data, you must first get the BFILE locator. You can get the locator from either a callable statement or a result set. Once you obtain the locator, you can call a number of methods on the BFILE without opening it. For example, you can use the oracle.sql.BFILE methods fileExists() and isFileOpen() to determine whether the BFILE exists and if it is open. However, if you want to read and manipulate the data, then you must open and close the BFILE, as follows:

• Use the openFile method of the oracle.sql.BFILE class to open a BFILE.

• When you are done, use the closeFile method of the BFILE class.

BFILE data is through a Java stream. To read from a BFILE, use the getBinaryStream method of an oracle.sql.BFILE object to access the file 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 file data and use the close method when you finish.

Example: Reading BFILE Data

The following example uses the getBinaryStream method of an oracle.sql.BFILE object to read BFILE data into a byte stream and then read the byte stream into a byte array. The example assumes that the BFILE has already been opened.

// Read BFILE data from a BFILE locator
Inputstream in = bfile.getBinaryStream();
byte[] byte_array = new byte{10};
int byte_read = in.read(byte_array);

Creating and Populating a BFILE Column

This section discusses how to create a BFILE column in a table with SQL operations and specify the location where the BFILE resides. The examples in this section assume that you have already created your Connection object conn and Statement object stmt.

Creating a BFILE Column in a New Table

To work with BFILE data, create a BFILE column in a table, and specify the location of the BFILE. To specify the location of the BFILE, use the SQL CREATE DIRECTORY...AS statement to specify an alias for the directory where the BFILE resides. In this example, the directory alias is test_dir and the BFILE resides in the /home/work directory.

String cmd;
cmd = "CREATE DIRECTORY test_dir AS '/home/work'";
stmt.execute (cmd);

Use the SQL CREATE TABLE statement to create a table containing a BFILE column. In this example, the name of the table is my_bfile_table.

// Create a table containing a BFILE field 
cmd = "CREATE TABLE my_bfile_table (x varchar2 (30), b bfile)";
stmt.execute (cmd);

In this example, the VARCHAR2 column designates a row number and the BFILE column stores the locator of the BFILE data.

Populating a BFILE Column

Use the SQL INSERT INTO...VALUES statement to populate the VARCHAR2 and BFILE fields. The BFILE column is populated with the locator to the BFILE data. To populate the BFILE column, use the bfilename function to specify the directory alias and the name of the BFILE file.

cmd ="INSERT INTO my_bfile_table VALUES ('one', bfilename(test_dir, 
                                         'file1.data'))";
stmt.execute (cmd);
cmd ="INSERT INTO my_bfile_table VALUES ('two', bfilename(test_dir,  
                                         'jdbcTest.data'))";
stmt.execute (cmd);

In this example, the name of the directory alias is test_dir. The locator of the BFILE file1.data is loaded into the BFILE column on row one, and the locator of the BFILE jdbcTest.data is loaded into the bfile column on row two.

As an alternative, you may want to create the row for the row number and BFILE locator now, but wait until later to insert the locator. In this case, insert the row number into the table and null as a place holder for the BFILE locator.

cmd ="INSERT INTO my_bfile_table VALUES ('three', null)";
stmt.execute(cmd);

Here, three is inserted into the row number column and null is inserted as the place holder. Later in your program, insert the BFILE locator into the table by using a prepared statement.

First get a valid BFILE locator into the bfile object:

rs = stmt.executeQuery("SELECT b FROM my_bfile_table WHERE x='two'");
rs.next();
oracle.sql.BFILE bfile = ((OracleResultSet)rs).getBFILE(1);

Then, create your prepared statement. Note that because this example uses the setBFILE method to identify the BFILE, the prepared statement must be cast to OraclePreparedStatement:

OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
                      (UPDATE my_bfile_table SET b=? WHERE x = 'three');
ops.setBFILE(1, bfile);
ops.execute();

Now row two and row three contain the same BFILE.

Once you have the BFILE locators available in a table, you can access and manipulate the BFILE data.

Accessing and Manipulating BFILE Data

Once you have the BFILE locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you must first select its locator from a result set or a callable statement.

The following code continues the example from the preceding section, getting the locator of the BFILE from row two of a table into a result set. The result set is cast to OracleResultSet so that oracle.sql.* methods can be used on it. Several of the methods applied to the BFILE, such as getDirAlias and getName, do not require you to open the BFILE. Methods that manipulate the BFILE data, such as reading, getting the length, and displaying, do require you to open the BFILE.

When you finish manipulating the BFILE data, you must close the BFILE.

// select the bfile locator
cmd = "SELECT *  FROM my_bfile_table WHERE x = 'two'";
rset = stmt.executeQuery (cmd);

if (rset.next ())
    BFILE bfile = ((OracleResultSet)rset).getBFILE (2);

// for these methods, you do not have to open the bfile
println("getDirAlias() = " + bfile.getDirAlias());
println("getName() = " + bfile.getName());
println("fileExists() = " + bfile.fileExists());
println("isFileOpen() = " + bfile.isFileOpen());

// now open the bfile to get the data
bfile.openFile();

// get the BFILE data as a binary stream
InputStream in = bfile.getBinaryStream();
int length ;

// read the bfile data in 6-byte chunks
byte[] buf = new byte[6];

while ((length = in.read(buf)) != -1)
{
    // append and display the bfile data in 6-byte chunks 
   StringBuffer sb = new StringBuffer(length);
   for (int i=0; i<length; i++)
      sb.append( (char)buf[i] );
   System.out.println(sb.toString());
}

// we are done working with the input stream. Close it.   
in.close();

// we are done working with the BFILE. Close it.  
bfile.closeFile();