SQL & PL/SQL

Hello guys, I was wondering if you can share your knowledge with me about String Aggregation in Oracle. To understand my problem, here is a scenario:

I have a table with some sample data.

DROP TABLE tst_clobagg;

CREATE TABLE tst_clobagg 
(
  grp_id NUMBER(10),  -- group id
  rn     NUMBER(10),  -- order of the text in the group
  text   VARCHAR2(4000)  -- the content
);

INSERT INTO tst_clobagg(grp_id, rn, text) 
  VALUES (1, 1, 'This is the first lob - ' || DBMS_RANDOM.STRING('x', 3500));

INSERT INTO tst_clobagg(grp_id, rn, text) 
  VALUES (1, 2, 'This is the second lob - ' || DBMS_RANDOM.STRING('x', 3500));
  
INSERT INTO tst_clobagg(grp_id, rn, text) 
  VALUES (1, 3, 'This is the third lob - ' || DBMS_RANDOM.STRING('x', 3500));

INSERT INTO tst_clobagg(grp_id, rn, text) 
  VALUES (2, 3, 'Second lob(3) - ' || DBMS_RANDOM.STRING('x', 3500));

INSERT INTO tst_clobagg(grp_id, rn, text) 
  VALUES (2, 1, 'Second lob(1)  - ' || DBMS_RANDOM.STRING('x', 3500));
  
INSERT INTO tst_clobagg(grp_id, rn, text) 
  VALUES (2, 2, 'Second lob(2) - ' || DBMS_RANDOM.STRING('x', 3500));

COMMIT;

I'm on Oracle 11g and I have access to LISTAGG. Unfortunately, LISTAGG does not support aggregation of String bigger than 4000 characters.

SELECT grp_id, LISTAGG(text) WITHIN GROUP(ORDER BY rn)
  FROM tst_clobagg
 GROUP BY grp_id;

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

So ok, I need to find another solution. XML, CONNECT BY trick... or STRING_AGG from Tom Kyte (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402).


- CONNECT BY is not a solution since it is also limited to 4000 characters.

- With XML processing, it can do the job but it is very very slow and resource consuming.

- Tom Kyte's solution works but it's not perfect either because of the sorting.

SELECT t.grp_id, clobagg(t.text)
  FROM tst_clobagg t
 GROUP BY t.grp_id;

The following will work:

SELECT t.grp_id, clobagg(t.text)
  FROM (
        SELECT grp_id, rn, text
          FROM tst_clobagg
         ORDER BY grp_id, rn
        ) t
 GROUP BY t.grp_id;

Can anyone tell me by experience what is the best approach to aggregate String? I would gladly use LISTAGG but unfortunately it fails when the concatenation is more than 4000 chars. Is it somehow possible to ask LISTAGG to aggregate only the 4000 first chars and then stop (but not generate an exception?)

What I don't like about clobagg is that we need to ORDER BY. Since I have milions of records, it can take a while and it is a process that needs to be done daily. I need something very performant.

Any good idea?

Thanks,