将 CLOB 插入 Oracle 数据库 [英] Insert CLOB into Oracle database

问题描述

我的问题是:如何在使用 CLOB 插入(或在查询中执行任何操作)时解决 ORA-01704: string literal too long 错误s?

My question is: How do you get around the ORA-01704: string literal too long error when inserting (or doing anything in queries) with CLOBs?

我想要这样的查询:

INSERT ALL
   INTO mytable VALUES ('clob1')
   INTO mytable VALUES ('clob2') --some of these clobs are more than 4000 characters...
   INTO mytable VALUES ('clob3')
SELECT * FROM dual;

当我用实际值尝试它时，虽然我得到 ORA-01704: string literal too long 回来.这很明显，但是我如何插入 clob(或使用 clob 执行任何语句)?

When I try it with actual values though I get ORA-01704: string literal too long back. This is pretty obvious, but how do I insert clobs (or execute any statement at all with a clob)?

我试过查看这个问题，但我认为它不符合我的要求.我拥有的 clobs 位于 List 中，我遍历它们以做出声明.我的代码如下:

I've tried looking at this question, but I don't think it has what I'm looking for. The clobs I have are in a List<String> and I iterate through them to make the statement. My code as it is follows:

private void insertQueries(String tempTableName) throws FileNotFoundException, DataException, SQLException, IOException {
String preQuery = "  into " + tempTableName + " values ('";
String postQuery = "')" + StringHelper.newline;
StringBuilder inserts = new StringBuilder("insert all" + StringHelper.newline);
List<String> readQueries = getDomoQueries();
for (String query : readQueries) {
  inserts.append(preQuery).append(query).append(postQuery);
}
inserts.append("select * from dual;");

DatabaseController.getInstance().executeQuery(databaseConnectionURL, inserts.toString());

}

public ResultSet executeQuery(String connection, String query) throws DataException, SQLException {
  Connection conn = ConnectionPool.getInstance().get(connection);
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  conn.commit();
  ConnectionPool.getInstance().release(conn);
  return rs;
}

推荐答案

您正在使它变得复杂.

为列表中的每个 clob 使用 PreparedStatement 和 addBatch():

Use a PreparedStatement and addBatch() for each clob in your list:

String sql = "insert  into " + tempTableName + " values (?)";
PreparedStatement stmt = connection.prepareStatement(sql);
for (String query : readQueries) {
  stmt.setCharacterStream(1, new StringReader(query), query.lenght());
  stmt.addBatch();
}
stmt.exececuteBatch();

无需处理转义字符串，文字长度没有问题，无需创建临时 clobs.而且很可能与使用单个 INSERT ALL 语句一样快.

No messing around with escaping strings, no problem with the length of the literals, no need to create temporary clobs. And most probably just as fast as using a single INSERT ALL statement.

如果您使用的是当前驱动程序 (> 10.2)，那么我认为 setCharacterStream() 调用和 Reader 的创建也不是必需的.一个简单的 setString(1, query) 很可能也能工作.

If you are using a current driver (> 10.2) then I think the setCharacterStream() call and the creation of the Reader is not necessary either. A simple setString(1, query) will most probably work as well.

这篇关于将 CLOB 插入 Oracle 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！