Solved

ora-01489: result of string concatenation is too long

Posted on 2015-01-15
4
422 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 73

Accepted Solution

by:
sdstuber earned 500 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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now