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:
PROCEDURE
FUNCTION
PACKAGE
PACKAGE BODY
TRIGGER
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.
