ORA-06502 on SELECT with XMLTYPE & EXTRACT when XML Document Contains Empty Elements (Doc ID 1945442.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms

Having a table with XML documents with different empty elements, e.g.

<?xml version="1.0" encoding="windows-1251"?>
<CMS_REQUEST>
    <REQUEST_VALUE>
        <CONTACT>
            <CUSTOMER>
                <ID/>
                <DATA_SOURCE/>
            </CUSTOMER>
            <CREATE_DATE/>
            <CONTACT_SITE>
                <ID/>
            </CONTACT_SITE>
            <DIRECTION>
                <ID/>
            </DIRECTION>
            <CHANNEL>
                <ID>61</ID>
            </CHANNEL>
            <SUBSCRIBER>
                <ID/>
            </SUBSCRIBER>
            <CONTACT_PERSON>
                <ID/>
            </CONTACT_PERSON>
            <DESCRIPTION/>
        </CONTACT>
    </REQUEST_VALUE>
</CMS_REQUEST>

causes the following query:


SQL> SELECT
XMLTYPE(CMD).EXTRACT('//CMS_REQUEST/REQUEST/COMMAND/text()').getStringVal()
AS TXT
  FROM ( SELECT SUBSTR(COMMAND, INSTR(COMMAND, '<?xml')) CMD
           FROM TBL1
          WHERE NVL(INSTR(COMMAND, '<?xml'), 0) > 0   )
 WHERE
XMLTYPE(CMD).EXTRACT('//CMS_REQUEST/REQUEST/COMMAND/text()').getStringVal()
!= 'ANY VALUE';

to raise:


ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272

and returns 'no rows selected'

It works fine using the RULE optimizer

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms