Link to home
Start Free TrialLog in
Avatar of YZlat
YZlatFlag for United States of America

asked on

KSH script to output report form SQL Plus

I have the following script creating an html file with data from an Oracle view, via SQL PLus:

#!/usr/bin/ksh

export ORACLE_SID=DBName
ORAENV_ASK=NO
. oraenv
sqlplus -s /nolog <<EOF
CONNECT usr1/pwd
SET LINES 4000 TRIMS ON FEED OFF PAGES 0
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL myfile.html
SELECT * FROM my_view;
SPOOL OFF
EOF

mail -s "Subject" user@example.com < myfile.html

Open in new window


I have noticed that my report does not include column headings and was wondering how can I add them?

That's number 1, number 2 is that the e-mail gets sent in text format, not html format. How can I change that?
SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Docteur_Z
Docteur_Z
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of YZlat

ASKER

Mike, setting pages to 49999 didn't change anything
Avatar of YZlat

ASKER

Docteur_Z, with your code the html file came as an attachment to the e-mail and not as a body of the e-mail, It is good but is not exactly what I wanted
Some implementation of mail have the --append option :
mail --append="Content-type: text/html" -s "Built notification" address@example.com 

Open in new window

If it hasn't got it, the only solution I see, is to "forge" the mail :
cat <<'EOF' test.html | mutt -H -
To: to@address
Subject: hello
Content-Type: text/html

EOF

Open in new window

Taken from
For the colums header, did you tried the
set head on

Open in new window


And If you have size issues :
column colA heading "<p style='width:800px'>800SetColA</p>" format a40

Open in new window

Avatar of YZlat

ASKER

how can I see which options does the mail I use have?

man mail

didn't work
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of YZlat

ASKER

johnsone, thatnks for pointing that out! I just noticd it did. Great because i was very surprised something MikeOM_DBA suggested did not work, usually he is 100% on point:)