69

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?

Community
  • 1
  • 1
kentcdodds
  • 27,113
  • 32
  • 108
  • 187
  • Can you view the PreparedStatement as a string to see what it contains? – Dan Armstrong May 23 '12 at 21:54
  • I mentioned that in the post. I can't figure out how to do that. All I see on the internet is that it's pretty complicated for some reason... – kentcdodds May 23 '12 at 21:54
  • Some JDBC drivers allow toString() to show you the query from a PreparedStatement and some don't. Not sure about Oracle. – Dan Armstrong May 23 '12 at 21:57
  • @DanArmstrong, unfortunately, that's not the case here. I get a `oracle.jdbc.driver.OraclePreparedStatementWrapper@8870a2` on a `toString()` print out. – kentcdodds May 23 '12 at 21:59
  • Does Oracle support query logging. If so then you could see the query when it hits the server. – Dan Armstrong May 23 '12 at 22:01
  • Yeah, it does in `v$sql`. But apparently it's not logged unless it doesn't throw an error... – kentcdodds May 23 '12 at 22:04
  • What is the content of `query` that is passed to the method? It would need to have placeholders for each and every parameter you pass. And I don't understand why you are adding single quotes to the value that is passed to `clob.setString()`. One of the reasons to use PreparedStatements is that you don't have to worry about quoting. –  May 23 '12 at 22:07
  • @a_horse_with_no_name, good point, I forgot I added those single quotes there in my debugging. Unfortunately, that didn't solve the problem. The content of the `query` is described above in the original question. I show it in the code. And it runs fine in my developer tool... – kentcdodds May 23 '12 at 22:11
  • but it does not contain any `?` as parameter markers. Without those, the PreparedStatement is not going to work. For each PreparedStatement.setXXX() call there must be a coresponding `?` in the prepared SQL (and that must not be enclosed in single quotes) –  May 23 '12 at 22:13
  • Oh, yes, sorry. I'll update the post to show what the value of `query` is. – kentcdodds May 23 '12 at 22:19

4 Answers4

168

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();
  • Yeah, someone else mentioned that too. When I take it out I get an `ORA-00933: SQL command not properly ended` error... – kentcdodds May 23 '12 at 21:55
  • @kentcdodds: why are you using `insert all` in the first place? A normal insert should be just fine. –  May 23 '12 at 21:56
  • 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. – kentcdodds May 23 '12 at 21:58
  • @kentcdodds: see my edit. I think you are better off using batched statements and simplifying your CLOB handling. –  May 23 '12 at 22:04
  • Oh shoot! That totally worked! Thanks so much! :D Really appreciate the help. – kentcdodds May 23 '12 at 22:26
  • 16
    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 SQL*Plus) to tell where the statement ends and then sends the statement but not the ';' to the Oracle server. In SQL*PLus, 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 May 24 '12 at 00:24
  • 1
    PL/SQL does use `;` as a statement terminator, but it is a language from Oracle's implementation of SQL. – Shannon Severance May 24 '12 at 00:25
  • 6
    @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. –  May 24 '12 at 06:46
  • 2
    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 May 24 '12 at 16:28
  • 2
    Comment above should read: _PL/SQL does use `;` as a statement terminator, but it is a language_ separate _from Oracle's implementation of SQL._ – Shannon Severance May 24 '12 at 16:29
  • 13
    just a ; was killing me – Sumon Bappi Sep 01 '14 at 10:53
  • 1
    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. – codeMan Feb 14 '15 at 10:10
  • 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? – Aman Jun 29 '20 at 16:40
6

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'[...]'.

0

One of the reason may be if any one of table column have an underscore(_) in its name . That is considered as invalid characters by the JDBC . Rename the column by a ALTER Command and change in your code SQL , that will fix .

Pratik Roy
  • 117
  • 1
  • 3
0

Oracle provide some explanation for ORA-00911. You can got this explanation after executing SQL request in Oracle SQL Developer.

ORA-00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual

But in your case it seems to be double ' character

Mikhail Ionkin
  • 568
  • 4
  • 20