Writing large amounts of data into a field of an Oracle database seems to raise some difficulties. The VARCHAR2 datatype is limited to 2K (or 4K) bytes, so you get into LOB (or CLOB or BLOB) datatypes. Then you run into problems with how to uses these datatypes from jdbc as well as more mysterious problems with speed and size limits.

Writing LOB fields in JDBC

Two-phase write

Consists of a

  1. write of the row without the LOB field
  2. a select for update
  3. getting the stream of the LOB field
  4. writing to that stream
  5. performing an update.

Further explanation ToBeWritten

One-phase write

Consists of a

  1. write of the row including the LOB field 

  InputStream lobStream = ...;
  int lobLength = ...;
  String insertSql = "INSERT INTO tablename (otherfield, lobfield) VALUES (?, ?)";
  PreparedStatement statement = connection.prepareStatement(insertSql);
  statement.setString(1, "other data");
  statement.setBinaryStream(2, lobStream, lobLength);
  statement.executeUpdate();

Unfortunately, you have to know the length of the stream ahead of time.

Speed Issues

ToBeWritten

Size Issues

Random notes:

iDIAcomputing: OracleLobs (last edited 2009-07-27 18:26:35 by localhost)