Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ora-01489: result of string concatenation is too long

Posted on 2015-01-15
4
Medium Priority
?
588 Views
Last Modified: 2015-01-15
hi i have the following query  ,but am geting this error ora-01489: result of string concatenation is too long
how can i work around this

select
c.obj_name , obj_childs, obj_parents,d.Obj_type ,d.description,d.sub_system
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  
   and   ec.obj_child  = oc.obj_name(+) 
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  
   and   ep.obj_parent = op.obj_name(+)
 )
 group by obj_name
 ) p
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name

how can i immpelent this functions in my sql
xmlagg
or this

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.

or to_clob
select department, 
       cast(collect(name) as sys.DBMSOUTPUT_LINESARRAY) 
  from emp 
 group by department;

Open in new window

0
Comment
Question by:chalie001
  • 2
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40551179
you're running into the varchar2 limit

try xml aggregation and then extract a CLOB value


select
c.obj_name , obj_childs, obj_parents,d.Obj_type ,d.description,d.sub_system
from
(select  obj_name,RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_child || ',') order by obj_child), '/x/text()').getclobval(),',')  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  
   and   ec.obj_child  = oc.obj_name(+)
 )
 group by obj_name
 ) c
,
(select obj_name, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_parent || ',') order by obj_parent), '/x/text()').getsclobval(),',')  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  
   and   ep.obj_parent = op.obj_name(+)
 )
 group by obj_name
 ) p
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name
0
 

Author Comment

by:chalie001
ID: 40551601
how can i get clob value in this line (select obj_name, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_parent || ',') order by obj_parent), '/x/text()').getsclobval(),',')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40551616
it should already be there.  

but since you're asking I assume something is wrong in the output,  what are you getting?
0
 

Author Closing Comment

by:chalie001
ID: 40551645
thanks for helping is working
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question