CODEWITHSUNDEEP
oracle-databaseoracle11g
Kunal Sharma
Kunal Sharma

Reputation: 107

ORA-01489: Oracle - ORA-01489: result of string concatenation is too long

I work on this query and get this error:

Oracle - ORA-01489: result of string concatenation is too long

Some one please help to solve this issue

SELECT LISTAGG(RCRDNUM) WITHIN GROUP (ORDER BY RCRDNUM)
FROM (SELECT (ERR.RCRDNUM || ',') AS RCRDNUM
      FROM TABLENAME ERR
      INNER JOIN (SELECT UPPER(REGEXP_SUBSTR('No value present for CNTRY_CD column for the record',
                                             '[^,]+', 1, LEVEL)) ERR_MSG
                  FROM DUAL
                  CONNECT BY REGEXP_SUBSTR('No value present for CNTRY_CD column for the record',
                                           '[^,]+', 1, LEVEL)
                      IS NOT NULL) ERRMSG_P
      ON (UPPER(ERR.ERRMSG) = ERRMSG_P.ERR_MSG
          OR 'No value present for CNTRY_CD column for the record' IS NULL))

Upvotes: 1

Views: 8561

Answers (2)

user5683823
user5683823

Reputation:

If the aggregate list is a string longer than 4000 characters, the string needs to be a CLOB, and you can't use listagg(). However, you can use xmlagg(), which does not have the 4000 character limit. The result must be a CLOB though - and it is cast as CLOB in the solution.

. Here is a proof-of-concept; I will let you adapt it to your situation.

with a (id,val) as (select 10, 'x' from dual union all select 20, 'abc' from dual)
select listagg(val, ',') within group (order by id) as l_agg,
       rtrim( xmlcast( xmlagg( xmlelement(e, val || ',') order by id) as clob), ',')
       as clob_agg
from   a
;

Output

L_AGG      CLOB_AGG
---------- ----------
x,abc      x,abc

Upvotes: 2

Codo
Codo

Reputation: 78825

In Oracle's SQL queries, strings (columns of type VARCHAR) are limited to 4000 characters. Obviously, your query creates longer strings and therefore fails. This can easily happen with LISTAGG.

Should your query really return such long strings? If not, you need to work on your query.

If you really need values longer than 4000 characters, you can try to use CLOB instead of VARCHAR by using a custom user-defined aggregation function. Tom Kyte has an example in one of his questions.

Upvotes: 0

Related Questions