Create a Status File

Someone requested a separate file that showed whether a process was successful or not, and the date that it ran. Since Oracle error messages (ORA-xxx) are written to your spool file, I create an external table that would read each line of output looking for 'ORA' in the first 3 positions.

-------------------------------------------------------------------------------------
-- Create an external table that will be used to read the spooled output.
-------------------------------------------------------------------------------------
Drop Table Ext_SpoolStatus;
Create Table Ext_SpoolStatus
(  SpoolLine   Varchar2(2000) )
Organization External
(  Type Oracle_Loader
  Default Directory Ext
  Location (Ext:'Test.Txt') )
Reject Limit Unlimited;
-------------------------------------------------------------------------------------
-- Turn off everything and dump the data to a file
-------------------------------------------------------------------------------------
Set Echo Off Term Off Pagesize 0 Feedback Off Trimspool On

Spool Test.txt
Select '1', 'A'
 From Dual;
Spool Off
-------------------------------------------------------------------------------------
-- Turn the screen back on. Also set up the counter for one space in the message.
-------------------------------------------------------------------------------------
Set Term On
Column ErrorCnt Format 9
-------------------------------------------------------------------------------------
-- Spool your status file
-------------------------------------------------------------------------------------
Spool TestStatus.txt
-------------------------------------------------------------------------------------
-- Put a message on the front of the count from your previous output file based on the
-- number of 'ORA' messages, and then put in the date and time.
-------------------------------------------------------------------------------------
Select 'Your code ran with ' ||
                (Select Count(*) As ErrorCnt
                  From Ext_SpoolStatus
                 Where Substr(SpoolLine,1,3) = 'ORA')
       || ' Error(s) On ' || To_Char(Sysdate, 'Mm/Dd/Yy Hh24:Mi')
  From Dual;
-------------------------------------------------------------------------------------
-- Add the actual error messages to the status file if you like
-------------------------------------------------------------------------------------
Select SpoolLine
 From Ext_SpoolStatus
Where Substr(SpoolLine,1,3) = 'ORA';

Spool Off