Sql report in html & sending mail in linux

Introduction

Being part of the  Oracle community on the web is always a great experience to me. Great because it is inspiring since  a lot of the stories, blogs and  tweets that are shared by the colleagues in the field are top notch. This week a came across a  great tweet from one of my favorite bloggers : Uwe Hesse  (https://uhesse.com/2011/06/30/sqlplus-output-in-nice-html-format/) in which he shared how to create sql output in html format. If you do not follow his blogs yet , please do  because it is great. So of course had to test it myself since it is o so familiar that getting the data is one thing but a nice way to present is is a different story ( with all the set lines 999, col x  format y etc).

Details:

The scenario for this is great:

  1. Run your report( sql) as you are used to
  2. run this html part after.
  3. Either open Firefox with the html file so show – share the info ( or as in my aim find a way to send it from Linux box as an attachment).

In his blog Uwe showed in the sql script that is used in his post that you either run this kind of work either from a client (being a pc or a (l)Unix because once the job is done , the results are  loaded the report in Firefox).

set termout off

set markup HTML ON HEAD ” –

” –
BODY “” –
TABLE “border=’1′ align=’center’ summary=’Script output'” –
SPOOL ON ENTMAP ON PREFORMAT OFF

spool myoutput.html

l
/

spool off
set markup html off spool off
host firefox myoutput.html
set termout on;
quit;

On  the Linux boxes where I work most Firefox is not present. So instead of — host Firefox I am aiming to sending the output as a mail attachment. So far did a lot of reading and tried various suggestions that i should use mailx  -a  but that is not a valid parameter in this linux Redhat 5X release. Another option offered on the web was using uuencode  which I also cannot use because it is not in place. And last but not least also another great suggest to use mutt  failed me too (also not in place). After the weekend will talk to Linux colleagues at work if  there is another option. so  that matter is still 2 b continued . And of course if there is another solution this will be shared.

But when looking at this as an example , the work will be worth the effort! Thank you Uwe for this great tweet and Blog post (https://uhesse.com/2011/06/30/sqlplus-output-in-nice-html-format/).

Archivelogs per hour – day.

archives-script

report-in-cool-htm-format

To be continued…

As always,

Happy reading,

Mathijs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s