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

Question

 select t.name, listagg(t.text) 
   from user_source t 
  group by t.name;

I am trying to execute the code above but since varchar2 is limited by 4000 chars it throws error. I tried to convert listagg to xml but I could not solve the

ORA-64451: Conversion of special character to escaped character failed.

error. I also tried the answers from other posts from various websites including stackoverflow.

I do not want to truncate the string, also I can't change MAX_STRING_SIZE parameter.

This example below throws ORA-64451 as well. I tried but could not solve the problem.

select rtrim(
             xmlagg(
                    xmlelement(e, to_clob(t.TEXT), '; ').extract('//text()')
                   ).GetClobVal(),
             ',')
  from user_source t;

Answer 1

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.