PL/SQL Automated Reporting Tool using UNIX Scripting

Requirement:
We need to generate SQL Reports with color code formatting.

Pre-requisites:
1)      Unix Box
2)      SQL Developer
3)      Basic knowledge of both Shell Scripting and SQL

Normally, we need to generate many SQL reports on daily basis and need to monitor them closely.
So, if we can add some color code to differentiate error and successful rows. It will ease out our monitoring process and of course, increases our efficiency.



Let’s start with the real code.

We have three files here..

1)      Shell Script: From where we call SQL file (to get report) and mail that report to someone.
2)      Report SQL: In this file, we run SQL statement to get Report data and call CSS file to implement color coding on our report.
3)      CSS SQL: In this file, we include all CSS classes what we use further for formatting.






#! /bin/ksh

##########################################################################
# This script is to automate Daily RCR Status Mail.
###########################################################################

if [ -r $HOME/.profile ]
  then
   . $HOME/.profile
 else
  print "environment not available"
fi

TODAY=`date +%m_%d_%y`

LOG_FILE=$LOG_DIR/Daily_RCR_STATUS_${TODAY}.log

##Call SQL File 

var=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
                  #$SQL_SET
                  @Path/Scripts/Daily_RCR_report.sql
                  exit
                  EOF`

##Mail Code
echo "To: dummy-recepent@email.com
From:dummy-alert@email.com
Subject: Daily RCR Status on $sysdate 
MIME-version: 1.0
Content-type: text/html; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
" > ~/some

#echo "$var">> ~/some
cat report.htm >> ~/some
echo "
" >> ~/some
echo "Kindly let us know in case of any queries." >> ~/some
echo "
" >> ~/some
echo "Thanks" >> ~/some
echo "
" >> ~/some
echo "Your Team" >> ~/some


cat ~/some | mail dummy-recepent@email.com
rm ~/some
rm report.htm


-- cat /scripts/Daily_run_report.sql

---connect $DBUSER/$DBPWD@$DBHOST
----call css file here
@/opt/siebel/xcsprod/Xerox/Scripts/set_markup.sql

set heading on
set pages 100
TTITLE LEFT _DATE CENTER '

Daily RCR Report : Your Team 

' -
RIGHT '

Red = Quick Action Required

'

spool report.htm

--Insert SQL statement here, which will fetch data for your report. It uses 'Case-When-Else' of SQL to format color code on report. 

Select child.par_req_id "RCR ID",
CASE
when child.status = 'ERROR' then
''||child.status||''
ELSE
''||child.status||''
END "Status"
,to_char(child.actl_start_dt,'DD/MM/YYYY HH24:MI:SS') "Start Date(EST)",
to_char(child.actl_end_dt,'DD/MM/YYYY HH24:MI:SS') "End Date(EST)",param.value "WF Name",par.rpt_interval||' '||par.rpt_uom "Frequency",child.completion_text "Error Msg"
from siebel.S_SRM_REQUEST par,
siebel.S_SRM_REQUEST child, siebel.S_SRM_REQ_PARAM param
where par.row_id = child.par_req_id
and par.req_type_cd = 'RPT_PARENT'
and  par.row_id = param.req_id
and par.STATUS = 'ACTIVE'
and child.req_type_cd = 'RPT_INSTANCE'
and param.ACTPARAM_ID in ('1-4DTOMO','1-7WXQ','1-7X4F')
and param.value not like '%Pricing%'
and child.actl_end_dt is not null
and child.actl_end_dt in (
select max(actl_end_dt) from siebel.S_SRM_REQUEST
where req_type_cd = 'RPT_INSTANCE'
and actl_end_dt is not null
and par_req_id = par.Row_Id)
order by child.actl_start_dt desc;


spool off

exit



-- cat /scripts/set_markup.sql
set markup HTML ON HEAD " -
SQL*Plus Report" -
BODY "" -
TABLE "border='1' width='90%' align='center'" -
ENTMAP OFF SPOOL ON

Labels: , ,