(select concat((select granted_role from user_role_privs where granted_role = 'DBA') from DUAL))
That looks like an Oracle SQL snippet intended to check whether the current user has the DBA role by querying the data dictionary view USER_ROLE_PRIVS. However, as written the syntax isn’t valid Oracle SQL.
What the pieces do (in a clean context):
- USER_ROLE_PRIVS holds the roles granted to the current user. The column GRANTED_ROLE would contain 'DBA' if the user has the DBA role.
- A scalar subquery like (SELECT granted_role FROM user_role_privs WHERE granted_role = 'DBA') would yield 'DBA' if the user has the role, or NULL if not.
- The outer CONCAT(...) in your snippet is problematic because CONCAT in Oracle takes exactly two arguments. In addition, the inner subquery returns a single value, so you still need a second argument for CONCAT.
Practical, safe ways to check for DBA (in Oracle):
Simple existence check (returns a row only if DBA is granted): SELECT CASE WHEN EXISTS (SELECT 1 FROM user_role_privs WHERE granted_role = 'DBA') THEN 'YES' ELSE 'NO' END AS has_dba FROM dual;
Return the role if present (you’ll see nothing if not granted): SELECT granted_role FROM user_role_privs WHERE granted_role = 'DBA';
Count-based boolean: SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END AS has_dba FROM user_role_privs WHERE granted_role = 'DBA';
Scalar subquery form (single value, NULL if not granted): SELECT NVL((SELECT granted_role FROM user_role_privs WHERE granted_role = 'DBA'), 'NONE') AS dba_status FROM dual;
Notes:
- In Oracle, an empty string '' is treated as NULL, so concatenating with '' won’t help produce a usable string; use a different approach (like CASE/EXISTS or NVL) to get a clear YES/NO result.
- If you fix the syntax to include a second argument for CONCAT (e.g., CONCAT((SELECT granted_role FROM user_role_privs WHERE granted_role = 'DBA'), 'X')), you’ll just get 'DBAX' when DBA is granted and NULL when not, which is usually not what you want.
If you’re prepping for an exam, the key idea is: you can check for DBA by querying USER_ROLE_PRIVS and seeing whether 'DBA' appears, usually with EXISTS, COUNT, or a simple SELECT with a WHERE clause.
