Oracle PL/SQL WARNINGS
Versions 10.1 - 19.3

Security Advisory
Since the day we first found PL/SQL Warnings in a Beta test we have not deployed a single customer database without overriding Oracle's default. To benefit from enabling these you must compile PL/SQL objects in your database using SQL*Plus which you should be doing 100% of the time. If you have been compiling objects in your database using any other tool not only can you not benefit from enabling these warnings ... you also are very likely to have been compiling them in debug mode without even knowing it. And, equally important, they work even when code has been wrapped.
 
Recommended Security Rules

 NEVER
  • Put a database into a preproduction or production environment without enabling these messages
  • Execute any DDL in your database without using SQL*Plus while warning messages are enabled: And if there is a warning ... fix it before you proceed.
 WITH GREAT CARE
  • N/A
 CAUTIONS
  • PL/SQL warnings is also part of your existing Oracle license agreement no matter which version, 10.1 or above, or which edition you are licensed to install. If you are not using PL/SQL Warnings in every one of your production databases ... our  caution is that you should be.
 
How Oracle Works
PL/SQL Warnings are created by essentially the same set of processes that produce errors when you try to run SQL or create objects using invalid syntax.

The primary difference between these message and the error message you are used to seeing is that these are hidden from you on default installation.

Developers and Pre-Production DBAs can gain valuable insights by capturing these messages during installation of PL/SQL objects such as functions, operators, packages and procedures and the value received will help you write better code, get better performance, and in some cases avoid security issues that you could not detect by any other means.
Oracle Error Messages (Oracle shows you these by default) CREATE OR REPLACE PROCEDURE plw06002 AS
 x NUMBER := 10;
BEGIN
  IF x = 10 THEN
    x := 20;
  ELSE
    x = 100;
  END IF;
END plw06002;
/

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE PLW06002:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/7 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.
Oracle Warning Messages (the result of a default Oracle installation) set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'plsql%warn%';

NAME                           VALUE
------------------------------ ------------------------------
plsql_warnings                 DISABLE:ALL

The default on installation is DISABLE:ALL and there is no rational reason to not alter that during the installation process using DBCA. The following is an example of code with a security weakness that will undoubtedly slip through a code review.

Before running the following code see if you can find the security issue.

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

CREATE OR REPLACE PROCEDURE plw06002 AUTHID DEFINER AS
 x NUMBER := 10;
BEGIN
  IF x = 10 THEN
    x := 20;
  ELSE
    x := 100;
  END IF;
END plw06002;
/

Procedure created.

Didn't find the issue did you. Run the next demo to see what is wrong
Oracle Warning Messages with message display enabled ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE plw06002 AUTHID DEFINER AS
 x NUMBER := 10;
BEGIN
  IF x = 10 THEN
    x := 20;
  ELSE
    x := 100;
  END IF;
END plw06002;
/

SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PROCEDURE PLW06002:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/5 PLW-06002: Unreachable code
Oracle Warning Messages with message display enabled with wrapped code.

You may not be able to read the PL/SQL but the compiler can.		 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
87 b2
HLZcdemH+s62BMgrwCcUVlp6Lk0wg+nwNctnfI6iOxIYrRUXOGstZyTsaQmryzg4wXtK/JwC
PZGHGZHJJCp+rBduxumtzEDF2O0qfGijxhw9Ubmk0V8NiVGi8qM9aJOwpgXggKJUEkqmJZX6
2/swVM4XPhBQiFjelMccYf4JSShrXxx8

/

SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PROCEDURE PLW06002:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/5 PLW-06002: Unreachable code
As the above code clearly demonstrates, Oracle walked the code with the default value of "x" which is 10. Look closely at the first line of the IF statement ... IF x = 10. Well of course it does. x has no option but to have the value of 10 so at line 7 position 5, x := 100 will never execute.

Consider these implications if this code made it into production
  • If the code reviews performed in Development didn't catch it, what else didn't those code reviews catch?
  • Under the condition where x is not equal to 10 nothing happens
    • Perhaps an exception should have been raise about an improper value
    • Perhaps the IF statement was determining whether a user should be granted escalated privileges
    • Perhaps DBAs that score less than 21 don't get a bonus added to their salary on their next check.

Now that we have your attention ... be sure you enable PL/SQL warnings on all of your databases ... especially those in development and test.
 
