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

Last updated on APRIL 12, 2022

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.