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' 라는 메시지가 나옵니다....
다른 방법이 있으시면 부탁드리겠습니다....
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 이상 나올 수도 있겠네요.
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 ; |