Home » SQL & PL/SQL » SQL & PL/SQL » Sending table data in HTML format through PL/SQL (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
|
|
Re: Sending table data in HTML format through PL/SQL [message #333366 is a reply to message #333311] |
Fri, 11 July 2008 07:10 |
ThomasG
Messages: 3211 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
I written another solution some weeks ago, by using XMLQUERY and XSLT tranformations from XML to HTML, which might be of interest:
DECLARE
ctxh DBMS_XMLGEN.ctxHandle;
queryresult XMLTYPE;
xslt_tranfsorm XMLTYPE;
BEGIN
-- SQL Query :
ctxh := DBMS_XMLGEN.newContext
('select TABLE_NAME,
OWNER,
INITIAL_EXTENT
from all_tables
where rownum < 21');
-- XSLT Transformation to HTML :
xslt_tranfsorm := NEW XMLTYPE('
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/ROWSET">
<table>
<tr>
<th>Table</th>
<th>Owner</th>
<th>Initial Extends</th>
</tr>
<xsl:for-each select="ROW">
<tr>
<td style="text-align:left;"> <xsl:value-of select="TABLE_NAME"/> </td>
<td style="text-align:left;"> <xsl:value-of select="OWNER"/> </td>
<td style="text-align:right;"> <xsl:value-of select="INITIAL_EXTENT"/> </td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>');
queryresult := DBMS_XMLGEN.getXMLType(ctxh).transform(xslt_tranfsorm);
send_mail('Database','user@domain.org','Test Mail','
<h3>Hello,</h3>
<p>This is a Test</p>
<p>Query Results : </p>
' || queryresult.getClobVal()
);
END;
/
That piece is using this send_mail procedure for HTML Mails including a style sheet :
CREATE OR REPLACE PROCEDURE send_mail(
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN CLOB)
as
l_mailhost VARCHAR2(255) := 'servicemail';
l_mail_conn utl_smtp.connection;
v_add_src VARCHAR2(4000);
v_addr VARCHAR2(4000);
slen number := 1;
crlf VARCHAR2(2) := chr(13)||chr(10);
i NUMBER(12);
len NUMBER (12);
part NUMBER(12) := 16384;
l_stylesheet CLOB := '
<html><head>
<style type="text/css">
body { font-family : Verdana, Arial;
font-size : 10pt;}
.green { color : #00AA00;
font-weight : bold;}
.red { color : #FF0000;
font-weight : bold;}
pre { margin-left : 10px;}
table { empty-cells : show;
border-collapse : collapse;
width : 100%;
border : solid 2px #444444;}
td { border : solid 1px #444444;
font-size : 12pt;
padding : 2px;}
th { background : #EEEEEE;
border : solid 1px #444444;
font-size : 12pt;
padding : 2px;}
dt { font-weight : bold; }
</style>
</head>
<body>';
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost,25);
utl_smtp.helo(l_mail_conn, l_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
if(instr(p_recipient,';') = 0) then
utl_smtp.rcpt(l_mail_conn, p_recipient);
else
v_add_src := p_recipient || ';';
while(instr(v_add_src,';',slen) > 0) loop
v_addr := substr(v_add_src, slen, instr(substr(v_add_src, slen),';')-1);
slen := slen+instr(substr(v_add_src, slen),';');
Dbms_Output.put_line('rcpt ' || v_addr);
utl_smtp.rcpt(l_mail_conn, v_addr);
end loop;
end if;
--utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn,
'MIME-version: 1.0' || crlf ||
'Content-Type: text/html; charset=ISO-8859-15' || crlf ||
'Content-Transfer-Encoding: 8bit' || crlf ||
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || p_sender || crlf ||
'Subject: '|| p_subject || crlf ||
'To: ' || p_recipient || crlf);
utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(l_stylesheet));
i := 1;
len := DBMS_LOB.getLength(p_message);
WHILE (i < len) LOOP
utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(DBMS_LOB.SubStr(p_message,part, i)));
i := i + part;
END LOOP;
utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('</body></html>'));
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;
/
|
|
|
Re: Sending table data in HTML format through PL/SQL [message #583374 is a reply to message #333366] |
Tue, 30 April 2013 06:02 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
Thanks For A Wonderful Solution For Sending An Mail By Executing The Query.Is There Any Other Solution For This.My dbversion is Oracle10g.
And One Doubt From The Above Code Where Transform Function Exists.I Searched In Dbms_Xmlgen But Did Not Find.
Please explain this in details.
Queryresult := Dbms_Xmlgen.Getxmltype(Ctxh).Transform(Xslt_Tranfsorm);
Regards,
Nathan
[Updated on: Tue, 30 April 2013 06:03] Report message to a moderator
|
|
|
|
|
Re: Sending table data in HTML format through PL/SQL [message #583634 is a reply to message #583444] |
Thu, 02 May 2013 09:23 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
A more generalized way to do this as follows.I found it somewhere.
https://forums.oracle.com/forums/thread.jspa?threadID=2387316
create or replace FUNCTION get_html_report(p_query IN VARCHAR2) RETURN CLOB IS
ctxh dbms_xmlgen.ctxhandle;
xslt_tranfsorm XMLTYPE;
l_mail_body CLOB;
BEGIN
ctxh:= dbms_xmlgen.newcontext(p_query);
-- XSLT Transformation to HTML
xslt_tranfsorm := NEW XMLTYPE('
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/ROWSET">
<table>
<tr>
<xsl:for-each select="ROW[1]/*">
<th> <xsl:value-of select="name()"/></th>
</xsl:for-each>
<xsl:apply-templates/>
</tr>
</table>
</xsl:template>
<xsl:template match="ROW">
<tr><xsl:apply-templates/></tr>
</xsl:template>
<xsl:template match="ROW/*">
<td style="text-align:left;"><xsl:value-of select="."/></td>
</xsl:template>
</xsl:stylesheet>');
dbms_xmlgen.setnullhandling(ctxh, dbms_xmlgen.empty_tag);
dbms_xmlgen.setxslt(ctxh, xslt_tranfsorm);
l_mail_body := dbms_xmlgen.getxml(ctxh);
dbms_xmlgen.closecontext(ctxh);
RETURN l_mail_body;
END get_html_report;
DECLARE
l_var clob;
BEGIN
l_var:=l_var||a_b_pkg.get_html_report('select * from emp');
dbms_output.put_line(l_var);
send_mail('Database','user@domain.org','Test Mail','
<h3>Hello,</h3>
<p>This is a Test</p>
<p>Query Results : </p>
' || l_var
);
END;
[EDITED by LF: masked some confidential information because of OP's request]
[Updated on: Sat, 04 May 2013 11:26] by Moderator Report message to a moderator
|
|
|
|
Re: Sending table data in HTML format through PL/SQL [message #583752 is a reply to message #583747] |
Fri, 03 May 2013 10:31 |
ThomasG
Messages: 3211 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, when you look at the intermediate XML you see that the "dummy k" is used as a XML Tag, and spaces in XML Tags are not allowed.
<?xml version="1.0"?>
<ROWSET>
<ROW>
<dummy_x0020_k>X</dummy_x0020_k>
</ROW>
</ROWSET>
So you would have to either hardcode the Table headers in the XSLT, or you could run a "replace" on the CLOB and replace _x0020_ with ' '
Replace Line
l_mail_body := dbms_xmlgen.getxml(ctxh);
with
l_mail_body := REPLACE(dbms_xmlgen.getxml(ctxh),'_x0020_',' ');
in the procedure
|
|
|
Re: Sending table data in HTML format through PL/SQL [message #583753 is a reply to message #583752] |
Fri, 03 May 2013 10:34 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
Thank You ThomasG for your earlier contribution and this response.
But one small doubt Please have A look AT THE below two queries.
SELECT get_html_report('select count(*)total_cnt,count(decode(job,''MANAGER'',1,NULL))manager_cnt from emp')
FROM dual;
output
----
null
SELECT get_html_report('select count(*)total_cnt,sum(decode(job,''MANAGER'',1,0))manager_cnt from emp')
FROM dual;
output
-------
"<table>
<tr>
<th>TOTAL</th>
<th>MANAGER_CNT</th>
<tr>
<td style="text-align:left;">14</td>
<td style="text-align:left;">3</td>
</tr>
</tr>
</table>
"
Regards,
Nathan
[Updated on: Fri, 03 May 2013 10:44] Report message to a moderator
|
|
|
Re: Sending table data in HTML format through PL/SQL [message #583768 is a reply to message #583753] |
Fri, 03 May 2013 12:50 |
ThomasG
Messages: 3211 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
I don't have access to the EMP tables right now, but for me both versions seem to be working.
SQL> select GET_HTML_REPORT(
2 'select count(*) total_cnt,
3 count(decode(''BAR'',''FOO'',1,NULL)) manager_cnt
4 from dual') output
5 from dual;
OUTPUT
--------------------------------------------------------------------------
<table>
<tr>
<th>TOTAL_CNT</th>
<th>MANAGER_CNT</th>
<tr>
<td style="text-align:left;">1</td>
<td style="text-align:left;">0</td>
</tr>
</tr>
</table>
SQL>
SQL> select GET_HTML_REPORT(
2 'select count(*) total_cnt,
3 sum(decode(''BAR'',''FOO'',1,0)) manager_cnt
4 from dual') output
5 from dual;
OUTPUT
--------------------------------------------------------------------------
<table>
<tr>
<th>TOTAL_CNT</th>
<th>MANAGER_CNT</th>
<tr>
<td style="text-align:left;">1</td>
<td style="text-align:left;">0</td>
</tr>
</tr>
</table>
SQL>
SQL>
SQL>
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
|
|
|
Re: Sending table data in HTML format through PL/SQL [message #583781 is a reply to message #583768] |
Fri, 03 May 2013 15:31 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
It works for me in 11.2.0.1.0 also.
SCOTT@orcl_11gR2> select * from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
5 rows selected.
SCOTT@orcl_11gR2> create or replace FUNCTION get_html_report(p_query IN VARCHAR2) RETURN CLOB IS
2 ctxh dbms_xmlgen.ctxhandle;
3 xslt_tranfsorm XMLTYPE;
4 l_mail_body CLOB;
5 BEGIN
6 ctxh:= dbms_xmlgen.newcontext(p_query);
7
8 -- XSLT Transformation to HTML
9 xslt_tranfsorm := NEW XMLTYPE('
10 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
11 <xsl:template match="/ROWSET">
12 <table>
13 <tr>
14 <xsl:for-each select="ROW[1]/*">
15 <th> <xsl:value-of select="name()"/></th>
16 </xsl:for-each>
17 <xsl:apply-templates/>
18 </tr>
19 </table>
20 </xsl:template>
21 <xsl:template match="ROW">
22 <tr><xsl:apply-templates/></tr>
23 </xsl:template>
24 <xsl:template match="ROW/*">
25 <td style="text-align:left;"><xsl:value-of select="."/></td>
26 </xsl:template>
27 </xsl:stylesheet>');
28
29 dbms_xmlgen.setnullhandling(ctxh, dbms_xmlgen.empty_tag);
30
31 dbms_xmlgen.setxslt(ctxh, xslt_tranfsorm);
32
33 l_mail_body := dbms_xmlgen.getxml(ctxh);
34
35 dbms_xmlgen.closecontext(ctxh);
36
37 RETURN l_mail_body;
38 END get_html_report;
39 /
Function created.
SCOTT@orcl_11gR2> SELECT get_html_report
2 ('select count(*)total_cnt,
3 count(decode(job,''MANAGER'',1,NULL))manager_cnt
4 from emp')
5 FROM dual
6 /
GET_HTML_REPORT('SELECTCOUNT(*)TOTAL_CNT,COUNT(DECODE(JOB,''MANAGER'',1,NULL))MA
--------------------------------------------------------------------------------
<table>
<tr>
<th>TOTAL_CNT</th>
<th>MANAGER_CNT</th>
<tr>
<td style="text-align:left;">14</td>
<td style="text-align:left;">3</td>
</tr>
</tr>
</table>
1 row selected.
SCOTT@orcl_11gR2> SELECT get_html_report
2 ('select count(*)total_cnt,
3 sum(decode(job,''MANAGER'',1,0))manager_cnt
4 from emp')
5 FROM dual
6 /
GET_HTML_REPORT('SELECTCOUNT(*)TOTAL_CNT,SUM(DECODE(JOB,''MANAGER'',1,0))MANAGER
--------------------------------------------------------------------------------
<table>
<tr>
<th>TOTAL_CNT</th>
<th>MANAGER_CNT</th>
<tr>
<td style="text-align:left;">14</td>
<td style="text-align:left;">3</td>
</tr>
</tr>
</table>
1 row selected.
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jun 17 00:44:20 CDT 2024
|