auvillem
asked on
Stored procedures error PLS-00201
(p_username IN dba_role_privs.grantee%TYP
p_role OUT dba_role_privs.granted_rol
v_username dba_role_privs.grantee%TYP
v_role dba_role_privs.granted_rol
BEGIN
SELECT granted_role INTO v_role
FROM dba_role_privs
WHERE grantee = p_username
AND granted_role NOT LIKE 'CON%';
p_role:=v_role;
END;
This code will not compile I get error:
LINE/COL ERROR
--------------------------
2/15 PLS-00201: identifier 'DBA_ROLE_PRIVS' must be declared
0/0 PL/SQL: Compilation unit analysis terminated
Any ideas?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The problem is that in order to use a privilege in a stored procedure (PL/SQL) it needs to be granted to you directly, not via a role. You were granted the DBA role, which allows you to query basically everything, but you can't run those queries in PL/SQL unless granted directly.
Hope that explains it.
Hope that explains it.
ASKER
It does. Thank you!
ASKER
sys. didn't work
Hmmm...the user who owned this granted DBA with Admin option.
However, I granted select on dba_role_privs to this user and it worked?
Does that make sense? Why did I have to grant select on dba_role_privs?
Anyway, Thanks!