Oracle Version = Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Hi Gurus
I have got stuck on one of the query, I use listagg function in my query but after getting error result of string concatenation is too long oracle, I changed listagg function to
RTRIM(XMLAGG(XMLELEMENT(e,poi.OBJECT_ID||',').EXTRACT('//text()')),',') ,but after updating code when I executed my query on all set of data then I'm getting another new message, see below:
Error message
ORA19011:Character string buffer is too small
partial part of my query
Select * from
(
select distinct (substr(poi.OBJECT_ID,instr(poi.OBJECT_ID,'_')+1)) as TX_ID
from PARTICIPANT_OBJECT_ID poi
where
.EVENT_INSTANCE_ID = ei.EVENT_INSTANCE_ID
--and (poi.OBJECT_TYPE_CODE_ROLE = '24')
and (poi.OBJECT_ID_TYPE_CODE = '73')
) AS TX_ID
,ei.EVENT_INSTANCE_ID
,(
select
-- LISTAGG(poi.OBJECT_ID, '; ') WITHIN GROUP (ORDER BY poi.OBJECT_ID desc) --previous coding
RTRIM(XMLAGG(XMLELEMENT(e,poi.OBJECT_ID||',').EXTRACT('//text()')),',') --new code
from PARTICIPANT_OBJECT_ID poi
where poi.EVENT_INSTANCE_ID = ei.EVENT_INSTANCE_ID
and (poi.OBJECT_TYPE_CODE_ROLE = '1')
and (poi.OBJECT_ID_TYPE_CODE = '1')
) AS PATIENT
--some joining
In addition my both column type is varchar2(200)
Please guide. Thank you in advance