Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1506
  • Last Modified:

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
0
auvillem
Asked:
auvillem
  • 2
  • 2
1 Solution
 
pennnnCommented:
No privileges? Try adding "sys."...
If that doesn't work ask the DBA to grant select on dba_role_privs to your user directly (not via a role).
By the way, are you sure that the query will always return one row? You can add some exception handling...
Hope that helps!
0
 
auvillemAuthor Commented:

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!
0
 
pennnnCommented:
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.
0
 
auvillemAuthor Commented:

It does.  Thank you!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now