extractvalue处理XMLTYPE类型超过4000字节ORA-01706的解决方法

extractvalue在处理XMLTYPE类型运算时非常方便，但在处理超过4000字节column就力不从心了。

问题：

一位朋友在Oracle中使用extractvalue处理XMLTYPE类型时，发现column值超过4000字节时就会报错ORA-01706.

是否真会这样呢？

通过实验重现问题：

1.创建XML table

Create TABLE testxml(id NUMBER,data XMLTYPE);

2.创建目录

CREATE OR REPLACE DIRECTORY EXPDP_DIR AS '/data/expdp_dir/';

3.在目录/data/expdp_dir/ 中建立11.xml文件， 模拟Data2长度为4001字节，文件内容如下：

MES01234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340

4.通过bfile方式insert数据

insert into testxml(id,data)values(6,xmltype(bfilename('EXPDP_DIR','11.xml'),nls_charset_id('AL32UTF8')));commit;

5.查看insert后数据

select *from testxml

6.使用extractvalue查询小于4000字节column Data1，可以正常返回结果

select  extractvalue(data,'/Workbook/Row/Data1') from testxml;

7 . 使用 extractvalue 查询大于4000字节column Data2，出现报错 ORA-01706

select  extractvalue(data,'/Workbook/Row/Data2') from testxml;

以上证实extractvalue无法处理大于4000字节column

解决：

查看Oracle官方文档对extractvalue函数的介绍

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions061.htm#SQLRF06173

The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE , XMLCAST , and XMLQUERY for more information.

原来官方已建议不要再使用extractvalue，而建议使用XMLTABLE,XMLCASE,XMLQUERY函数

继续查阅XMLTABLE使用文档

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions253.htm#SQLRF06232

利用XMLTABLE函数处理大于4000字节column:

SELECT * FROM testxml,XMLTABLE('/Workbook' PASSING testxml.data COLUMNS CON clob PATH '/Workbook/Row/Data2' )

在XMLTABLE可以自定义字符类型，这里指定clob类型可成功获取超过4000字节数据