Viewing Oracle Compilation Errors :

 
When creating procedures, functions, packages, triggers, or package bodies via Oracle, if there are compilation errors, Oracle will store these errors in a system table.

To view these errors, you will need to know the type of object being compiled, such as a procedure, and the name of the object. With this information, the following query can be executed.

select * from SYS.USER_ERRORS where NAME = <object_name> and type = <object_type>

For example, if attempting to create a procedure via the following command:

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







CREATE OR REPLACE PROCEDURE RAISE_SALARY(dept_no number, percentage number) AS
employee_salary EMPLOYEE.SALARY%TYPE;
CURSOR ECursor IS
    SELECT SALARY FROM EMPLOYEE where DNO = dept_no FOR UPDATE;
BEGIN
    OPEN ECursor;
    LOOP
    FETCH ECursor into employee_salary;
    EXIT WHEN ECursor%NOTFOUND;
    UPDATE EMPLOYEE SET SALARY = (employee_salary*percentage)
        WHERE CURRENT OF ECursor;
    END LOOP;
    CLOSE ECursor;
    COMMIT;
END RAISE_SALARY;
/


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

 To view any errors associated with the above procedure, you can use the following query:

select * from SYS.USER_ERRORS where NAME = 'RAISE_SALARY' and type = 'PROCEDURE';

Let's say, for example, that the table EMPLOYEE does not exist. The above query will then return an error such as the following:

PL/SQL: ORA-00942: table or view does not exist

The type column can be types such as the following:

PROCEDURE
FUNCTION
PACKAGE
PACKAGE BODY
TRIGGER