Where's my invalid character (ORA-00911)

Question

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

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();

Comments

[kentcdodds]

Yeah, someone else mentioned that too. When I take it out I get an ORA-00933: SQL command not properly ended error...

[a_horse_with_no_name]

@kentcdodds: why are you using insert all in the first place? A normal insert should be just fine.

[kentcdodds]

In my original question I mention that what you see there is only a test to make sure that only one insert gets run. In all actuality, there will be about 85 inserts in this statement.

[a_horse_with_no_name]

@kentcdodds: see my edit. I think you are better off using batched statements and simplifying your CLOB handling.

[kentcdodds]

Oh shoot! That totally worked! Thanks so much! :D Really appreciate the help.

[Shannon Severance]

To add, many think that ; is statement terminator in SQL on Oracle. It isn't. The ; at an end of statement is used by the client (for example SQLPlus) to tell where the statement ends and then sends the statement but not the ';' to the Oracle server. In SQLPLus, with defaults try select * from dual; --semicolon to terminate. You will get a 2 prompt for the rest of the command, because SQL*Plus only uses the ; if it is the last character in a line, but select * from dual --weird that this works; The ; appears to part of a comment, but still "terminates" the statement.

[Shannon Severance]

PL/SQL does use ; as a statement terminator, but it is a language from Oracle's implementation of SQL.

[a_horse_with_no_name]

@ShannonSeverance: the ; is defined as the statement termination character by the SQL standard. But to increase the confusion regarding this: SQL Server (not the client!) requires some statements to be sent with a ; at the end even through JDBC. And apparently it also sometimes requires the ; at the front of the statement.

[Shannon Severance]

I tried to make it clear I was discussing Oracle's implementation of SQL, not standard SQL. SQL Server is wacky with sometimes requiring a ; to separate statements, but not having required a ; all along.

[Shannon Severance]

Comment above should read: PL/SQL does use ; as a statement terminator, but it is a language separate from Oracle's implementation of SQL.

[Sumon Bappi]

just a ; was killing me

[codeMan]

I had a ; at the end of an insert query and it made my life miserable for 4 hours! I was trying to do a batch insert of 2000+rows from java to mySql.

[Aman]

Is there any official documentation article related to this weird exception, related to why there is no need for semicolon and when there is need for it?