복붙노트

[SQL] LISTAGG 기능 : "문자열 연결의 결과가 너무 깁니다"

SQL

LISTAGG 기능 : "문자열 연결의 결과가 너무 깁니다"

나는 오라클 SQL 개발자 버전 3.0.04을 사용하고 있습니다. 나는 데이터를 함께 그룹 함수 LISTAGG를 사용하려고 ..

    CREATE TABLE FINAL_LOG AS
    SELECT SESSION_DT, C_IP, CS_USER_AGENT,
    listagg(WEB_LINK, ' ')
        WITHIN GROUP(ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS"
        FROM webviews
        GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
        ORDER BY SESSION_DT

그러나, 나는 오류가 계속

SQL Error: ORA-01489: result of string concatenation is too long

나는 꽤 확인 WEB_LINK 여기에 언급 URL 줄기 및 URL 쿼리의 연결된 값이기 때문에 출력이 더 많은 4000보다 수 있음을 해요.

주변에 갈 수있는 방법이 있습니까 또는 다른 어떤 대안이 있습니까?

해결법

  1. ==============================

    1.집계 문자열은 4000 바이트를 초과 할 수 있기 때문에, 당신은 LISTAGG 기능을 사용할 수 없습니다. 당신은 잠재적으로 VARCHAR2가 아닌 CLOB을 반환하는 사용자 정의 집계 함수를 만들 수 있습니다. 사용자 정의 집계의 예가 그 반환 원래 askTom 토론에서 CLOB 그 첫 번째 토론에서에 팀 링크.

    집계 문자열은 4000 바이트를 초과 할 수 있기 때문에, 당신은 LISTAGG 기능을 사용할 수 없습니다. 당신은 잠재적으로 VARCHAR2가 아닌 CLOB을 반환하는 사용자 정의 집계 함수를 만들 수 있습니다. 사용자 정의 집계의 예가 그 반환 원래 askTom 토론에서 CLOB 그 첫 번째 토론에서에 팀 링크.

  2. ==============================

    2.

    SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST
FROM tablename;

    이것은 CLOB 값 때문에 행에 제한을 반환합니다.

  3. ==============================

    3.listagg는 최근 ISO SQL 표준 (: 2016 SQL)이 적용되었다. 그 일환으로, 그것은 또한 오라클 12cR2에서 지원하는 오버 플로우 절, 상을 받았습니다.

    listagg는 최근 ISO SQL 표준 (: 2016 SQL)이 적용되었다. 그 일환으로, 그것은 또한 오라클 12cR2에서 지원하는 오버 플로우 절, 상을 받았습니다.

    LISTAGG(<expression>, <separator> ON OVERFLOW …)

    오버 플로우에 절은 (오버플로 오류 동작의 기본에 대한 대안 등) 잘라 내기 옵션을 지원합니다.

    ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

    절단이 발생하는 경우 세 개의 마침표 (...)과에 옵션 기본값은 마지막 요소로서 추가됩니다.

    카운트 지정 절단이 발생하는 경우 생략 값의 개수는 괄호 안에 넣어 결과에 부가된다.

    더 listagg에 대한 오버 플로우 절에있다 : http://modern-sql.com/feature/listagg

  4. ==============================

    4.당신은뿐만 아니라 LISTAGG에 적용 4000 바이트의 SQL 제한을 초과합니다.

    당신은뿐만 아니라 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 바이트를 가지고 여러 열을 연결하고 싶은 경우에, 당신은 4000 바이트의 SQL 제한을 피하기 위해 각 열의 XMLAGG 출력을 연결할 수 있습니다.

    예를 들어,

    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;
  5. ==============================

    5.12cR2에 추가 된 새로운 기능은 LISTAGG의 ON OVERFLOW 절입니다. 같을 것이다이 절을 포함한 쿼리 :

    12cR2에 추가 된 새로운 기능은 LISTAGG의 ON OVERFLOW 절입니다. 같을 것이다이 절을 포함한 쿼리 :

    SELECT pid, LISTAGG(Desc, ' ' ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

    위는 4000 개 문자로 출력이 제한됩니다하지만 ORA-01489 오류가 발생하지 않습니다.

    이들은 ON 용량 초과 조항의 추가 옵션 중 일부입니다 :

    12C R2의 향상 LISTAGG

  6. ==============================

    6.내 필드에는 각 덜 4000 자 제한보다는 여러 행에 연결된 참을 수 - 다음을했다 :

    내 필드에는 각 덜 4000 자 제한보다는 여러 행에 연결된 참을 수 - 다음을했다 :

    with PRECALC as (select 
                 floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH
                 from MY_TABLE)
select LISTAGG(MY_COLUMN,',') WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH), MY_COLUMN)
from MY_TABLE, PRECALC
group by floor(rownum/MAX_FIELD_LENGTH)
;
  7. ==============================

    7.허용 대답에에 추가. 나는 비슷한 문제로 실행 및 VARCHAR2 대신 CLOB를 반환하는 사용자 정의 함수를 사용하여 끝났다. 여기 내 솔루션입니다 :

    허용 대답에에 추가. 나는 비슷한 문제로 실행 및 VARCHAR2 대신 CLOB를 반환하는 사용자 정의 함수를 사용하여 끝났다. 여기 내 솔루션입니다 :

    CREATE OR REPLACE TYPE temp_data FORCE AS OBJECT
