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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google