Compiler Warnings & Optimization

The PL/SQL compiler can do far more than reject invalid code. With compiler warnings enabled it flags risky-but-legal constructs — unreachable code, missing NOCOPY, dead subprograms — and with the optimizer level you control how aggressively it rewrites your code for speed. Both are settings you attach at compile time.

Enabling Warnings with PLSQL_WARNINGS

Warnings are off by default. Turn them on for your session (or system), then recompile:

-- Enable every warning category for this session
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

-- Recompile so the warnings are (re)evaluated
ALTER PROCEDURE give_raise COMPILE;

-- View any warnings raised
SHOW ERRORS PROCEDURE give_raise;

The Three Warning Categories

Category	Flags	Example
`SEVERE`	Likely-wrong constructs that can produce unexpected results	Aliasing through parameter passing
`PERFORMANCE`	Code that will run slower than necessary	A large `OUT` collection parameter that should be `NOCOPY`
`INFORMATIONAL`	Style / maintainability issues	Unreachable code, a declared-but-unused variable

You can enable, disable, or treat-as-error each category — or an individual warning number:

-- Enable performance warnings, but demote nothing to an error
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:PERFORMANCE';

-- Enable all, but silence "unreachable code" (PLW-06002)
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL', 'DISABLE:6002';

-- Treat a specific warning as a hard compile error
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL', 'ERROR:6002';

Common Warning Codes

Code	Meaning
`PLW-05005`	Subprogram never used (dead code)
`PLW-06002`	Unreachable code
`PLW-06009`	`WHEN OTHERS` handler does not end in `RAISE` or `RAISE_APPLICATION_ERROR`
`PLW-07203`	Parameter could benefit from `NOCOPY`
`PLW-05004`	Identifier is also a keyword

CREATE OR REPLACE PROCEDURE demo_warn IS
    v_unused NUMBER;              -- PLW-06006 / informational: never used
BEGIN
    RETURN;
    DBMS_OUTPUT.PUT_LINE('never runs');  -- PLW-06002: unreachable code
END demo_warn;
/

Per-Unit Warning Settings

You can bake a warning setting into a single compilation, independent of the session:

ALTER PROCEDURE give_raise COMPILE PLSQL_WARNINGS = 'ENABLE:ALL' REUSE SETTINGS;

The settings a unit was last compiled with are recorded in the data dictionary:

SELECT plsql_warnings, plsql_optimize_level, plsql_code_type
FROM   user_plsql_object_settings
WHERE  name = 'GIVE_RAISE';

Managing Warnings Programmatically: DBMS_WARNING

DBMS_WARNING lets code read and change warning settings at runtime — handy in deployment scripts that recompile many objects with a consistent policy:

BEGIN
    DBMS_WARNING.SET_WARNING_SETTING_STRING(
        value  => 'ENABLE:ALL',
        scope  => 'SESSION'
    );
    DBMS_OUTPUT.PUT_LINE('Current: ' || DBMS_WARNING.GET_WARNING_SETTING_STRING);
END;
/

The Optimizer Level: PLSQL_OPTIMIZE_LEVEL

The PL/SQL compiler optimizes your code. PLSQL_OPTIMIZE_LEVEL controls how far it goes:

Level	Behaviour
`0`	No optimization — closest to the source; slowest runtime
`1`	Basic optimizations (removes some redundant code)
`2`	Default — full optimization, including reordering and moving code
`3`	Everything in level 2 plus automatic inlining of local subprograms

-- Level 3 can inline small helper subprograms into their callers
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
ALTER PROCEDURE process_department COMPILE;

ℹ

Level 2 is almost always the right default — it enables the optimizations that make BULK COLLECT/FORALL and most real code fast. Level 3 adds inlining, which helps tight loops that call small helpers; you can also request inlining for one call with PRAGMA INLINE(helper_name, 'YES').

Warnings + Optimization Together

A typical development-time compile turns on all warnings and keeps the default optimizer level:

ALTER SESSION SET PLSQL_WARNINGS   = 'ENABLE:ALL';
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

-- Recompile every invalid object in the schema with these settings
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);

⚠

Warnings are evaluated at compile time, so simply changing PLSQL_WARNINGS shows nothing until you recompile the unit. And because WHEN OTHERS without a RAISE is one of PL/SQL's most dangerous habits, treating PLW-06009 as an error ('ERROR:6009') is a worthwhile team policy.

Summary

PLSQL_WARNINGS enables compiler warnings in three categories: SEVERE, PERFORMANCE, INFORMATIONAL (or ALL).
Use ENABLE:, DISABLE:, and ERROR: to tune categories or individual PLW- codes.
Warnings are evaluated at compile time — recompile (ALTER … COMPILE) after changing the setting.
Per-unit settings and the compile history live in USER_PLSQL_OBJECT_SETTINGS.
DBMS_WARNING reads and sets warning policy programmatically.
PLSQL_OPTIMIZE_LEVEL (0–3) controls optimization; 2 is the default, 3 adds inlining.
Treating PLW-06009 (silent WHEN OTHERS) as an error is a strong safeguard.