How to fetch the list of errors for invalid objects in Oracle 10g

oracle, oracle10g, plsql

Solution

You could query [DBA/ALL/USER]_ERRORS. It describes current errors on all stored objects (views, procedures, functions, packages, and package bodies) owned by the current user.

Chose which view to query, depending on the privileges you have:

- DBA_ : All objects in the database

- ALL_ : All objects owned by the user and on which the user has been granted privileges

- USER_ : All objects owned by the user

For example,

I create a procedure with a compilation error, and I want to query the error details:

SQL> CREATE OR REPLACE PROCEDURE p
  2  BEGIN
  3  NULL
  4  END;
  5  /

Warning: Procedure created with compilation errors.

SQL>
SQL> SELECT NAME, TYPE, line, text FROM user_errors;

NAME  TYPE             LINE TEXT
----- ---------- ---------- --------------------------------------------------
P     PROCEDURE           2 PLS-00103: Encountered the symbol "BEGIN" when exp
                            ecting one of the following:

                               ( ; is with authid as cluster compress order us
                            ing compiled
                               wrapped external deterministic parallel_enable
                            pipelined
                               result_cache accessible


SQL>

Read more about it in documentation here

Problem

Explanation: I have more than 200 invalid objects in my DB, the reasons could be couple of objects only (others due to dependancy). Is there a way we can select the object name and the 'Error Reason' for it being invalid.

Original source