CLOB

Character Large Objects (CLOB)
  • A CLOB is a collection of Character data stored as a single entity in the database.
  • CLOB can be used to store large text documents(may plain text or xml documents)
  • CLOB Type can store maximum of 4GB data.
Steps to insert CLOB type file in the database

All steps are exactly same as BLOB, except the following differences

  • 1. Instead of FileInputStream, we have to take FileReader.
  • 2. Instead of setBinaryStream() method we have to use setCharacterStream() method.
public void setCharacterStream(int index,Reader r) throws SQLException
public void setCharacterStream(int index,Reader r,int length) throws SQLException
public void setCharacterStream(int index,Reader r,long length) throws SQLException

E.g

import java.sql.*;
import java.util.*;
import java.text.*;
**
 * 
 * @author ashok.mariyala
 *
 */
public class ClobInsertTest { 
   public static void main(String[] args) throws Exception { 
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      String sqlQuery = "insert into students values(?,?)"; 
      PreparedStatement pst = con.prepareStatement(sqlQuery); 
      pst.setString(1,"Ashok Kumar"); 
      File f = new File("ashok_resume.doc");
      FileReader fr = new FileReader(f);
      ps.setCharacterStream(2,fr);
      System.out.println("file is inserting from :"+f.getAbsolutePath());
      int rowCount = pst.executeUpdate(); 
      if(rowCount == 0) 
         System.out.println("Record Not inserted"); 
      else
         System.out.println("Record inserted"); 
      con.close();
   }
}
Retrieving CLOB Type from Database

All steps are exactly same as BLOB, except the following differences.

  1. Instead of using FileOutputStream,we have to use FileWriter
  2. Instead of using getBinaryStream() method we have to use getCharacterStream() method
import java.sql.*;
import java.util.*;
import java.text.*;
**
 * 
 * @author ashok.mariyala
 *
 */
public class ClobRetriveTest { 
   public static void main(String[] args) throws Exception { 
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      String sqlQuery = "select * from students"; 
      PreparedStatement pst = con.prepareStatement(sqlQuery); 
      ResultSet rs = pst.executeQuery(); 
      FileOutputStream os = new FileOutputStream("ashok_resume_db.jpeg");
      while(rs.next()) { 
         String name = rs.getString(1); 
         Reader r = rs.getCharacterStream(2);
         int i=r.read();
         while(i != -1)
            fw.write(i);
            i = r.read();
         }
         fw.flush(); 
         System.out.println("Retrieved Successfully file :ashok_resume_db.txt");
      }
      con.close();
   }
}
CLOB

Scroll to top