[Oracle] ORA-31061: XDB 오류: special char to escaped char conversion failed.

[Oracle] ORA-31061: XDB 오류: special char to escaped char conversion failed.

 

 

 

1. 현상

여러 행의 데이터를 특정 구분자를 넣거나, 넣지 않고 한줄로 표현할 때 다음과 같은 함수들을 사용 하게 된다.
LISTAGG, WM_CONCAT, XMLAGG, XMLELEMENT 등등
참고) 오라클 피벗 - https://goddaehee.tistory.com/57

오라클 상품 상세 테이블의 HTML 컨텐츠 컬럼이 CLOB으로 설계 되어있지 않고, VARCHAR2로 설계되어 있어 위 XMLAGG, XMLELEMENT 를 이용하여 한 ROW로 조회하고 싶을 때,
XML로 변환할 수 업는 문자열이 포함도어있어서 XDB 오류가 발생 하였다.

 

 

2. 원인 분석

SELECT
    XMLAgg(XMLElement(x, '', DTL_DESC_CONT) order by DTL_DESC_SN).Extract('//text()').getCLOBval()
FROM 
    (select '두번째 순번 
  <= 이친구 xml 에러 발생' as DTL_DESC_CONT, 2 as DTL_DESC_SN from dual
    union all
    select '첫번째 순번 r' as DTL_DESC_CONT, 1 from dual
    ORDER BY DTL_DESC_SN
    )

▶ "
" 해당 문자가 XML로 변환 시 에러를 발생하고 있는 문자 였다.

▶참고)
XML 문서 하위 ASCII 문자를 포함 하면 오류 메시지가 나타난다

MSXML 파서의 3.0 이상 버전을 사용 하 여 특정 하위 순서 인쇄할 수 없는 ASCII 문자 (ASCII 32 아래 문자)를 포함 하는 XML 문서를 구문 분석 하려고 할 때 오류가 발생할 수 있다.
버전 3.0 및 MSXML 파서의 나중 (W3C) 웹 컨소시엄 XML 언어 사양에 정의 된 유효한 XML 문자 범위를 엄격 하 게 적용 된다.
MSXML 3.0 이상 버전을 사용 하는 구문 분석 된 XML 문서를 정의 된 유효한 XML 문자 범위를 벗어나는 문자를 포함할 수 없다.

 

 

3. 해결

1) WM_CONCAT을 응용하여 쿼리문 변경
(LISTAGG는 VARCHAR type으로 값을 리턴, WM_CONCAT은 VARCHAR type으로 리턴하지만, 결과 같이 너무 긴 경우 CLOB type으로 리턴한다. 정확하지 않으면, 답변 부탁드립니다.)

SELECT
REPLACE(REPLACE(WM_CONCAT(REPLACE(DTL_DESC_CONT, ',', '<COMMA>')), ',', ''), '<COMMA>',',') AS DTL_DESC_CONT
FROM
(select '두번째 순번 
 <= 이친구 xml 에러 발생' as DTL_DESC_CONT, 2 as DTL_DESC_SN from dual
union all
select '첫번째 순번 r' as DTL_DESC_CONT, 1 from dual
ORDER BY DTL_DESC_SN
)

 

2) 문제가 되는 ASCII 문자를 정규식으로 제거 처리.
 - 특수한 문자로 인해 시스템 오류 발생하는 것보다, 해당 문자열을 제거하여 데이터를 조회 처리.
 - 참고 URL
2.1) https://it.toolbox.com/question/removing-ascii-control-charcters-list-of-from-a-table-column-field-070812
2.2) https://stackoverflow.com/questions/37451038/oracle-regular-expression-for-null-character

SELECT
    XMLAGG(XMLELEMENT(X, '', CONT) ORDER BY SEQ).EXTRACT('//text()').GETCLOBVAL() AS SUM_CONT
FROM
(
    SELECT
        CONT_KEY
        , REGEXP_REPLACE (CONT ,'[[:cntrl:]]','') AS CONT       
        , SEQ
    FROM 
    (
        SELECT '001' AS CONT_KEY, '두번째컨텐츠
' AS CONT, 2 AS SEQ FROM DUAL
        UNION ALL
        SELECT '001' AS CONT_KEY, '첫번째컨텐츠, ' AS CONT, 1 AS SEQ FROM DUAL
    ) 
);

※ 각각의 싸이트, 업무 성격에 따라 다양한 해결방법이 있을 것 같다.