How to write multi-line headers in html output file of oracle sqlplus? - Stack Overflow

admin管理员组

文章数量:1398975

The output I need (I need to add multi-line headers):

The output I am getting:

My Query:

-- Enable SQL*Plus HTML Markup
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
SPOOL "C:\Grocery_data.html"

-- Define HTML Styling
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<title>Application Count</title> -
<style> -
body {background: #ffffff; font-family: Arial, sans-serif;} -
table {width: 100%; border-collapse: collapse; margin: 25px auto; font-size: 16px; text-align: center;} -
th, td {padding: 8px; border: 2px solid #ddd; text-align: center; white-space: nowrap;} -
th {background-color: #c90421; color: #ffffff; font-weight: bold;} -
td {min-width: 50px;} -
tr:nth-child(even) {background-color: #f2f2f2;} -
</style>"

-- Inject custom HTML table header
SET MARKUP HTML OFF
PROMPT <table>
PROMPT <thead>
PROMPT <tr>
PROMPT <th colspan="4" style="width:460px;">Fruits</th>
PROMPT <th colspan="3" style="width:350px;">Vegetables</th>
PROMPT <th colspan="2" style="width:300px;">Desserts</th>
PROMPT </tr>
PROMPT </thead>
PROMPT <tbody>
SET MARKUP HTML ON

COLUMN BANANA_DATA HEADING 'Banana'
COLUMN APPLE_DATA HEADING 'Apple'
COLUMN MANGO_DATA HEADING 'Mango'
COLUMN CHERRY_DATA HEADING 'Cherry'
COLUMN POTATO_DATA HEADING 'Potato'
COLUMN BRINJAL_DATA HEADING 'Brinjal'
COLUMN BEANS_DATA HEADING 'Beans'
COLUMN TIRAMISU_DATA HEADING 'Tiramisu'
COLUMN OREO_DATA HEADING 'Oreo Cake'

SELECT 
    TO_CHAR(subquery1.fruit1) AS BANANA_DATA,
    TO_CHAR(subquery2.fruit2) AS APPLE_DATA,
    TO_CHAR(subquery3.fruit3) AS MANGO_DATA,
    TO_CHAR(subquery4.fruit4) AS CHERRY_DATA,
    TO_CHAR(subquery5.veg1) AS POTATO_DATA,
    TO_CHAR(subquery6.veg2) AS BRINJAL_DATA,
    TO_CHAR(subquery7.veg3) AS BEANS_DATA,
    TO_CHAR(subquery8.dessert1) AS TIRAMISU_DATA,
    TO_CHAR(subquery9.dessert2) AS OREO_DATA 
FROM
........ (my sql query to fetch data from the groceries database)

Questions:

• Is there a native workaround that keeps the query and formatting in SQL, but renders correctly in HTML?

• Is it possible in SQL*Plus (especially older versions like 18.4) to render multi-row or grouped headers cleanly?

Any help or elegant workarounds would be hugely appreciated.

What I’ve Tried So Far:

I've tried injecting the top-level group headers (Fruits / Vegetables / Desserts) using PROMPT before the actual SQL query. The column headers come from COLUMN ... HEADING, and the data rows from the SQL query.

But the moment I try to combine them:

• SQL*Plus outputs the data in a separate table
• The group header row doesn’t align with the actual table
• If I try to use UNION to create a "fake" header row, I hit limitations or errors (SP2-0042)
• Even using SELECT '\<tr\>\<td\>' || value || ... || '\</td\>\</tr\>' FROM dual has alignment challenges

Also, SQL*Plus’s COLUMN heading format is flat — not hierarchical.

Additionally, if one uses an SQL-only approach in SQL*Plus:

• You can’t span headers across multiple columns (like "Gross Lead" spanning 4 columns)

• You can’t add a top header row above COLUMN headings unless you break out of SQL (e.g., PROMPT, HTML, union tricks)

What I'm Looking For:

I’m trying to create multi-row headers (i.e., category grouping) within SQL*Plus output, preferably:

• Without using raw HTML injection for the entire table

• Without resorting to UNION or UNION ALL

• While keeping the actual data query and formatting inside SQL

The output I need (I need to add multi-line headers):

The output I am getting:

My Query:

-- Enable SQL*Plus HTML Markup
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
SPOOL "C:\Grocery_data.html"

-- Define HTML Styling
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<title>Application Count</title> -
<style> -
body {background: #ffffff; font-family: Arial, sans-serif;} -
table {width: 100%; border-collapse: collapse; margin: 25px auto; font-size: 16px; text-align: center;} -
th, td {padding: 8px; border: 2px solid #ddd; text-align: center; white-space: nowrap;} -
th {background-color: #c90421; color: #ffffff; font-weight: bold;} -
td {min-width: 50px;} -
tr:nth-child(even) {background-color: #f2f2f2;} -
</style>"

-- Inject custom HTML table header
SET MARKUP HTML OFF
PROMPT <table>
PROMPT <thead>
PROMPT <tr>
PROMPT <th colspan="4" style="width:460px;">Fruits</th>
PROMPT <th colspan="3" style="width:350px;">Vegetables</th>
PROMPT <th colspan="2" style="width:300px;">Desserts</th>
PROMPT </tr>
PROMPT </thead>
PROMPT <tbody>
SET MARKUP HTML ON

COLUMN BANANA_DATA HEADING 'Banana'
COLUMN APPLE_DATA HEADING 'Apple'
COLUMN MANGO_DATA HEADING 'Mango'
COLUMN CHERRY_DATA HEADING 'Cherry'
COLUMN POTATO_DATA HEADING 'Potato'
COLUMN BRINJAL_DATA HEADING 'Brinjal'
COLUMN BEANS_DATA HEADING 'Beans'
COLUMN TIRAMISU_DATA HEADING 'Tiramisu'
COLUMN OREO_DATA HEADING 'Oreo Cake'

SELECT 
    TO_CHAR(subquery1.fruit1) AS BANANA_DATA,
    TO_CHAR(subquery2.fruit2) AS APPLE_DATA,
    TO_CHAR(subquery3.fruit3) AS MANGO_DATA,
    TO_CHAR(subquery4.fruit4) AS CHERRY_DATA,
    TO_CHAR(subquery5.veg1) AS POTATO_DATA,
    TO_CHAR(subquery6.veg2) AS BRINJAL_DATA,
    TO_CHAR(subquery7.veg3) AS BEANS_DATA,
    TO_CHAR(subquery8.dessert1) AS TIRAMISU_DATA,
    TO_CHAR(subquery9.dessert2) AS OREO_DATA 
FROM
........ (my sql query to fetch data from the groceries database)

Questions:

• Is there a native workaround that keeps the query and formatting in SQL, but renders correctly in HTML?

• Is it possible in SQL*Plus (especially older versions like 18.4) to render multi-row or grouped headers cleanly?

Any help or elegant workarounds would be hugely appreciated.

What I’ve Tried So Far:

I've tried injecting the top-level group headers (Fruits / Vegetables / Desserts) using PROMPT before the actual SQL query. The column headers come from COLUMN ... HEADING, and the data rows from the SQL query.

But the moment I try to combine them:

• SQL*Plus outputs the data in a separate table
• The group header row doesn’t align with the actual table
• If I try to use UNION to create a "fake" header row, I hit limitations or errors (SP2-0042)
• Even using SELECT '\<tr\>\<td\>' || value || ... || '\</td\>\</tr\>' FROM dual has alignment challenges

Also, SQL*Plus’s COLUMN heading format is flat — not hierarchical.

Additionally, if one uses an SQL-only approach in SQL*Plus:

• You can’t span headers across multiple columns (like "Gross Lead" spanning 4 columns)

• You can’t add a top header row above COLUMN headings unless you break out of SQL (e.g., PROMPT, HTML, union tricks)

What I'm Looking For:

I’m trying to create multi-row headers (i.e., category grouping) within SQL*Plus output, preferably:

• Without using raw HTML injection for the entire table

• Without resorting to UNION or UNION ALL

• While keeping the actual data query and formatting inside SQL

• html
• oracle-database
• sqlplus

Share Improve this question Follow asked Mar 25 at 9:53 MehakMehak 2311 silver badge44 bronze badges 1

• 1 SQL Plus is really not a very good tool for presentational reports. While it does have some formatting capabilities and was intended for basic reports decades ago when it was developed, it is very rudimentary and far surpassed by many modern reporting tools. It is unlikely you would ever use it for a purpose like this in most anizations. – Paul W Commented Mar 25 at 11:16

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Summary: No. SQL*Plus does not support that level of customisation.

---

Is it possible in SQL*Plus (especially older versions like 18.4) to render multi-row or grouped headers cleanly?

No. SQL*Plus commands only supports simple column headers.

In the text output, you can include line breaks in column headers by using the pipe character:

COLUMN banana_data HEADING 'Fruits|Banana'

You can test whether this has any effect in a HTML formatted report but it will not support spanning multiple columns.

Is there a native workaround that keeps the query and formatting in SQL, but renders correctly in HTML?

This is relatively simple if you are going to query the data in SQL and then build the report in a third-party languages (PHP, Java, C#, Python, ColdFusion, etc.) as you can use the formatting options of the third-party language.

If you particularly want to get the data using only SQL*Plus then you could manually build the output in SQL:

SELECT XMLELEMENT(
         "TABLE",
         XMLELEMENT(
           "THEAD",
           XMLELEMENT(
             "TR",
             XMLELEMENT("TH", XMLATTRIBUTES(2 AS "rowspan"), 'TOTAL'),
             XMLELEMENT("TH", XMLATTRIBUTES(3 AS "colspan"), 'Fruits'),
             XMLELEMENT("TH", XMLATTRIBUTES(2 AS "colspan"), 'Vegetables'),
             XMLELEMENT("TH", XMLATTRIBUTES(2 AS "colspan"), 'Deserts')
           ),
           XMLELEMENT(
             "TR",
             XMLFOREST(
               'Banana' AS "TH",
               'Apple' AS "TH",
               'Grape' AS "TH",
               'Carrot' AS "TH",
               'Potato' AS "TH",
               'Tiramisu' AS "TH",
               'Oreo' AS "TH"
             )
           )
         ),
         XMLELEMENT(
           "TBODY",
           XMLELEMENT(
             "TR",
             XMLFOREST(
               'Grand Total' AS "TD",
               SUM(CASE name WHEN 'Banana'   THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Apple'    THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Grape'    THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Carrot'   THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Potato'   THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Tiramisu' THEN quantity ELSE 0 END) AS "TD",
               SUM(CASE name WHEN 'Oreo'     THEN quantity ELSE 0 END) AS "TD"
             )
           )
         )
       ) AS html
FROM   groceries;

fiddle

Then you can save the result to a variable and spool it to a file (this may require you to manually build the entire HTML page and spool it as text).

I’m trying to create multi-row headers (i.e., category grouping) within SQL*Plus output, preferably:

• Without using raw HTML injection for the entire table
• Without resorting to UNION or UNION ALL
• While keeping the actual data query and formatting inside SQL

It does not appear to be possible as there are no obvious options, nor examples, to support this in the SQL*Plus documentation (see Generating Reports from SQL*Plus or Formatting SQL*Plus Reports).

本文标签： How to write multiline headers in html output file of oracle sqlplusStack Overflow