Java JDBC How to - Execute sql statement via JDBC with CLOB binding in Oracle

Question

We would like to know how to execute sql statement via JDBC with CLOB binding in Oracle.

Answer

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
// w ww  .  j a v a  2 s .  co  m
public class Main {
  public static final int SIZE = 8192;
  public static void main(String[] args) throws Exception {
    byte[] data = new byte[SIZE];
    for (int i = 0; i < SIZE; ++i) {
      data[i] = (byte) (64 + (i % 32));
    }
    ByteArrayInputStream stream = new ByteArrayInputStream(data);
    // DriverManager.registerDriver(new OracleDriver());
    Connection c = DriverManager.getConnection(
        "jdbc:oracle:thin:@some_database", "user", "password");

    String sql = "DECLARE\n" + //
        "  l_line    CLOB;\n" + //
        "BEGIN\n" + //
        "  l_line := ?;\n" + //
        "  UPDATE table SET log = log || l_line || CHR(10) WHERE id = ?;\n" + //
        "END;\n"; //

    PreparedStatement stmt = c.prepareStatement(sql);
    stmt.setAsciiStream(1, stream, SIZE);
    stmt.setInt(2, 1);
    stmt.execute();
    stmt.close();
    c.commit();
    c.close();
  }
}