XMLAGG, MAX함수 사용시 에러 발생 문의 0 7 228

by 생각 [PL/SQL] [2018.03.12 11:00:34]


저장 프로시저에 서브쿼리로 구현중인 코드가 저번주 금욜부터 오류가 발생하네요.

데이터 정합성을 통해 문제가 있는지 확인해봤으나, ch_name, cd_name에 null이 있거나 컬럼 크기가 문제가 있지는 않았습니다.

1. ORA-19011 : 데이터 버퍼가 작다는 오류가 발생하여 getCLOBval()을 추가했습니다.

2. 데이터 버퍼가 작다는 오류는 사라졌지만, 

   ORA-24347 함수합계에 NULL 경고가 있다는 오류가 발생했습니다.

3. 해당 MAX함수를 NVL(MAX(b.CH_NAME,''), MAX(NVL(b.ch_name,'') 동일 현상이 발생했습니다.

4. 행열 전환 함수 XMLAGG와 MAX함수간의 문제로 보이는데 해결 방안을 여쭙고 싶습니다.

SELECT  a.CODE  CODE
       ,Max(b.NAME) NAME
       ,SubStr(SUBSTR(XMLAGG(XMLELEMENT(X,'/',a.NAME) ORDER BY a.NAME).EXTRACT('//text()').getCLOBval(),2),1,200) NAME       
FROM   SETCODEd a
           SETCODEh b
WHERE  a.CODE  = b.CODE (+)
GROUP BY  a.CODE ;

by 마농 [2018.03.12 11:39:16]

1. 정상적인 에러는 아닌것 같네요. 버그성 에러인 듯 합니다.
2. NVL 처리시 '' 이 아닌 ' ' 으로 해보세요.
  - 오라클에서의 ''은 널과 같으므로, NVL(, '') 은 하나마나한 코드입니다.
3. a 와 b 의 관계가 어떻게 되나요?
  - 1:1 또는 m:1 관계라면?
  - 방안1 : Group by 에 b.name 을 추가하고 max 함수를 제거하세요.
  - 방안2 : 조인 후 그룹바이 하지 마시고 그룹바이 하고 난 후 조인하세요.


by 생각 [2018.03.12 13:35:02]

1. getCLOBval()함수를 통해 문자열 버퍼 에러는 없어졌지만, 출력되는 문자열이 data type error로 나오네요.

2. 이게 여러 개발 언어를 접하다보니 개념이 점점 옅어지는것 같습니다. 조언 고맙습니다.

3. m:1 관계입니다.


by 마농 [2018.03.12 13:59:55]

cast 함수를 통해 데이터 타입을 바꿀 수 있습니다.
200 자만 필요한거면 애초에 200자 넘지 않는 범위만 가져와 사용하는 방법도 있을 거구요.
이 방법이 가능하다면? 버전이 11G라면 XmlAgg 대신 ListAgg 사용하는 게 좋구요.
합치려는 문자열 이름에 혹시 중복은 없나요? 중복이 있다면 우선 제거해야 할 것 같구요.


by 생각 [2018.03.12 14:30:40]

네 말씀하신대로 LISTAGG를 사용했는데 오히려 그건 문자열 버퍼가 길다고 나오네요~

합치려는 문자열중에 '기타'라는 단어가 공통으로 쓰는부분이 있는데 한번 체크해보겠습니다.


by 마농 [2018.03.12 14:49:19]

앞의 단서조항(200자 넘지 않는 범위만 가져와...)에 대한 처리 없이 ListAgg 만 적용하신 듯 하네요.


by 마농 [2018.03.12 14:44:50]
SELECT a.code
     , b.name b_name
     , SUBSTR(a.name, 1, 200) a_name
  FROM (SELECT code
             , LISTAGG(name, '/') WITHIN GROUP(ORDER BY name) name
          FROM (-- 중복 제거 및 누적 길이 측정 --
                SELECT code, name
                     , SUM(LENGTH(name)) OVER(PARTITION BY code ORDER BY name) v
                  FROM setcoded
                 WHERE name IS NOT NULL
                 GROUP BY code, name
                )
         WHERE v - LENGTH(name) < 200  -- 이전까지 누적길이 200 미만인 것만 추출
         GROUP BY code
        ) a
     , setcodeh b
 WHERE a.code = b.code(+)
;

 


by 생각 [2018.03.12 16:20:10]

마농님 정말 많은것을 배우게 됩니다.

데이터 타입 에러 및 200자 제한 둘다 문제없이 해결되었네요.

인라인뷰 사용과 관련한 공부가 필요하겠네요.

감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입