| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsplsw.sql |
| First Availability |
10.1 |
| Constants |
| Name |
Data Type |
Values |
| WARNING_CATEGORY |
VARCHAR2 |
| ALL |
PERFORMANCE |
|
INFORMATIONAL |
SEVERE |
|
| WARNING_VALUE |
VARCHAR2 |
|
| SCOPE |
VARCHAR2 |
|
|
| Dependencies |
| ALL_PLSQL_OBJECT_SETTINGS |
DBA_WARNING_SETTINGS |
|
ALL_WARNING_SETTINGS |
DBMS_PLSQL_WARNING_LIB
|
| DBA_PLSQL_OBJECT_SETTINGS |
USER_WARNING_SETTINGS |
|
| Initialization Parameters |
PLSQL_WARNINGS |
| Current Warning Status |
set linesize 121
col name format a30
col value format a30
SELECT name, value
FROM gv$parameter
WHERE name LIKE 'plsql%warn%'; |
| Changing Warning Status |
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:INFORMATIONAL';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE'; |
| |
| Severe Warning Messages
(5000-5999) |
PLW-05000 |
Mismatch in NOCOPY qualification between specification and body
The indicated parameter was declared with the NOCOPY hint in the subprogram specification but not in the body, or it was declared with the NOCOPY hint in the body but not in the specification. |
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
CREATE OR REPLACE PACKAGE plwpk IS
PROCEDURE p(param OUT NOCOPY VARCHAR2);
END plwpk;
/
CREATE OR REPLACE PACKAGE BODY plwpk IS
PROCEDURE p(param OUT NOCOPY VARCHAR2)
IS
BEGIN
param := 'test';
END p;
END plwpk;
/
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PACKAGE BODY plwpk IS
PROCEDURE p(param OUT VARCHAR2) IS
BEGIN
param := 'test';
END p;
END plwpk;
/
sho err |
PLW-05001 |
Previous use of
'string' (at line string) conflicts with this use
While looking for prior declarations of a variable or constant, the compiler found another object with the same name in the same scope. Or, the headers of subprogram in a package specification and body are not identical. |
| TBD |
PLW-05003 |
Same actual
parameter(string and string) at IN and NOCOPY may have side effects
The indicated parameter was declared with the NOCOPY hint in the subprogram specification but not in the body, or it was declared with the NOCOPY hint in the body but not in the specification. |
| TBD |
PLW-05004 |
Identifier string is also declared in STANDARD or is a SQL builtin
The indicated identifier was also either:
- declared in package STANDARD
- a SQL built-in function
- a pseudo-column.
This situation can result in name resolution issues since the
STANDARD/builtin declaration will be chosen over the local declaration in SQL statements; however the local declaration will be chosen over the
STANDARD/builtin declaration outside SQL scope. |
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
-- test a function
CREATE OR REPLACE PROCEDURE plw05004 IS
a varchar2(20);
-- same declaration as in SQL dbms_standard
FUNCTION trim(v IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN v;
END;
BEGIN
a := TRIM(' X ');
END plw05004;
/
-- test a SQL built-in
CREATE OR REPLACE PROCEDURE plw05004 IS
n NUMBER;
FUNCTION sqrt(n IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN 1;
END;
BEGIN
n := sqrt(1);
END plw05004;
/
-- test a pseudocolumn
CREATE OR REPLACE PROCEDURE plw05004 IS
rowid BOOLEAN := TRUE;
BEGIN
IF rowid THEN
NULL;
END IF;
END plw05004;
/ |
PLW-05005 |
Function string returns without value at line
string
The function exited without a return value. Return statements are required for all PL/SQL functions. |
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
CREATE OR REPLACE FUNCTION plw05005 RETURN VARCHAR2 IS
BEGIN
RETURN 'A';
EXCEPTION
WHEN OTHERS THEN
NULL;
END plw05005;
/
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE FUNCTION plw05005 RETURN VARCHAR2 IS
BEGIN
RETURN 'A';
EXCEPTION
WHEN OTHERS THEN
NULL;
END plw05005;
/
sho err |
| PLW-05006 |
Shadow type generation resulted in
warning string
Compilation of a table function causes the creation of internally- generated types. A problem occurred during creation of these types which was not severe enough to prevent the creation of the types, but which may impact the usage of the table function. |
| TBD |
| PLW-05007 |
First parameter to pragma INLINE must be an identifier
The first parameter to the pragma INLINE must be an identifier denoting a procedure or function. |
| TBD |
| PLW-05008 |
Illegal number of arguments for pragma string
The number of arguments (actual parameters) passed to the named pragma (compiler directive) is incorrect. A required argument was omitted from the argument list, or the pragma syntax is faulty (for example, a comma might be missing between two parameters). |
| TBD |
| PLW-05009 |
Pragma INLINE may not appear in a declaration list
Pragma INLINE applies only to the following statement, and can not appear immediately prior to a declaration. |
| TBD |
| PLW-05010 |
Duplicate pragma INLINE on procedure
'string'
Pragma INLINE was applied twice to the same procedure in the same statement. The procedure will be inlined only if all duplicates specify a control value of 'YES'. |
| TBD |
| PLW-05011 |
Pragma INLINE for procedure
'string' does not apply to any calls
A pragma INLINE was provided, but there was no call to the mentioned procedure in the next statement. |
| TBD |
| PLW-05012 |
Pragma INLINE for procedure
'string' could not be applied
A pragma INLINE(<name>, 'yes') was used, but the named procedure could not be
inlined. |
| TBD |
| PLW-05013 |
Second parameter to pragma INLINE
'string' is not 'YES' or 'NO'
The second parameter to the pragma INLINE governs whether or not a procedure call can be
inlined. The only legal values are 'YES', meaning that the compiler should attempt to inline the call, and 'NO', meaning that the compiler must not inline the call. |
| TBD |
| PLW-05014 |
Pragma string cannot be specified here
The pragma was specified in an inappropriate context. |
| TBD |
| PLW-05015 |
Implementation restriction - too many debug records
The program is so large that it overflowed a compiler limit on the size of the debug symbol table. The module was compiled with partial debugging information. |
| TBD |
PLW-05016 |
INTO clause should not be specified here
The INTO clause was incorrectly specified on a cursor declaration. It should have been specified on the FETCH statement |
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
CREATE OR REPLACE PROCEDURE plw05016 AS
t VARCHAR2(30);
CURSOR c IS
SELECT table_name
INTO t
FROM all_tables;
BEGIN
NULL;
END plw05016;
/
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE plw05016 IS
t VARCHAR2(30);
CURSOR c IS
SELECT table_name
INTO t
FROM all_tables;
BEGIN
OPEN c;
CLOSE c;
END plw05016;
/
CREATE OR REPLACE PROCEDURE plw05016 AS
TYPE at_type IS TABLE OF all_tables.table_name%TYPE;
at_array at_type;
CURSOR c IS
SELECT table_name
BULK COLLECT INTO at_array
FROM all_tables;
BEGIN
OPEN c;
CLOSE c;
END plw05016;
/ |
| PLW-05017 |
Prefix of the expression will be ignored
Generalized invocation with explicit SELF and a dotted name was used to call the type method causing the prefix of the dotted name to be ignored. |
| TBD |
| |
| Informational Warning Messages
(6000-6249) |
PLW-06002 |
Unreachable code
Static program analysis determined that some code on the specified line would never be reached during execution. This may come as a result of expression evaluation at compile time. |
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
CREATE OR REPLACE PROCEDURE plw06002 AS
x NUMBER := 10;
BEGIN
IF x = 10 THEN
x := 20;
ELSE
-- dead code (never reached)
x := 100;
END IF;
END plw06002;
/
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE plw06002 IS
x NUMBER := 10;
BEGIN
IF x = 10 THEN
x := 20;
ELSE
-- dead code (never reached)
x := 100;
END IF;
END plw06002;
/
sho err
CREATE OR REPLACE PROCEDURE plw06002 AS
x NUMBER := 10;
BEGIN
IF x = 10 THEN
x := 20;
-- ELSE
-- x := 100;
END IF;
END plw06002;
/
CREATE OR REPLACE PROCEDURE plw06002 IS
x NUMBER(5) := 100;
BEGIN
WHILE x < 100 LOOP
x := 10;
END LOOP;
END plw06002;
/
show err
CREATE OR REPLACE PROCEDURE plw06002 AS
x NUMBER(5) := 10;
BEGIN
WHILE x < 100 LOOP
x := 10;
END LOOP;
END plw06002;
/ |
PLW-06002 with Wrapped Code |
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE plw06002 wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
6d a2
ASXzD4E60Xyv09790pDE+Doltlcwg5nnm7+fMr2ywFwWhT54fIJ8fAmldIsJ42mlmYEywLIl
fD3DS8JcfCF8S4vAwDL+0oYJaef+pdJSROVL+73Vzs74W6ZElKnhcAI8Z0oKT5lUaOyZ256l
vVv9ftempqqKBBE=
/ |
PLW-06003 |
Unknown inquiry directive 'string'
An unknown inquiry directive was used. The inquiry directive is neither predefined nor user-defined. |
| TBD |
PLW-06004 |
Inlining of call of procedure
'string' requested
A pragma INLINE(, 'YES') referring to the named procedure was found. The compiler will, if possible, inline this call. |
| TBD |
PLW-06005 |
Inlining of call of procedure 'string' was done
A call to the procedure was inlined. |
| TBD |
PLW-06006 |
Uncalled procedure 'string' is
removed
Static program analysis determined that the procedure can never be called or executed. Therefore, it has been removed to save time during compilation and space during execution. |
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE plw06006 IS
t VARCHAR2(30);
CURSOR c IS
SELECT table_name
FROM all_tables;
BEGIN
NULL;
END plw06006;
/ |
PLW-06007 |
Procedure 'string' is removed because optimization removed calls
Static program analysis determined that the procedure can never be called or executed. Therefore, it has been removed to save time during compilation and space during execution. |
| TBD |
PLW-06008 |
Call of procedure 'string' will not be inlined
A pragma INLINE(, 'NO') referring to the named procedure was found. The compiler is prevented from inlining this call. |
| TBD |
PLW-06009 |
Procedure 'string' OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
The OTHERS handler can exit without executing some form of RAISE or or a call to the standard procedure RAISE_APPLICATION_ERROR. |
CREATE OR REPLACE PROCEDURE plw06009 IS
CURSOR c IS
SELECT table_name
FROM all_tables;
BEGIN
OPEN c;
CLOSE c;
EXCEPTION
WHEN OTHERS THEN
NULL;
END plw06009;
/ |
PLW-06010 |
Keyword 'string' used as a defined name
A PL/SQL or SQL keyword was used as defined name. Although legal, this is not recommended. |
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE string IS
BEGIN
NULL;
END string;
/ |
PLW-06011 |
Detailed dependency information discarded due to size limitations
The compiled unit was so large that the compiler could not store detailed dependency information that could be used to quickly recompile the unit if it was invalidated. |
| TBD |
PLW-06012 |
SQL statement cannot be inlined. string
The SQL statement could not be inlined. |
| TBD |
PLW-06013 |
Deprecated parameter PLSQL_DEBUG forces PLSQL_OPTIMIZE_LEVEL <= 1
The PLSQL_DEBUG parameter was set to TRUE forcing the optimization level to 1 (or 0 if the user selected 0) regardless of the setting of the PLSQL_OPTIMIZE_LEVEL parameter to a higher level |
show parameter level
ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE PROCEDURE test IS
CURSOR c IS
SELECT *
FROM all_tables;
BEGIN
FOR r IN c LOOP
r.table_name := SUBSTR(r.table_name,1,1);
END LOOP;
END;
/ |
PLW-06014 |
PLSQL_OPTIMIZE_LEVEL <= 1 turns off native code generation
Native code generation was not done because the optimization was set to 1 or
less. |
SELECT DISTINCT owner, plsql_optimize_level
FROM dba_plsql_object_settings;
ALTER SESSION SET plsql_optimize_level = 1;
SELECT DISTINCT owner, plsql_optimize_level
FROM dba_plsql_object_settings;
ALTER SESSION SET plsql_compiler_flags = 'NATIVE';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE FUNCTION plw06014 RETURN VARCHAR2 IS
BEGIN
RETURN 'A';
END plw06014;
/
ALTER SESSION SET plsql_optimize_level = 2;
ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED'; |
PLW-06015 |
Parameter PLSQL_DEBUG is deprecated; use PLSQL_OPTIMIZE_LEVEL = 1
The PLSQL_DEBUG parameter was specified. |
show parameter level
ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE PROCEDURE test IS
CURSOR c IS
SELECT *
FROM all_tables;
BEGIN
FOR r IN c LOOP
r.table_name := SUBSTR(r.table_name,1,1);
END LOOP;
END;
/ |
| |
| Performance Warning Messages
(7000-7249) |
| PLW-07202 |
Bind type will result in conversion away from column type
The column type and the bind type do not exactly match. This will result in the column being converted to the type of the bind variable. This type conversion may prevent the SQL optimizer from using any index the column participates in. This may adversely affect the execution performance of this statement. |
| TBD |
PLW-07203 |
Parameter 'string' may benefit from use of the NOCOPY compiler hint
The mode of the specified parameter was IN OUT or OUT. Use of the NOCOPY compiler hint may speed up calls to this subprogram. |
ALTER SESSION SET
PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE plw07203(sa IN OUT
dbms_sql.varchar2a) IS
BEGIN
dbms_output.put_line('This is a test');
END plw07203;
/
CREATE OR REPLACE PROCEDURE plw07203(sa IN OUT NOCOPY
dbms_sql.varchar2a) IS
BEGIN
dbms_output.put_line('This is a test');
END plw07203;
/ |
PLW-07204 |
Conversion away from column type may result in sub-optimal query plan.
The column type and the bind type do not exactly match. This may result in the column being converted to the type of the bind variable. This type conversion may prevent the SQL optimizer from using any index the column participates in. This may adversely affect the execution performance of this statement. |
desc servers
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE plw07204 IS
v VARCHAR2(10);
i VARCHAR2(10);
BEGIN
SELECT srvr_id
INTO v
FROM servers
WHERE rownum = 1;
SELECT COUNT(*)
INTO i
FROM servers
WHERE srvr_id = v;
END plw07204;
/ |
| PLW-07205 |
SIMPLE_INTEGER is mixed with BINARY_INTEGER or PLS_INTEGER
Using SIMPLE_INTEGER and BINARY_INTEGER or PLS_INTEGER arguments in the same arithmetic expression may inhibit certain optimizations. |
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE plw07205
IS
si SIMPLE_INTEGER := 1;
ri PLS_INTEGER := 1;
n NUMBER;
BEGIN
n := si + ri;
END plw07205;
/ |
PLW-07206 |
Analysis suggests that the assignment to 'string' may be unnecessary
This assignment may be unnecessary; the PL/SQL optimizer could not find any further use of the variable which was being set. If the assignment was being done for some side-effect such as raising a specific exception, the compiler may not have been able to understand the side-effect and this warning may be inappropriate. |
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
CREATE OR REPLACE PACKAGE plw07206 IS
PROCEDURE p(param VARCHAR2);
END plw07206;
/
CREATE OR REPLACE PACKAGE BODY plw07206 IS
PROCEDURE p(param VARCHAR2) IS
x VARCHAR2(10);
BEGIN
x := param;
END p;
END plw07206;
/
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PACKAGE BODY plw07206 IS
PROCEDURE p(param VARCHAR2) IS
x VARCHAR2(10);
BEGIN
x := param;
END p;
END plw07206;
/
sho err |
