LISTAGG gives “java.sql.SQLException: ORA-01489: result of string concatenation is too long” due to limitation for VARCHAR2.

select
  CONCAT(CONCAT(t2.REF_NUM_TYPE,':'), (listagg(t2.REF_NUM,',') within group (order by t2.REF_NUM asc))) as REF_NUMS_BK
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '123132123123'
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

Therefore, XMLELEMENT can be workaround.

select
  CONCAT(CONCAT(t2.REF_NUM_TYPE,':'),(RTRIM(XMLAGG(XMLELEMENT(e,t2.REF_NUM,',').EXTRACT('//text()')).GetClobVal(),','))) AS REF_NUMS
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '123132123123'
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

It can be trim as well.

select
 CONCAT(
  CONCAT(t2.REF_NUM_TYPE,':'),
   SUBSTR(
    (RTRIM(XMLAGG(XMLELEMENT(e,t2.REF_NUM,',').EXTRACT('//text()')).GetClobVal(),',')
   ),1,1000
  )
 ) AS REF_NUMS
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '30277174594' -- 202501494 202498201 30277174594(too long)
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

Example for XMLAGG with order by

with ref_base as (
    select 
     max(a.PIPELINE_TX_ID) as ppl
    ,a.REF_NUM_TYPE
    ,a.REF_NUM
    from REFERENCE_NUMBERS a where 1=1
    and a.PIPELINE_TX_ID = '30277174594'
    group by a.REF_NUM_TYPE, a.REF_NUM
    order by a.REF_NUM_TYPE, a.REF_NUM
)
select
(
    select RTRIM((XMLAGG(XMLELEMENT(e, b.REF_NUM,',') order by b.REF_NUM).EXTRACT('//text()')).GetClobVal(),',') AS REF_NUMS
    from   ref_base b where  b.REF_NUM_TYPE = 'ED1'
    group  by b.REF_NUM_TYPE
) as REF_NUMS_ED1
from dual

Example for LISTAGG with limited elements

select
  LISTAGG(
   case
      when rownum <= 300 
      then (
        case
           when rownum = 300
           then rn.ref_num || ' MORE....'
           else rn.ref_num
        end
      )
      else null
   end,
   ','
  ) within group (order by rn.ref_num)
  as ref_nums
from reference_numbers rn
where rn.pipeline_tx_id = '123456'