How to Manage Compiler Warnings with DBMS_WARNING

The PLSQL_WARNINGS parameter controls which compiler warnings are reported, and ALTER SESSION sets it from SQL. DBMS_WARNING sets and reads the same settings from PL/SQL: add one warning at a time, read the current setting, find a warning's category, and restore a setting afterward.

Code for This Guide

The main examples are in the examples/pkg-utilities 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

dbms_warning.set_warning_setting_string('ENABLE:ALL', 'SESSION');
dbms_warning.add_warning_setting_cat('PERFORMANCE', 'ENABLE', 'SESSION');
dbms_warning.add_warning_setting_num(warning_number, 'ERROR', 'SESSION');

dbms_warning.get_warning_setting_string            -- the current setting
dbms_warning.get_warning_setting_num(number)       -- one warning's setting
dbms_warning.get_category(number)                  -- SEVERE, PERFORMANCE, INFORMATIONAL

The scope is SESSION or SYSTEM; SYSTEM needs the ALTER SYSTEM privilege.

Change and Read Settings

Example:

begin
  dbms_warning.set_warning_setting_string('ENABLE:PERFORMANCE', 'SESSION');
  dbms_warning.add_warning_setting_num(6002, 'ERROR', 'SESSION');   -- unreachable code
  dbms_output.put_line(dbms_warning.get_warning_setting_string);
  dbms_output.put_line('PLW-06002 is: ' || dbms_warning.get_warning_setting_num(6002));
  dbms_output.put_line('category of PLW-07204: ' || dbms_warning.get_category(7204));
end;
/

Output:

DISABLE:INFORMATIONAL,ENABLE:PERFORMANCE,DISABLE:SEVERE,ERROR:  6002
PLW-06002 is: ERROR:  6002
category of PLW-07204: PERFORMANCE

PL/SQL procedure successfully completed.

The block enables performance warnings and makes PLW-06002, unreachable code, an error. The setting string shows each category and the individual warning, and GET_CATEGORY reports that PLW-07204 is a performance warning.

Save and Restore

A build script can turn on all warnings for its own compilations and put back whatever the session had before.

Example:

declare
  v_saved varchar2(1000) := dbms_warning.get_warning_setting_string;
begin
  dbms_warning.set_warning_setting_string('ENABLE:ALL', 'SESSION');
  dbms_output.put_line('during: ' || dbms_warning.get_warning_setting_string);
  -- ... compile code here with all warnings ...
  dbms_warning.set_warning_setting_string(v_saved, 'SESSION');
  dbms_output.put_line('after:  ' || dbms_warning.get_warning_setting_string);
end;
/

Output:

during: ENABLE:ALL
after:  DISABLE:ALL

PL/SQL procedure successfully completed.

The saved string is passed back to SET_WARNING_SETTING_STRING, so the session returns to its previous setting.

Things to Know

• Settings apply to units compiled afterward; compiled units keep the setting they were compiled with.
• ADD_ procedures change one category or warning and keep the rest; SET_WARNING_SETTING_STRING replaces everything.
• The warnings themselves appear in USER_ERRORS after compilation.

Related Guides

• How to Use PL/SQL Compiler Warnings
• How to Use DBMS_PREPROCESSOR

Conclusion

DBMS_WARNING sets and reads PL/SQL compiler warning settings from code: whole strings, single categories, or individual warnings. Use it in build scripts to compile with strict warnings and restore the previous setting afterward.