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  lt   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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

[java] Where's my invalid character (ORA-00911)

Examples related to java

Examples related to oracle

Examples related to jdbc

Examples related to prepared-statement