Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE Table1 (Col1, Col2) AS
SELECT 'First',  'Code1,Code2,Code3' FROM DUAL UNION ALL
SELECT 'Second', 'Code2' FROM DUAL;
2 rows affected

CREATE TABLE Table2 (ColA, ColB) AS
SELECT 'Code1', 'XXXX' FROM DUAL UNION ALL
SELECT 'Code2', 'ZZZZ' FROM DUAL UNION ALL
SELECT 'Code3', 'YYYY' FROM DUAL;
3 rows affected

SELECT t1.col1,
       LISTAGG(t2.colb, ',') WITHIN GROUP (
         ORDER BY INSTR(','||t1.col2||',', ','||t2.colA||',')
       ) AS value2
FROM   table1 t1
       INNER JOIN table2 t2
       ON INSTR(','||t1.col2||',', ','||t2.colA||',') > 0
GROUP BY 
       t1.col1


COL1
VALUE2


First
XXXX,ZZZZ,YYYY

Second
ZZZZ