8 Replies Latest reply on Jan 16, 2015 6:43 AM by Tony007

ora-01489: result of string concatenation is too long

Tony007 Jan 16, 2015 6:41 AM

hi i have the following query which is geting this error ora-01489: result of string concatenation is too long

my databse is 11gr2

1. Re: ora-01489: result of string concatenation is too long

JackK Jan 15, 2015 9:24 AM (in response to Tony007)

The result of LISTAGG function must not exceed 4000 characters, I suppose.
Like Show 0 Likes(0)

Actions
2. Re: ora-01489: result of string concatenation is too long

BluShadow Jan 15, 2015 9:25 AM (in response to JackK)

JackK wrote:

The result of LISTAGG function may not exceed 4000 characters, I suppose.

Correction, 4000 bytes, which may be less characters if using a multi-byte character set on the database.
Like Show 0 Likes(0)

Actions

3. Re: Re: ora-01489: result of string concatenation is too long

Tony007 Jan 15, 2015 10:14 AM (in response to JackK)

yes i know how can i work around this

how can i use one of this functions in my sql

xmlagg,
select owner, type_name, coll_type, elem_type_name, upper_bound, length 
from all_coll_types
where elem_type_name = 'VARCHAR2';
e.g. on my db, I can use sys.DBMSOUTPUT_LINESARRAY which is a varray of considerable size.
select department, 
      cast(collect(name) as sys.DBMSOUTPUT_LINESARRAY) 
  from emp 
group by department;

or to_clob

4. Re: Re: ora-01489: result of string concatenation is too long

BluShadow Jan 15, 2015 10:24 AM (in response to Tony007)

Why do you even want to create such a large concatenated string?

If you must, you can use a CLOB and build it up in that, but in most applications where there's a need to collect large volumes of data together, it's better to use a structured datatype, like XMLTYPE and built up an XML document from the aggregation of data.
Like Show 0 Likes(0)

Actions
5. Re: ora-01489: result of string concatenation is too long

James Su Jan 15, 2015 2:58 PM (in response to Tony007)

wmsys.wm_concat in 11g returns a CLOB. But this function is not documented.
Like Show 0 Likes(0)

Actions
6. Re: ora-01489: result of string concatenation is too long

BluShadow Jan 15, 2015 3:12 PM (in response to James Su)

Not only is it not documented, but Oracle do not recommend you use it, neither does Tom Kyte....

Re: DISTINCT not working with wmsys.wm_concat

So, recommending it is somewhat pointless.
Like Show 0 Likes(0)

Actions
7. Re: ora-01489: result of string concatenation is too long

James Su Jan 15, 2015 3:39 PM (in response to BluShadow)

True. Don't use wmsys.wm_concat.
Go with the stragg by Tom, the benefit is it supports distinct.
I'm surprised to see Tom in this forum
Like Show 0 Likes(0)

Actions
8. Re: Re: Re: ora-01489: result of string concatenation is too long

Tony007 Jan 16, 2015 6:43 AM (in response to BluShadow)

i try this

select obj_name,RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_child || ',') order by obj_child), '/x/text()').getclobval(),',') obj_childs

select obj_name, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_parent || ',') order by obj_parent), '/x/text()').getsclobval(),',') obj_parents
Like Show 0 Likes(0)

Actions

Go to original post