LOB column read/write in Oracle
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
public class OracleLobType {
public static void insertLOB() throws ClassNotFoundException, SQLException, IOException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("ConnectionString");
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
conn.setAutoCommit(false);
sql = "INSERT INTO Table(id, clobColumn) VALUES(?, empty_clob())";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 1);
pstmt.executeUpdate();
pstmt.close();
pstmt = null;
sql = "SELECT clobColumn FROM Table WHERE id = 1 FOR UPDATE";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
CLOB clobValue = null;
while (rs.next()) {
clobValue = (CLOB) rs.getClob("clobColumn");
}
Writer wr = clobValue.getCharacterOutputStream();
wr.write("clob data");
wr.flush();
wr.close();
rs.close();
pstmt.close();
conn.commit();
conn.close();
}
public static void getLOG() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("ConnectionString");
String sql = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
sql = "SELECT clobColumn FROM Table WHERE id = 1";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
CLOB clobValue = null;
while (rs.next()) {
OracleResultSet ors = (OracleResultSet) rs;
clobValue = (CLOB) ors.getCLOB("clobColumn");
}
String value = "";
if (clobValue != null && clobValue.length() > 0)
// clobValue.stringValue();
value = clobValue.getSubString(1, (int) clobValue.length());
rs.close();
pstmt.close();
conn.close();
}
}