How to get compilation errors in oracle?

We DBAs used to compile invalid objects to make them valid. You may compile the whole database using utlrp.sql, or all invalid objects in a schema or even using manual scripts.

Read : Manual script to compile invalid objects in Oracle

While compiling, if there are any errors in the code, it will end up with a warning as given below

SQL> alter procedure empsal compile ;

Warning: Procedure altered with compilation errors.

To get the actual errors with the object (here it is a procedure in the example) you have to run SHOW  ERRORS after compiling the object.

SQL> show errors

Errors for PROCEDURE EMPSAL:

LINE/COL ERROR

-------- -------------------------------------------------

3/1      PL/SQL: SQL Statement ignored

3/20     PLS-00201: identifier 'V_SAL' must be declared

3/26     PL/SQL: ORA-00904: : invalid identifier

4/1      PL/SQL: Statement ignored

4/23     PLS-00201: identifier 'V_SAL' must be declared

Here you can see the actual error with the procedure. (or function, package, package body etc)

If you have compiled many objects together with a script, and a few has the warning, then show errors will give you the errors with the latest compiled one only. In such cases you have to give the object type and object name to get errors related to a specific object. The complete Syntax of SHOW ERRORS is given below.

 SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |   PACKAGE BODY | TRIGGER | VIEW   | TYPE | TYPE BODY | DIMENSION   | JAVA SOURCE | JAVA CLASS } [schema.]name]

The same for the given example is given below

SQL> show errors procedure empsal

Errors for PROCEDURE EMPSAL:

LINE/COL ERROR

-------- -------------------------------------------------

3/1      PL/SQL: SQL Statement ignored

3/20     PLS-00201: identifier 'V_SAL' must be declared

3/26     PL/SQL: ORA-00904: : invalid identifier

4/1      PL/SQL: Statement ignored

4/23     PLS-00201: identifier 'V_SAL' must be declared

SHOW ERRORS can be abbreviated to SHO ERR also.

SQL> sho err

Errors for PROCEDURE EMPSAL:

LINE/COL ERROR

-------- ---------------------------------------------------

3/1      PL/SQL: SQL Statement ignored

3/20     PLS-00201: identifier 'V_SAL' must be declared

3/26     PL/SQL: ORA-00904: : invalid identifier

4/1      PL/SQL: Statement ignored

4/23     PLS-00201: identifier 'V_SAL' must be declared

You can use SHOW ERRORS command if you are using SQLPLUS,SQLDeveleoper, TOAD or any such interfaces.