Generate report in HTML file from Oracle DB | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

Generate report in HTML file from Oracle DB

Shell Programming and Scripting


Tags
pearl, report generation, unix

Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 10-17-2008
Amit.Sagpariya Amit.Sagpariya is offline
Registered User
 
Join Date: Sep 2008
Last Activity: 11 July 2011, 4:29 PM EDT
Posts: 136
Thanks: 0
Thanked 0 Times in 0 Posts
Generate report in HTML file from Oracle DB

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
Sponsored Links
    #2  
Old 10-18-2008
radoulov's Avatar
radoulov radoulov is offline Forum Staff  
Moderator
 
Join Date: Jan 2007
Last Activity: 17 April 2014, 10:17 AM EDT
Location: Варна, България / Milano, Italia
Posts: 5,638
Thanks: 177
Thanked 607 Times in 565 Posts
Java

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


Code:
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"


Last edited by radoulov; 10-18-2008 at 05:46 PM..
Sponsored Links
    #3  
Old 10-19-2008
dennis.jacob's Avatar
dennis.jacob dennis.jacob is offline Forum Advisor  
Registered User
 
Join Date: Feb 2007
Last Activity: 30 March 2014, 9:33 AM EDT
Location: Singapore/Cochin
Posts: 871
Thanks: 0
Thanked 12 Times in 11 Posts
Quote:
Originally Posted by Amit.Sagpariya View Post
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
Please paste a portion of data and the html format you ar elookng for.
    #4  
Old 10-20-2008
Amit.Sagpariya Amit.Sagpariya is offline
Registered User
 
Join Date: Sep 2008
Last Activity: 11 July 2011, 4:29 PM EDT
Posts: 136
Thanks: 0
Thanked 0 Times in 0 Posts
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
Sponsored Links
    #5  
Old 10-20-2008
radoulov's Avatar
radoulov radoulov is offline Forum Staff  
Moderator
 
Join Date: Jan 2007
Last Activity: 17 April 2014, 10:17 AM EDT
Location: Варна, България / Milano, Italia
Posts: 5,638
Thanks: 177
Thanked 607 Times in 565 Posts
This should work:


Code:
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"

Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
generate a report gustave Shell Programming and Scripting 2 03-21-2010 04:44 AM
Re-Generate error report Dardeer AIX 5 09-28-2009 06:03 AM
generate a report sailaja_80 Shell Programming and Scripting 8 08-21-2009 01:12 PM
how to generate html file using script? kittusri9 Shell Programming and Scripting 2 05-25-2008 11:38 PM
Help generate report from log files bucci Shell Programming and Scripting 4 08-24-2007 04:11 AM



All times are GMT -4. The time now is 01:52 AM.