chalie001
asked on
ora-01489: result of string concatenation is too long
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
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it should already be there.
but since you're asking I assume something is wrong in the output, what are you getting?
but since you're asking I assume something is wrong in the output, what are you getting?
ASKER
thanks for helping is working
ASKER