Wednesday, January 6, 2010

Oracle’s Strange way of Dealing with LOB’s

A LOB is a database term for a data type that is very big; a Large Object so to speak.  There are times when you want to create a record that has one of these LOBs but for some reason, Oracle has some strange limitations when it comes to the size of one of these objects when you ‘insert’ them into the database.

For JDBC calls it looks like there is a max of 4Kbytes.  If your insert will never be bigger than this, then stop reading, you’re fine.  You can get around this limitation by using PL/SQL (wrap your insert in a BEGIN INSERT…; END;) as long as you don’t need to insert anything greater than 32Kbytes.

If you need to go higher than this, you need to do something a little strange.

Basically you need to insert a blank LOB into the record, then select the record for update and then write your data to the LOB you selected.  Here’s how it goes.

InputStream is = new ByteArrayInputStream("hello world".getBytes());
PreparedStatement insert = 
   conn.prepareStatement(
      "INSERT INTO test_table (id, data) values (?, empty_blob())");
insert.setLong(1, 1L);
insert.execute();
insert.close();
PreparedStatement select = 
   conn.prepareStatement(
      "SELECT * FROM test_table WHERE id = ? FOR UPDATE");
select.setLong(1, 1L);
ResultSet rs = select.executeQuery();
rs.next();
Blob blob = rs.getBlob("data");
OutputStream os = blob.setBinaryStream(0L);
byte[] buf = new byte[1024];
int len = 0;
while ((len = is.read()) > 0)
{
	os.write(buf, 0, len);
}
is.close();
os.flush();
os.close();
select.close();

Not a whole lot of fun, but it gets the job done.  I managed to find a way to get it done in a single statement but it requires PL/SQL

InputStream is = new ByteArrayInputStream("hello world".getBytes());
CallableStatement insert = 
   conn.prepareCall(
      "BEGIN 
         INSERT INTO test_table (id, data) values (?, empty_blob()) 
         RETURNING data INTO ?; 
       END;");
insert.registerOutParameter(2, Types.BLOB);
insert.setLong(1, 1L);
insert.execute();
Blob blob = insert.getBlob(2);
OutputStream os = blob.setBinaryStream(0L);
byte[] buf = new byte[1024];
int len = 0;
while ((len = is.read()) > 0)
{
	os.write(buf, 0, len);
}
is.close();
os.flush();
os.close();
insert.close();

No comments: