A Generic function that can take ref-cursor as input and print HTML as Output.
rajesh@10GR2> CREATE OR REPLACE FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR)
2 RETURN CLOB
3 IS
4 lRetVal CLOB;
5 lHTMLOutput XMLType;
6 lXSL CLOB;
7 lXMLData XMLType;
8 lContext DBMS_XMLGEN.CTXHANDLE;
9 begin
10 -- get a handle on the ref cursor --
11 lContext := DBMS_XMLGEN.NEWCONTEXT(rf);
12 -- setNullHandling to 1 (or 2) to allow null columns to be displayed --
13 DBMS_XMLGEN.setNullHandling(lContext,1);
14 -- create XML from ref cursor --
15 lXMLData := DBMS_XMLGEN.GETXMLTYPE(lContext,DBMS_XMLGEN.NONE);
16
17 -- this is a generic XSL for Oracle's default XML row and rowset tags --
18 -- " " is a non-breaking space --
19 lXSL := lXSL || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
20 lXSL := lXSL || q'[<xsl:stylesheet version="1.0"
21 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
22 lXSL := lXSL || q'[ <xsl:output method="html"/>]';
23 lXSL := lXSL || q'[ <xsl:template match="/">]';
24 lXSL := lXSL || q'[ <html>]';
25 lXSL := lXSL || q'[ <body>]';
26 lXSL := lXSL || q'[ <table border="1">]';
27 lXSL := lXSL || q'[ <tr bgcolor="cyan">]';
28 lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/ROW[1]/*">]';
29 lXSL := lXSL || q'[ <th><xsl:value-of select="name()"/></th>]';
30 lXSL := lXSL || q'[ </xsl:for-each>]';
31 lXSL := lXSL || q'[ </tr>]';
32 lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/*">]';
33 lXSL := lXSL || q'[ <tr>]';
34 lXSL := lXSL || q'[ <xsl:for-each select="./*">]';
35 lXSL := lXSL || q'[ <td><xsl:value-of select="text()"/> </td>]';
36 lXSL := lXSL || q'[ </xsl:for-each>]';
37 lXSL := lXSL || q'[ </tr>]';
38 lXSL := lXSL || q'[ </xsl:for-each>]';
39 lXSL := lXSL || q'[ </table>]';
40 lXSL := lXSL || q'[ </body>]';
41 lXSL := lXSL || q'[ </html>]';
42 lXSL := lXSL || q'[ </xsl:template>]';
43 lXSL := lXSL || q'[</xsl:stylesheet>]';
44
45 -- XSL transformation to convert XML to HTML --
46 lHTMLOutput := lXMLData.transform(XMLType(lXSL));
47
48 -- convert XMLType to Clob --
49 lRetVal := lHTMLOutput.getClobVal();
50
51 -- Close Context ---
52 dbms_xmlgen.closeContext(lContext);
53
54 RETURN lRetVal;
55
56 end fncRefCursor2HTML;
57 /
Function created.
Elapsed: 00:00:00.14
rajesh@10GR2>
rajesh@10GR2> variable x refcursor;
rajesh@10GR2> variable y clob;
rajesh@10GR2>
rajesh@10GR2> set autoprint on;
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> begin
2 open :x for select * from dept;
3 :y := fncRefCursor2HTML(:x);
4 end;
5 /
PL/SQL procedure successfully completed.
Y
--------------------------------------------------------------------------------
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<th>DEPTNO</th>
<th>DNAME</th>
<th>LOC</th>
<th>LOCATION_ID</th>
</tr>
<tr>
<td>10</td>
<td>ACCOUNTING</td>
<td>NEW YORK</td>
<td>1</td>
</tr>
<tr>
<td>20</td>
<td>RESEARCH</td>
<td>DALLAS</td>
<td>2</td>
</tr>
<tr>
<td>30</td>
<td>SALES</td>
<td>CHICAGO</td>
<td>1</td>
</tr>
<tr>
<td>40</td>
<td>OPERATIONS</td>
<td>BOSTON</td>
<td>1</td>
</tr>
</table>
</body>
</html>
Elapsed: 00:00:00.03
rajesh@10GR2>
No comments:
Post a Comment