STRING CONCATENATION USING XMLAGG AND LISTAGG FUNCTIONS

 STRING CONCATENATION USING XMLAGG AND LISTAGG FUNCTIONS

XMLAGG function will return data in CLOB DATATYPE.

LISTAGG function will return data in VARCHAR2(4000 CHAR).

------------------XMLAGG QUERY---------------------------

SELECT FM_FORM_MST_B.FORMULA_ID    FORMULA_ID,
       RTRIM(XMLAGG(XMLELEMENT(E,COLNAME,',').EXTRACT('//TEXT()') ORDER BY COLNAME).GETCLOBVAL(),',') X_REASON_FOR_REVISION
FROM    APPS.FM_TEXT_TBL   FM_TEXT_TBL, APPS.FM_FORM_MST_B   FM_FORM_MST_B
WHERE   (1=1)
      AND (FM_TEXT_TBL.TEXT_CODE (+)=FM_FORM_MST_B.TEXT_CODE)
      AND (FM_TEXT_TBL.PARAGRAPH_CODE (+) = 'RSRV')
      AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

----------------QUERY TO TRIM STRING 3900 CHARACTERS-----------------------

  SELECT FM_FORM_MST_B.FORMULA_ID FORMULA_ID,
         DBMS_LOB.SUBSTR( RTRIM(XMLAGG(XMLELEMENT(E,FM_TEXT_TBL.TEXT,',').EXTRACT('//TEXT()') ORDER BY FM_TEXT_TBL.LINE_NO).GETCLOBVAL(),',')  , 3900,1) X_REASON_FOR_REVISION
--If you set the limit to 4000 characters the query might fail as sometimes it might extend beyond 4000 characters so setting it to 3900 characters.
    FROM APPS.FM_TEXT_TBL FM_TEXT_TBL, APPS.FM_FORM_MST_B FM_FORM_MST_B
    WHERE     (1 = 1)
         AND (FM_TEXT_TBL.TEXT_CODE(+) = FM_FORM_MST_B.TEXT_CODE)
         AND (FM_TEXT_TBL.PARAGRAPH_CODE(+) = 'RSRV')
         AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

------------------LISTAGG QUERY-----------------------

SELECT 
    FM_FORM_MST_B.FORMULA_ID    FORMULA_ID,
    LISTAGG (FM_TEXT_TBL.TEXT, '') WITHIN GROUP (ORDER BY FM_TEXT_TBL.LINE_NO)    X_REASON_FOR_REVISION
FROM    APPS.FM_TEXT_TBL   FM_TEXT_TBL, APPS.FM_FORM_MST_B   FM_FORM_MST_B
WHERE    (1=1)
 AND (FM_TEXT_TBL.TEXT_CODE (+)=FM_FORM_MST_B.TEXT_CODE)
AND (FM_TEXT_TBL.PARAGRAPH_CODE (+) = 'RSRV')
AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

-------------------------------------------------------