My question is: How do you get around the ORA-01704: string literal too long
error when inserting (or doing anything in queries) with CLOB
s?
I want to have a query like this:
INSERT ALL
INTO mytable VALUES ('clob1')
INTO mytable VALUES ('clob2') --some of these clobs are more than 4000 characters...
INTO mytable VALUES ('clob3')
SELECT * FROM dual;
When I try it with actual values though I get ORA-01704: string literal too long
back. This is pretty obvious, but how do I insert clobs (or execute any statement at all with a clob)?
I've tried looking at this question, but I don't think it has what I'm looking for. The clobs I have are in a List<String>
and I iterate through them to make the statement. My code as it is follows:
private void insertQueries(String tempTableName) throws FileNotFoundException, DataException, SQLException, IOException {
String preQuery = " into " + tempTableName + " values ('";
String postQuery = "')" + StringHelper.newline;
StringBuilder inserts = new StringBuilder("insert all" + StringHelper.newline);
List<String> readQueries = getDomoQueries();
for (String query : readQueries) {
inserts.append(preQuery).append(query).append(postQuery);
}
inserts.append("select * from dual;");
DatabaseController.getInstance().executeQuery(databaseConnectionURL, inserts.toString());
}
public ResultSet executeQuery(String connection, String query) throws DataException, SQLException {
Connection conn = ConnectionPool.getInstance().get(connection);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
conn.commit();
ConnectionPool.getInstance().release(conn);
return rs;
}
You are making it way to complicated.
Use a PreparedStatement and addBatch() for each clob in your list:
String sql = "insert into " + tempTableName + " values (?)";
PreparedStatement stmt = connection.prepareStatement(sql);
for (String query : readQueries) {
stmt.setCharacterStream(1, new StringReader(query), query.lenght());
stmt.addBatch();
}
stmt.exececuteBatch();
No messing around with escaping strings, no problem with the length of the literals, no need to create temporary clobs. And most probably just as fast as using a single INSERT ALL statement.
If you are using a current driver (> 10.2) then I think the setCharacterStream() call and the creation of the Reader is not necessary either. A simple setString(1, query)
will most probably work as well.