Blog / 2011 / May / 13

Writing to an Oracle CLOB from Java

May 13, 2011

This code inserts a row into an Oracle table including writing a CLOB. Note that the CLOB isn't inserted normally, but a blank one is inserted and then updated.

final String lSql = "INSERT INTO applications (app_date_time, key, application_data) values (SYSTIMESTAMP,?,?)";
PreparedStatement lStatement = null;
CLOB lTempClob = null;

try
{
  lStatement = lConnection.prepareStatement(lSql);
  lStatement.setString(1, lRandomKey);

  lTempClob = CLOB.createTemporary(lConnection, true, CLOB.DURATION_SESSION);
  lTempClob.open(CLOB.MODE_READWRITE);

  Writer lTempClobWriter = lTempClob.getCharacterOutputStream();
  lTempClobWriter.write(lXmlToStore);
  lTempClobWriter.flush();
  lTempClobWriter.close();
  lStatement.setObject(2, lTempClob);

  lStatement.execute();
  lConnection.commit();
}
catch (IOException lSqlEx)
{
  throw new RuntimeException("IOException saving application data, underlying problem is " + lSqlEx.getMessage(), lSqlEx);
}
catch (SQLException lSqlEx)
{
  throw new RuntimeException("Problems saving application data, underlying problem is " + lSqlEx.getMessage(), lSqlEx);
}
finally
{
  if (lTempClob != null)
  {
    try
    {
      lTempClob.freeTemporary();
    }
    catch (SQLException lEx)
    {
      // ignore exception
    }
  }
}

Tags: oracle sql clob