Search This Blog

Wednesday, September 14, 2011

Email Package : UTL_SMTP

UTL_SMTP : Package is used to send out an email via PL/SQL




Sample Code:

DECLARE
   c             UTL_SMTP.CONNECTION;
   datasetclob   CLOB;

   FUNCTION generatexml (sqlstatement CLOB)
      RETURN CLOB
   IS
      xmlcontextHandle   DBMS_XMLGEN.CTXHANDLE;
      xml                XMLTYPE;
      xmlclob            CLOB;
      XSLT_str_w_css VARCHAR2 (4000)
            := '<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><HTML><HEAD><style type="text/css">body{ background-color:#FFFFFF;} h1{color:black;text-align:center;}    td{background-color:#FFCC80;font-family:"Helvetica"; font-size:14px; color:black; text-align:left;}</style>        </HEAD><BODY><xsl:apply-templates/></BODY></HTML></xsl:template><xsl:template match="/*"><TABLE BORDER="1"><TR><xsl:for-each select="*[position() = 1]/*"><TD><xsl:value-of select="local-name()"/></TD></xsl:for-each></TR><xsl:apply-templates/></TABLE></xsl:template><xsl:template match="/*/*"><TR><xsl:apply-templates/></TR></xsl:template><xsl:template match="/*/*/*"><TD><xsl:value-of select="."/></TD></xsl:template></xsl:stylesheet>';
      XSLT               XMLTYPE;
   BEGIN
      xmlcontextHandle := DBMS_XMLGEN.newcontext (sqlstatement);
      DBMS_XMLGEN.setRowsetTag (xmlcontextHandle, 'Data');
      DBMS_XMLGEN.setRowTag (xmlcontextHandle, 'DataRow');
      xmlclob := DBMS_XMLGEN.GETXML (xmlcontextHandle);
      DBMS_XMLGEN.closecontext (xmlcontextHandle);

      XSLT := XMLType (XSLT_str_w_css);
      xml := XMLType.transform (xmltype (xmlclob), XSLT);
      xmlclob := XML.getCLOBVal;
      RETURN xmlclob;
      
   END generatexml;
   
   
BEGIN
   datasetclob := generatexml ('select sysdate currentdate,sysdate+1 nextday from dual union select sysdate-1,sysdate from dual');
   DBMS_OUTPUT.put_line (datasetclob);
   c := UTL_SMTP.OPEN_CONNECTION ('webmail.nrgenergy.com'); -- open a connection with smtp server
   
   UTL_SMTP.HELO (c, 'webmail.nrgenergy.com'); -- Handshaking with the server (Ping)
   UTL_SMTP.MAIL (c, 'kkodava1@reliant.com');                          -- from
   UTL_SMTP.RCPT (c, 'kkodava1@reliant.com');                            -- To
   UTL_SMTP.RCPT(c, 'kiran.kodavati@gmail.com'); -- To
   
   UTL_SMTP.OPEN_DATA (c);                             -- Open data connection
   
   UTL_SMTP.WRITE_DATA (C, 'Subject: ' || 'my test subject'); -- Set the subject
   UTL_SMTP.WRITE_DATA (C, UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (C, 'To: ' || 'kiran,vijay');                -- To list
   UTL_SMTP.WRITE_DATA (C, UTL_TCP.CRLF);
   
   -- Send email as HTML : Start
   UTL_SMTP.write_data (c, 'MIME-Version: 1.0');
   UTL_SMTP.write_data (c, UTL_TCP.CRLF);
   UTL_SMTP.write_data (c, 'Content-Type: text/html');
   UTL_SMTP.write_data (c, UTL_TCP.CRLF);
   UTL_SMTP.write_data (c, 'Content-Transfer-Encoding: 7bit;');
   UTL_SMTP.write_data (c, UTL_TCP.CRLF);
   -- Send email as HTML : End
   
   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || 'Hello, world!');          -- email message
   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || 'kiran testmsg 1');      -- email message
   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || 'kiran testmsg 2');      -- email message
   
   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || datasetclob);  -- SQL output in Tabular format 
   
   UTL_SMTP.CLOSE_DATA (c);                           -- Close data connection
   UTL_SMTP.QUIT (c);                                             -- Close the
END;
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)