Stored procedures error PLS-00201

Posted on 2003-02-21
Medium Priority
Last Modified: 2013-12-12

(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;
SELECT granted_role INTO v_role
FROM dba_role_privs
WHERE grantee = p_username
AND granted_role NOT LIKE 'CON%';

This code will not compile I get error:

2/15     PLS-00201: identifier 'DBA_ROLE_PRIVS' must be declared                
0/0      PL/SQL: Compilation unit analysis terminated                          

Any ideas?

Question by:auvillem
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 11

Accepted Solution

pennnn earned 400 total points
ID: 7994998
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!

Author Comment

ID: 7995063

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!
LVL 11

Expert Comment

ID: 7995326
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.

Author Comment

ID: 7998846

It does.  Thank you!

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question