오라클 10g 에서의 CLOB처리

기존 오라클에서 empty_clob() 를 써서 하던 부분을 오라클 10g에서는 standard API로 clob를 사용할 수 있도록
수정되었다고 합니다.

직접 테스트 해보진 않아서 정확한 작동은 보장하진 않습니다 -_-;

1.  property 사용하여 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()); }