ORACLE DEVELOPMENT: September 2011

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

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;

Continue - Specifies to skip the iteration in a loop and move on to next iteration.

Example code:

begin

for i in 1 .. 100

loop

dbms_output.put_line ('Iteration:' || i || 'Start');

if mod (i, 2) = 0

then

dbms_output.put_line (

'skip and move on to next record-Condition 1 satisfied');

continue;

end if;

dbms_output.put_line ('Condition 1 failed:' || i);

if mod (i, 5) = 0

then

dbms_output.put_line (

'skip and move on to next record-Condition 2 satisfied');

continue;

end if;

dbms_output.put_line ('Condition 2 failed:' || i);

if mod (i, 11) = 0

then

dbms_output.put_line (

'skip and move on to next record-Condition 3 satisfied');

continue;

end if;

dbms_output.put_line ('Condition 3 failed:' || i);

dbms_output.put_line ('Iteration:' || i || 'End');

end loop;

end;

ORACLE DEVELOPMENT

Search This Blog

Wednesday, September 14, 2011

Email Package : UTL_SMTP

Continue Statement. (11g)

PAGE VISIT

Followers