- PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation
- The message code of a PL/SQL warning has the form PLW-nnnnn
- Warnings can be:
SEVERE, PERFORMANCE, or INFORMATIONAL.
- Compilation parameter:
PLSQL_WARNINGS. You may:
- Enable or disable the reporting of warning messages by the PL/SQL compiler, and specify which warning messages to show as errors.
- Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit)
- Use with
ALTER SESSION or ALTER SYSTEM or on a stored PL/SQL unit.
Syntax:
PLSQL_WARNINGS = { ENABLE | DISABLE | ERROR }:
{ ALL | SEVERE | INFORMATIONAL | PERFORMANCE | { int | (int [, int ] ...)}}
---- i.e. ----
SQL> ALTER SESSION SET PLSQL_WARINGS = 'ENABLE:ALL';
SQL> ALTER PROCEDURE p1 COMPILE
PLSQL_WARNINGS = 'ENABLE:PERFORMANCE';
SQL> ALTER SYSTEM SET PLSQL_WARNINGS = 'ENABLE:(5000,5001,5002)', 'DISABLE:(6000,6001)';
- With Oracle 11g, a new PL/SQL Compiler Warning has been added to identify WHEN OTHERS exception handlers that do no re-raise errors using RAISE or RAISE_APPLICATION_ERROR.
- Such exception handlers can often hide code failures that result in hard to identify bugs.
- The example below shows the expected compiler warning when the PLSQL_WARNINGS flag is set.
-- to check the current warnings status, execute the query below as SYSTEM:
SQL> select name, value from gv$parameter
where name like 'plsql%warn%';
NAME VALUE
---------------------- -------------
plsql_warnings DISABLE:ALL
Alternativelly, you can also use DBMS_WARNING pagckage:
SQL> select dbms_warning.get_warning_setting_string() from dual;
DBMS_WARNING.GET_WARNING_SETTING_STRING()
-----------------------------------------
DISABLE:ALL
SQL> alter session set plsql_warnings = 'ENABLE:ALL';
SQL> create or replace procedure others_test as
begin
raise_application_error(-20000, 'Force and exception');
exception
when others then
null;
end;
/
Warning(5,1): PLW-06009: procedure "OTHERS_TEST" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
SQL> desc dbms_warning
PROCEDURE Argument Name Type IN/OUT Default
------------------------------------- ---------------- -------------- ------ -------
ADD_WARNING_SETTING_CAT WARNING_CATEGORY VARCHAR2 IN unknown
ADD_WARNING_SETTING_CAT WARNING_VALUE VARCHAR2 IN unknown
ADD_WARNING_SETTING_CAT SCOPE VARCHAR2 IN unknown
ADD_WARNING_SETTING_NUM WARNING_NUMBER BINARY_INTEGER IN unknown
ADD_WARNING_SETTING_NUM WARNING_VALUE VARCHAR2 IN unknown
ADD_WARNING_SETTING_NUM SCOPE VARCHAR2 IN unknown
GET_CATEGORY (FUNCTION) VARCHAR2 OUT unknown
GET_CATEGORY WARNING_NUMBER BINARY_INTEGER IN unknown
GET_WARNING_SETTING_CAT (FUNCTION) VARCHAR2 OUT unknown
GET_WARNING_SETTING_CAT WARNING_CATEGORY VARCHAR2 IN unknown
GET_WARNING_SETTING_NUM (FUNCTION) VARCHAR2 OUT unknown
GET_WARNING_SETTING_NUM WARNING_NUMBER BINARY_INTEGER IN unknown
GET_WARNING_SETTING_STRING (FUNCTION) VARCHAR2 OUT unknown
SET_WARNING_SETTING_STRING VALUE VARCHAR2 IN unknown
SET_WARNING_SETTING_STRING SCOPE VARCHAR2 IN unknown
- with all warnings disabled, a procedure with unreachable code compiles silently.
- In the example below, since x is constant, the IF always evaluates TRUE: ELSE code never runs (unreachable).
SQL> alter session set plsql_warnings='DISABLE:ALL';
Session altered.
SQL> create or replace procedure unreach
authid definer as
x constant boolean := true;
begin
if x then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end unreach;
/
Procedure created.
(2) Now enable plsql warnings using the DBMS_WARNING package.
When the procedure is recompiled, a warning is issued.
SQL> call dbms_warning.set_warning_setting_string('ENABLE:ALL', 'SESSION');
Call completed
SQL> select dbms_warning.get_warning_setting_string() from dual;
DBMS_WARNING.GET_WARNING_SETTING_STRING()
----------------------------------------------
ENABLE:ALL
-- Recompile the procedure.
SQL> alter procedure unreach compile;
SP2-0805: Procedure altered with compilation warnings
-- Check the errors in SQL*PLUS:
SQL> show errors procedure unreach;
Errors for PROCEDURE BROKEN:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5 PLW-06002: Unreachable code
OR Query the USER_ERRORS view.
SQL> select name, type, line, position, text, attribute, message_number err
from user_errors;
(formatted output):
NAME TYPE LINE POSITION TEXT ATTRIBUTE ERR
--------- ----------- ----- ---------- ----------------------------- --------- -----
BROKEN PROCEDURE 4 6 PLW-06002: Unreachable code WARNING 6002
BROKEN PROCEDURE 7 5 PLW-06002: Unreachable code WARNING 6002
- Generally these are typing errors
- the parser transforms the PL/SQL text file into a set of interpreted instructions (p-code): it may find lexical errors.
- Lexical errors caught when the plain text file is parsed during compilation.
- Deferred compilation errors: when actual data values don’t fit during assignment because they are too large or the wrong datatype.
- Compilation errors cannot be handled by your local exception handler, but you can catch them in a wrapper (containing outer) block.
- Compilation errors can be: prior line, current line or declaration errors.
| Viewing errors |
To see warnings (and errors) generated during compilation, use either:
- Static data dictionary view
*_ERRORS
- In SQL*Plus: command
SHOW ERRORS.
|
- Occur when actual data fails to meet the rules defined by your program unit
- run-time manageable errors (execution or exception blocks)
- run-time unmanageable errors (declaration section)
No comments:
Post a Comment