Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_MONITOR - invalid identifier when used in the body of a package

VerdiJan 29 2013 — edited Jan 29 2013
NLSRTL 11.2.0.3.0 Production
Oracle Database 11g Enterprise Edition 11.2.0.3.0 64bit Production
PL/SQL 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: 11.2.0.3.0 Production

I have a database user U1 and the user has been granted the role DBA
connect u1/<password>@db

SELECT * FROM user_role_privs;

The output is:

U1	CONNECT                             NO  YES  NO
U1    DBA                                     NO  YES  NO
U1    EXECUTE_CATALOG_ROLE      NO  YES  NO
U1    EXEC_SYS_PACKAGES_ROLE   NO  YES  NO
If I execute with the same user U1 call to the package DBMS_MONITOR it works fine:
begin
  DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);
end;
/
If I try to make a call to the package DBMS_MONITOR within the body of a function/procedure/package owned by the same user U1, it fails to compile with the error:
create or replace procedure trace_proc
is
begin
  DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => FALSE, binds => TRUE);
end trace_proc;
/

The compilation fails with error: [Error] PLS-00201 (4: 3): PLS-00201: identifier 'DBMS_MONITOR' must be declared
I tried the same by prefixing the package with "SYS" but it still fails to compile.

Can you please give some clues why is it happening?

Thank you
This post has been answered by Paul Horth on Jan 29 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 26 2013
Added on Jan 29 2013
2 comments
447 views