When you compile stored PL units, 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-compatible mode.
PLSQL_WARNINGS parameter
The format of a PL warning message code is PLW-nnnnn.
The following table describes the warning categories during compilation.
| Category | Description | Example |
|---|---|---|
| SEVERE | May cause unexpected behavior or incorrect results. | Overlapping 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 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 the PLSQL_WARNINGS parameter:
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 preceding ALTER statements, you can set the value of the PLSQL_WARNINGS parameter as follows:
PLSQL_WARNINGS = 'value_clause [, value_clause] ...'
The following examples show how to set the value of the PLSQL_WARNINGS parameter 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, disable SEVERE warnings, and treat PLW-07002 warnings as errors for the current 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 the PLSQL_WARNINGS parameter.
When you compile complex applications, you can use the DBMS_WARNING subprograms to set different values for the PLSQL_WARNINGS parameter. The DBMS_WARNING subprograms can save the current value of the PLSQL_WARNINGS parameter, change the value to compile a specified subprogram set, and then restore the value to its original value.
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 setting.
+-------------------------------------------+
| DBMS_WARNING.GET_WARNING_SETTING_STRING() |
+-------------------------------------------+
| ENABLE:ALL |
+-------------------------------------------+
1 row in set