PL/SQL Warnings General Information
Constants
Name Data Type Values
WARNING_CATEGORY VARCHAR2
ALL PERFORMANCE
INFORMATIONAL SEVERE
WARNING_VALUE VARCHAR2
DISABLE ERROR
ENABLE  
SCOPE VARCHAR2
SESSION SYSTEM
Data Dictionary Objects
ALL_PLSQL_OBJECT_SETTINGS DBA_PLSQL_OBJECT_SETTINGS DBMS_WARNING_INTERNAL
ALL_WARNING_SETTINGS DBA_WARNING_SETTINGS USER_PLSQL_OBJECT_SETTINGS
CDB_PLSQL_OBJECT_SETTINGS DBMS_PLSQL_WARNING_LIB USER_WARNING_SETTINGS
CDB_WARNING_SETTINGS DBMS_WARNING WARNING_SETTINGS$
First Available 10.1
Initialization Parameter PLSQL_WARNINGS
ALTER SYSTEM SET plsql_warnings = 'ENABLE:ALL'
COMMENT = 'Warnings enabled on <date>'
SID = '*'
SCOPE = BOTH;

System altered.

col description format a40
col update_comment format a40

SELECT name, value, description, update_comment
FROM v$parameter
WHERE name LIKE 'plsql%warn%';

NAME           VALUE      DESCRIPTION                       UPDATE_COMMENT
-------------- ---------- --------------------------------- --------------------------
plsql_warnings ENABLE:ALL PL/SQL compiler warnings settings Warnings enabled on <date>
 
Syntax and Usage
Basic Examples PLSQL_WARNINGS = '<DISABLE | ENABLE | ERROR>:<ALL | INFORMATIONAL | PERFORMANCE | SEVERE>'

PLSQL_WARNINGS = 'DISABLE:<warning#>'ENABLE:<warning#>', 'ERROR:<warning#>'
col value format a80

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:INFORMATIONAL';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:INFORMATIONAL';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:5000', 'ENABLE:5001', 'ERROR:5002';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:(5004,6002,7206)', 'DISABLE:(5000,5003,7203)';

SELECT value FROM v$parameter WHERE name = 'plsql_warnings';
 
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;
/

sho err
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.
eALTER 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).
I have tried numerous times to generate this warning without success. In every case the compiler has raised a full exception due to the syntax being invalid.
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.
CREATE OR REPLACE PROCEDURE plw05011 AUTHID DEFINER IS
BEGIN
  PRAGMA INLINE('A', 'YES');
  NULL;
END plw05011;
/

sho err
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.
CREATE OR REPLACE PROCEDURE plw05013 AUTHID DEFINER IS
 l_loops  NUMBER := 100000;
 l_start  NUMBER;
 l_return NUMBER;

FUNCTION add_numbers(p_1 IN NUMBER, p_2 IN NUMBER) RETURN NUMBER AS
BEGIN
  RETURN p_1 + p_2;
END add_numbers;

BEGIN
  l_start := dbms_utility.get_time;

  FOR i IN 1 .. l_loops LOOP
    PRAGMA INLINE (add_numbers, 'notYESorNO');
    l_return := add_numbers(1, i);
  END LOOP;

  dbms_output.put_line('Elapsed Time: ' || (dbms_utility.get_time - l_start) || ' hsecs');
END plw05013;
/

sho err
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_all_tables;
BEGIN
  NULL;
END plw05016;
/

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE plw05016 AUTHID DEFINER IS
 t VARCHAR2(30);

 CURSOR c IS
 SELECT table_name
 INTO t
 FROM all_all_tables;
BEGIN
  OPEN c;
  CLOSE c;
END plw05016;
/

sho err
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
PLW-05018 Package, procedure, function, or PL/SQL type does not contain the AUTHID clause
Unit omitted optional AUTHID clause; default value DEFINER used.This warning is a generic reminder to explicitly define AUTHID
CREATE OR REPLACE PROCEDURE plw05018 IS
BEGIN
  NULL;
END;
/

sho err
PLW-05019 Language element 'string' is deprecated in the indicated version
The language element near keyword string is deprecated beginning with version string. A deprecated language element was used. The particular language element is deprecated in PL/SQL versions greater than or equal to the version number given in the message text.
CREATE OR REPLACE PACKAGE plw05019 AUTHID DEFINER AS
  FUNCTION test(val IN NUMBER) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES (test, WNDS, RNPS);
