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

问题描述

我的问题是：在插入（或在查询中执行任何操作）时，如何绕过 ORA-01704：字符串文字太长错误） CLOB 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?

我希望得到这样的查询：

I want to have a query like this:

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文字太长了回来了。这很明显，但是如何插入clobs（或者使用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)?

我试过看问题，但我认为它没有我想要的东西。我拥有的clob在 List< String> 中，我遍历它们以生成语句。我的代码如下：

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

不要乱转字符串，文字长度没问题，不需要创建临时字符串CLOB进行。并且大多数可能与使用单个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屋！