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

Question

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

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.

You can try and wrap this using the packages such as UTL_FILE and write to files. You would still need some kind of stored procedure that would be executed from your non-SQLPLUS client. — vmachan
– vmachan, Commented Dec 24, 2015 at 19:41
Just concatenate all the html tags in your select statement. It will be long and ugly, though. — OldProgrammer
– OldProgrammer, Commented Dec 24, 2015 at 21:29

MT0 · Accepted Answer · 2015-12-25 01:39:20Z

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> |

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related