I struggled to update the content of a LONG column in an Oracle database with Spring JDBC. However, (in retrospective) this is more of a general JDBC problem than a Spring problem.

The problem first occured when I tried to use a regular SQL update statement to save a few thousand characters in that LONG field.

simpleJdbcTemplate.update("update mytable set content = ? where id = ?", 
new Object[] { o.toString(), o.getId() });

Hence, I tried to execute the same statement in a SQL editor, Oracle’s SQL Developer in my case, and got “ORA-1704: string literal too long” as a feedback from the database. Further analysis revealed that the LONG datatype has been deprecated in Oracle ever since 9i. It can hold 2GB max. but you’re encouraged to use CLOB instead. Some poking around the JDBC pages at oracle.com revealed sample code that describes how to manipulate LONG fields: http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/basic/LongSample/Readme.html.

Now, whether the below code is optimized or not is yet to be seen. It solves my problem, though.

final LobHandler lobHandler = new DefaultLobHandler();
final byte[] contentBytes = o.toString().getBytes();
final InputStreamReader clobReader = new InputStreamReader(new ByteArrayInputStream(contentBytes));
simpleJdbcTemplate.getJdbcOperations().execute("update mytable set content = ? where id = ?",
    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
    protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
        lobCreator.setClobAsCharacterStream(ps, 1, clobReader, contentBytes.length);
        ps.setString(2, o.getId());
    }
});