toddlermenot toddlermenot - 4 years ago 108
SQL Question

oracle result set in html similar to SQL*Plus (using SQL only)

To give an example straightaway, below is what I would like to do (without using SQLPlus):
In SQL
Plus:

SQL> set feed off markup html on
SQL> select * from emp where rownum<=2;


gives this output:

<p>
<table border="1" width="90%" summary="Script output">
<tr>
<th scope="col">
EMPNO
</th>
<th scope="col">
ENAME
</th>
<th scope="col">
JOB
</th>
<th scope="col">
MGR
</th>
<th scope="col">
HIREDATE
</th>
<th scope="col">
SAL
</th>
<th scope="col">
COMM
</th>
<th scope="col">
DEPTNO
</th>
</tr>
<tr>
<td align="right">
7369
</td>
<td>
SMITH
</td>
<td>
CLERK
</td>
<td align="right">
7902
</td>
<td>
17-DEC-80
</td>
<td align="right">
800
</td>
<td align="right">
&nbsp;
</td>
<td align="right">
20
</td>
</tr>
<tr>
<td align="right">
7499
</td>
<td>
ALLEN
</td>
<td>
SALESMAN
</td>
<td align="right">
7698
</td>
<td>
20-FEB-81
</td>
<td align="right">
1600
</td>
<td align="right">
300
</td>
<td align="right">
30
</td>
</tr>
</table>
<p>


Is there a straightforward way to do this in SQL only? (using one or more Oracle's XML functions, for example). Any pointers would be most welcome.

EDIT:
To clarify a bit more, I do NOT care for the whitespace or the
p
/
table
tags and the attributes of each tags, just the structured html.

Answer Source

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) AS
SELECT 7369, 'Smith', 'Clerk', 7902, DATE '1980-12-17', 800, CAST( NULL AS NUMBER(5,2) ), 20 FROM DUAL UNION ALL
SELECT 7499, 'Allen', 'Salesman', 7698, DATE '1981-02-20', 1600, 300, 30 FROM DUAL

Query 1:

SELECT XMLElement(
         "p",
         XMLElement(
           "table",
           XMLElement( "tr",
             XMLForest(
               'empno'    AS "th",
               'ename'    AS "th",
               'job'      AS "th",
               'mgr'      AS "th",
               'hiredate' AS "th",
               'sal'      AS "th",
               'comm'     AS "th",
               'deptno'   AS "th"
             )
           ),
           XMLAgg(
             XMLElement( "tr",
               XMLForest(
                 empno    AS "td",
                 ename    AS "td",
                 job      AS "td",
                 mgr      AS "td",
                 hiredate AS "td",
                 sal      AS "td",
                 comm     AS "td",
                 deptno   AS "td"
               )
             )
           )
         )
       ).getClobVal() AS XML
FROM   EMP

Results:

|                                                                                                                                                                                                                                                                                                                                                                            XML |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| <p><table><tr><th>empno</th><th>ename</th><th>job</th><th>mgr</th><th>hiredate</th><th>sal</th><th>comm</th><th>deptno</th></tr><tr><td>7369</td><td>Smith</td><td>Clerk</td><td>7902</td><td>1980-12-17</td><td>800</td><td>20</td></tr><tr><td>7499</td><td>Allen</td><td>Salesman</td><td>7698</td><td>1981-02-20</td><td>1600</td><td>300</td><td>30</td></tr></table></p> |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download