an example to insert data into Oracle Clob

[holly wang]

For theory backgroud:
http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/oralob.htm#1000888
Step1. Insert empty_clob() into the Clob column of Oracle
Step2. Set autocommit to false
Step3. Select Clob as oracle.sql.CLOB from database
Step4. Insert String into Clob
Step5. Commit
Example:
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
public class TestOracleClob implements Serializable{
public static void main(String[] args)
{

//create table test (id integer,content clob);
System.out.println("-------------------insert -----------------");
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection ("oracle server",
"loginid", "loginpassword");
//Class.forName("oracle.jdbc.driver.OracleDriver");

con.setAutoCommit(false);
//Ok 1
String sql="insert into test values(1,empty_clob())";
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(sql);
System.out.println("-------------------insert -----------------");
String sqll="select content from test where id=1 for update";
ResultSet rss=stmt.executeQuery(sqll);

if(rss.next()){
//CLOB clob = ((OracleResultSet)rss).getCLOB(1);
oracle.sql.CLOB clob= (oracle.sql.CLOB)rss.getClob("content");
clob.putString(1,"here is a string which contains more than 4000 character");
sql="update test set content=? where id=1";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setClob(1,clob);
pstmt.executeUpdate();
pstmt.close();
}
con.commit();

//Ok 2
//String sql1="insert into test values(2,empty_clob())";
//ResultSet rs3=stmt.executeQuery(sql1);
String sql12="insert into test values(?,?)";
PreparedStatement pstmt1=con.prepareStatement(sql12);
pstmt1.setInt(1,2);
pstmt1.setClob(2,oracle.sql.CLOB.empty_lob());
pstmt1.executeUpdate();

String sqll2="select content from test where id=2 for update";
ResultSet rss2=stmt.executeQuery(sqll2);
if(rss2.next()){
CLOB clob = ((OracleResultSet)rss2).getCLOB(1);
clob.putString(1,"affffffffffdfdfdfdddddddffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffdddfff");
String sql1="update test set content=? where id=2";
PreparedStatement pstmt=con.prepareStatement(sql1);
pstmt.setClob(1,clob);
pstmt.executeUpdate();
pstmt.close();
}
con.commit();
rss.close();
rss2.close();
pstmt1.close();
rs.close();
stmt.close();
con.close();
System.out.println("-------------insert ok-------------");
}catch(Exception e){
System.out.println("insert:"+e);
}
System.out.println("-------------------query -----------------");
try{
String content="";
//Class.forName("oracle.jdbc.driver.OracleDriver");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection ("oracleserver",
"id", "pass");

Statement stmt=con.createStatement();
String sql="select content from test where id=1";
ResultSet rs=stmt.executeQuery(sql);
if(rs.next()){
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
if(clob!=null){
Reader is=clob.getCharacterStream();
BufferedReader br=new BufferedReader(is);
String s=br.readLine();
while(s!=null){
content+=s+",";
s=br.readLine();
}
}

}
rs.close();
stmt.close();
con.close();
System.out.println("clob:"+content);
System.out.println("-------------query ok-------------");
}catch(Exception ee){
System.out.println("Exception:"+ee);
}

}
}

I use Oracle 9i and Java 1.4, JDBC 9i, friend also test above code on Oracle 8i with Java 1.2, hope this helps.