Oracle 23c

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 table_name (dm_category) AS
SELECT 'W' FROM DUAL UNION ALL
SELECT 'X' FROM DUAL UNION ALL
SELECT 'Y' FROM DUAL UNION ALL
SELECT 'Z' FROM DUAL;
4 rows affected

CREATE TABLE preslists (othertext, tablename, functionname) AS
SELECT EMPTY_CLOB()
       || RPAD('A', 4000, 'A')
       || RPAD(',', 4000, 'B')
       || ',X,Y',
       'tablename',
       'functionname'
FROM   DUAL UNION ALL
SELECT EMPTY_CLOB()
       || RPAD('C', 4000, 'C')
       || RPAD(',', 4000, 'D')
       || ',Z,Y',
       'tablename',
       'functionname'
FROM   DUAL;
2 rows affected

SELECT *
FROM   table_name t
WHERE  EXISTS(
         SELECT 1
         FROM   Preslists p
         WHERE  p.tablename = 'tablename' 
         AND    p.functionname='functionname'
         AND    ',' || p.othertext || ',' LIKE '%,' || t.DM_CATEGORY || ',%'
       );


DM_CATEGORY


X

Y

Z


SELECT *
FROM   table_name t
WHERE  EXISTS(
         SELECT 1
         FROM   Preslists p
         WHERE  p.tablename = 'tablename' 
         AND    p.functionname='functionname'
         AND    DBMS_LOB.INSTR(
                  ',' || p.othertext || ',',
                  ',' || t.DM_CATEGORY || ','
                ) > 0
       );


DM_CATEGORY


X

Y

Z