Hi,
aggregation of strings to an CLOB column seems to be a horrorstory until now!
There severall ways and my decision was the SQL way because in Carstens C.'s way ("Oracle SQL und PL/SQL" von Carsten Czarski: SQL LISTAGG mit CLOB-Ausgabe - kein VARCHAR2-Limit mehr) it's complicated to add a second parameter for the delimiter in the function.
But every time i run into ORA-00932. My structure:
CREATE TABLE test_clob
(
ID NUMBER,
CVALUE CLOB
);
CREATE TABLE test_varchar
(
ID NUMBER,
SVALUE VARCHAR2(4000 CHAR)
);
INSERT INTO test_clob (ID, CVALUE)
VALUES(1, 'CLOB Value 1');
INSERT INTO test_clob (ID, CVALUE)
VALUES(2, 'CLOB Value 2');
INSERT INTO test_varchar (ID, SVALUE)
VALUES(1, 'VARCHAR Value 1');
INSERT INTO test_varchar (ID, SVALUE)
VALUES(1, 'VARCHAR Value 2');
INSERT INTO test_varchar (ID, SVALUE)
VALUES(2, 'VARCHAR Value 3');
INSERT INTO test_varchar (ID, SVALUE)
VALUES(2, 'VARCHAR Value 4');
COMMIT;
This two queries working without trouble:
SELECT t1.id
, xmlcast(
xmlagg(
xmlelement(E, t1.svalue||CHR(13)||CHR(10))
ORDER BY t1.id
) AS CLOB
) AS clob_str
FROM test_varchar t1
GROUP BY t1.id
;
SELECT t1.id
, t1.cvalue
, t2.clob_str
FROM test_clob t1
INNER JOIN (SELECT id
, xmlcast(
xmlagg(
xmlelement(E, svalue||CHR(13)||CHR(10))
ORDER BY id
) AS CLOB
) AS clob_str
FROM test_varchar
GROUP BY id) t2 ON t1.id = t2.id
;
But now i need a third table and a grouping, and there the problems start:
SELECT t1.id
, t1.cvalue
, t2.clob_str
FROM test_clob t1
INNER JOIN (SELECT id
, xmlcast(
xmlagg(
xmlelement(E, svalue||CHR(13)||CHR(10))
ORDER BY id
) AS CLOB
) AS clob_str
FROM test_varchar
GROUP BY id) t2 ON t1.id = t2.id
GROUP BY t1.id
, t1.cvalue
, t2.clob_str
;
Has any one a good working solution for string aggregation with strings bigger than 4/32 k?
Thanks,
Andreas
Environment:
-------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production