On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Thursday, July 24, 2008

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

No comments:

Post a Comment