您好,登录后才能下订单哦!
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字节,文件内容如下:
<Workbook> <Row> <Data1>MES</Data1> <Data2>01234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340</Data2> </Row> </Workbook>
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字节数据
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。