Identify out of scope references, out of scope references should be avoided

Instead of this...

CREATE OR REPLACE PROCEDURE my_proc AUTHID DEFINER
IS
   TYPE numbers_t IS TABLE OF NUMBER;

   l_numbers   numbers_t;

   PROCEDURE double_up
   IS
   BEGIN
      FOR indx IN l_numbers.FIRST .. l_numbers.LAST
      LOOP
         l_numbers (indx) := 2 * l_numbers (indx);
      END LOOP;
   END;
BEGIN
   l_numbers := numbers_t (1,2,3,4,5);

   double_up;
END;
/

do this...

CREATE OR REPLACE PROCEDURE my_proc AUTHID DEFINER
IS
   TYPE numbers_t IS TABLE OF NUMBER;

   l_numbers   numbers_t;

   PROCEDURE double_up (numbers_io IN OUT numbers_t)
   IS
   BEGIN
      FOR indx IN numbers_io.FIRST .. numbers_io.LAST
      LOOP
         numbers_io (indx) := 2 * numbers_io (indx);
      END LOOP;
   END;
BEGIN
   l_numbers := numbers_t (1,2,3,4,5);

   double_up (l_numbers);
END;
/

Identify out of scope references

So suppose I execute the following statements (first, I enable collection of identifier data for PL/Scope, then I compile a package). Out of scope references marked in orange.

ALTER SESSION SET plscope_settings='identifiers:all'
/

CREATE OR REPLACE PACKAGE plscope_demo AUTHID DEFINER
IS
   g_global   NUMBER;

   PROCEDURE my_procedure (
      param1_in   IN INTEGER,
      param2      IN employees.last_name%TYPE);
END plscope_demo;
/

CREATE OR REPLACE PACKAGE BODY plscope_demo
IS
   g_private_global   NUMBER;

   PROCEDURE my_procedure (
      param1_in   IN INTEGER,
      param2      IN employees.last_name%TYPE)
   IS
      c_no_such       CONSTANT NUMBER := 100;
      l_local_variable         NUMBER;
      another_local_variable   DATE;
      
      FUNCTION return_local RETURN NUMBER
      IS
      BEGIN
         RETURN l_local_variable;
      END;
   BEGIN
      IF param1_in > l_local_variable
      THEN
         DBMS_OUTPUT.put_line (param2);
         g_global := 100;
      ELSE
         DBMS_OUTPUT.put_line (c_no_such);
      END IF;
      
      IF c_no_such IS NOT NULL
      THEN 
         g_private_global := 1;
      END IF;
   END my_procedure;
END plscope_demo;
/

I can then run the following query to identify variables, constants and exceptions that are referenced out of scope:

WITH declared_in
     AS (SELECT decl.NAME variable_name,
                ctxt.TYPE || '-' || ctxt.NAME declared_in
           FROM USER_IDENTIFIERS decl, USER_IDENTIFIERS ctxt
          WHERE     decl.USAGE_CONTEXT_ID = ctxt.USAGE_ID
                AND decl.TYPE IN ('CONSTANT',
                                  'VARIABLE',
                                  'EXCEPTION')
                AND decl.USAGE = 'DECLARATION'
                AND decl.OBJECT_NAME = ctxt.OBJECT_NAME
                AND decl.OBJECT_TYPE = ctxt.OBJECT_TYPE),
     vars_used_in
     AS (SELECT decl.NAME variable_name,
                ctxt.TYPE || '-' || ctxt.NAME referenced_in
           FROM USER_IDENTIFIERS decl, USER_IDENTIFIERS ctxt
          WHERE     decl.USAGE_CONTEXT_ID = ctxt.USAGE_ID
                AND decl.TYPE IN ('VARIABLE',
                                  'EXCEPTION')
                AND decl.USAGE IN ('REFERENCE', 'ASSIGNMENT')
                AND decl.OBJECT_NAME = ctxt.OBJECT_NAME
                AND decl.OBJECT_TYPE = ctxt.OBJECT_TYPE),
     constants_used_in 
     AS (SELECT decl.NAME variable_name,
                ctxt.TYPE || '-' || ctxt.NAME referenced_in
           FROM USER_IDENTIFIERS decl, USER_IDENTIFIERS ctxt
          WHERE     decl.USAGE_CONTEXT_ID = ctxt.USAGE_ID
                AND decl.TYPE = 'CONSTANT'
                AND decl.USAGE = 'REFERENCE'
                AND decl.OBJECT_NAME = ctxt.OBJECT_NAME
                AND decl.OBJECT_TYPE = ctxt.OBJECT_TYPE)
(SELECT * FROM vars_used_in UNION SELECT * FROM constants_used_in) 
MINUS
SELECT * FROM declared_in
/

Here's my output:

Now there is certainly more to be done here: add line numbers to make it easy to locate the out of scope ref, and more. 

I hope you all have enjoyed reading this article. Comments are welcome....