我对Oracle的XDB完全陌生,试图在生产环境中破解我完全陌生的代码。
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*
当我尝试执行上述查询时,Oracle返回以下错误
ORA-31061:XDB错误:特殊字符到转义字符的转换失败
这曾经在旧版本的Oracle中工作,在迁移到Oracle12c之后,我们面临着这个问题。
当XML数据中存在控制字符(坏数据(时,通常会发生此错误。
我可以用错误的XML数据重现错误,但是的,错误代码不同,我不知道为什么。
SQL> WITH DATAA AS ( 2 SELECT UNISTR('SO 013bad') TEST FROM DUAL 3 UNION ALL 4 SELECT UNISTR('SO 0aegood') 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.
要解决此错误,
- 您需要找出包含错误XML数据的记录并更正这些数据。您可以按如下方式使用
REGEXP_LIKE
来查找错误的XML数据并更正这些数据:
SQL> WITH DATAA AS ( 2 SELECT UNISTR('SO 013bad') TEST FROM DUAL 3 UNION ALL 4 SELECT UNISTR('SO 0aegood') TEST FROM DUAL 5 ) 6 SELECT * 7 FROM DATAA 8 WHERE REGEXP_LIKE ( TEST, '[[:cntrl:]]' ); TEST ------- SObad SQL>
--OR--
- 您可以使用
REGEXP_REPLACE
跳过处理XML数据中的控制字符preent,如下所示:
SQL> WITH DATAA AS ( 2 SELECT UNISTR('SO 013bad') TEST FROM DUAL 3 UNION ALL 4 SELECT UNISTR('SO 0aegood') 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>
干杯!!