在PL/SQL过程中,将查询或refcursor包装在HTML表中 [英] Within a PL/SQL procedure, wrap a query or refcursor in HTML table
问题描述
如果您使用SQL * Plus来使用
It seems really easy if you are using SQL*Plus to use
SQL> set markup html on;
并在SQL * Plus窗口中获得一些可爱的结果.我们有一个oracle作业,该作业可以整夜运行,并将结果电子邮件发送给许多人.我想在该消息中将sql语句包装在HTML表中.最好的方法是什么?
and get some lovely results to the SQL*Plus window. we have an oracle job that runs overnight and sends an email of results to a number of people. I would like to wrap a sql statement in an HTML table to be in that message. What is the best way of doing that?
推荐答案
通过汤姆·凯特(Tom Kyte)的博客文章很棒正是我所希望的.简而言之,这就是我实现的目标:
From posting over On the DBA site I got close to the answer that I was looking for. Tom Kyte has a great blog post with a function that does exactly what I was hoping for. In short here is what I implemented:
我创建了一个将sys_refcursor用作变量的函数:
I created a function that took a sys_refcursor as a variable:
CREATE OR REPLACE FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR) RETURN CLOB
IS
lRetVal CLOB;
lHTMLOutput XMLType;
lXSL CLOB;
lXMLData XMLType;
lContext DBMS_XMLGEN.CTXHANDLE;
BEGIN
-- get a handle on the ref cursor --
lContext := DBMS_XMLGEN.NEWCONTEXT(rf);
-- setNullHandling to 1 (or 2) to allow null columns to be displayed --
DBMS_XMLGEN.setNullHandling(lContext,1);
-- create XML from ref cursor --
lXMLData := DBMS_XMLGEN.GETXMLTYPE(lContext,DBMS_XMLGEN.NONE);
-- this is a generic XSL for Oracle's default XML row and rowset tags --
-- " " is a non-breaking space --
lXSL := lXSL || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
lXSL := lXSL || q'[<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
lXSL := lXSL || q'[ <xsl:output method="html"/>]';
lXSL := lXSL || q'[ <xsl:template match="/">]';
lXSL := lXSL || q'[ <html>]';
lXSL := lXSL || q'[ <body>]';
lXSL := lXSL || q'[ <table border="1">]';
lXSL := lXSL || q'[ <tr bgcolor="cyan">]';
lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/ROW[1]/*">]';
lXSL := lXSL || q'[ <th><xsl:value-of select="name()"/></th>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </tr>]';
lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/*">]';
lXSL := lXSL || q'[ <tr>]';
lXSL := lXSL || q'[ <xsl:for-each select="./*">]';
lXSL := lXSL || q'[ <td><xsl:value-of select="text()"/> </td>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </tr>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </table>]';
lXSL := lXSL || q'[ </body>]';
lXSL := lXSL || q'[ </html>]';
lXSL := lXSL || q'[ </xsl:template>]';
lXSL := lXSL || q'[</xsl:stylesheet>]';
-- XSL transformation to convert XML to HTML --
lHTMLOutput := lXMLData.transform(XMLType(lXSL));
-- convert XMLType to Clob --
lRetVal := lHTMLOutput.getClobVal();
RETURN lRetVal;
END;
然后在PL/SQL Developer中的测试"窗口中对其进行测试
Then to test it in a Test window in PL/SQL Developer
declare
l_cursor sys_refcursor;
begin
open l_cursor for select * from employees;
:x:= fncRefCursor2HTML(l_cursor);
close l_cursor;
end;
这是我很久以来一直希望找到的东西.谢谢汤姆·基特!
This is something that I have been hoping to find for a long time. THANKS Tom Kyte!
这篇关于在PL/SQL过程中,将查询或refcursor包装在HTML表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!