How to SHOW ERRORS in Oracle

How do I use SHOW ERRORS to debug Oracle PL/SQL compilation failures?

SHOW ERRORS displays the most recent compilation errors for PL/SQL objects, views, or Java sources in an Oracle session.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use SHOW ERRORS?

Diagnose compile-time problems fast. SHOW ERRORS instantly lists line numbers, positions, and messages for the last DDL you ran, saving you from querying USER_ERRORS manually.

What objects can it inspect?

SHOW ERRORS works with functions, procedures, packages, package bodies, views, triggers, and Java classes, sources, or resources created in the current session.

How does the basic syntax look?

The command is entered in SQL*Plus or any compatible CLI: SHOW ERRORS.Optional clauses let you narrow output to a specific object type and name.

How to filter errors for one object?

Add TYPE and NAME clauses: SHOW ERRORS TYPE PROCEDURE NAME PROC_GET_ORDERS. This prints only that procedure’s errors, even if several objects failed.

Can I clear the error list?

Yes. Re-compile the object, or run ALTER ... COMPILE.Once compilation succeeds, SHOW ERRORS returns “No errors”.

Example: debugging an ecommerce procedure

After creating PROC_CREATE_ORDER you run SHOW ERRORS and see “PL/SQL: ORA-00942: table or view does not exist” at line 15. You correct the table name, re-compile, and rerun SHOW ERRORS to confirm the fix.

Best practice: store output

Redirect SHOW ERRORS to a file (SPOOL) so teammates can review exact compiler feedback and speed up code reviews.

Why How to SHOW ERRORS in Oracle is important

How to SHOW ERRORS in Oracle Example Usage


--Create a buggy procedure that inserts a new order
a CREATE OR REPLACE PROCEDURE PROC_CREATE_ORDER(p_customer_id NUMBER,
                                              p_total NUMBER) AS
BEGIN
    INSERT INTO Orders(customer_id, order_date, total_amount)
    VALUES (p_customer_id, SYSDATE, p_total);
    --Intentional typo to trigger error
    INSERT INTO OrderItemz(order_id, product_id, quantity) VALUES (999, 1, 1);
END;
/
--Compile errors exist; now list them
SHOW ERRORS TYPE PROCEDURE NAME PROC_CREATE_ORDER;

How to SHOW ERRORS in Oracle Syntax


SHOW ERRORS [TYPE {FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | VIEW | TRIGGER | JAVA CLASS | JAVA SOURCE | JAVA RESOURCE}] [NAME object_name]

--Ecommerce example
--See errors for a newly created order procedure
SHOW ERRORS TYPE PROCEDURE NAME PROC_CREATE_ORDER;

Common Mistakes

Running SHOW ERRORS in GUI clients that don’t support SQL*Plus commands. Solution: open SQL*Plus, SQLcl, or enable SQL*Plus command passthrough in your IDE.
Forgetting the TYPE clause when multiple objects failed, leading to unrelated error lists. Always add TYPE and NAME to isolate the object you care about.