Oracle LISTAGG Example¶

select 'found!'
from dual
where
regexp_like(

(select LISTAGG(doc_cd, ',') WITHIN GROUP(ORDER BY DOC_CLSS_NAME) as fbs_cds
        FROM    DOC_CLSS A
        CONNECT BY PRIOR A.DOC_CD = A.UP_DOC_CD
        AND             A.PJT_CD    = 'GLB_PMIS'
        AND             A.DOC_DIV   = '6'
        START WITH A.PJT_CD     = 'GLB_PMIS'
        AND             A.DOC_CD  = '0000'
        AND             A.DOC_DIV   = '6')

, replace('17782,12345', ',', '|') );

<!-- fbs names list as string separated by comma -->
SELECT LISTAGG(DOC_CLSS_NAME, ', ') WITHIN GROUP(ORDER BY DOC_CLSS_NAME)
FROM DOC_CLSS
WHERE PJT_CD = #pjt_cd# AND DOC_DIV = '6' AND DOC_CD IN (
        SELECT REGEXP_SUBSTR(A.FBS_CDS,'[^,]+', 1, level) FROM DUAL
        CONNECT BY REGEXP_SUBSTR(A.FBS_CDS, '[^,]+', 1, level) IS NOT NULL
);