gpt4 book ai didi

java - 我的无效字符在哪里(ORA-00911)

转载 作者:行者123 更新时间:2023-12-02 00:24:27 25 4
gpt4 key购买 nike

我正在尝试将 CLOB 插入数据库(请参阅 related question )。我不太明白出了什么问题。我有一个大约 85 个 clob 的列表,我想插入到表中。即使仅插入第一个 clob,我也会收到 ORA-00911:无效字符。我不知道如何在执行之前从 PreparedStatement 中获取该语句,因此我不能 100% 确定它是正确的,但如果我做对了,那么它应该看起来像就像这样:

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 命令未正确结束 错误。

无论如何,这是我用于执行查询的代码(以及上面示例的变量值)。

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 调用的查询字符串中。

由于您只插入一行,因此即使插入多行,常规的 INSERT 也应该没问题。无论如何,使用批处理语句可能更有效。不需要INSERT ALL。此外,您不需要临时的 clob 之类的东西。您可以将您的方法简化为如下所示(假设我的参数正确):

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

关于java - 我的无效字符在哪里(ORA-00911),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58061260/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com