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

User · 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

[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