How to View Oracle Compile Errors for Procedures, Triggers, Packages, etc. Options

codeling Posts: 1089 Points: 4569
Posted: Monday, January 18, 2016 9:41:54 AM

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
 

codeling Posts: 1089 Points: 4569
Posted: Monday, January 18, 2016 9:51:38 AM

After a store procedure is compiled, its name will in upper case in the Oracle dictionary table, so the UPPER function is used in the above query.

Users browsing this topic
Guest