When you compile a stored PL unit, the PL compiler generates warnings for situations 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 format of the message code for PL warnings is PLW-nnnnn.
The following table describes the categories of compile-time warnings.
| Category | Description | Example |
| SEVERE | May cause unexpected behavior or incorrect results. | Ambiguous parameter names. |
| 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 set 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 corrected before compiling the PL unit).
The following items support setting the value of PLSQL_WARNINGS:
Database instances use the
ALTER SYSTEMstatement.Sessions use the
ALTER SESSIONstatement.Stored PL units use the
ALTERstatement and itscompile_parameters_clause.
For any of the above ALTER statements, you can set the value of PLSQL_WARNINGS using the following syntax:
PLSQL_WARNINGS = 'value_clause [, value_clause] ...'
The following examples show how to set the value of PLSQL_WARNINGS using the ALTER statement.
Example 1: Enable all warnings for the session.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
Example 2: Disable INFORMATIONAL category warnings for the session.
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL';
Example 3: Enable PERFORMANCE category warnings, disable SEVERE category warnings, and treat the PLW-07002 warning as an error for the session.
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