6개의 필드 중 하나의 필드가 원본 테이블의 두 필드를 연결한 뷰 하나를 만들었습니다. listagg를 사용하여 연결의 결과를 잠재적으로 결합하는 두 번째 뷰를 만들었습니다. 이 두 단계 연산을 사용하면 원본 테이블에서 두 필드의 listagg 연결이 올바르게 반환됩니다.
연결과 listagg를 모두 생성하는 하나의 뷰를 만들려고 하면 오라클 오류 ora-01489 문자열 연결의 결과가 너무 깁니다.
테스트용으로 max(length(연결된 필드)를 선택했는데 837이 반환되었습니다. 따라서 명시된 오류는 오류인 것 같습니다.
따라서 구문에 문제가 있는 것 같습니다. rtrim, trim, 심지어 substr까지 시도해 보았지만 listagg(concatenation)를 컴파일하고 데이터를 반환하는 뷰 문을 얻을 수 없습니다.
데이터를 올바르게 반환하는 두 개의 뷰를 개발할 수 있다는 것은 기본 구문을 알고 있다는 것을 의미하지만 연결을 listagg 함수와 결합하는 작업은 알아낼 수 없었습니다.
별도의 상황에서는 하나의 문에서 연결과 listagg 함수를 결합할 수 있었습니다:
LISTAGG (STATEMENTS || ' - ' || BIRTH_DATE, '; ')
WITHIN GROUP (ORDER BY STATEMENTS || ' - ' || BIRTH_DATE)
AS GROWING_UP
이 구문은 원하는 데이터를 올바르게 반환합니다.........
그러나 위에서 설명한 상황에서는 유사한 구문으로 인해 Ora-01489 오류가 발생합니다.
때때로 오라클 오류가 오해의 소지가 있을 수 있다는 것을 알고 있는데, 오라클이 이 오류를 발생시킨다고 명시한 것 외에 다른 이유를 알고 있는 전문가가 있는지 궁금합니다.
LISTAGG ((NUMBER || '-' || text), ',') WITHIN GROUP (ORDER BY (NUMBER || '-' || text))
AS
restrictions FROM source
오류를 반환합니다.
'LISTAGG'에도 적용되는 4000바이트의 SQL 제한을 초과하고 있습니다.
SQL> SELECT listagg(text, ',') WITHIN GROUP (
2 ORDER BY NULL)
3 FROM
4 (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
5 )
6 /
SELECT listagg(text, ',') WITHIN GROUP (
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
해결 방법으로 XMLAGG를 사용할 수 있습니다.
예를 들어
SQL> SET LONG 2000000
SQL> SET pagesize 50000
SQL> SELECT rtrim(xmlagg(XMLELEMENT(e,text,',').EXTRACT('//text()')
2 ).GetClobVal(),',') very_long_text
3 FROM
4 (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
5 )
6 /
VERY_LONG_TEXT
--------------------------------------------------------------------------------
one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen
,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty-one,twenty-two,twenty
-three,twenty-four,twenty-five,twenty-six,twenty-seven,twenty-eight,twenty-nine,
thirty,thirty-one,thirty-two,thirty-three,thirty-four,thirty-five,thirty-six,thi
rty-seven,thirty-eight,thirty-nine,forty,forty-one,forty-two,forty-three,forty-f
our,forty-five,forty-six,forty-seven,forty-eight,forty-nine,fifty,fifty-one,fift
y-two,fifty-three,fifty-four,fifty-five,fifty-six,fifty-seven,fifty-eight,fifty-
nine,sixty,sixty-one,sixty-two,sixty-three,sixty-four,sixty-five,sixty-six,sixty
-seven,sixty-eight,sixty-nine,seventy,seventy-one,seventy-two,seventy-three,seve
nty-four,seventy-five,seventy-six,seventy-seven,seventy-eight,seventy-nine,eight
y,eighty-one,eighty-two,eighty-three,eighty-four,eighty-five,eighty-six,eighty-s
even,eighty-eight,eighty-nine,ninety,ninety-one,ninety-two,ninety-three,ninety-f
our,ninety-five,ninety-six,ninety-seven,ninety-eight,ninety-nine,one hundred,one
hundred one,one hundred two,one hundred three,one hundred four,one hundred five
,one hundred six,one hundred seven,one hundred eight,one hundred nine,one hundre
d ten,one hundred eleven,one hundred twelve,one hundred thirteen,one hundred fou
rteen,one hundred fifteen,one hundred sixteen,one hundred seventeen,one hundred
eighteen,one hundred nineteen,one hundred twenty,one hundred twenty-one,one hund
red twenty-two,one hundred twenty-three,one hundred twenty-four,one hundred twen
ty-five,one hundred twenty-six,one hundred twenty-seven,one hundred twenty-eight
,one hundred twenty-nine,one hundred thirty,one hundred thirty-one,one hundred t
hirty-two,one hundred thirty-three,one hundred thirty-four,one hundred thirty-fi
ve,one hundred thirty-six,one hundred thirty-seven,one hundred thirty-eight,one
hundred thirty-nine,one hundred forty,one hundred forty-one,one hundred forty-tw
o,one hundred forty-three,one hundred forty-four,one hundred forty-five,one hund
red forty-six,one hundred forty-seven,one hundred forty-eight,one hundred forty-
nine,one hundred fifty,one hundred fifty-one,one hundred fifty-two,one hundred f
ifty-three,one hundred fifty-four,one hundred fifty-five,one hundred fifty-six,o
ne hundred fifty-seven,one hundred fifty-eight,one hundred fifty-nine,one hundre
d sixty,one hundred sixty-one,one hundred sixty-two,one hundred sixty-three,one
hundred sixty-four,one hundred sixty-five,one hundred sixty-six,one hundred sixt
y-seven,one hundred sixty-eight,one hundred sixty-nine,one hundred seventy,one h
undred seventy-one,one hundred seventy-two,one hundred seventy-three,one hundred
seventy-four,one hundred seventy-five,one hundred seventy-six,one hundred seven
ty-seven,one hundred seventy-eight,one hundred seventy-nine,one hundred eighty,o
ne hundred eighty-one,one hundred eighty-two,one hundred eighty-three,one hundre
d eighty-four,one hundred eighty-five,one hundred eighty-six,one hundred eighty-
seven,one hundred eighty-eight,one hundred eighty-nine,one hundred ninety,one hu
ndred ninety-one,one hundred ninety-two,one hundred ninety-three,one hundred nin
ety-four,one hundred ninety-five,one hundred ninety-six,one hundred ninety-seven
,one hundred ninety-eight,one hundred ninety-nine,two hundred,two hundred one,tw
o hundred two,two hundred three,two hundred four,two hundred five,two hundred si
x,two hundred seven,two hundred eight,two hundred nine,two hundred ten,two hundr
ed eleven,two hundred twelve,two hundred thirteen,two hundred fourteen,two hundr
ed fifteen,two hundred sixteen,two hundred seventeen,two hundred eighteen,two hu
ndred nineteen,two hundred twenty,two hundred twenty-one,two hundred twenty-two,
two hundred twenty-three,two hundred twenty-four,two hundred twenty-five,two hun
dred twenty-six,two hundred twenty-seven,two hundred twenty-eight,two hundred tw
enty-nine,two hundred thirty,two hundred thirty-one,two hundred thirty-two,two h
undred thirty-three,two hundred thirty-four,two hundred thirty-five,two hundred
thirty-six,two hundred thirty-seven,two hundred thirty-eight,two hundred thirty-
nine,two hundred forty,two hundred forty-one,two hundred forty-two,two hundred f
orty-three,two hundred forty-four,two hundred forty-five,two hundred forty-six,t
wo hundred forty-seven,two hundred forty-eight,two hundred forty-nine
자체적으로 4000바이트인 여러 열을 연결하려는 경우, 각 열의 XMLAGG 출력을 연결하여 4000바이트의 SQL 제한을 피할 수 있습니다.
예를 들어
WITH DATA AS
( SELECT 1 id, rpad('a1',4000,'*') col1, rpad('b1',4000,'*') col2 FROM dual
UNION
SELECT 2 id, rpad('a2',4000,'*') col1, rpad('b2',4000,'*') col2 FROM dual
)
SELECT ID,
rtrim(xmlagg(XMLELEMENT(e,col1,',').EXTRACT('//text()') ).GetClobVal(), ',')
||
rtrim(xmlagg(XMLELEMENT(e,col2,',').EXTRACT('//text()') ).GetClobVal(), ',')
AS very_long_text
FROM DATA
GROUP BY ID
ORDER BY ID;