-
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.
-
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.
-
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.
-
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.
-
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.
-
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
-
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