When you compile a stored PL unit, the PL compiler generates warnings for conditions that do not cause errors or prevent the compilation. For example, when you use deprecated PL features.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
PLSQL_WARNINGS parameter
The message code of a PL warning is in the PLW-nnnnn format.
The following table describes the categories of compile-time warnings.
| Category | Description | Example |
| SEVERE | May cause unexpected behaviors or incorrect results. | Conflicting parameters. |
| PERFORMANCE | May cause performance issues. | Passing a VARCHAR2 value to a NUMBER column in an INSERT statement. |
| INFORMATIONAL | Does not affect performance or correctness, but may require changes to make the code easier to maintain. | Code that can never run. |
You can use the PLSQL_WARNINGS parameter to enable or disable all warnings, including one or more warning categories and specific warnings.
Enable or disable all warnings, including one or more warning categories and specific warnings.
Treat specified warnings as errors (which must be fixed before the PL unit is compiled).
The following items support setting the value of PLSQL_WARNINGS:
Database instances by using the
ALTER SYSTEMstatement.Sessions by using the
ALTER SESSIONstatement.Stored PL units by using the
ALTERstatement and itscompile_parameters_clause.
For any ALTER statement, you can use the following syntax to set the value of PLSQL_WARNINGS:
PLSQL_WARNINGS = 'value_clause [, value_clause] ...'
The following examples show how to use the ALTER statement to set the value of PLSQL_WARNINGS.
Example 1: Enable all warnings for the current session.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
Example 2: Disable INFORMATIONAL warnings for the current session.
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL';
Example 3: Enable PERFORMANCE warnings for the current session, disable SEVERE warnings, and treat PLW-07002 warnings as errors.
ALTER SESSION
SET PLSQL_WARNINGS='ENABLE:PERFORMANCE, DISABLE:SEVERE, ERROR:07002';
DBMS_WARNING package
If you want to write PL units in a development environment that supports compiling PL units, you can call subprograms in the DBMS_WARNING package to display and set the value of PLSQL_WARNINGS. When you compile complex applications, you can use the DBMS_WARNING subprograms to set different PLSQL_WARNINGS values. The DBMS_WARNING subprograms can save the current PLSQL_WARNINGS settings, change the settings to compile a specified subprogram set, and then restore the settings to their original values.
Here is an example:
obclient> ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'; --Disable all warning messages for this session.
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PROCEDURE sample_code AUTHID DEFINER AS
n CONSTANT BOOLEAN := TRUE;
BEGIN
IF n THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END sample_code;
/
Query OK, 0 rows affected
obclient> CALL DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL', 'SESSION'); --Enable all warning messages for this session.
Query OK, 0 rows affected
obclient> SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL; --View the current warning settings.
+-------------------------------------------+
| DBMS_WARNING.GET_WARNING_SETTING_STRING() |
+-------------------------------------------+
| ENABLE:ALL |
+-------------------------------------------+
1 row in set
1 row in set