Show Error Returns with No errors: How to find out PLSQL Compilation Errors?

Error Description

The PLSQL object created with compilation error. But SQLPLUS show error returns ‘No errors.’

SQL> create force view VW_Force_Invalid

    as select * from bonus;

Warning: View created with compilation errors.

SQL> show error

No errors.

Solution Description

Some cases ‘show error’ command will not display the errors during the PLSQL compilation. You can use USER_ERRORS, DBA_ERRORS view can be used to find out the corresponding compilation errors.

Here is the SQL for finding out the error.

SQL> select name, text from user_errors where name='VW_FORCE_INVALID';

NAME

------------------------------
TEXT
--------------------------------------------
VW_FORCE_INVALID
ORA-00942: table or view does not exist

The columns NAME & TEXT columns are important from USER_ERRORS.

Name is your object name which you are going to compile and TEXT is the error message.

SQL> desc USER_ERRORS

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 SEQUENCE                                  NOT NULL NUMBER
 LINE                                      NOT NULL NUMBER
 POSITION                                  NOT NULL NUMBER
 TEXT                                      NOT NULL VARCHAR2(4000)
 ATTRIBUTE                                          VARCHAR2(9)
 MESSAGE_NUMBER                                     NUMBER