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

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

发布时间：2020-08-14 15:40:17

来源：ITPUB博客

阅读：226

作者：guocun09

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字节，文件内容如下：

MES

01234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340

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字节数据

总结

以上是生活随笔为你收集整理的oracle xmltype转字节,extractvalue处理XMLTYPE类型超过4000字节ORA-01706的解决方法的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。