Generate report in HTML file from Oracle DB

Shell Programming and Scripting
1

Hi Team,
I need a suggestion/comments for my below requirement.
I have a procedure which performs some DDL operations & loads data into a Oracle table. This status contains Audit data.
What i wanted to do is, once the procedure is completed (daily), shell script should retrive the data from the table, & put the data in html file. Once html file is generated, it should attached it in mail & send it to customer.
I have heard use of Unix & pearl scripting for this. But not getting required information, on how to do it?
I am calling Oracle procedure from Shell Script.
Thanks in advance for your hints/suggestions.
Regards,
ACE

2

You could use something like this and of course you can tweak your html code (see Tanel Poder's sqlplus htmlizer for example).

report="report.htm"
boss_email="boss_email"

sqlplus -s <<!
/ as sysdba
set markup html on 
set feed off head off pages 0 lines 200
spool "$report"
select * from owner.table_name;
!

[ -f "$report" ] && uuencode "$report" "$report" | mailx -s "Your report" "$boss_email"
3

Please paste a portion of data and the html format you ar elookng for.

4

Hi Radoul,

Thank you for your suggestion. This is what i wanted to do & also it is a short one.

One more thing which i need to know about.

  1. I can create a html file & it is working fine. But it also shows the query at top of the html file & ii shows total number of fetched rows at end of the html file. which i need to delete.

  2. While sending mail to user, i wanted to send it to multiple user & also mail should contain body. I have tried using above code, but it is not able to add the body & not able to send it to multiple user.

body should be like...

Hi All,

Please find attached status report for the "date";

Thanks,

xyz team

5

This should work:

report="report.htm"
mailto="one_at_domain.com two_at_domain.com"


sqlplus -s <<! >/dev/null
/ as sysdba
set feed off head off pages 0 lines 200
set markup html on
spool $report
select * from owner.table_name;
!

[ -f "$report" ] && {
cat <<-\!
  Your message here
!
uuencode "$report" "$report"
  } | mailx -s "Your report" "$mailto"