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


The Eucharistic Miracles of the World

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';

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.

 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


Website Stats


Post a Comment


Oracle (580) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer