htmlsqloracle11gsqlplus

SQL*Plus HTML Markup: Preventing Raw HTML Tags from Appearing in Output File

Output needed in this format:

enter image description hereI am using SQL*Plus to generate an HTML report from an Oracle database using SET MARKUP HTML ON. However, the output file contains raw HTML tags as plain text instead of rendering them properly.

Current Output (Incorrect)

My generated HTML file unexpectedly includes raw HTML tags like this:

'<HTML><HEAD><TITLE>SALES REPORT</TITLE><STYLE>BODY{...};</STYLE></HEAD><BODY><TABLE>'
<html><head><title>Sales Report</title>
<style>
body {background: #ffffff; font-family: Arial, sans-serif;}
table {width: 80%; border-collapse: collapse; margin: 25px auto; font-size: 16px; text- 
align: center;}
th, td {padding: 10px; border: 2px solid #ddd; text-align: center;}
th {background-color: #c90421; color: #ffffff; font-weight: bold;}
tr:nth-child(even) {background-color: #f2f2f2;}
</style>
</head><body><table>

As you can see, the first row is the SQL output treating the HTML tags as text, while the actual HTML table structure appears again correctly.

Expected Output (Correct)

I want only the valid HTML to be written to the file like this:

<html><head><title>Sales Report</title>
<style>
body {background: #ffffff; font-family: Arial, sans-serif;}
table {width: 80%; border-collapse: collapse; margin: 25px auto; font-size: 16px; text- 
align: center;}
th, td {padding: 10px; border: 2px solid #ddd; text-align: center;}
th {background-color: #c90421; color: #ffffff; font-weight: bold;}
tr:nth-child(even) {background-color: #f2f2f2;}
</style>
</head><body><table>

Current SQL*Plus Code

Here’s a simplified version of my script:

SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
SPOOL "D:\Reports\sales_report.html"

SELECT '<html><head><title>Sales Report</title>
<style>
body {background: #ffffff; font-family: Arial, sans-serif;}
table {width: 80%; border-collapse: collapse; margin: 25px auto; font-size: 16px; text- 
align: center;}
th, td {padding: 10px; border: 2px solid #ddd; text-align: center;}
th {background-color: #c90421; color: #ffffff; font-weight: bold;}
tr:nth-child(even) {background-color: #f2f2f2;}
</style>
</head><body><table>' FROM dual;

SELECT '<thead><tr>
<th rowspan="2">TOTAL</th>
<th colspan="2">Revenue</th>
<th rowspan="2">New Customers</th>
<th rowspan="2">Total Sales</th>
<th colspan="2">Performance</th>
</tr>
<tr>
<th>REVENUE_PREV</th>
<th>REVENUE_CURRENT</th>
<th>NEW_SIGNUPS</th>
<th>PERFORMANCE_SCORE</th>
</tr></thead><tbody>' FROM dual;

SELECT '</tbody></table></body></html>' FROM dual;

SPOOL OFF;
EXIT;
Solution

  • Don't try to build the HTML manually and combine it automatically generated HTML markup. Use one or the other. Your code is automatically embedding the output of your queries into an automatically generated HTML page so you end up with your HTML embedded as literals into a HTML template.

    Using Generating Reports from SQL*Plus, something like:

    SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<title>Sales Report</title> -
<style> -
body {background: #ffffff; font-family: Arial, sans-serif;} -
table {width: 80%; border-collapse: collapse; margin: 25px auto; font-size: 16px; text-align: center;} -
th, td {padding: 10px; border: 2px solid #ddd; text-align: center;} -
th {background-color: #c90421; color: #ffffff; font-weight: bold;} -
tr:nth-child(even) {background-color: #f2f2f2;} -
</style>"
   
SPOOL "D:\Reports\sales_report.html"

COLUMN REVENUE_PREV HEADING 'Previous Revenue'
COLUMN REVENUE_CURRENT HEADING 'Current Revenue'
COLUMN NEW_SIGNUPS HEADING 'New Sign-Ups'
COLUMN PERFORNACE_SCORE HEADING 'Performance'

SELECT REVENUE_PREV,
       REVENUE_CURRENT,
       NEW_SIGNUPS,
       PERFORMANCE_SCORE
FROM   your_table;

SPOOL OFF;
EXIT;

    Note: Untested, but it should give you the general idea and you can use the reference to look up other, more detailed examples.