END plw05019;
/

sho err
PLW-05020 Parameter name must be an identifier
A string literal was used as the parameter name in a call that used named notation.
CREATE OR REPLACE PROCEDURE child (parm IN VARCHAR2) AUTHID CURRENT_USER IS
BEGIN
  NULL;
END;
/

CREATE OR REPLACE PROCEDURE plw05020 AUTHID CURRENT_USER IS
BEGIN
  child('parm' => 'ABC');
END;
/

sho err
PLW-05021 Exception <exception_name> does not have a pragma
A user defined exception was created but not mapped to a pragma. I can see this warning as a real pain unless disabled which I do in my setup glogin.sql.
CREATE OR REPLACE PROCEDURE plw05021 AUTHID DEFINER IS
 FieldsLeftNull EXCEPTION;
BEGIN
  NULL;
END plw05021;
/

sho err
PLW-05022 Argument 'string' is not comparable
An argument to either IN, BETWEEN or MEMBER OF was not of a comparable type. Or, if the argument was of an object type, no MAP or ORDER method was specified for the type.
-- multiple attempts to produce this warning message have failed. Here is one of them.

conn scott/tiger@pdbdev

CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR(2);
/

CREATE OR REPLACE PROCEDURE plw05022 AUTHID DEFINER IS
 i PLS_INTEGER;
 x InStrTab := InStrTab(10,30, 'A');
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM emp
  WHERE deptno MEMBER OF (x);

  dbms_output.put_line(i);
END plw05022;
/
PLW-05023 PL/Scope is disabled because SYSAUX is offline
PL/Scope was disabled for the current PL/SQL unit compilation because the SYSAUX tablespace is or was offline at the time of compilation.
TBD
PLW-05024 Do not use BULK COLLECT clause in a cursor declaration
A BULK COLLECT clause was specified in a cursor declaration.
CREATE OR REPLACE PROCEDURE plw05024 AUTHID DEFINER AS
 TYPE at_type IS TABLE OF all_all_tables.table_name%TYPE;
 at_array at_type;

 CURSOR c IS
 SELECT table_name
 BULK COLLECT INTO at_array
 FROM all_all_tables;
BEGIN
  OPEN c;
  CLOSE c;
END plw05024;
/

sho err
PLW-05025 Do not refer to formal parameter string from this same subprogram declaration
The default expression of a formal parameter referred to another formal parameter from the same subprogram declaration. This is only permitted in a formal %CHARSET expression. Action: Remove the reference.
TBD
PLW-05999 Implementation restriction (may be temporary) string
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';

SQL> CREATE OR REPLACE PROCEDURE plw06002 AUTHID DEFINER AS
  2   x NUMBER := 10;
  3  BEGIN
  4    IF x = 10 THEN
  5      x := 20;
  6    ELSE
  7      x := 100;
  8    END IF;
  9  END plw06002;
 10  /

Procedure created.

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

SQL> CREATE OR REPLACE PROCEDURE plw06002 AUTHID DEFINER AS
  2   x NUMBER := 10;
  3  BEGIN
  4    IF x = 10 THEN
  5      x := 20;
  6    ELSE
  7      x := 100;
  8    END IF;
  9  END plw06002;
 10  /
SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PROCEDURE DEAD_CODE:

LINE/COL ERROR
-------- ----------------------
7/5 PLW-06002: Unreachable code
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=
/

sho err
PLW-06003 Unknown inquiry directive 'string'
An unknown inquiry directive was used. The inquiry directive is neither predefined nor user-defined.
CREATE OR REPLACE PROCEDURE plw06003 IS
BEGIN
  dbms_output.put_line('I am: ' || $$plsql_unit);
END plw06003;
/

CREATE OR REPLACE PROCEDURE plw0603 IS
BEGIN
  dbms_output.put_line('I am: ' || $$plsql_unitzz);
END plw06003;
/

sho err
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.
CREATE OR REPLACE TYPE t_airplanes_row AUTHID CURRENT_USER AS OBJECT (
program_id     VARCHAR2(3),
line_number    NUMBER(10),
customer_id    VARCHAR2(4),
order_date     DATE,
delivered_date DATE);
/

