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)
Sending table data in HTML format through PL/SQL [message #333309] Fri, 11 July 2008 03:45 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi guys,

I need to send data (mail it ) which is stored in my Oracle Database through PL/SQL in HTML table format.

1. Does anyone have any idea how to do it?
2. If YES, Can you PLEASE throw some light on it ?

[Updated on: Fri, 11 July 2008 03:52]

Report message to a moderator

Re: Sending table data in HTML format through PL/SQL [message #333311 is a reply to message #333309] Fri, 11 July 2008 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use SET MARKUP HTML ON
Execute the query
SET MARKUP HTML OFF
Re: Sending table data in HTML format through PL/SQL [message #333366 is a reply to message #333311] Fri, 11 July 2008 07:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
sss111ind
Messages: 563
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 #583440 is a reply to message #583374] Tue, 30 April 2013 22:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
sss111ind wrote on Tue, 30 April 2013 04:02

...
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);

...


http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb08tra.htm#ADXDB0900
Re: Sending table data in HTML format through PL/SQL [message #583444 is a reply to message #583440] Wed, 01 May 2013 01:12 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

Thank you Barbara Madam for a good link.
Re: Sending table data in HTML format through PL/SQL [message #583634 is a reply to message #583444] Thu, 02 May 2013 09:23 Go to previous messageGo to next message
sss111ind
Messages: 563
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 #583747 is a reply to message #583634] Fri, 03 May 2013 10:02 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

Hi All,

It's getting correct everything but for space it is not taking.As follows..
select  GET_HTML_REPORT('select dummy  "dummy k" from dual') from dual;


output is
"<table>
  <tr>
    <th>dummy_x0020_k</th>
    <tr>
      <td style="text-align:left;">X</td>
    </tr>
  </tr>
</table>
"


Please help me.

Regards,
Nathan
Re: Sending table data in HTML format through PL/SQL [message #583752 is a reply to message #583747] Fri, 03 May 2013 10:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
sss111ind
Messages: 563
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
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.

Re: Sending table data in HTML format through PL/SQL [message #583794 is a reply to message #583781] Sat, 04 May 2013 03:33 Go to previous message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

Hi All,

My db version is
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE	10.2.0.4.0	Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


And for me it is not working might be some issue is there .
Previous Topic: merge two tables in to one and knock out the duplicate records.
Next Topic: Update Display Name via SQL
Goto Forum:
  


Current Time: Fri Dec 02 12:28:16 CST 2016

Total time taken to generate the page: 0.07875 seconds