(
    temporary_data NVARCHAR2(4000)
)
/

CREATE OR REPLACE TYPE temp_data_table FORCE AS TABLE OF temp_data;
/

CREATE OR REPLACE FUNCTION my_agg_func (p_temp_data_table IN temp_data_table, p_delimiter IN NVARCHAR2)
RETURN CLOB IS
  l_string CLOB;
BEGIN
  FOR i IN p_temp_data_table.FIRST .. p_temp_data_table.LAST LOOP
    IF i != p_temp_data_table.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_temp_data_table(i).temporary_data;
  END LOOP;
  RETURN l_string;
END my_agg_func;
/

    자, 대신 일을

    LISTAGG(column_to_aggregate, '#any_delimiter#') WITHIN GROUP (ORDER BY column_to_order_by)

    나는이 작업을 수행해야

    my_agg_func (
    cast(
        collect(
            temp_data(column_to_aggregate)
            order by column_to_order_by
        ) as temp_data_table
    ),
    '#any_delimiter#'
)
  8. ==============================

    8.LISTAGG의 관리 오버 플로우

    LISTAGG의 관리 오버 플로우

    우리는 한계를 초과하지 않는 값 목록을 반환하는 데이터베이스 12C SQL 패턴 매칭 기능, MATCH_RECOGNIZE를 사용할 수 있습니다.

    예제 코드 및 링크 아래에 더 설명.

    https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg

  9. ==============================

    9.일부 시나리오에서는 의도는 모든 DISTINCT LISTAGG 키를 얻는 것입니다 및 오버 플로우는 LISTAGG가 모든 키을 연결한다는 사실에 기인한다.

    일부 시나리오에서는 의도는 모든 DISTINCT LISTAGG 키를 얻는 것입니다 및 오버 플로우는 LISTAGG가 모든 키을 연결한다는 사실에 기인한다.

    여기서 작은 예는

    create table tab as
select 
  trunc(rownum/10) x,
  'GRP'||to_char(mod(rownum,4)) y,
  mod(rownum,10) z
 from dual connect by level < 100;


select  
 x,
 LISTAGG(y, '; ') WITHIN GROUP (ORDER BY y) y_lst
from tab
group by x;


        X Y_LST                                                            
