Oracle 10g CLOB 처리

10g 오라클 이전에는 EMPTY_CLOB()를 이용하던 것을 10g에서는 Standard API로 CLOB을 사용할 수 있도록 수정 되었다고 합니다.

1. Properties 사용하여 clob 입력

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.util.Properties;

..........   

// Load the database details into the variables.
String url      = "jdbc:oracle:thin:@localhost:1521:orcl";
String user     = "scott";
String password = "tiger";

// Create the properties object that holds all database details
Properties props = new Properties();

props.put("user", user );
props.put("password", password);
props.put("SetBigStringTryClob", "true");

// Load the Oracle JDBC driver class.
DriverManager.registerDriver(new OracleDriver());

// Get the database connection
Connection conn = DriverManager.getConnection( this.url, this.props );
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO clob_tab VALUES(?)");

// Read a big file(larger than 32765 bytes).
// Note: method readFile() not listed here.
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");

// The string data is automatically transformed into a CLOB and
// inserted into the database column.
// Make sure that the Connection property - 'SetBigStringTryClob' is
// set to true for the insert to happen.
pstmt.setString(1, str);pstmt.executeUpdate();

2. OraclePreparedStatement를 사용하여 CLOB 사용

import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

..........

// Create SQL query to insert CLOB data and other columns in the database.
String sql = "INSERT INTO clob_tab VALUES(?)";

// Read a big file(larger than 32765 bytes).
// Note: method readFile() not listed here.
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");

// Create the OraclePreparedStatement object
opstmt = (OraclePreparedStatement)conn.prepareStatement(sql);

// Use the new method to insert the CLOB data (for data greater or lesser than 32 KB)
opstmt.setStringForClob(1,str);

// Execute the OraclePreparedStatement
opstmt.executeUpdate();

...........

3. CLOB 데이터 가져오기

.....

// Create a PreparedStatement object
PreparedStatement pstmt = null;

// Create a ResultSet to hold the records retrieved.
ResultSet rset = null;

.......

// Create SQL query statement to retrieve records having CLOB data from
// the database.
String sqlCall = "SELECT clob_col FROM clob_tab";
pstmt= conn.prepareStatement(sqlCall);

// Execute the PrepareStatement
rset = pstmt.executeQuery();
String clobVal = null;

// Get the CLOB value larger than 32765 bytes from the resultset
while (rset.next())
{
    clobVal = rset.getString(1);
    System.out.println("CLOB length: "+clobVal.length());
}

출처: 잊어 먹었어요.