To overcome the listagg issue ORA-01489: result of string concatenation is too long Use XMLagg instead of listagg

If you using listagg sometimes we will face the error "ORA-01489: result of string concatenation is too long"

Then you can modify your query with below sample

SELECT replace(dbms_lob.substr( rtrim(XMLAgg( XMLElement(a, ',',your_data_column) order by your_data_column ).getClobVal()),3999,1),',','') alias_name from table_name


Example: SELECT replace(dbms_lob.substr( rtrim(XMLAgg( XMLElement(a, ',',info) order by info ).getClobVal()),3999,1),',','') combined_info from bab


For more query please leave your comments

Find all objects containing specific text in Oracle or all resource text

SELECT * FROM ALL_source WHERE UPPER(text) LIKE upper('%SUM(PROV_GR_AMT)%')

Easy way to change/update the oracle table column datatype without pain

ALTER TABLE es.response ADD approval_amount1 number(12,2); UPDATE es.response SET approval_amount1 = approval_amount; ALTER es.response MODIFY approval_amount (12,2); UPDATE es.response SET approval_amount=approval_amount1; ALTER TABLE es.response DROP COLUMN approval_amount1;