---------- ------------------------------------------------------------------
         0 GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3               
         1 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         2 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         3 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         4 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         5 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         6 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         7 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         8 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         9 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3

    그룹이 큰 경우, 반복되는 키는 신속하게 허용 된 최대 길이에 도달하면 ORA-01489를 얻을 : 문자열 연결의 결과가 너무 깁니다.

    ( ';'DISTINCT Y,)하지만이 사실을 해결 LISTAGG의 무시에 NULL을 것을 사용할 수 있습니다 불행히도 LISTAGG에 대한 지원은 없다. 우리가 첫 번째 키 고려해야 할 ROW_NUMBER 사용.

    with rn as (
select x,y,z,
row_number() over (partition by x,y order by y) rn
from tab
)
select  
 x,
 LISTAGG( case when rn = 1 then y end, '; ') WITHIN GROUP (ORDER BY y) y_lst,
 sum(z) z 
from rn
group by x
order by x;

         X Y_LST                                       Z
---------- ---------------------------------- ----------
         0 GRP0; GRP1; GRP2; GRP3             45 
         1 GRP0; GRP1; GRP2; GRP3             45 
         2 GRP0; GRP1; GRP2; GRP3             45 
         3 GRP0; GRP1; GRP2; GRP3             45 
         4 GRP0; GRP1; GRP2; GRP3             45 
         5 GRP0; GRP1; GRP2; GRP3             45 
         6 GRP0; GRP1; GRP2; GRP3             45 
         7 GRP0; GRP1; GRP2; GRP3             45 
         8 GRP0; GRP1; GRP2; GRP3             45 
         9 GRP0; GRP1; GRP2; GRP3             45

    물론, 동일한 결과의 하위 쿼리 GROUP BY X, Y를 이용하여 도달 할 수있다. ROW_NUMBER의 장점은 SUM (z)에 도시 된 바와 같이 다른 집계 기능이 사용될 수 있다는 것이다.

  10. ==============================

    10.우리는 오라클 LISTAGG를 사용하여 여기에 비슷한 문제를 해결할 수 있었다. 이 우리가 4K 제한을 초과 그룹화 된 어떤 점이었다 그러나 이것은 쉽게 첫 세트는 256K 제한이 각각의 집계에 처음 15 개 항목을 취함으로써 해결되었다.

    우리는 오라클 LISTAGG를 사용하여 여기에 비슷한 문제를 해결할 수 있었다. 이 우리가 4K 제한을 초과 그룹화 된 어떤 점이었다 그러나 이것은 쉽게 첫 세트는 256K 제한이 각각의 집계에 처음 15 개 항목을 취함으로써 해결되었다.

    추가 정보 : 우리는 다시 설명을 변경 주문을 가지고 프로젝트를 가지고있다. 데이터베이스는 256K 알려져 있지 않다 제한하지만, 설계 제약의 그것의 하나의 덩어리의 변화 텍스트를 취하도록 설정되어 왜. 테이블에 변경 설명을 공급하는 응용 프로그램이 254K 및 삽입에 정차 그래서, 다음, 우리는 주문 변경에 프로젝트가 그래서 254K는 다른 행 등을 발생> 경우 1 텍스트의 다음 세트를 가져 와서 : 1. N 설명은 : 그런 다음 우리는 이러한 1로 있습니다. LISTAGG이 모든를 연결합니다. 우리는 각각의 말과 / 또는 문자의 각 254K에 대한 RMRKS_SN 값 1 있습니다.

    나는 15 SN 공을 잡아 당겨 첫 번째 데이터 세트, 30 번째 데이터 세트 (16)와 마지막 세트 31 ~ 45 않았다, 그래서 가장 큰 RMRKS_SN는 31로 밝혀졌다 - 헤이, 일부 변화에 대한 설명을 많이 추가 누군가의 계획을 보자 명령!

    는 SQL 보고서에서 첫 번째 데이터 세트에 테이블 릭스 관계. 다른 데이터를 얻으려면, 여기에 표현입니다 :

    우선 = (필드! NON_STD_TXT.Value "DataSet_EXPLAN") 최초의 (필드! NON_STD_TXT.Value "ds_EXPLAN_SN_16_TO_30") 최초의 (필드! NON_STD_TXT.Value "ds_EXPLAN_SN_31_TO_45")

    우리를 위해, 우리는 DB 그룹은 보안상의 제약 등의 기능을 만들 수 있어야합니다. 그래서 창의성의 비트와 함께, 우리는 사용자 집계 또는 UDF를 할 필요가 없었어요.

    응용 프로그램에 의해 집계에 SN 어떤 종류가있는 경우,이 방법은 작동합니다. 나는 해당 TSQL이 무엇인지 모른다 - 우리는 LISTAGG는 신의 선물 인이 보고서, 오라클을 처리 할 운입니다.

    코드는 다음과 같습니다

    SELECT
