Check dependencies for procedure : user_dependencies « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. user_dependencies

SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );

Table created.

SQL> create or replace procedure P1 is
  2   v_cnt number;
  3   begin
  4       select count(*) into v_cnt from emp;
  5   end;
  6  /



SQL> create or replace procedure P2 is
  2   begin
  3       P1;
  4   end;
  5  /


SQL> create or replace procedure P3 is
  2   begin
  3       P2;
  4   end;
  5  /


SQL> create or replace procedure P4 is
  2   begin
  3       P3;
  4   end;
  5  /


SQL>
SQL> select name, type,referenced_name, referenced_type from user_Dependencies where rownum < 20
  2  /
Enter...

SQL>