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, INFORMATIONALThe 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
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.
