ORA-04042: procedure, function, package, or package body does not exist

BUG:
I have encountered below ORA-04042 exception while doing pl/sql coding.

ERROR at line 1:

ORA-04042: procedure, function, package, or package body does not exist

ORA-06512: at "SYS.DBMS_JAVA_XXX", line 33

ORA-06512: at line 1

When encountered this exception I googled on resolution but couldn't found any help to what I exactly working on. As my code is bit complex I have to spent so many hours debugging the issue and finally managed to resolve the issue. Sharing the fix so that it may helps someone someday.

Fix: (explained with suedo example)

stored procedure: p1

package: a1 

package body: ab1

a1  package definition (contains definitions of stored procedures, functions etc etc)
p1 defined inside a1

ab1 contains actual procedure body of p1

I was trying to execute p1 but failing with above error. After debugging found that p1 is trying to grant privileges to oracle db object  "DBMS_JAVA" of object type package but failing due to package DBMS_JAVA does not exist in my db ( in my case it's DBMS_JAVA, it may be something else object and type in your case ). 

I debugged code by adding dbms output statements because my code creates a collection of db objects
dynamically run-time and grants privileges.

Below  is the exact line of code where above stated error occured.

grant execute on sys.DBMS_JAVA to "SYS"

Since I found out what object is failing (DBMS_JAVA). I checked the status of this object in database using below select query. Row highlighted in red below was missing which means for object_name DBMS_JAVA object_type "Package Body" exist where as object_type "Package" is missing. So my database is somewhat in corrupt state I got it fixed which solved the actual problem.

Showing the output of select query after fixing the issue for your understanding purposes. Before fixing, below query resulted in 2 rows, row highlighted in red below doesn't exist.

SQL> select object_name, object_type, status from dba_objects where object_name = 'DBMS_JAVA';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
DBMS_JAVA                      PACKAGE             VALID
DBMS_JAVA                      PACKAGE BODY        VALID
DBMS_JAVA                      SYNONYM             VALID

3 rows selected.

Hope  this helps. Please comment if you find it useful. 

 

Thanks!!!!!