1  PROCEDURE encap_compliance
 2  IS
 3     SUBTYPE qualified_name_t IS VARCHAR2 (200);
 4
 5     TYPE refby_rt IS RECORD (
 6        NAME qualified_name_t
 7      , referenced_by qualified_name_t
 8     );
 9
10     TYPE refby_aat IS TABLE OF refby_rt
11        INDEX BY PLS_INTEGER;
12
13     refby_aa refby_aat;
14  BEGIN
15     SELECT   owner || '.' || NAME refs_table
16            , referenced_owner || '.' || referenced_name
17                                               table_referenced
18     BULK COLLECT INTO refby_aa
19         FROM all_dependencies
20        WHERE owner = USER
21          AND TYPE IN
22                 ('PACKAGE'
23                , 'PACKAGE BODY'
24                , 'PROCEDURE'
25                , 'FUNCTION'
26                 )
27          AND referenced_type IN ('TABLE', 'VIEW')
28          AND referenced_owner NOT IN ('SYS', 'SYSTEM')
29     ORDER BY owner, NAME, referenced_owner, referenced_name;
30
31     disp_header ('Programs that reference tables or views');
32
33     FOR indx IN refby_aa.FIRST .. refby_aa.LAST
34     LOOP
35        DBMS_OUTPUT.PUT_LINE (refby_aa (indx).NAME
36           , refby_aa (indx).referenced_by
37            );
38     END LOOP;
39* END encap_compliance;