ORA-31061: XDB error: special char to escaped char conversion failed

Question

I'm completely new to Oracle's XDB, trying to breakfix a code in production environment which i am completely new to.

SELECT  DBMS_LOB.SubStr(RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES (ntx.nttx_str || '|' AS "Seg")) ORDER BY ntx.ref_id, ntx.ntnb_input_dtm desc)
                                   .EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg').GetClobVal(),','),1000,1)
    FROM
       (SELECT umum.ref_id,
               ntnb.ntnb_input_dtm,
               DBMS_LOB.SubStr(RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES (nttx.nttx_text || '' AS "Seg")) ORDER BY nttx.nttx_seq_no asc)
                                   .EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg').GetClobVal(),','),500,1) AS nttx_str
        FROM umum_util_mgt umum,
             ntnb_note_base ntnb,
             nttx_note_text nttx
        WHERE ntnb.ntnb_id = umum.ntnb_id
          AND ntnb.ntnb_id = nttx.ntnb_id
          AND ntnb.ntnb_input_dtm = nttx.ntnb_input_dtm
          GROUP BY umum.ref_id, ntnb.ntnb_input_dtm
        ) ntx,
        php_mdx_auth_ext_tbl paex
        WHERE ntx.ref_id = paex.ref_id*

When i try to execute the above query, Oracle is returning the below error

ORA-31061: XDB error: special char to escaped char conversion failed.

This used to work in older version of Oracle, after moving to oracle 12c we are facing this issue.

Answer 1

This error generally occurs when there is a control character (Bad data) present in the XML data.

I am able to reproduce the error with bad XML data but yes, error code is different, I am not sure why.

SQL> WITH DATAA AS (
  2      SELECT UNISTR('SO\0013bad') TEST FROM DUAL
  3      UNION ALL
  4      SELECT UNISTR('SO\00aegood') TEST FROM DUAL
  5  )
  6  SELECT xmlelement("a", test) AS TEST
  7    FROM DATAA;
ERROR:
ORA-64451: Conversion of special character to escaped character failed.

To resolve this error,

1. You need to find out the records with bad XML data and correct those data. You can use REGEXP_LIKE as following to find the bad XML data and correct those data:

SQL> WITH DATAA AS (
  2      SELECT UNISTR('SO\0013bad') TEST FROM DUAL
  3      UNION ALL
  4      SELECT UNISTR('SO\00aegood') TEST FROM DUAL
  5  )
  6  SELECT *
  7  FROM DATAA
  8  WHERE REGEXP_LIKE ( TEST, '[[:cntrl:]]' );

TEST
-------
SObad

SQL>

-- OR --

2. You can use REGEXP_REPLACE to skip the control characters preent in the XML data from processing as follows:

SQL> WITH DATAA AS (
  2      SELECT UNISTR('SO\0013bad') TEST FROM DUAL
  3      UNION ALL
  4      SELECT UNISTR('SO\00aegood') TEST FROM DUAL
  5  )
  6  SELECT xmlelement("a", REGEXP_REPLACE(TEST, '[[:cntrl:]]', '')) AS TEST
  7    FROM DATAA;

TEST
--------------------------------------------------------------------------------
<a>SObad</a>
<a>SO«good</a>

SQL>

Cheers!!