Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ora-01489: result of string concatenation is too long

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What's wrong with this T-SQL Foreign Key? 7 51
Currency in SQL? 2 30
Checking for column width 8 28
why truncate is faster than delete in oracle ? 4 20
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

808 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