Where's my invalid character (ORA-00911)

I'm trying to insert CLOBs into a database (see related question). I can't quite figure out what's wrong. I have a list of about 85 clobs I want to insert into a table. Even when inserting only the first clob I get ORA-00911: invalid character. I can't figure out how to get the statement out of the PreparedStatement before it executes, so I can't be 100% certain that it's right, but if I got it right, then it should look exactly like this:

insert all
  into domo_queries values ('select 
substr(to_char(max_data),1,4) as year,
substr(to_char(max_data),5,6) as month,
max_data
from dss_fin_user.acq_dashboard_src_load_success
where source = ''CHQ PeopleSoft FS''')
select * from dual;

Ultimately, this insert all statement would have a lot of into's, which is why I just don't do a regular insert statement. I don't see an invalid character in there, do you? (Oh, and that code above runs fine when I run it in my sql developer tool.) And I if I remove the semi-colon in the PreparedStatement, it throws an ORA-00933: SQL command not properly ended error.

In any case, here's my code for executing the query (and the values of the variables for the example above).

public ResultSet executeQuery(String connection, String query, QueryParameter... params) throws DataException, SQLException {
  // query at this point = "insert all
                          //into domo_queries values (?)
                          //select * from dual;"
  Connection conn = ConnectionPool.getInstance().get(connection);
  PreparedStatement pstmt = conn.prepareStatement(query);
  for (int i = 1; i <= params.length; i++) {
    QueryParameter param = params[i - 1];
    switch (param.getType()) { //The type in the example is QueryParameter.CLOB
      case QueryParameter.CLOB:
        Clob clob = CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
        clob.setString(i, "'" + param.getValue() + "'");
        //the value of param.getValue() at this point is:
        /*
         * select 
         * substr(to_char(max_data),1,4) as year,
         * substr(to_char(max_data),5,6) as month,
         * max_data
         * from dss_fin_user.acq_dashboard_src_load_success
         * where source = ''CHQ PeopleSoft FS''
         */
        pstmt.setClob(i, clob);
        break;
      case QueryParameter.STRING:
        pstmt.setString(i, "'" + param.getValue() + "'");
        break;
    }
  }
  ResultSet rs = pstmt.executeQuery(); //Obviously, this is where the error is thrown
  conn.commit();
  ConnectionPool.getInstance().release(conn);
  return rs;
}

Is there anything I'm just missing big time?

Solution 1:

If you use the string literal exactly as you have shown us, the problem is the ; character at the end. You may not include that in the query string in the JDBC calls.

As you are inserting only a single row, a regular INSERT should be just fine even when inserting multiple rows. Using a batched statement is probable more efficient anywy. No need for INSERT ALL. Additionally you don't need the temporary clob and all that. You can simplify your method to something like this (assuming I got the parameters right):

String query1 = "select substr(to_char(max_data),1,4) as year, " + 
  "substr(to_char(max_data),5,6) as month, max_data " +
  "from dss_fin_user.acq_dashboard_src_load_success " + 
  "where source = 'CHQ PeopleSoft FS'";

String query2 = ".....";

String sql = "insert into domo_queries (clob_column) values (?)";
PreparedStatement pstmt = con.prepareStatement(sql);
StringReader reader = new StringReader(query1);
pstmt.setCharacterStream(1, reader, query1.length());
pstmt.addBatch();

reader = new StringReader(query2);
pstmt.setCharacterStream(1, reader, query2.length());
pstmt.addBatch();

pstmt.executeBatch();   
con.commit();

Solution 2:

Of the top of my head, can you try to use the 'q' operator for the string literal

something like

insert all
  into domo_queries values (q'[select 
substr(to_char(max_data),1,4) as year,
substr(to_char(max_data),5,6) as month,
max_data
from dss_fin_user.acq_dashboard_src_load_success
where source = 'CHQ PeopleSoft FS']')
select * from dual;

Note that the single quotes of your predicate are not escaped, and the string sits between q'[...]'.