Shulou(Shulou.com)06/01 Report--

Extractvalue is very convenient when dealing with XMLTYPE type operations, but it is not good enough to handle more than 4000 bytes of column.

Question:

When a friend uses extractvalue to deal with XMLTYPE types in Oracle, he will report an error ORA-01706 when the column value exceeds 4000 bytes.

Will this really be the case?

Reproduce the problem through experiments:

1. Create XML table

Create TABLE testxml (id NUMBER,data XMLTYPE)

two。 Create a directory

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

3. Create a 11.xml file in the directory / data/expdp_dir/. The length of the simulated Data2 is 4001 bytes. The contents of the file are as follows:

MES01234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340

4. Insert data through bfile

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

5. View data after insert

Select * from testxml

6. Using extractvalue query less than 4000 bytes column Data1, the result can be returned normally.

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

7. Using extractvalue to query more than 4000 bytes of column Data2, an error ORA-01706 occurred

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

It is confirmed that extractvalue cannot handle more than 4000 bytes of column

Resolve:

Check the official Oracle documentation for an introduction to the extractvalue function.

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.

It turns out that officials have advised not to use extractvalue, but to use the XMLTABLE,XMLCASE,XMLQUERY function.

Continue to consult the XMLTABLE usage documentation

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

Use the XMLTABLE function to deal with more than 4000 bytes of column:

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

You can customize character types in XMLTABLE. Here you can specify the clob type to successfully obtain more than 4000 bytes of data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.