René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

August 19, 2006: On generating HTML output with SQL*Plus

SQL*Plus generates HTML output if it is invoked with the -m flag.
In order to demonstrate that, I am going to create a table: 
create table html_output_ex (
  a number,
  b varchar2(20),
  c date
);
I insert some values into the table. Some values contain HTML markup (<b>, </b> <i> and </i>): 
insert into html_output_ex values (1, 'foo'          , sysdate - exp(1));
insert into html_output_ex values (2, '<b>bold</b>'  , sysdate - exp(2));
insert into html_output_ex values (3, '<i>italic</i>', sysdate - exp(3));

commit;
Here's the script that I will execute from within SQL*Plus. It is very simple: a select statement followed by the SQL*Plus exit command so that SQL*Plus returns to the prompt when the select statement has finished.
gen_html.sql
select a, b, c from html_output_ex;

exit
I execute SQL*Plus and pass the following parameters:
  • -r 3
    because I don't want to have the login.sql executed when SQL*Plus is started.
  • -l
    because I don't want SQL*Plus to ask twice for the password if I specify the wrong one.
  • -s
    because I don't want the prompt or the SQL*Plus banner in the output
  • -m
    because I want markup output. I pass entmap off to preserve the < and > in the output:
  • @
    because I want to execute the gen_html.sql script. 
C:\>sqlplus -r 3 -l -s -m "html on entmap off" rene/rene @gen_html > output_entmap_off_.html
The generated output is displayed in the following <iframe>. Because I have specified entmap off, the strings 'bold' and 'italic' appear bold and italic, respectively:
Now, I do the same thing, but with entmap on; 
C:\>sqlplus -r 3 -l -s -m "html on entmap on" rene/rene @gen_html > output_entmap_on_.html
This returns the strings as actually inserted into the table, that is the < and > are translated into &lt; and &gt;

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.