The CHR() function is used to insert control characters into a string.
1) CHR(10) is used to insert line breaks,
2) CHR(9) is for tabs
3) CHR(13) is for carriage returns.
SELECT
'Notify Party '||rownum||' : '||GET_PARTY_NAME_ADD( 'N',A.SETUP_PARTY_COM_ID) || chr(10) || GET_PARTY_NAME_ADD( 'A',A.SETUP_PARTY_COM_ID) NOTIFY
FROM AIREXPORT_NOTIFY A, HAWB_MST B
where A.AIREXPORT_ID = B.BOOKING_ID
AND B.ID= 1020
select listagg (STF, chr(10)) within group (order by id) as STF
from (
select null id, m.GOODS_DESCRIPTION STF
from
hawb_mst d, airexport_mst m, SETUP_ISSUING_AGENT a, SETUP_PARTY_COM B, SETUP_COMPANY H
where d.BOOKING_ID=M.ID
and a.id(+)=D.ISSU_AGENT_ID
and B.ID=M.SETUP_PARTY_COM_ID
AND h.ID=d.COM_ID
and d.id = 1041
UNION ALL
select id, STF
from (
SELECT a.id, get_name_lookup ('STAKEHOLDER REFERENCE TYPE',A.TYPE)||'-'||REF_NUMBER ||'-'||TO_CHAR (REF_DATE,'DD-MON-RR') STF
from
hawb_mst d, airexport_mst m, airexport_ST_REF a
where d.BOOKING_ID=M.ID
and a.airexport_ID(+)=D.BOOKING_ID
and d.id = 1041
order by a.id))
==================================================
select listagg(NOTIFY, chr(10)) within group (order by ID) as NOTIFY
from (
SELECT 1 ID , 'Notify Party '||rownum||' : '||GET_PARTY_NAME_ADD( 'N',A.SETUP_PARTY_COM_ID) NOTIFY
FROM AIREXPORT_NOTIFY A, HAWB_MST B
where A.AIREXPORT_ID = B.BOOKING_ID
AND B.ID= 1020
UNION ALL
SELECT NULL ID, GET_PARTY_NAME_ADD( 'A',A.SETUP_PARTY_COM_ID) NOTIFY
FROM AIREXPORT_NOTIFY A, HAWB_MST B
where A.AIREXPORT_ID = B.BOOKING_ID
AND B.ID= 1020)
==================================================
SELECT
'Notify Party '||rownum||' : '||GET_PARTY_NAME_ADD( 'N',A.SETUP_PARTY_COM_ID) || chr(10) || GET_PARTY_NAME_ADD( 'A',A.SETUP_PARTY_COM_ID) NOTIFY
FROM AIREXPORT_NOTIFY A, HAWB_MST B
where A.AIREXPORT_ID = B.BOOKING_ID
AND B.ID= 1020