Oracle PL/SQL: SHOW ERRORS and dba_errors Explained

calendar Mar 16, 2024 / · 9 min read · oracle plsql dba_errors user_errors all_errors all_plsql_object_settings dbms_warning  ·
Share on: linkedin copy

Oracle PL/SQL: SHOW ERRORS and dba_errors Explained

Purpose

DBA_ERRORS is the data dictionary view that holds the current compilation errors for every stored object in the database. It is the first place to look when a procedure, function, package or trigger is created but does not work. The view describes the current errors on all stored objects in the database, so one query can show every broken object in every schema.

The same errors are also available through the SHOW ERRORS command in SQL*Plus. This command reads the errors of one stored object and prints them with line and column numbers. Use SHOW ERRORS right after you create a unit. Use DBA_ERRORS when you need to look across schemas, sort the problems, or find them later.

This check also covers compiler warnings. A warning is a condition that is not serious enough to stop compilation. Warnings are off by default, so you must enable them before the compiler reports them. The sections below show how to read errors, how to enable warnings, and how to tell the two apart. Recompiling invalid objects has its own page: read the page on recompiling invalid objects and the page on how to identify and fix invalid objects.

Code

 1-- 1. Show the errors of the most recently created or altered stored object
 2SHOW ERRORS;
 3
 4-- 2. Show the errors of one named object
 5SHOW ERRORS PROCEDURE schema.name
 6
 7-- 3. List the compilation errors for one procedure
 8SELECT * FROM dba_errors
 9WHERE NAME = '<procedure_name>'
10AND TYPE = 'PROCEDURE'
11AND ATTRIBUTE = 'ERROR';
12
13-- 4. List the warnings in the current schema
14SELECT * FROM dba_errors
15WHERE OWNER = USER
16AND ATTRIBUTE = 'WARNING';
17
18-- 5. Count errors and warnings per owner and object type
19SELECT owner, type, attribute, COUNT(*) AS entries
20FROM dba_errors
21GROUP BY owner, type, attribute
22ORDER BY owner, type, attribute;
23
24-- 6. Read the messages of one object in order
25SELECT sequence, line, position, attribute, message_number, text
26FROM dba_errors
27WHERE owner = '<owner>'
28AND name = '<object_name>'
29ORDER BY sequence;
30
31-- 7. Enable all compiler warnings for the session, then recompile
32ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
33
34-- 8. Check the warning setting stored with your units
35SELECT * FROM all_plsql_object_settings WHERE owner = USER;

Code Breakdown

SHOW ERRORS

  • SHOW ERRORS with no arguments. When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure. This is the fast check after a CREATE OR REPLACE.
  • SHOW ERRORS <type> [schema.]name. You can name the type and the object. The type can be a function, procedure, package, package body, trigger, view, type, type body, dimension, or Java class, among others. If you omit the schema, SHOW ERRORS assumes the object is in your current schema.
  • Output columns. The output shows the line and column number of the error (LINE/COL) and the error itself (ERROR). The default widths are 8 and 65. You can change them with the COLUMN command, which helps when a long message is cut off.
  • A clean result. For a procedure with no compilation errors, the output reads NO ERRORS.

The three error views

  • DBA_ERRORS describes the current errors on all stored objects in the database.
  • ALL_ERRORS describes the current errors on the stored objects accessible to the current user.
  • USER_ERRORS describes the current errors on the stored objects owned by the current user. This view does not display the OWNER column.

All three views share the same columns, except for OWNER in USER_ERRORS. The columns are:

  • OWNER - the owner of the object.
  • NAME - the name of the object.
  • TYPE - the type of the object, for example PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY or VIEW.
  • SEQUENCE - a sequence number, used for ordering.
  • LINE - the line number at which the error occurred.
  • POSITION - the position in the line at which the error occurred.
  • TEXT - the text of the error.
  • ATTRIBUTE - tells you if the entry is an error (ERROR) or a warning (WARNING).
  • MESSAGE_NUMBER - the numeric error number, without any prefix.

The queries

  • Query 3 filters on ATTRIBUTE = 'ERROR'. The ATTRIBUTE column is the correct way to separate errors from warnings. Do not search the TEXT column for the words "error" or "warning".
  • Query 4 uses OWNER = USER to limit the list to the schema you are logged in as. Because DBA_ERRORS covers all schemas, the OWNER filter is how you narrow it down.
  • Query 5 gives a count per owner, type and attribute. Run it first when you do not know where the problems are.
  • Query 6 sorts one object's entries by SEQUENCE, so they read in the order that the database stores them.
  • Query 7 and query 8 deal with warnings and are explained below.

