Link to home
Start Free TrialLog in
Avatar of auvillem
auvillem

asked on

Stored procedures error PLS-00201


(p_username IN dba_role_privs.grantee%TYPE,
p_role OUT dba_role_privs.granted_role%TYPE) IS
v_username dba_role_privs.grantee%TYPE;
v_role dba_role_privs.granted_role%TYPE;
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
Avatar of pennnn
pennnn

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of auvillem
auvillem

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!
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.

It does.  Thank you!