The view USER_DEPENDENCIES shows all dependencies for objects including procedures, functions, package specifications, and package bodies. Query this view to see all PL/SQL procedures that are dependent on a table you are about to drop. The description is:

SQL> desc user_dependencies
 Name                           Null?    Type
 ------------------------------ -------- ---------------
 NAME                           NOT NULL VARCHAR2(30)
 TYPE                                    VARCHAR2(12)
 REFERENCED_OWNER                        VARCHAR2(30)
 REFERENCED_NAME                         VARCHAR2(64)
 REFERENCED_TYPE                         VARCHAR2(12)
 REFERENCED_LINK_NAME                    VARCHAR2(128)
 SCHEMAID                                NUMBER
 DEPENDENCY_TYPE                         VARCHAR2(4)

If you write a procedure, HELLO, that inserts a row in the STUDENTS table, there will be a row in this view with NAME=HELLO and REFERENCED_NAME=STUDENTS.

NAME ...

Get Programming Oracle® Triggers and Stored Procedures, Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.