The coding performed in an Oracle Procedure can be done in a script file but since I do not do that often I created this post for a copy-paste sample that can be run as a script.

--Declare the VARS outside the begin
declare 
ln_count number;

begin -- Start of Coding

for i in (select constraint_name
          from all_constraints
          where constraint_type = 'R'
          and owner = 'MDSYS'
          and status = 'ENABLED') 
  LOOP
    
  dbms_output.put_line('constraint_name' || i.constraint_name);
  
end loop;

end; -- End Coding

The one that causes the most frustration is looking for Oracle Views that have an invalid database link, because the database link is stores as a LONG type. The queries below will find all the invalid objects including views and provide a list of objects to recompile.  I have a uses  global recompile script and will post at a later time. This will search objects looking for either invalid objects or specific values (such as database links in views) 

 

--------------------------------------------
--Get Invalid Objects
--------------------------------------------
select distinct o.OWNER, o.OBJECT_TYPE, o.OBJECT_NAME
from  dba_objects o
where o.OWNER in ('XXXX','XXXXX')
and o.OBJECT_TYPE in ('PACKAGE','PACKAGE BODY','TRIGGER','VIEW','PROCEDURE','FUNCTION')
and status != 'VALID'
order by 1;

--------------------------------------------
--Look for any code that has DB Link of name XXXXXX
--------------------------------------------
select *
from DBA_Source s
where lower(s.text) like '%XXXXXX%'

--VIEWS TWO OPTIONS 1: Dump to XLS or 2: Run a Cursor

--------------------------------------------
-- 1. Export to Excel and search the XLS Doc
--------------------------------------------
select v.owner, v.VIEW_NAME, v.text
from DBA_Views v
where OWNER in ('XXXXX','XXXXX')
order by 1,2;

 --------------------------------------------------
 -- 2. Run Cursor to DBMS Dump
 --------------------------------------------------
declare 
     cursor v_cur is select * from all_views where owner != 'SYS';
 begin
   for v_rec in v_cur
    loop
       if (instr(lower(v_rec.text), 'XXXXX') > 0) then
            dbms_output.put_line('Match found in ' || v_rec.owner || '.' || v_rec.view_name);
       end if;
   end loop;
end;