How do I place large (or at least nontrivial) BLOBs into Oracle with JDBC?

Question

I'm working on an application to do some batch processing, and want to store the input and output data as files in BLOB fields in an Oracle database. The Oracle version is 10g r2.

Using the PreparedStatement.setBinaryStream() method as below will insert a small text file into the database, but I'm not having any luck with a larger image file.

Am I doing something wrong? Is this possible to do with JDBC? Will I need to bother the DBA? Thanks for your help.

EDIT: The issue has been resolved. I've updated this code to a working sample:

import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class WriteBlobDriver {
    public static void main(String[] args) {
        Connection con = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(
                    "blahblah",
                    "blahblah",
                    "blahblah");
            con.setAutoCommit(false);
            Statement statement = con.createStatement();
            //statement.executeUpdate("UPDATE BATCH_GC_JOBS SET INPUT_BATCH_FILE = EMPTY_BLOB() WHERE JOB_ID = 'a'");

            //Get blob and associated output stream
            ResultSet resultSet = statement.executeQuery("SELECT INPUT_BATCH_FILE FROM BATCH_GC_JOBS WHERE JOB_ID = 'a' FOR UPDATE");
            resultSet.next();
            Blob blob = resultSet.getBlob(1);
            OutputStream outputStream = ((oracle.sql.BLOB)blob).getBinaryOutputStream();

            // Buffer to hold chunks of data to being written to the Blob.
            byte[] buffer = new byte[10* 1024];
            int nread = 0;

            //Write file to output stream
            File file = new File("C:\\TEMP\\Javanese_cat.jpg");
            FileInputStream fileInputStream = new FileInputStream(file);
            while ((nread = fileInputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, nread);
            }

            //Cleanup
            fileInputStream.close();
            outputStream.close();
            statement.close();
            con.commit();
            con.close();            
            System.out.println("done!");
        } catch (Exception e){
            e.printStackTrace();
        }
    }
}

Answer 1

Hi Phil,

I don't think you can update or insert into a BLOB/CLOB with JDBC in a single step (for data > 4k). From this example from Oracle, it seems you need to:

1. Insert an empty LOB with the SQL function empty_clob()
2. Select for update the LOB you've inserted
3. get the LOB in java with ResultSet.getBlob() then get the output stream with oracle.sql.BLOB.getBinaryOutputStream()
4. write to this output stream
5. close the output stream when you are finished

You would do something similar in Pl/SQL (SELECT FOR UPDATE a LOB, then write to it).