Oracle String Literal Too Long Solution

I recently ran into a problem when trying to insert more than 4000 characters into a CLOB field (Character Large Object) in an Oracle database. The issue previously was my system was building the SQL from scratch... concatenating strings and such. The solution was to use "Bind Variables" and Java Prepared Statements, instead of the Statement object.

This was done using Java 5. You'll also need the latest (compatible version with your DB) copy of the oracle JDBC driver. The download page is here: Oracle JDBC Driver

This code when used with MSSQL and DB2 databases still worked the same.


String connectionUrl = "jdbc:oracle:thin:.....etc....etc....";
String user = "user";
String pass = "password";
String sql = "INSERT INTO SCHEMA.TABLENAME (UUID,TEST_FIELD_NAME) VALUES (?,?)";

Connection connection = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

connection = DriverManager.getConnection(connectionUrl, user, pass);
PreparedStatement pstate = connection.prepareStatement(sql);

String testVar = "test root id";

pstate.setString(1, "testmatt12345");
pstate.setString(2, testVar);

boolean success = pstate.execute();

}catch(SQLException e)
{
[handle exception]
} catch (IllegalAccessException e) {
[handle exception]
} catch (InstantiationException e) {
[handle exception]
} catch (ClassNotFoundException e) {
[handle exception]
}finally
{
try {
connection.close();
} catch (SQLException e) {
[handle exception]
}
}