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 you want to create a simple Oracle stored procedure named PrintHelloWorld with the following PL/SQL:
CREATE OR REPLACE PROCEDURE PrintHelloWorld
IS
BEGIN
DBMS_OUTPUT.PUT_LINE("Hello World!");
END;
The logic of the stored procedure is simple, just print out "Hello World!", but when compile the above code in the Oracle sqldeveloper, RapidSQL or SQL*Plus, there is a compilation error:
PLS-00201: identifier 'Hello World!' must be declared
If you read the above code carefully, the string literals "Hello World" is quoted with the double quotation marks, in oracle, the right way to quote string literals is to use single quotation marks,
DBMS_OUTPUT.PUT_LINE('Hello World!');
You can to view any compilationerrors associated with the above procedure with the following query:
select * from SYS.USER_ERRORS where NAME = UPPER('PrintHelloWorld') and type = 'PROCEDURE'
For above example, you will see the following compilation errors are retrieved:
NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
PRINTHELLOWORLD PROCEDURE 1 5 25 PLS-00201: identifier 'Hello World!' must be declared ERROR 201
PRINTHELLOWORLD PROCEDURE 2 5 3 PL/SQL: Statement ignored ERROR 0
To view compilation error for other types, change the type column to the value as below:
- PROCEDURE
- FUNCTION
- PACKAGE
- PACKAGE BODY
- TRIGGER