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

问题描述

我正在尝试将 CLOB 插入数据库(请参阅相关问题).我无法弄清楚出了什么问题.我有一个要插入表格的大约 85 个 clob 的列表.即使只插入第一个clob，我也会得到ORA-00911: invalid character.我不知道如何在执行之前从 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;

最终，这个 insert all 语句将有很多into，这就是为什么我不做常规的insert 语句.我没有在那里看到无效字符，是吗?(哦，上面的代码在我的 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;
}

有什么我很想念的吗?

推荐答案

如果您完全按照向我们展示的方式使用字符串字面量，问题在于末尾的 ; 字符.您不能将其包含在 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屋！