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.

[Mostafa Mirzayi]

Thank you.

[Martin Vajsar]

Please note that this was posted many years ago. The procedure is still valid in newer Oracle versions, though it could use a few modifications:

1) Step two should come first. This way it is commiting the insert of an empty clob. Depending on other circumstances, that might not hurt, but since the intent is to insert a populated clob, it should all happen in one transaction. (The code does it this way actually, only the summary is wrong.)

2) I'd emphasize the need to select for update in step three (again, it is done in the code, but not mentioned in the summary). However, when it actually is just one transaction, the select for update is not needed, as the row is already locked (but it doesn't hurt).

Oracle's own documentation is also a good source if you need to clear more doubts about Oracle's LOBs.