How to transform a RefCursor query resultset into HTML table in SQL
March 6th, 2012 By Frank Zhou
The following SQL pattern can be used to tranform a RefCursor query resultset into data in HTML table format by utilizing the XSLT functionality. The query can handle NULL in the data columns. The SQL input string should be based on the following format:
SELECT Col AS name_x, Function() AS name_y, Analytical_function( ) AS name_z FROM ( ***ANYTHING*** )
You can change your query into this required format by wrapping your sql inside an inline view if needed. The “AS” keyword and column_name “alias” are required for building the XML tags.
CREATE TABLE PERSON
(ID NUMBER,
FIRSTNAME VARCHAR2(20 BYTE),
MIDDLENAME VARCHAR2(20 BYTE),
LASTNAME VARCHAR2(20 BYTE),
LOCATION_ID NUMBER);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (1, 'Frank', null, 'Zhou', 1);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (2, 'Peter', 'B', 'Lee', 1);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (3, 'John' , 'C', 'Adam', 2);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (4, 'Dave' , null, 'Washington', 2);
CREATE TABLE LOCATION
(LOCATION_ID NUMBER,
LOCATION_NAME VARCHAR2(20 BYTE)
);
INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (1, 'BOSTON');
INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (2, 'NEW YORK');
COMMIT;
set long 10000;
variable sql_input varchar2(500)
BEGIN
:sql_input:= q'[
SELECT b.location_name AS location,
COUNT (*) AS COUNT,
LISTAGG (a.lastname, ' , ') WITHIN GROUP (ORDER BY a.id)
AS lastname_stragg
FROM person a, location b
WHERE a.location_id = b.location_id
GROUP BY ROLLUP (b.location_name)
HAVING MAX (b.location_id) > 0
]';
END;
/
------------------------------------------SQL QUERY------------------------------------------------------------
WITH SQL_QUERY AS
(SELECT UPPER(SUBSTR(sql_str, 0, REGEXP_INSTR(sql_str, ' FROM', 1,1,0, 'i'))) AS SELECT_STR,
SUBSTR(sql_str, REGEXP_INSTR(sql_str, ' FROM', 1,1,0, 'i')) AS FROM_STR
FROM
(SELECT DBMS_XMLGEN.CONVERT( XMLQUERY('fn:substring(fn:normalize-space(str), 8) '
PASSING XMLELEMENT("str", SQL_STR)
RETURNING CONTENT).GETSTRINGVAL(), 1) AS sql_str
FROM (SELECT :sql_input as sql_str FROM dual )
)
),
COL_NAMES AS
(SELECT SUBSTR(COL_NAME, 0, INSTR(COL_NAME, ' AS ')) AS VAL,
SUBSTR(COL_NAME, INSTR(COL_NAME, ' AS ') + 4) AS ALIAS, ROWNUM rn
FROM SQL_QUERY, XMLTABLE('ora:tokenize(.,"#")'
PASSING REGEXP_REPLACE (SELECT_STR, '([[:space:]]AS[[:space:]][^,]+)[[:space:]]?[,]?' , '\1#')
COLUMNS COL_NAME VARCHAR2 (100) PATH '.')
),
XML_QUERY AS
(SELECT ' SELECT 1, XMLELEMENT(ROW_DATA, '||LISTAGG(REPLACE(VAL, VAL, 'XMLELEMENT('||ALIAS||' , '||VAL||')') , ' , ')
WITHIN GROUP (ORDER BY rn)||')' AS SELECT_STR,
MAX((SELECT FROM_STR FROM SQL_QUERY)) AS FROM_AND_CONDITIONS_STR
FROM COL_NAMES
),
XML_DATA AS
(SELECT DBMS_XMLGEN.GETXMLTYPE(DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY(
SELECT_STR||' '||FROM_AND_CONDITIONS_STR)) AS XML_DOC
FROM XML_QUERY
)
SELECT XMLSERIALIZE(DOCUMENT XMLTRANSFORM(
(SELECT XMLELEMENT (ROWSET, XML_DOC) FROM XML_DATA),
XMLTYPE (
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">
<html><body><xsl:apply-templates/> </body></html>
</xsl:template>
<xsl:template match="ROWSET">
<table border = "1" cellspacing = "0"> <tr> <xsl:apply-templates select ="ROW_DATA[1]/*" mode ="Col_Name"/> </tr>
<xsl:apply-templates/> </table>
</xsl:template>
<xsl:template match="ROW_DATA">
<tr><xsl:apply-templates/> </tr>
</xsl:template>
<xsl:template match="ROW_DATA/*">
<td><xsl:apply-templates/> </td>
</xsl:template>
<xsl:template match="ROW_DATA/*" mode = "Col_Name">
<th><xsl:value-of select ="name(.)"/> </th>
</xsl:template>
</xsl:stylesheet>'))) AS HTML_TABL_DATA
FROM DUAL;
HTML_TABL_DATA
——————————————————————————–
| LOCATION | COUNT | LASTNAME_STRAGG |
|---|---|---|
| BOSTON | 2 | Zhou , Lee |
| NEW YORK | 2 | Adam , Washington |
| 4 | Zhou , Lee , Adam , Washington |