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

<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字節數據