Resolving "PLS-00201: identifier 'DBMS_SYSTEM.XXXX' must be declared" Error

Here is a failure sample.

SQL> set serveroutput on
SQL> alter package APPS.FND_TRACE compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY APPS.FND_TRACE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
235/6    PL/SQL: Statement ignored
235/6    PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared
..

By default, DBMS_SYSTEM package is accessible only from SYS schema. Also there is no public synonym created for this package. So, the solution is to create the public synonym and grant "execute" privilege on DBMS_SYSTEM package to all database users or a specific user.

eg.,

SQL> CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;

Synonym created.

SQL> GRANT EXECUTE ON dbms_system TO APPS;

Grant succeeded.

- OR -

SQL> GRANT EXECUTE ON dbms_system TO PUBLIC;

Grant succeeded.

SQL>  alter package APPS.FND_TRACE compile body;

Package body altered.

Note that merely granting execute privilege is not enough -- creating the public synonym is as important to resolve this issue.

Comments:

I faced a similar issue but resolved it. thank u, the post was really useful.

Posted by MANISH GANGULY on February 26, 2013 at 04:20 AM PST #

Hi, I`m upgrading a db but getting this message:

ORA-06550: LINE 1, column 7:
PLS-00201: identifier `TCSDBOWNER.GET_TS`must be declared
ORA-06550: LINE 1, column 7:
PL/SQL: Statement ignored

Any idea how can i fix it?, already attempt to créate a synonym like this:

CREATE PUBLIC SYNONYM TCSDBOWNER.GET_TS FROM TCSDBOWNER.GET_TS;

but received message

ORA-00905: missing keyword.

thanks!!

Posted by Newbie on April 21, 2013 at 12:25 PM PDT #

Hi Newbie,

Your keyword is incorrect.
Use FOR instead of FROM.

Posted by Iman on July 03, 2013 at 11:39 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today