Viewing Oracle Compile 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:
When using RazorSQL, any compilation errors will automatically be retrieved by RazorSQL and displayed in a new window after executing a create or create or replace command that contains compile errors.