Key Points

  • DBA_ERRORS is not limited to your session. It describes the current errors on all stored objects in the database.
  • SHOW ERRORS with no arguments looks at one object only: the most recently created or altered stored procedure. To check any other object, name it.
  • ATTRIBUTE separates an error from a warning. Both appear in the same views.
  • The message code of a PL/SQL warning has the form PLW-nnnnn.
  • To see warnings and errors generated during compilation, either query the static data dictionary view *_ERRORS or, in the SQL*Plus environment, use the command SHOW ERRORS.
  • The PLSQL_WARNINGS parameter has the default value 'DISABLE:ALL'. A fresh session reports no warnings until you change it.

Insights and Best Practices

Check right after every create

When you create a stored procedure, a message is displayed if it has any compilation errors. To see the errors, you use SHOW ERRORS. Make this the next command after every CREATE OR REPLACE that you run by hand. Do it before you test the code, because a unit with errors cannot run as you planned.

When you load many scripts in one run, SHOW ERRORS with no arguments only shows the last object. Name each object, or query USER_ERRORS after the whole run finishes.

Use the views for a schema-wide view

After a release or a schema import, count the errors with query 5. Then read the objects with the highest counts with query 6. Sort by SEQUENCE and read the first entry for an object before the later ones. Start with the first message and fix it first. Compile again and read the list once more.

Use USER_ERRORS when you work in your own schema and do not have access to DBA_ERRORS. Use ALL_ERRORS when you need the objects that you can access in other schemas. The columns are the same, so the same query works with a changed view name.

Turn on warnings during development

Warnings are not shown unless you ask for them. The reference for PLSQL_WARNINGS says it enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors. The default value is 'DISABLE:ALL'. You can change it with ALTER SESSION or ALTER SYSTEM.

The warning categories are:

  • SEVERE - the condition might cause unexpected action or wrong results.
  • PERFORMANCE - the condition might cause performance problems.
  • INFORMATIONAL - the condition does not affect performance or correctness, but you might want to change it to make the code more maintainable.

For the session, enable all warnings, which is highly recommended during development:

1ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

To enable only one category, name it, for example 'ENABLE:PERFORMANCE'. Warnings that you recompile under this setting then show up in USER_ERRORS with ATTRIBUTE = 'WARNING', and in SHOW ERRORS.

Make serious warnings stop the build

You can treat specific warnings as errors, so that those conditions must be corrected before you can compile the PL/SQL unit. This example enables SEVERE warnings, disables PERFORMANCE warnings, and treats PLW-06002 warnings as errors:

1ALTER SESSION
2  SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE', 'ERROR:06002';

Keep this setting in the script that builds your release. Then a warning that you consider serious stops the build instead of reaching production.

Check what a unit was compiled with

To display the current value of PLSQL_WARNINGS, query the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS. This is query 8. Use it when a unit gave no warnings and you need to know if warnings were enabled when it was compiled.

If you write PL/SQL units in a development environment that compiles them, such as SQL*Plus, you can also display and set the value of PLSQL_WARNINGS by calling subprograms in the DBMS_WARNING package:

1CALL DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL', 'SESSION');
2SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL;

Warnings can find hidden faults

A warning only points at possible problem code. One compiler warning, added in Oracle 11g, identifies WHEN OTHERS exception handlers. Such exception handlers can often hide code failures that result in hard to identify bugs. When SQL*Plus creates a procedure that has warnings, it prints SP2-0804: Procedure created with compilation warnings. That message is your cue to run SHOW ERRORS.

When to Run This

  • Right after you create or replace a procedure, function, package or trigger.
  • After a deployment script, to list every object that has errors.
  • After a schema import or an upgrade, to find objects that did not compile.
  • When a developer says that code "does nothing" and you suspect that it never compiled.
  • Before a release, with all warnings enabled, to find code that is legal but risky.
  • When you audit an older schema to see which units give SEVERE warnings.

Troubleshooting Common Issues

SHOW ERRORS prints NO ERRORS. but the object still fails. The command reports compilation errors only. A run-time failure is a different problem. Check that you looked at the right object. With no arguments, the command shows the most recently created or altered stored procedure, so name your object.

SHOW ERRORS shows nothing for a named object. Check the schema. If you omit the schema, SHOW ERRORS assumes the object is in your current schema. Add schema. in front of the name.

The message is cut off. LINE/COL and ERROR have default widths of 8 and 65. Change them with the COLUMN command. Or read the full TEXT column from USER_ERRORS, which holds up to 4000 characters.

No warnings appear. The default of PLSQL_WARNINGS is 'DISABLE:ALL'. Run ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; and compile the unit again. Check ALL_PLSQL_OBJECT_SETTINGS to see which setting the unit has.

DBA_ERRORS returns too many rows. The view covers all schemas. Add an OWNER filter, and an ATTRIBUTE filter if you only want errors.

Errors stay after you fixed the code. These views describe the current errors. Compile the unit again, then query again to see the new state.

References

Posts in this series