哪里是我的无效字符（ORA-00911） [英] Where's my invalid character (ORA-00911)

问题描述

我正在尝试将 CLOB 插入数据库（请参阅相关问题）。我无法弄清楚出了什么问题。我有一个大约85个clobs的列表，我想插入表中。即使只插入第一个clob，我也会得到 ORA-00911：无效字符。我无法弄清楚如何在执行之前从 PreparedStatement 中获取声明，所以我不能100％确定它是正确的，但如果我得到它是的，它看起来应该是这样的：

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;

最终， 插入所有 语句会有很多进入这就是为什么我不做常规的插入语句。我在那里看不到无效的角色，对吗？ （哦，当我在我的sql开发人员工具中运行时，上面的代码运行正常。）如果我在 PreparedStatement中删除分号，它抛出 ORA-00933：SQL命令未正确结束错误。

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?

推荐答案

如果您完全按照向我们展示的那样使用字符串文字，则问题是; 最后的角色。您可能不会在JDBC调用的查询字符串中包含它。

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.

因为您只插入一行，所以常规 INSERT 即使插入多行也应该没问题。使用批处理语句可能更有效率。不需要 INSERT ALL 。此外，您不需要临时clob和所有这些。你可以简化你的方法（假设我得到了正确的参数）：

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

这篇关于哪里是我的无效字符（ORA-00911）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！