When you compile a stored PL unit, the PL compiler generates warnings for conditions that do not cause errors or prevent compilation. For example, when you use deprecated PL features.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
PLSQL_WARNINGS parameter
The format of a PL warning message code 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 will never run. |
You can use the PLSQL_WARNINGS compilation parameter to enable or disable all warnings, including one or more warning categories and specific warnings.
You can enable or disable all warnings, including one or more warning categories and specific warnings.
You can treat specified warnings as errors (which must be corrected before the PL unit is compiled).
The following items support setting the value of PLSQL_WARNINGS:
A database instance by using the
ALTER SYSTEMstatement.A session by using the
ALTER SESSIONstatement.A stored PL unit 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 set the value of PLSQL_WARNINGS by using the ALTER statement.
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 values for PLSQL_WARNINGS. These 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