This section describes how to insert CLOB values as string literals using SQL INSERT statements.
The simplest way to insert a character string into a CLOB column is to use a SQL INSERT statement and include the character string
a SQL string literal in the statement as shown in this sample program:
/**
* OracleClobInsert.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class OracleClobInsert {
public static void main(String [] args) {
Connection con = null;
try {
oracle.jdbc.pool.OracleDataSource ds
= new oracle.jdbc.pool.OracleDataSource();
ds.setDriverType("thin");
ds.setServerName("localhost");
ds.setPortNumber(1521);
ds.setDatabaseName("XE");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Deleting the record for re-testing
String subject = "Test on INSERT statement";
Statement sta = con.createStatement();
sta.executeUpdate("DELETE FROM Article WHERE Subject = '"
+subject+"'");
// Inserting CLOB value with a regular insert statement
sta = con.createStatement();
int count = sta.executeUpdate(
"INSERT INTO Article"
+" (ID, Subject, Body)"
+" VALUES (1, '"+subject+"', 'A BLOB (Binary Large OBject) is"
+" a large chunk of data which is stored in a database.')");
// Retrieving CLOB value with getString()
ResultSet res = sta.executeQuery(
"SELECT * FROM Article WHERE Subject = '"+subject+"'");
res.next();
System.out.println("The inserted record: ");
System.out.println(" Subject = "+res.getString("Subject"));
System.out.println(" Body = "+res.getString("Body"));
res.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
}
}
}
Compilation and execution of this program is below.
The output confirms that the character string value was correctly inserted
into the CLOB column:
C:\>javac -cp .;\local\lib\ojdbc14.jar OracleClobInsert.java
C:\>java -cp .;\local\lib\ojdbc14.jar OracleClobInsert
The inserted record:
Subject = Test on INSERT statement
Body = A BLOB (Binary Large OBject) is a large chunk of data
which is stored in a database.
Using SQL string literals to insert CLOB values into database is not recommended,
because quote characters (') in the CLOB values must be replaced with escape sequences ('').
Using PreparedStatement with setXXX() method is a much safer choice.
