XMLTYPE类型更新ORA-31167: XML nodes over 64K in size cannot be inserted等问题

今天在批量更新Oracle数据库中一个XMLTYPE类型字段时，部分数据更新时抛出了如下异常：

java.sql.SQLException: ORA-31167: XML nodes over 64K in size cannot be inserted

看异常提示，意思是节点大小超过了64K不能插入。

这里采用的是Oracle提供的oracle.xdb.XMLType类的方式，具体请看:java操作Oracle类型XMLType总结

Connection conn = ... ;//获得Connection 
PreparedStatement ps = ...;//获得PreparedSatement 
String sql = "insert into xmltable (XML) values(?)"; 
String xmldata = "<label>This is an XML fragment</label>"; 
//创建一个XMLType对象 
XMLType xmltype = XMLType.createXML(conn, xmldata); 
ps.setObject(1, xmltype); 
ps.executeUpdate();

都说XMLType是继承了Clob的存在，没理由这点数据保存不进去啊，难道是这种保存的方式不对么？（实际大小文本复制出来保存后10K不到，不知道怎么更新时会超过64K，可能oracle的算法不一样，但谁让oracle是规则的制定者，我也只能遵守）。

查询了一下相关资料，Oracle官方貌似是真的有这个限制，其目的大意是XMLTYPE类型提供了很多xml操作的函数，为了这些函数的运行而限制了文档大小。

可是数据库中明明有超过我这个大小的数据啊，那原来是怎么插入进去的？

换成Clob的操作方式:

final InputStream is = new ByteArrayInputStream(xml.getBytes());
final Reader reader = new StringReader(xml);
jdbcTemplate.execute(sql, new AbstractLobCreatingPreparedStatementCallback(
    this.lobHandler) {
    protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                                                                         throws SQLException {
        ps.setLong(1, finalId);
        lobCreator.setClobAsCharacterStream(ps, 2, reader, xml.getBytes().length);
    }
});

还是不行，抛出了以下异常：

nested exception is java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

网上有建议先用Clob保存到临时表，再使用sql将数据更新到XMLTYPE中，看到原来代码中也有这个作法，想着应该可行便试了一下，将数据保存到Clob的临时表后用以下sql更新：

update xml_table t1 set t1.xml_file=sys.xmlType.createXML((select t2.CLOB_TYPE from T_XML_CLOB_EXCHENGE t2 where t2.XML_CLOB_EXCHENGE_ID = ?));

Clob数据倒是保存进去了，可是在更新到XMLTYPE中的时候仍然抛出了java.sql.SQLException: ORA-31167: XML nodes over 64K in size cannot be inserted异常。

将该sql语句复制到Oracle SQL Developer工具中直接执行，异常仍在。

这使用了官方提供的类，使用了官方的工具都不行，难道这个无解么？那原来的数据是怎么保存进去的？

没办法，找到原来的jar包反编译源码(外包的，源码已不可找），发现有这么一段：

if (contents.getBytes("utf-8").length > 64 * 1024) {
    result = contentDao.saveXmlToClob(id,xml);
} else {
    result = contentDao.saveXmlData(clazz,"xmltype", xml,id);
}

看来对大于64K的数据确实是做了特殊处理的，继续反编译，终于发现关键的代码：

//select t.xmltype.getClobVal() from xml_table t where t.id = ? for update
Object result = query.getSingleResult();
if (result != null) {
    Clob clob = (Clob) result;
    java.io.BufferedWriter out = new java.io.BufferedWriter(clob.setCharacterStream(1));
    out.write(xml);
    out.flush();
    out.close();
    session.flush();
}

使用的是Hibernate，先将XMLTYPE通过Clob方式查出来之后，在内存中保存了一个数据库的映射引用，在改变数据后直接弄回去，利用查询来完成了更新。

试了一下果然好使，这是绕过了Oracle的验证了么？

但是大家知道我并不怎么喜欢用Hibernate，这次也不例外使用了Spring JdbcTemplate封装的Dao，看这里：Jdbc通用Dao

JdbcTemplate没有所谓的flush方法啊，那要怎么来完成这个利用查询来更新的操作？

查看JdbcTemplate提供的方法，发现有这么一个：

<T> T execute(ConnectionCallback<T> action) throws DataAccessException;

这里提供了直接能拿到Connection连接，那应该就能完成我们想要的功能，试用如下代码：

jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
    public Boolean doInConnection(Connection con) throws SQLException,
                                                 DataAccessException {
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setLong(1, finalId);
            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                Clob clob = rs.getClob(1);
                BufferedWriter out = new BufferedWriter(clob.setCharacterStream(1));
                out.write(xml);
                out.flush();
                out.close();
                con.commit();
            }
            rs.close();
        } catch (IOException e) {
            throw new AssistantException(e);
        }
        return true;
    }
});

终于更新成功了，激动啊！

到这里差不多折腾完了，虽然成功更新了数据，但缺点也很明显，就是这条记录的更新不能使用事务，因为它单独的commit了。

如果是批量的操作中有这么一个更新操作，还是有一定的数据一致性风险的。

具体如何取舍还要自己权衡！

纠正

这两天又对这功能进行了测试，发现当上下文中存在事务时，去掉con.commit()这一行，在事务统一提交时仍旧是可以更新进去的。

但当上下文中没有事务时，去掉这一行将会不能更新。

签于这个原因，我们可以在提交时判断当前上下文中是否存在事务，如果不存在则提交，反之则等待外围事务一起提交：

//当上下文中没有事务时为true
if (con.getAutoCommit()) {
    con.commit();
}

XMLTYPE类型更新ORA-31167: XML nodes over 64K in size cannot be inserted等问题

纠正

selfly