현상
xmlagg 함수 사용 시 ERROR_EXP_INVALID_PSM_RETURN_TYPE(-11032) 에러가 발생합니다.
원인
xmlagg 함수의 반환 타입은 CLOB입니다.
이로 인해 .extract, .getStringVal 등의 방식으로 함수 접근이 불가하여 발생합니다.
해결
해당 문제는 티베로 내부 패키지를 설치하여 해결할 수 있습니다.
Tibero에서 제공하는 $TB_HOME/scripts/pkg/install_xmltype.sql 스크립트를 설치하여 object type 접근 방식을 지원하도록 설정해주셔야 합니다.
작업 순서
1.
사전 점검: $TB_HOME/scripts/pkg/xmltype_install_checker.sql 스크립트를 실행하여 XMLTYPE 설치 여부와 XMLTYPE column을 갖는 테이블 및 관련된 PSM목록을 사전에 확인합니다.
2.
패키지 설치: $TB_HOME/scripts/pkg/install_xmltype.sql을 실행하여 XMLTYPE을 지원하도록 설치합니다.
3.
Invalid Object 처리: 설치 후 발생된 Invalid Object에 대해 re-compile을 수행합니다. (1번 단계에서 List-up 및 compile 구문 스크립트 사전 확보가 필요합니다.)
4.
에러 쿼리 정상 동작 재확인: 기존 오류가 발생했던 쿼리를 재실행하여 정상 동작 여부를 확인합니다.
위험 요소
1.
$TB_HOME/scripts/pkg/install_xmltype.sql 스크립트 내 ALTER SYSTEM FLUSH PPC; 구문
•
영향도: DB Instance에 생성되어 있는 파싱된 쿼리들이 모두 초기화됨에 따라 모든 쿼리들은 새롭게 Hard Parsing되며 순간적인 성능 저하 발생 가능성 존재합니다.
•
결론: ALTER SYSTEM FLUSH PPC; 생략 불가능합니다.
XMLTYPE 변경시 이전의 SQL함수가 사용된 쿼리가 Physical Plan에 캐싱되며 변경 후에 기존 캐싱된 Physical Plan이 사용되는 문제가 존재하기 때문에 해당 구문은 생략 불가능합니다.
2.
$TB_HOME/scripts/pkg/xmltype_install_checker.sql 수행을 통해 xmltype column을 갖는 TABLE 및 연관되는 PSM 목록 확인 필요
2.1) xmltype column을 갖는 table이 존재하는 경우, 해당 table들을 모두 drop 처리가 필요합니다.
2.2) $TB_HOME/scripts/pkg/xmltype_install_checker.sql 수행합니다. (수행 결과 테이블0건)
이후, $TB_HOME/scripts/pkg/install_xmltype.sql 스크립트 수행합니다.
2.3) 2.1)에서 drop했던 table을 재생성 합니다.
참고
•
백업 및 재생성 가이드
1.
임의의 clob column 을 갖는 table (A)을 생성합니다.
2.
xmltype data들을 ctas로 table A에 insert 해줍니다.
3.
그뒤 install_xmltype.sql 수행합니다.
4.
이후 재생성한 xmltype column을 갖는 table에 clob column을 갖는 table (A)로부터 반대로 data insert 수행합니다. (이때, xmltype column 개수만큼 clob column이 필요합니다.)
2.4) DEPENDENCY OBJECT 목록들이 나오며 해당 목록에 나온 PROCEDURE, FUNCTION, VIEW 등은 install_xmltype.sql 수행 시 VALID -> INVALID로 변합니다. Invalid일 시 업무에 연관된 Procedure가 수행되지 않아 운영에 영향 끼칠 가능성 존재합니다.
따라서 빠르게 re-compile을 하기 위해 사전에 List-Up 및 compile 구문 확보 필요.
에러 쿼리
•
extract('//text()') 를 제거하면 쿼리는 실행되지만, 정상적인 실행을 위해 install_xmltype.sql 설치를 권장합니다.
SELECT A.ROUTE_ID
, A.ORD
, A.LINK_ID
, B.XY AS GEO_XY
, NVL( (
SELECT DISTINCT X.ROUTE_NM
FROM TEST.TEST_MS_TABLE X
WHERE X.ROUTE_ID = A.ROUTE_ID
AND X.LANG_KEY = 'ko')
, 'UNKNOWN') AS ROUTE_NM
FROM TEST2.TEST_TMP A
, (SELECT LINKID , XMLAGG(XMLELEMENT(XY, XY || '|')).extract('//text()') AS XY
FROM (SELECT LINKID , POSX || ',' || POSY AS XY
FROM TEST3.G_LINKPLINE ORDER BY ORD )
GROUP BY LINKID ) B
WHERE A.LINK_ID = B.LINKID
;
SQL
복사