LT.C_O_NBR AS LT_CO_NUM,
RT.C_O_NBR AS RT_CO_NUM,
LT.STD_LN_ITM_NBR, 
RT.NON_STD_LN_ITM_NBR,
RT.NON_STD_PRJ_NBR, 
LT.STD_PRJ_NBR, 
NVL(LT.PRPSL_LN_NBR, RT.PRPSL_LN_NBR) AS PRPSL_LN_NBR,
LT.STD_CO_EXPL_TXT AS STD_TXT,
LT.STD_CO_EXPLN_T, 
LT.STD_CO_EXPL_SN, 
RT.NON_STD_CO_EXPLN_T,
LISTAGG(RT.RMRKS_TXT_FLD, '') 
    WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT

FROM ...

    WHERE RT.RMRKS_SN BETWEEN 0 AND 15

GROUP BY 
    LT.C_O_NBR,
    RT.C_O_NBR,
    ...

    그리고 다른 두 데이터 세트에서 단지 단지에서 하위 쿼리에 대한 LISTAGG를 선택 :

    SELECT
LISTAGG(RT.RMRKS_TXT_FLD, '') 
    WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT

    FROM ...

    WHERE RT.RMRKS_SN BETWEEN 31 AND 45

    ...

    ... 등등.

  11. ==============================

    11.조언 주셔서 감사합니다. CONCATENATE 여러 분야지만, 심지어 XMLAGG이없는 저를 도와 때 저도 같은 문제를 겪고 - 난 여전히 ORA-01489를 얻었다. 여러 번 시도 후 나는 원인과 해결책을 발견 :

    조언 주셔서 감사합니다. CONCATENATE 여러 분야지만, 심지어 XMLAGG이없는 저를 도와 때 저도 같은 문제를 겪고 - 난 여전히 ORA-01489를 얻었다. 여러 번 시도 후 나는 원인과 해결책을 발견 :

    예:

    rtrim(xmlagg(xmlelement(t, t.field1 ||'|'|| 
                           t.field2 ||'|'|| 
                           t.field3 ||'|'|| 
                           to_clob(t.field4),'; ').extract('//text()')).GetClobVal(),',')

    이 도움말 누구를 바랍니다.

  12. ==============================

    12.CLOB를 사용하여 12C 오버 플로우를 사용하여 짧은 및 SUBSTR 것 또한 일

    CLOB를 사용하여 12C 오버 플로우를 사용하여 짧은 및 SUBSTR 것 또한 일

    RTRIM (dbms_lob.substr (XMLAGG (XMLELEMENT (E, COLUMN_NAME, ','). 추출물 ( '// 텍스트 ()') COLUMN_NAME BY ORDER) .GetClobVal (), 1000,1), ',')

    13. from https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 열은 다른 열에서 계산?  (0) 2020.03.17
[SQL] Reporting Services의 단일 매개 변수에 대해 여러 값을 전달  (0) 2020.03.17
[SQL] MySQL의 외래 키 제약 조건은 잘못 오류가 형성된다  (0) 2020.03.17
[SQL] MySQL의 순위 그룹화 수행하는 방법  (0) 2020.03.17
[SQL] VARCHAR에 대한 장점은 (500)를 통해 VARCHAR (8000)가있다?  (0) 2020.03.17
,

티스토리툴바