Write Oracle Loop in a Script File

Typically any code I write in Oracle it is most often in a procedure or function in a package. At times a stand alone procedure.

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
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') 
  dbms_output.put_line('constraint_name' || i.constraint_name);
end loop;

end; -- End Coding

Here is the DBMS Output from the query code above.


Oracle Find Invalid Objects (Packages, Views, Procedures, Functions...)

Working on database refreshes can be very time consuming to detect invalid objects when Oracle Database Links have been changed. 

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 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
     cursor v_cur is select * from all_views where owner != 'SYS';
   for v_rec in v_cur
       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;

How To Subtract Minutes and Days From an Oracle Timestamp

The following SQL will subtract 15 minutes from an Oracle Timestamp, and 20 hours from a timestamp.

--15 Minutes Ago
select systimestamp, 
          cast(to_char(sysdate - (15/(24*60)),'dd-mon-yyyy hh:mi:ss') as timestamp) 
from dual;

--Yesterday 20 hours ago
select systimestamp, 
          cast(to_char(sysdate - (1200/(24*60)),'dd-mon-yyyy hh:mi:ss') as timestamp) 
from dual;