PLSQL - Simple excel file output created for sql query through UTL file command
/************************************************************************
Name : XX01_CONC_ERR_REPORT.sql
Program Type : Anonymous PLSQL
Process Type : DML process
Author : --
Created On : 09-FEB-2016
Purpose : Create Concurrent error reports in .xls format
Called By : Program name
Calls : none
Spec Doc : none
Tables/views : custom table xx01
Change History :
-----------------------------------------------------------------------
Module Modified By Modified On
Version Number Modification
-----------------------------------------------------------------------
-----------------------------------------------------------------------
Failure Recovery Strategy
-----------------------------------------------------------------------
-- select statements only. No recovery strategy needed
-----------------------------------------------------------------------
-----------------------------------------------------------------------
Description of the program
-----------------------------------------------------------------------
This program will create a .csv report detailing information about
concurrent program errrors. It will write this report to a file in the
$XX01_TOP/out directory called "XX01_REPORT.csv".
----------------------------------------------------------------------
-----------------------------------------------------------------------
Notes / Comments
-----------------------------------------------------------------------
--
-----------------------------------------------------------------------*/
--This program must print error ,progress, other information like datafile name,logfile name
--and other debugging information in the concurrent manager log or some other log
SET SERVEROUTPUT ON SIZE 1000000
whenever sqlerror exit 1 rollback
DECLARE
--- passed parameters
v_startDate DATE := trunc(to_date('&1','YYYY/MM/DD HH24:MI:SS'));
v_endDate DATE := trunc(to_date('&2','YYYY/MM/DD HH24:MI:SS'));
v_moduleName varchar2(10) := '&3';
v_moduleNameHead varchar2(10) :='';
v_rFlag varchar2(3) := '&4';
v_rFlagHead varchar2(5) := '';
v_incFalseErrors varchar2(3) := '&5';
v_incFalseErrorsHead varchar2(5) := '';
v_dataBase varchar2(10) := '&6';
v_date DATE;
v_rCount NUMBER := 0;
--- utl_file paramters
v_buffer varchar2(32000);
v_colNames varchar2(400);
v_fileHandle utl_file.file_type;
v_fileLocation varchar2(50) := 'XX01_OUT';
v_fileName varchar2(50) := 'XX01_AUDIT_REPORT.xls';
v_title varchar2(100) := 'CONCURRENT ERROR LOGS REPORT';
/*
The two cursors behave relatively the same.
The single cursor will fetch data relevenat for the report for a single
module. The all cursor will fetch data for all modules that are in error table.
*/
cursor conc_error_report_all is
SELECT group_id,
child_request_flag,
request_id,
parent_request_id,
requested_start_date,
rtrim(ltrim(program_name)) program_name,
app_short_name,
actual_start_date,
actual_completion_date,
completion_text,
requested_by,
status,
reason,
impact,
feedback_by,
feedback_date,
reviewed_flag,
reviewed_by,
review_date
FROM XX01_ERROR_LOG
WHERE requested_start_date BETWEEN v_startDate AND v_endDate
AND app_short_name = NVL(v_moduleName,app_short_name)
AND reviewed_flag = NVL(v_rFlag,reviewed_flag)
AND false_error_flag = NVL(v_incFalseErrors,false_error_flag)
order by group_id, request_id, parent_request_id,actual_start_date,app_short_name;
-- end of decalration for all_module cursor --
BEGIN
IF (v_moduleName IS NULL)
then
v_moduleNameHead := 'ALL';
else
v_moduleNameHead := v_moduleName;
END IF;
IF (v_rFlag IS NULL)
then
v_rFlagHead := 'ALL';
else
v_rFlagHead := v_rFlag;
END IF;
IF (v_incFalseErrors IS NULL)
then
v_incFalseErrorsHead := 'ALL';
else
v_incFalseErrorsHead := v_incFalseErrors;
END IF;
dbms_output.put_line('Beginning the Concurrent Error Report');
dbms_output.put_line(' ');
-- list all the parameters into log for debugging
dbms_output.put_line('Start Date: '||v_startDate);
dbms_output.put_line('End Date: '||v_endDate);
dbms_output.put_line('Module: '||v_moduleName);
dbms_output.put_line('Reviewed Flag: '||v_rFlag);
dbms_output.put_line('Include False Errors Flag: '||v_incFalseErrors);
-- get the sysdate here for the report file
BEGIN
select sysdate
into v_date
from dual;
END;
-- open file for writing
v_fileHandle := utl_file.fopen(v_fileLocation,v_fileName, 'W');
-- write title and control info ( date, DB name )
utl_file.put_line(v_fileHandle,v_title);
utl_file.put_line(v_fileHandle,'Report Run Date: '||v_date||' '||
'Report Database: '||v_dataBase);
-- write the paramter list to the xls data file
utl_file.put_line(v_fileHandle,'PARAMETER LIST ');
v_buffer := 'START DATE|END DATE|MODULE|REVIEWED BY FLAG|INCLUDE FALSE ERRORS FLAG';
utl_file.put_line(v_fileHandle,v_buffer);
v_buffer := v_startDate||'|'||v_endDate||'|'||v_moduleNameHead||'|'||v_rFlagHead||'|'||v_incFalseErrorsHead;
utl_file.put_line(v_fileHandle,v_buffer);
utl_file.put_line(v_fileHandle,' ');
-- write the column headers to the file
v_colNames := 'GROUP ID|CHILD REQUEST FLAG|REQUEST ID|PARENT REQUEST ID|REQUEST DATE|PROGRAM NAME|MODULE|ACTUAL START DATE|ACTUAL COMPLETION DATE|COMPLETION TEXT|REQUESTED BY|STATUS|REASON|IMPACT|FEEDBACK BY|FEEDBACK DATE|REVIEWED FLAG|REVIEWED BY|REVIEW DATE';
utl_file.put_line(v_fileHandle,v_colNames);
-- begin cursor loop --
FOR conc_error_report_rec IN conc_error_report_all LOOP
v_buffer:= conc_error_report_rec.group_id||'|'||
conc_error_report_rec.child_request_flag||'|'||
conc_error_report_rec.request_id||'|'||
conc_error_report_rec.parent_request_id||'|'||
conc_error_report_rec.requested_start_date||'|'||
conc_error_report_rec.program_name||'|'||
conc_error_report_rec.app_short_name||'|'||
conc_error_report_rec.actual_start_date||'|'||
conc_error_report_rec.actual_completion_date||'|'||
conc_error_report_rec.completion_text||'|'||
conc_error_report_rec.requested_by||'|'||
conc_error_report_rec.status||'|'||
conc_error_report_rec.reason||'|'||
conc_error_report_rec.impact||'|'||
conc_error_report_rec.feedback_by||'|'||
conc_error_report_rec.feedback_date||'|'||
conc_error_report_rec.reviewed_flag||'|'||
conc_error_report_rec.reviewed_by||'|'||
conc_error_report_rec.review_date;
--dbms_output.put_line(v_buffer);
utl_file.put_line(v_fileHandle,v_buffer);
v_rCount :=conc_error_report_all%ROWCOUNT;
END LOOP;
-- check for # of rows in cursor. If 0, then write error message to file to be mailed
dbms_output.put_line('ROW COUNT: '||v_rCount);
IF(v_rCount = 0)
THEN
utl_file.put_line(v_fileHandle,' ');
utl_file.put_line(v_fileHandle,'NO DATA RETURNED FOR PARAMETER LIST:' );
utl_file.put_line(v_fileHandle,' ');
utl_file.put_line(v_fileHandle,'START DATE: '||v_startDate);
utl_file.put_line(v_fileHandle,'END DATE: '||v_endDate);
utl_file.put_line(v_fileHandle,'MODULE: '||v_moduleName);
utl_file.put_line(v_fileHandle,'REVIEWED BY FLAG: '||v_rFlag);
utl_file.put_line(v_fileHandle,'INCLUDE FALSE ERRORS FLAG: '||v_incFalseErrors);
END IF;
utl_file.fclose(v_fileHandle);
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN others THEN
dbms_output.put_line(to_char(sqlcode));
dbms_output.put_line(sqlerrm);
END;
/
exit
