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:

• A thread on The Server Side mentions that

  • Oracle thin drivers prior to version 10 seem to have a size-limit problem.
  • You can use the Oracle 10.1.0.4 JDBC thin driver against the Oracle 9i database.
  • Oracle OCI drivers don't seem to have the same problem.

• Philip Greenspun on Oracle limits (mostly 8i)