How to Use PL/SQL Compiler Warnings

A PL/SQL unit can compile without errors and still have problems: code that can never run, missing clauses with surprising defaults, or constructs that are slow. Compiler warnings report these. They are off by default; turn them on with the PLSQL_WARNINGS parameter and read them like errors.

Code for This Guide

The main examples are in the examples/advanced-plsql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

alter session set plsql_warnings = 'action:category' [, 'action:category' ...];

-- action:   enable | disable | error
-- category: all | severe | performance | informational | warning number

alter procedure name compile plsql_warnings = '...';

Warnings have three categories: SEVERE, PERFORMANCE, and INFORMATIONAL. ERROR turns a warning into a compilation error.

See the Warnings

The procedure compiles successfully, but has no AUTHID clause and an IF branch that can never run.

Example:

alter session set plsql_warnings = 'enable:all';

create or replace procedure warn_me (p_code varchar2) is
  v_unused number;
begin
  if 1 = 2 then
    dbms_output.put_line('never');
  end if;
end;
/
select line, attribute, message_number, substr(text, 1, 70) as text
from   user_errors
where  name = 'WARN_ME'
order  by sequence;

Output:

Session altered.

Procedure WARN_ME compiled

Errors for PROCEDURE WARN_ME:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit WARN_ME omitted optional AUTHID clause; default value
         DEFINER used
5/5      PLW-06002: Unreachable code

   LINE ATTRIBUTE       MESSAGE_NUMBER TEXT
_______ ____________ _________________ _________________________________________________________________________
      1 WARNING                   5018 PLW-05018: unit WARN_ME omitted optional AUTHID clause; default value
      5 WARNING                   6002 PLW-06002: Unreachable code

The procedure compiles, with two warnings: PLW-05018 for the missing AUTHID clause and PLW-06002 for the unreachable line. USER_ERRORS holds them with ATTRIBUTE set to WARNING, next to real errors. The example drops the procedure afterward.

Turn a Warning into an Error

For warnings your team never accepts, make them errors so the unit cannot be compiled valid.

Example:

-- treat unreachable code as an error, not just a warning
alter session set plsql_warnings = 'enable:all', 'error:06002';

create or replace procedure warn_me is
begin
  return;
  dbms_output.put_line('never');
end;
/
select status from user_objects where object_name = 'WARN_ME';

Output:

Session altered.

Procedure WARN_ME compiled

Errors for PROCEDURE WARN_ME:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit WARN_ME omitted optional AUTHID clause; default value
         DEFINER used
4/3      PLS-06002: Unreachable code

STATUS
__________
INVALID

Code after RETURN is unreachable, and with error:06002 the warning becomes PLS-06002, so the procedure is left INVALID. The example drops it afterward.

Things to Know

• Warnings are stored with the unit; recompiling with REUSE SETTINGS keeps its warning setting.
• Turn on all warnings in development, and fix or deliberately accept each one.
• USER_PLSQL_OBJECT_SETTINGS shows the PLSQL_WARNINGS setting each unit was compiled with.

Related Guides

• How to Use AUTHID in PL/SQL
• How to Use Conditional Compilation in PL/SQL

Conclusion

PL/SQL compiler warnings point out code that compiles but is probably wrong or slow. Enable them with PLSQL_WARNINGS, read them in USER_ERRORS, and turn the ones you never accept into errors.