잘린 LISTAGG 문자열

잘린 LISTAGG 문자열

이 질문에는 이미 다음과 같은 답변이 있습니다.

LISTAGG 함수: " 문자열 연결 결과가 너무 깁니다." (14개 답변)

닫힘9년 전에.

Oracle 11gr2를 사용하고 있는데 여러 행의 문자열(VARCHAR2, 300)을 연결해야 합니다.사용하고 있습니다.LISTAGG연결된 문자열이 한계에 도달할 때까지 잘 작동합니다.그 시점에서 나는 a를 받습니다.ORA-01489: result of string concatenation is too long.

결국 연결된 문자열의 첫 4000자만 원합니다.제가 어떻게 가든지 상관없습니다.비효율적인 해결책을 받아들이겠습니다.

제 질문은 이렇습니다.

SELECT LISTAGG(T.NAME, ' ') WITHIN GROUP (ORDER BY NULL)
FROM T

이 코드는 데이터 길이에 상관없이 충분히 빠릅니다.

SELECT REPLACE(
         REPLACE(
           XMLAGG(
             XMLELEMENT("A",T.NAME)
           ORDER BY 1).getClobVal(),
         '<A>',''),
       '</A>','[delimiter]')
FROM T

기본 제공(단, 사용되지 않음) STRAGGG 기능을 사용할 수 있습니다.

    select sys.stragg(distinct name) from t

(중복을 피하기 위해서는 구별이 필요할 것으로 보여 주의하시기 바랍니다.

또는 사용자 고유의 집계 함수/유형을 정의합니다.

CREATE OR REPLACE TYPE "STRING_AGG_TYPE" as object
(
  total varchar2(4000),

  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type) return number,

  member function ODCIAggregateIterate(self  IN OUT string_agg_type,
                                       value IN varchar2) return number,

  member function ODCIAggregateTerminate(self        IN string_agg_type,
                                         returnValue OUT varchar2,
                                         flags       IN number) return number,

  member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                     ctx2 IN string_agg_type) return number
);

CREATE OR REPLACE TYPE BODY "STRING_AGG_TYPE" is

  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type) return number is
  begin
    sctx := string_agg_type(null);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self  IN OUT string_agg_type,
                                       value IN varchar2) return number is
  begin
    -- prevent buffer overflow for more than 4,000 characters
    if nvl(length(self.total),
           0) + nvl(length(value),
                    0) < 4000 then
      self.total := self.total || ';' || value;
    end if;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self        IN string_agg_type,
                                         returnValue OUT varchar2,
                                         flags       IN number) return number is
  begin
    returnValue := ltrim(self.total,
                         ';');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                     ctx2 IN string_agg_type) return number is
  begin
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
  end;

end;

CREATE OR REPLACE FUNCTION stragg(input varchar2 )
   RETURN varchar2
   PARALLEL_ENABLE AGGREGATE USING string_agg_type;

다음과 같이 사용합니다.

select STRAGG(name) from t

이 접근 방식은 원래 Tom Kyte가 제안한 것이라고 생각합니다. (적어도 거기서 얻은 것입니다.) - Asktom: StringAgg

아마 도움이 될 것입니다.

substr(string, 1, 4000)

편집:

아니면 시도해보든지

SELECT [column], rtrim(
         xmlserialize(content 
           extract(
             xmlagg(xmlelement("n", (T.NAME||',') order by [column])
           , '//text()'
           )
         )
       , ','
       ) as list
FROM [table]
GROUP BY [column]
;

이것은 LISTAGG 함수의 단점이며, LISTAGG 분석 함수로 인해 생성된 문자열의 한계를 다루지 않습니다.이를 위해서는 길이의 누적합을 구하고 이를 기준으로 제한해야 합니다.

worked out example on emp table 

select listagg(ename,' ')within group (order by null)
from
(
select ename,
         sum( length( ename ) + 1) 
            over ( order by ename rows between unbounded preceding and current row) length
 from emp

 )where lngth <= 4000
 ;

그러나 내부 쿼리를 보면 아래와 같이 이름과 길이를 가진 열이 생성되기 때문에 완벽한 결과를 얻을 수 없습니다.

 ename      lenght 
===================
gaurav        6
rohan         11
:
:
garima        3996
anshoo        4002
=====================

그래서 위의 기능은 당신에게 결과를 줄 것입니다.garima......나중에anshlistagg는 내부 쿼리의 길이 열을 기반으로 하기 때문입니다.

언급URL : https://stackoverflow.com/questions/12315243/truncated-listagg-string