(select concat((select granted_role from user_role_privs where granted_role = 'DBA') from DUAL))

That query as written won’t run in Oracle. It has syntax issues and misuse of CONCAT and DUAL. Here’s what’s wrong and how you can fix it.

What’s wrong

• CONCAT in Oracle requires two arguments (CONCAT(a, b)). Your expression provides only one argument or places FROM DUAL in a way that’s not valid.
• The structure "(select concat(... from DUAL))" is not valid syntax; a subquery used in SELECT must have a valid SELECT list and FROM clause.
• If you just want to know whether the current user has the DBA role, you don’t need CONCAT or DUAL.

Correct approaches

1. Just check if the DBA role exists for the current user

• This will return a row with DBA if present, or no rows if not. SELECT granted_role FROM user_role_privs WHERE granted_role = 'DBA';

2. Return a simple YES/NO using a scalar subquery

• This returns 'DBA' when the role exists, or 'NONE' if not. SELECT NVL((SELECT granted_role FROM user_role_privs WHERE granted_role = 'DBA'), 'NONE') AS role_check FROM dual;

3. Use concatenation correctly with a scalar subquery

• If you want to display a message like "Role: DBA" when present: SELECT 'Role: ' || (SELECT granted_role FROM user_role_privs WHERE granted_role = 'DBA') FROM dual;

4. Alternative with EXISTS (no risk of multiple rows)

• Returns a row only if the role exists. SELECT 'DBA' AS has_dba FROM dual WHERE EXISTS (SELECT 1 FROM user_role_privs WHERE granted_role = 'DBA');

Notes

• In Oracle, USER_ROLE_PRIVS lists roles granted to the current user. If you’re checking for a privilege instead, use USER_SYS_PRIVS.
• If the subquery in option 2 could return more than one row, you’d get ORA-01427. The patterns above avoid that by ensuring a single scalar result or using EXISTS.

If you share what you’re aiming to test (existence, exact value, or a formatted string), I can tailor the exact query.