(select concat(CASE WHEN EXISTS (SELECT 1 FROM session_roles WHERE role = 'DBA') THEN 'Y' ELSE 'N' END from DUAL))

You're trying to return Y if there exists a row with role = 'DBA' in session_roles, otherwise N.

Correct Oracle syntax (simple and clear):

SELECT CASE WHEN EXISTS (SELECT 1 FROM session_roles WHERE role = 'DBA') THEN 'Y' ELSE 'N' END AS has_dba FROM dual;

Notes:

• The CONCAT usage isn’t needed here; CASE ... END already yields a single string.
• If you want to be extra safe with whitespace/case, you could use: WHERE TRIM(UPPER(role)) = 'DBA'
• Alternative: you could do SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END FROM session_roles WHERE role = 'DBA'; which avoids EXISTS and DUAL.