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函式的介紹

 

  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 , , and for more information.

原來官方已建議不要再使用extractvalue，而建議使用XMLTABLE,XMLCASE,XMLQUERY函式

繼續查閱XMLTABLE使用文件 

利用XMLTABLE函式處理大於4000位元組column:

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

在XMLTABLE可以自定義字元型別，這裡指定clob型別可成功獲取超過4000位元組資料