가. POWER 함수

POWER 함수는 num1의 num2 제곱 값(num1^num2)을 반환하는 함수이다.

SELECT POWER(2, 3) FROM DUAL;

 POWER(2,3)
 ----------
 8 

이때 num1에 해당하는 값이 음수일 경우 ‘Given String does not represent anumber in proper format’의 SQL Fail이 발생하였으며 엔진 패치를 통해 해결이 되었다(에러 발생원인 연구소 답변 : OS Platform 중 AIX 의 경우 Power 함수를 128bit floating number based 연산인 powl 함수를 사용하도록 하고 있는데 해당 함수를 사용 시 인자가 음수일 때 number가 long double로 Casting하는 과정이 고려되지 못함).

• [참고 4-1] POWER 함수 실패 예시

• 실제 사용사례) A.단가 - A.단가 평균이 음수일 경우 에러가 발생
  
  SUM(POWER( (A.단가 - A.단가평균), 2) ) AS BODY1,
  SUM(POWER( (B.단가 - B.단가평균), 2) ) AS BODY2,
  
  재현 ) SELECT POWER(-1,2) FROM DUAL ;
  > TBR-5074 : Given String does not represent a number in proper format
  

나. 전각문자

우연히 사용된 전각문자가 많은 개발자에 의해 복사되어 여러 SQL에 포함되게 되었는데 티베로에서 해당 전각문자 지원이 불가능하여 SQL Fail 이슈가 발생하였다. 육안으로는 전각문자인지 아닌지 구별이 불가능하여 문제의 원인을 분석하는데 상당한 시간이 소요되었으나 문제 분석 이후 패치를 통해 원활하게 이슈가 처리되었다

 .... SQL 중략 ....
 FROM ( SELECT 기준연도,
 유의사항,
 연간일정상태,
 공개여부
 FROM 연간시험일정) YEJ INNER JOIN
 .... SQL 중략 ....

TBR-8026 : Invalid identifier
 at line 32, column 44

* 전각문자의 확인
SELECT ASCII('， ') C1 , ASCII(',') C2 from dual ;
 C1 C2
--------------- --------------
 61372 44

 SELECT DUMP('， ') C1 , DUMP(',') C2 from dual ;
 C1 C2
-------------------------------- --------------------
 Len=1: 239,188,140 Len=1: 44

다. WM_CONCAT with Partition by

SQL을 작성하다 보면 N개의 ROWS로 표현된 문자열을 특정 컬럼을 기준으로 1개의 ROWS로 연결하는 형태를 자주 사용하게 되는데 이때 티베로가 공식 지원하는 함수는 AGGR_CONCAT이다.

그러나 티베로는 AGGR_CONCAT 외에도 외산 DBMS에 널리 사용되는 XMLAGG, LISTAGG, WM_CONCAT 등을 모두 지원하며 SQL 호환성을 높이고 있는데 WM_CONCAT과 함께 PARTITION BY를 사용할 경우는(정렬을 위해) 소속 회사 프로젝트 당시 지원되지 않아 SQL Fail이 발생하였다.

물론 티베로에서 공식 지원하는 AGGR_CONCAT 함수를 사용하는 것으로 SQL을 변경하여 이슈를 해결할 수도 있었지만, 상당히 많은 화면에서 WM_CONCAT PARTITION BY를 사용하고 있어 현실적으로 모든 SQL을 수정하는 것은 불가능하여 엔진 패치를 통해 SQL Fail 이슈를 조치하였다(문자열 연결 관련 상세 사항은 4.2장 ‘7)비공식 지원 문자열 연결 함수의 과도한 사용’을 참고한다).

1. 기본 데이터

SELECT '아이템' PROD_ID , '001' CARGO_NO, '마우스' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '002' CARGO_NO, '키보드' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '003' CARGO_NO, '쿨링팬' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '004' CARGO_NO, '헤드셋' CARGO FROM DUAL ;

PROD_ID CARGO_NO CARGO
-------- ---------- ------
아이템 001 마우스
아이템 002 키보드
아이템 003 쿨링팬
아이템 004 헤드셋


2. 문자열 연결

WITH LOGIS AS
( SELECT '아이템' PROD_ID , '001' CARGO_NO, '마우스' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '002' CARGO_NO, '키보드' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '003' CARGO_NO, '쿨링팬' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '004' CARGO_NO, '헤드셋' CARGO FROM DUAL
)
SELECT PROD_ID , WM_CONCAT(CARGO) 
 FROM LOGIS
 group by PROD_ID ;

PROD_ID WM_CONCAT(CARGO)
--------- ----------------------------------
아이템 마우스,키보드,쿨링팬,헤드셋


3. 문자열 연결 + 정렬 시도시 에러

WITH LOGIS AS
( SELECT '아이템' PROD_ID , '001' CARGO_NO, '마우스' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '002' CARGO_NO, '키보드' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '003' CARGO_NO, '쿨링팬' CARGO FROM DUAL UNION ALL
SELECT '아이템' PROD_ID , '004' CARGO_NO, '헤드셋' CARGO FROM DUAL
)
SELECT PROD_ID ,CARGO_NO , WM_CONCAT(CARGO) OVER (PARTITION BY PROD_ID
ORDER BY CARGO ) AS CARGO_LIST
 FROM LOGIS ;
 
