Generating HTML from SQL*Plus
============================
State of the art has moved on since SQL*Plus was written. There are pretty and sexy Graphical User Interfaces to play with in both the Operating System and tools that we use every day. However, SQL*Plus is not dead yet – Did you know that you can produce HTML reports using SQL*Plus without any knowledge of HTML? I’ll explain to get started producing HTML output from SQL*Plus and I’ll give a brief snippet of code that can be used to email these reports from UNIX.
The innocuous command that Oracle introduced to SQL*Plus back in release 8.1.6 is SET MARKUP. Here’s an example of using that feature in SQL*Plus:
set markup HTML on
spool session.html
select username, status from v$session;
spool off
set markup HTML off
select username, status from v$session;
What about Style?
======================
You can use the HEAD option to specify HTML tags to beautify the output. In the next example, we’ll use a tittle and define an internal style sheet:
set markup html on
head ‘ -
-
table {background: #eee; fone-size: 80% } -
th { background: #ccc: } -
td { padding: 0px; } -
‘
spool result.html
set termout off
set pagesize 80
column oprid heading ‘Operator ID’
column oprdefndesc heading ‘User Name’
column salary heading ‘Salary’ format $99,990
select oprid, oprdefndesc, acctlock, prcsprflcls
from sysadm.psoprdefn
order by oprdefndesc
/
spool off
set markup html off
In release 8.1.6 the full syntax is as follows:
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF] [SPOOL [ON|OFF] [PRE[FORMAT] {ON|OFF}]
These options are briefly described below:
You can define:
HTML [ON|OFF] programmatically to control if output is marked up as HTML
[HEAD text] content for the tag
[BODY text] attributes for the tag
[TABLE text] attributes for the TABLE tag
[ENTMAP {ON|OFF}] toggles entity mapping, if entity mapping is off, web browsers may treat data as invalid HTML – more on this later!
[SPOOL {ON|OFF}] controls whether SQL*Plus writes opening and closing HTML and BODY tags for spooled files
[PRE[FORMAT] {ON|OFF}] controls whether SQL*Plus uses PRE tags or TABLE tags
Entity Mapping
====================
The HTML language defines a special meaning for crtain characters. For example, ” are used to define a HTML tag. In order to display these special characters, literally equivalent entities have been defined. For example, ", &, < and >. The ENTMAP option can be defined in the MARKUP command, or controlled for individual columns with the COLUMN command. This is particulary useful if you wish to dynamically generate a URL from the output of a SQL Query.
In the above example, we might wish the PRCSPRFLCLS to be a link to the PRCSPRFLCLS table.
set markup html on
head ‘ -
-
table {background: #eee; fone-size: 80% } -
th { background: #ccc: } -
td { padding: 0px; } -
‘
spool result2.html
set termout off
set pagesize 80
column oprid heading ‘Operator ID’
column oprdefndesc heading ‘User Name’
column prcsprflcls heading ‘Process Profile’ entmap off
select oprid, oprdefndesc, acctlock,
‘‘||prcsprflcls||’‘ as prcsprflcls
from sysadm.psoprdefn
order by oprdefndesc
/
spool off
set markup html off
The above change in code will produce a hyperlink for the PRCSPRFLCLS values.
MIME Types
================
Often there are requirements to e-mail reports. With html based e-mail, you can send easy to read, high quality reports using standard mail programs. In UNIX the standard mail program varies by flavor and version of the OS. In our environments the default mail program is mailx for the command line and elm for simple interactive mail.
Without the correct MIME type (a header string at the beginning of your message), email clients won’t know how to interpret the remainder of the message. In this case, the message is usually considered to be unencoded and simply displayed as ASCII text.
To overcome this problem, we can use the following snippet of a shell script that can be used to add the MIME type to the header message of an e-mail message:
#!/bin/sh
NOTIFY=”repettas@vanguardcar.com”
wrkfile=$HOME/repetts/`basename $0.sh`_$$.wrk
touch wrkfile
# trap “rm -f $wrkfile $wrkfile2″ 0 1 2 15
MAIL_HDR=”Operator ID Report for `echo $ORACLE_SID` at `date` “
echo “From; `hostname`
TO: $NOTIFY
Reply-To: repettas@vanguardcar.com
Subject: $MAIL_HDR
Content-Type: text/html\n” > ${wrkfile}
echo “@stan_html2.sql
quit” | sqlplus -s username/password >> ${wrkfile} 2>&1
cat $wrkfile | /bin/mailx $NOTIFY
# sqlplus -S username/password @stan_html2.sql >> ${wrkfile} 2>&1
# exit
# cat $wrkfile | /bin/mailx $NOTIFY
In this case, the script will generate the required HTML file and this shell script will add the MIME details and email it out.
NOTE: The above snippet emails but on my Apple the body of the message reflects a problem with the SQL script execution. The SQL Script – stan_html2.sql appears to execute fine from the command line without any problems, I can open the html output file produced by the script and it looks good but I’m getting an error when I run the same script via the shell script. I’ll figure out what the problem is and repost this blog entry.
Conclusion
===============
SQL*Plus can produce output in acceptable HTML format. This can be used to produce html reports, with active hyperlinks. The reports can be distributed via e-mail and retain their format.
