I have a query which uses listagg it is as follows
SELECT listagg(path1.PATHWAY_ID,'|like||id|')within GROUP ( ORDER BY path1.PATHWAY_ID), listagg(path1.PATHWAY_NAME,'|like||name|') within GROUP ( ORDER BY path1.PATHWAY_ID) FROM CI_PUBLIC.GG_PATHWAY path1, ci_public.gg_pathway_folder_tree path2 WHERE path1.PATHWAY_ID=path2.DESCENDANT_ID AND path2.ANCESTOR_TREE_CODE='**VALUE TO BE PASSED**' AND path2.ANCESTOR_DEPTH=path2.DESCENDANT_DEPTH-1;
The number of results the query should return is 131 but I get below error
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.
But when I pass a different value to path2.ANCESTOR_TREE_CODE it retrieves results. There is nothing wrong with the query the only problem is when it has more results it gives error.
Is there anyway that I can get more results using listagg?