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
Oracle Database

Avatar of undefined
Last Comment
auvillem

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
pennnn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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!
pennnn

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

ASKER

It does.  Thank you!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy