ORA-01489: result of string concatenation is too long 문의 0 3 7,970

by 선택자 [Oracle 기초] [2016.12.13 13:18:13]


안녕하세요. 쿼리를 짜고 실행하면 

'ORA-01489: result of string concatenation is too long' 라는 메시지가 나오는데요. 현재 오라클 버전은 10g를 사용하고 있고요... 11g 에서는 잘 돌아가고요...

구글링 해보면 to_clob으로 해보라고 하고 또, RTRIM(XMLAGG(XMLELEMENT(E,EDU_DATE,',').EXTRACT('//text()') ).GetClobVal(),',') 방식으로 해보라고 나와서 적용해보면 

'ORA-01489: result of string concatenation is too long' 라는 메시지가 나옵니다....

다른 방법이 있으시면 부탁드리겠습니다....

by 마농 [2016.12.13 13:44:25]

1. 오류에 대해.
  해당 오류는 문자열 연결을 4000 Byte 이상 연결할 때 나는 에러입니다.
  제가 작성해 드린 쿼리 중 "LEVEL <= 99" 부분을 그대로 적용하셨네요?
  그렇다면 날짜 8 자리 + 콤마 한자리 총 9자리 문자가 99 개로 연결된다고 하면
  99 * 9 = 891 Byte 로 에러가 발생 수준에 못미치는 수입니다.
  또한 연속일자를 묶어서 표현하는 것이므로 결과는 원본보다 길이가 줄어드는 쿼리입니다.
  따라서 에러 발생은 wm_concat 이 아닌 connect_by_path 에서 발생하는 거네요.
  wm_concat 을 xmlagg 의 getclobval 로 바꿔도 에러나는 이유이구요.
  4000Byte / 9 = 444.444... 이므로
  444일까지는 에러 안나지만 445일부터는 에러나겠네요.
  즉, edu_start ~ edu_end 사이 일자가 445 일이 넘는 거네요.


2. 쿼리의 개선
  edu_date 에는 날짜가 컴마로 연결되어 들어가 있네요.
  edu_date 에 값이 없는 경우는 edu_start ~ edu_end 까지 모든 날짜가 있다고 보는 모양이네요.
  그렇다면 edu_date 에 값이 없는 경우의 최종 결과는 그냥 edu_start ~ edu_end 이 됩니다.
  이걸 굳이 일별 날짜 생성해서 다시 연결한 뒤에 다시 쪼개고 다시 붙이고 하는 과정이 불필요 하죠.
  edu_date 에 값이 있을 때에만 쪼개고 붙이는 과정이 필요합니다.
  edu_date 에 값이 없을 때에는 쪼개고 붙이는 과정이 불필요합니다.


3. RS_CD 는 PK 인가요?
  RS_CD 가 중복이라면?
  여러 RS_CD 의 날짜들을 하나로 엮다보면 4000Byte 이상 나올 수도 있겠네요.


by 마농 [2016.12.13 13:56:09]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT rs_cd
     , SUBSTR(
       XMLAGG(XMLELEMENT(x, ',', edu_date) ORDER BY edu_date).EXTRACT('//text()').getStringVal()
       , 2) edu_date_div
  FROM (SELECT rs_cd
             , NVL( MIN(edu_date) || DECODE(COUNT(*), 1, '', '~'||MAX(edu_date))
                  , edu_start || DECODE(edu_start, edu_end, '', '~'||edu_end)
                  ) edu_date
          FROM (SELECT rs_cd
                     , edu_start
                     , edu_end
                     , SUBSTR(edu_date, lv*9-8, 8) edu_date
                     , ROW_NUMBER() OVER(PARTITION BY rs_cd ORDER BY SUBSTR(edu_date, lv*9-8, 8)) rn
                  FROM th_resource_seq
                     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
                 WHERE lv(+) <= (LENGTH(edu_date)+1)/9
                )
         GROUP BY rs_cd, edu_start, edu_end
             , TO_DATE(edu_date, 'yyyymmdd') - rn)
        )
 GROUP BY rs_cd
 ORDER BY rs_cd
;

 


by 선택자 [2016.12.13 14:19:48]

감사합니다~ 마농님~ 

XMLAGG(XMLELEMENT(x, ',', edu_date) ORDER BY edu_date).EXTRACT('//text()').GETCLOBVAL() 을 적용했을때 토드가 자꾸 중지가 되서 잘못 된건가 했는데 다시 테스트를 해봐야겠습니다^^

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