-- 기대결과
PROD_ID CARGO_NO CARGO_LIST
------- ----------- -------------------------------
아이템 001 마우스
아이템 003 마우스,쿨링팬
아이템 002 마우스,쿨링팬,키보드
아이템 004 마우스,쿨링팬,키보드,헤드셋

-- 개선 전 결과 - SQL FAIL
TBR-8006 : Missing From keyword at line 8, column 42 

라. pivot/unpivot 동시 사용

한 개의 SQL에서 pivot 함수를 사용하여 행열 전환한 데이터를 다시 unpivot 처리하는 기능을 티베로가 제공하지 않아 SQL Fail이 발생하였다. 해당 기능은 일부 외산 DBMS 제품 매뉴얼에도 지원하지 않는다고 명시되어 있어 엔진 패치 요청을 망설였지만 본회가 사용 중인 외산 DBMS에는 문제없이 처리가 되고 있고 SQL 수정시 변경해야 할 부분이 많아 패치를 통해 해결하였다.

티베로에서 지원하는 pivot/unpivot 함수의 사용법을 간단히 확인해보자.

1. 원본 데이터 구성

SELECT '001' AS RN, 'ORANGE' AS FRUIT FROM DUAL UNION ALL
SELECT '002' AS RN, 'APPLE' AS FRUIT FROM DUAL UNION ALL
SELECT '003' AS RN, 'GRAPE' AS FRUIT FROM DUAL UNION ALL
SELECT '004' AS RN, 'MELON' AS FRUIT FROM DUAL

RN FRUIT
---- ---------
001 ORANGE
002 APPLE
003 GRAPE
004 MELON


2. pivot 함수를 통해 행열 전환

SELECT * FROM (
 SELECT '001' AS RN, 'ORANGE' AS FRUIT FROM DUAL UNION ALL
 SELECT '002' AS RN, 'APPLE' AS FRUIT FROM DUAL UNION ALL
 SELECT '003' AS RN, 'GRAPE' AS FRUIT FROM DUAL UNION ALL
 SELECT '004' AS RN, 'MELON' AS FRUIT FROM DUAL
 )PIVOT (MIN(FRUIT) FOR RN IN ('001' COL1, '002' COL2, '003' COL3, '004'
COL4))

COL1 COL2 COL3 COL4
------- ----- ------ -----
ORANGE APPLE GRAPE MELON


3. unpivoit 함수를 통해 pivot 함수를 통해 형열 전환한 것을 다시 한 번 전환

SELECT * FROM (
 SELECT *
 FROM (
 SELECT '001' AS RN, 'ORANGE' AS FRUIT FROM DUAL UNION ALL
 SELECT '002' AS RN, 'APPLE' AS FRUIT FROM DUAL UNION ALL
 SELECT '003' AS RN, 'GRAPE' AS FRUIT FROM DUAL UNION ALL
 SELECT '004' AS RN, 'MELON' AS FRUIT FROM DUAL
 )PIVOT (MIN(FRUIT) FOR RN IN ('001' COL1, '002' COL2, '003' COL3,
'004' COL4))
) UNPIVOT (NEWFRUIT FOR GUBUN IN (COL1 AS '001', COL2 AS '002', COL3 AS
'003', COL4 AS '004'));

GUBUN NEWFRUIT
------- ----------
001 ORANGE
002 APPLE
003 GRAPE
004 MELON

마. 딕셔너리 뷰 ALL_TAB_COLS의 부재

소속 회사는 딕셔너리 뷰를 통해 테이블명, 컬럼명 등의 필요한 정보를 추출하여 SQL을 생성/수행시키는 다소 복잡한 프로시져들이 여럿 존재한다.

따라서 딕셔너리 뷰의 이름과 스키마가 외산 DBMS의 것과 상이하다면 프로시져를 수정해야 했으므로 딕셔너리 뷰의 동일 여부가 전환 시 주요 관심사 중 하나였다(데이터베이스 관리자로서 관리 목적으로 생성해둔 여러 가지 스크립트들 또한 딕셔너리 뷰가 상이하면 전부 수정해야 하므로 딕셔버리 뷰의 계속 사용 여부는 매우 중요한 사항 중 하나였다).

다행히도 ALL_TAB_COLS 뷰를 제외하고는 소속 회사가 사용할 때 불편을 주는 딕셔너리 뷰의 상이점은 존재하지 않았으며 ALL_TAB_COLS또한 내부 스키마 구조가 유사한 ALL_TAB_COLUMNS 뷰가 존재하여 시노님(SYNONYM)을 생성, 프로시져의 수정 없이 해당 이슈를 처리할 수 있었다.

SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME = 'ALL_TAB_COLS';

OWNER SYNONYM_NAME ORG_OBJECT_OWNER ORG_OBJECT_NAME
------ -------------- ----------------- ------------------
PUBLIC ALL_TAB_COLS PUBLIC ALL_TAB_COLUMNS