G-7140: Always ensure that locally defined procedures or functions are referenced.

Major

Maintainability, Reliability

Reason

This can occur as the result of changes to code over time, but you should make sure that this situation does not reflect a problem. And you should remove the declaration to avoid maintenance errors in the future.

You should go through your programs and remove any part of your code that is no longer used. This is a relatively straightforward process for variables and named constants. Simply execute searches for a variable's name in that variable's scope. If you find that the only place it appears is in its declaration, delete the declaration.

There is never a better time to review all the steps you took, and to understand the reasons you took them, then immediately upon completion of your program. If you wait, you will find it particularly difficult to remember those parts of the program that were needed at one point, but were rendered unnecessary in the end.

Example (bad)

CREATE OR REPLACE PACKAGE BODY my_package IS
   PROCEDURE my_procedure IS
      FUNCTION my_func RETURN NUMBER IS
         co_true CONSTANT INTEGER := 1;
      BEGIN
         RETURN co_true;
      END my_func;
   BEGIN
       NULL;
   END my_procedure;
END my_package;
/

Example (good)

CREATE OR REPLACE PACKAGE BODY my_package IS
   PROCEDURE my_procedure IS
      FUNCTION my_func RETURN NUMBER IS
         co_true CONSTANT INTEGER := 1;
      BEGIN
         RETURN co_true;
      END my_func;
   BEGIN
       sys.dbms_output.put_line(my_func());
   END my_procedure;
END my_package;
/