by BehindJava

Listagg function overflow error ORA-01489 in Java

Home » java » Listagg function overflow error ORA-01489 in Java

How to resolve Listagg function overflow and ORA-01489: result of string concatenation is too long error?

In this we use listagg function that transforms values from a group of rows into a list of values that are delimited by a configurable separator. Listagg is typically used to denormalize rows into a string of comma-separated values (CSV) or other comparable formats suitable for human reading. I know the problem is that the query being run to aggregate the data is returning so many rows that the string concatenation that listagg is doing violates the 4000 char limit. However, for my use case it’s perfectly acceptable to truncate to the first 4000 characters and fails after 4000. So we have BLOB and CLOB that holds upto 2 GB.

Sample Code Snippet:

//sample query with the replacement of XMLAGG instead of LISTAGG
	RTRIM( (XMLAGG (XMLELEMENT (e, usr.firstname || ' ' || usr.lastname, ',') ORDER BY firstname)).EXTRACT ('//text()').getClobVal(),',') data_team, ");

		//Java code to read Clob data and convert into String
		Clob dataClob = (Clob) Detail[5];		Reader dataReader = dataClob.getCharacterStream();
	    StringBuffer dataBuffer = new StringBuffer();
		int ch;
      	while ((ch =!=-1) {
      		dataBuffer.append(""+(char)ch); }
        //If String having only comma then need not to append         if(dataBuffer.lastIndexOf(",") != dataBuffer.length()-2) {