How to get Oracle SQL output in HTML format and send as a Mail Body 


SQL Script to get the output as HTML

SET MARKUP HTML ON SPOOL ON
set termout off
set pages 999
set pagesize 999
set feedback off
SET MARKUP HTML ON TABLE "class=sysaud cellspacing=2 border='2' width='95%' align='center' " ENTMAP OFF

spool fnd_debug_report.html

set pagesize 1000
set pause off
set linesize 150

prompt
prompt FND Debug Profile Enabled Report 
prompt
prompt

/* Below can be any query based on your requirement*/

SELECT upper(sys_context('USERENV','DB_NAME')) "DB_NAME",fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT_NAME",
         fpov.profile_option_value VALUE
    FROM apps.fnd_profile_options fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_profile_options_tl fpot,
         apps.fnd_user fu,
         apps.fnd_application fap,
         apps.fnd_responsibility frsp,
         apps.fnd_nodes fnod,
         apps.hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
                and fpot.language='US'
          and fpot.user_profile_option_name like 'FND: Debug Log Enabled%'
          and fpov.profile_option_value='Y';



spool off
exit

Example:




Sample script to send this as a mail body

. ~/.bash_profile
Inst_name=`echo $TWO_TASK|tr [a-z] [A-Z]`
Mail_id=himanshu@dummy.com
output_report=$HOME/scripts/fnd_debug_report.html
export content=${output_report}
export subject="FND Debug Profile is enabled in ${Inst_name}"
rm -f ${output_report}
cd $HOME/scripts
sqlplus $APPSLOGIN @fnd_debug.sql


if grep --quiet 'Log' $HOME/scripts/fnd_debug_report.html;
then
echo "Sending email"

(
echo "Subject: $subject"
echo "To: $Mail_id"
echo "MIME-version: 1.0"
echo "content-Type: text/html"
echo "content-Disposition: inline"
cat $content
) | sendmail -t
fi