CREATE OR REPLACE TYPE t_airplanes_tab AS TABLE OF t_airplanes_row;
/

CREATE OR REPLACE PACKAGE cache_demo AUTHID CURRENT_USER AS
 l_tab t_airplanes_tab;
 PROCEDURE populate_cache;
 FUNCTION get_airplanes RETURN t_airplanes_tab;
END cache_demo;
/

CREATE OR REPLACE PACKAGE BODY cache_demo AS
 PROCEDURE populate_cache AS
 BEGIN
   SELECT t_airplanes_row(program_id, line_number,
   customer_id, order_date, delivered_date)
   BULK COLLECT INTO l_tab
   FROM airplanes
   WHERE rownum < 50001;
 END;
 ------------------------------------------------
 FUNCTION get_airplanes RETURN t_airplanes_tab AS
 BEGIN
   RETURN l_tab;
 END;
BEGIN
  populate_cache;
END cache_demo;
/

SP2-0810: Package Body created with compilation warnings

SQL> SHO ERR
Errors for PACKAGE BODY CACHE_DEMO:

LINE/COL
------------------------------------------------------------------
ERROR
------------------------------------------------------------------
16/3
PLW-06005: inlining of call of procedure 'POPULATE_CACHE' was done
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 AUTHID DEFINER IS
 t VARCHAR2(30);

 CURSOR c IS
 SELECT table_name
 FROM all_all_tables;
BEGIN
  NULL;
END plw06006;
/

sho err
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 AUTHID DEFINER IS
 CURSOR c IS
 SELECT table_name
 FROM all_all_tables;
BEGIN
  OPEN c;
  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END plw06009;
/

sho err
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 AUTHID DEFINER IS
BEGIN
  NULL;
END string;
/

sho err
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 plw06013 AUTHID DEFINER IS
 CURSOR c IS
 SELECT *
 FROM all_all_tables;
BEGIN
  FOR r IN c LOOP
    r.table_name := SUBSTR(r.table_name,1,1);
  END LOOP;
END plw06013;
/

sho err
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;
/

sho err

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 plw06015 IS
 CURSOR c IS
  SELECT *
  FROM all_all_tables;
BEGIN
  FOR r IN c LOOP
    r.table_name := SUBSTR(r.table_name,1,1);
  END LOOP;
END plw06015;
/

sho err
PLW-06016 Unit string is too large; native code generation turned off
Because the program was extremely large, analysis took an excessive amount of time or space to complete causing native compilation to be turned off.
TBD
PLW-06017 An operation will raise an exception
An operation near this location is certain to raise an exception during execution; it may be a programming error because it is not an explicit RAISE.
-- the signtype data type can only handle the values -1, 0, and +1

CREATE OR REPLACE FUNCTION plw6017f RETURN SIGNTYPE AUTHID DEFINER IS
BEGIN
  RETURN 42;
END plw6017f;
/

sho err

CREATE OR REPLACE PROCEDURE plw6017p AUTHID DEFINER IS
 x VARCHAR2(20);
BEGIN
  x := 'ABCDEFGHIJKLMNOPQRSTUVXXYZ';
END plw6017p;
/

sho err
PLW-06018 An infinity or NaN value is computed or used
An operation near this location computes or uses an infinite NUMBER, BINARY_FLOAT, or BINARY_DOUBLE value or a NaN BINARY_FLOAT or BINARY_DOUBLE value.
TBD
PLW-06019 Entity string is deprecated
The entity was deprecated and could be removed in a future release.
SQL> CREATE OR REPLACE PACKAGE plw06019a AUTHID DEFINER IS
  2   PRAGMA DEPRECATE(pragma_dep);
  3   PROCEDURE foo;
  4   PROCEDURE bar;
  5  END plw06019a;
  6  /

SP2-0808: Package created with compilation warnings

SQL> sho err
Errors for PACKAGE PLW06019A:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/8 PLW-06021: PRAGMA DEPRECATE on PRAGMA_DEP is misplaced
PLW-06020 Reference to a deprecated entity: declared in unit
The referenced entity was deprecated and could be removed in a future release. Do not use the deprecated entity. Follow the specific instructions in the warning if any are given.
TBD
PLW-06021 PRAGMA DEPRECATE on string is misplaced
The pragma deprecate should follow immediately after the declaration of the entity that is being deprecated. Place the pragma immediately after the declaration of the entity that is being deprecated.
SQL> CREATE OR REPLACE PROCEDURE plw06021 AUTHID DEFINER IS
  2  PRAGMA DEPRECATE(pragma_dep);
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('PLW06021');
  5  END plw06021;
  6  /

SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PACKAGE PLW06019B:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/8 PLW-06021: PRAGMA DEPRECATE on PRAGMA_DEP is misplaced
PLW-06022 Cannot use PRAGMA DEPRECATE on this entity
This entity cannot be deprecated. Deprecation only applies to entities that may be declared in a package or type specification as well as to top-level procedure and function definitions.
SQL> CREATE OR REPLACE PROCEDURE plw06022 AUTHID DEFINER IS
  2  PROCEDURE nested_bar IS
  3  BEGIN
  4    dbms_output.put_line('Executing nested_bar');
  5  END nested_bar;
  6  PROCEDURE nested_foo IS
 10   PRAGMA DEPRECATE(NESTED_FOO , 'procedure nested_foo is deprecated');
 11  BEGIN
 12     dbms_output.put_line('Executing nested_foo');
 13  END nested_foo;
 14  BEGIN
 15    dbms_output.put_line('Executing foo');
 16    nested_bar;
 17  END plw-6022;
 18  /

SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PROCEDURE FOO:

LINE/COL ERROR
-------- ---------------------------------------------------
 8/4 PLW-06006: uncalled procedure "NESTED_FOO" is removed.
10/4 PLW-06022: cannot use PRAGMA DEPRECATE on this entity
PLW-06023 Invocation of string computes trivial value
An IS NULL, IS NOT NULL or NVL operator was used on an object that is either never null or always null. The code may be made more efficient by eliding the call. Rewrite the code to remove the call.
TBD
PLW-06024 Incorrect use of PRAGMA COVERAGE
PRAGMA COVERAGE was encountered in a location in which it did not belong. Rewrite the code so that occurrences of NOT_FEASIBLE_START match occurrences of NOT_FEASIBLE_END. Pragmas may not be nested.
TBD
PLW-06025 Implicit use of NLS session parameters may be a security risk
A conversion to a CHAR or VARCHAR type without the explicit specification of National Language Support (NLS) conversion parameters may introduce security risks. This may occur during implicit conversion, as when non-character types are concatenated. Rewrite implicit conversions to explicitly specify the format model to use in the conversion.
TBD
PLW-06026 Package specification exposes global variable
A global variable was directly declared in a package specification. Rewrite the code to avoid exposing a global variable directly in the package specification, for example, by moving the global variable to the package body and providing a function to access and return its value.
SQL> CREATE OR REPLACE PACKAGE plw06026package AUTHID DEFINER AS
  2    globalVar NUMBER := 1;
  3  END plw06026;
  4  /

SP2-0808: Package created with compilation warnings

SQL> sho err
Errors for PACKAGE PLW06026:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/7 PLW-06026: package specification exposes global variable
 
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.

The 18c docs claim this warning does not exist ... but Oracle is incorrect as shown below.
SQL> CREATE TABLE test (datecol DATE);

Table created.

SQL> CREATE OR REPLACE PROCEDURE plw07202(datestring IN VARCHAR2) AUTHID DEFINER IS
  2  BEGIN
  3    INSERT INTO test VALUES (datestring);
  4    COMMIT;
  5* END plw07202;
  6  /

SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PROCEDURE PLW07202:

LINE/COL ERROR
-------- ------------------------------------------------------------------
3/28 PLW-07202: bind type would result in conversion away from column type
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;
/

sho err
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;
/

sho err
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;
/

sho err
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
PLW-07207 BULK COLLECT used without the LIMIT clause
The BULK COLLECT FETCH did not have a LIMIT clause potentially producing in a memory shortage at run time.
-- this compilation is successful due to the presence of the limit clause

CREATE TABLE airplanes(program_id NUMBER);

CREATE OR REPLACE PROCEDURE plw07207 AUTHID DEFINER IS
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;

 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END plw07207;
/

-- this compilation, without the limit clause, generates a warning
CREATE OR REPLACE PROCEDURE plw07207 AUTHID DEFINER IS
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;

 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END plw07207;
/

sho err

Related Topics
DBMS_WARNING
DBMS_WARNING_INTERNAL
PL/SQL Object Settings