Listagg Function: "Result of String Concatenation Is Too Long"

LISTAGG function: result of string concatenation is too long

Since the aggregates string can be longer than 4000 bytes, you can't use the LISTAGG function. You could potentially create a user-defined aggregate function that returns a CLOB rather than a VARCHAR2. There is an example of a user-defined aggregate that returns a CLOB in the original askTom discussion that Tim links to from that first discussion.

LISTAGG 4000 Character Limit - Result of string concatenation is too long

The best solution I know is posted somewhere in the Internet... You could probably just google for it. It basically consist of few steps:

1. Creating a collection type to store each text value to concatenate

create or replace type string_array_t as table of VARCHAR2(4000);

2. Creating a PL/SQL function which takes string_array_t as parameter and returns concatenated text as CLOB:

create or replace function 
  string_array2clob(
    p_string_array string_array_t
    ,p_delimiter varchar2 default ','
) RETURN CLOB IS
  v_string CLOB;
BEGIN
  -- inside is a loop over p_string_array to concatenate all elements
  --
  -- below is just a draft because in real you should use a while loop
  -- to handle sparse collection and you should put some checks to handle not initialized collection
  -- and other important cases
  -- furthermore it's better to create additional varchar2 variable as a buffer
  -- and convert that buffer to clob when it's full for a better performance
  for indx in p_string_array.first..p_string_array.last loop
    v_string := v_string || to_clob(p_string_array(indx) || p_delimiter);
  end loop;

  RETURN substr(v_string, 1, nvl(length(v_string),0) - nvl(length(p_delimiter),0));
END string_array2clob;
/

3. Aggregate query as usual but using cast and collect instead of listagg and at the end convert it to clob with function from step above:

select t.name, string_array2clob(cast(collect(t.text order by t.line) as string_array_t ), p_delimiter => chr(10)) as text
   from user_source t 
  group by t.name;

If your query is not just an example of concept and really you're trying to get a source of some object in database, then you should read about dbms_metadata.get_ddl function. It's made for it.

Subquery Listagg get errors: result of string concatenation is too long

Xmlagg method can be used to aggregate strings.

Example.

select rtrim(
           xmlserialize(content extract(xmlagg(xmlelement(e, object_name || ',') order by object_id), '//text()')
                        no indent)
         , ',')
       , object_type
    from user_objects
group by object_type;

In your case first subquery will be look like this.

  select ITM.PRODUCT_NO
       , rtrim(
           xmlserialize(
             content extract(
                       xmlagg(
                         xmlelement(
                           e
                         ,    nvl2(ITM.OPTION1, ITM.OPTION1 || (chr(13) || chr(10)), '')
                           || nvl2(ITM.OPTION2, ITM.OPTION2 || (chr(13) || chr(10)), '')
                           || nvl2(ITM.OPTION3, ITM.OPTION3 || (chr(13) || chr(10)), '')
                           || nvl2(ITM.OPTION4, ITM.OPTION4 || (chr(13) || chr(10)), '')
                           || ',')
                         order by ITM.itm_no)
                     , '//text()')
             no indent)
         , ',')
    from ITEM ITM
group by ITM.PRODUCT_NO

Here you can find more method
https://www.programmingmusings.com/index.php/2015/05/22/string-aggregation-in-oracle-sql/

---

Related Topics