Oracle Apps, Oracle APEX and PL/SQL: UTL_SMTP API to send HTML Emails from PL/SQL

Sample Code 1 :
DECLARE
 psender VARCHAR2(4000) := '<sender_mail_id>';
 precipient VARCHAR2(4000) := '<recipient_mail_id>';
 psubject VARCHAR2(4000) := 'Mail Test Subject';
 c utl_smtp.connection;
 pmessage VARCHAR2(4000) := '<!doctype html>

    <html>

    <head>

      <title>Test HTML message</title>

    </head>

    <body>

      <p>This is a <b>HTML</b> <i>version</i> of the test message.</p>

      <p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" />

    </body>

</html>';

 PROCEDURE Send_header(name VARCHAR2, header VARCHAR2) AS
 BEGIN
 utl_smtp.Write_data(c, name||':'|| header|| utl_tcp.crlf);
 END;

BEGIN
 --c := utl_smtp.Open_connection('smtp-relay.gmail.com');
 c := utl_smtp.Open_connection(host => 'smtp-relay.gmail.com', port => 25);

 utl_smtp.Helo(c, '<SMTP host name>'); -- utl_smtp.helo –> introduce yourself

 utl_smtp.Mail(c, psender); -- utl_smtp.MAIL –> specify the sender

 utl_smtp.Rcpt(c, precipient); -- utl_smtp.RCPT –> specify the recipient

 utl_smtp.Open_data(c);

 Send_header('From', '"Sender" <'||psender||'>');

 Send_header('To', '"Recipient" <'||precipient||'>');

 Send_header('Subject', psubject);

 utl_smtp.Write_data(c, 'MIME-Version: 1.0'
 || utl_tcp.crlf
 || 'Content-type: text/html'
 || utl_tcp.crlf
 || pmessage);

 utl_smtp.Close_data(c);

 utl_smtp.Quit(c);

EXCEPTION
 WHEN utl_smtp.invalid_operation THEN
dbms_output.Put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
WHEN utl_smtp.transient_error THEN
dbms_output.Put_line(' Temporary e-mail issue - try again');
WHEN utl_smtp.permanent_error THEN
dbms_output.Put_line(' Permanent Error Encountered.');
/*WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 BEGIN
 utl_smtp.Quit(c);
 EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 dbms_output.Put_line(' Error Encountered.'
 ||SQLERRM);
 END;

Raise_application_error(-20000, SQLERRM); */
END;

Sample Code 2 :
DECLARE
 psender VARCHAR2(4000) := '<sender_mail_id>';
 precipient VARCHAR2(4000) := '<recipient_mail_id>';
 precipient2 VARCHAR2(4000) := '<recipient_mail_id_2>';
 psubject VARCHAR2(4000) := 'Mail Test Subject';
 pbody VARCHAR2(10000);
 crlf VARCHAR2(2) := Chr(13)||Chr(10); --can be replaced by utl_tcp.crlf
 c utl_smtp.connection;
 v_mail_host VARCHAR2(80) := '<SMTP host name>'; --SMTP host name
 pmessage VARCHAR2(4000) := '<!doctype html>

    <html>

    <head>

      <title>Test HTML message</title>

    </head>

    <body>

      <p>This is a <b>HTML</b> <i>version</i> of the test message.</p>

      <p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" />

    </body>

</html>';
BEGIN
 c := utl_smtp.Open_connection(v_mail_host, 25); --25 is SMTP Port Number

 utl_smtp.Helo(c, v_mail_host); -- utl_smtp.helo –> introduce yourself

 utl_smtp.Mail(c, psender); -- utl_smtp.MAIL –> specify the sender

 utl_smtp.Rcpt(c, precipient); -- utl_smtp.RCPT –> specify the recipient

 utl_smtp.Open_data(c);

 pbody := 'Date: ' || To_char(SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: ' || psender || crlf ||
'Subject: ' ||psubject || crlf ||
'To: ' || precipient || crlf ||
'CC: ' || precipient2 || crlf ||
pmessage || '';

 utl_smtp.Write_data(c, 'MIME-Version: 1.0' || utl_tcp.crlf ||
'Content-type: text/html' || utl_tcp.crlf || pbody);

 utl_smtp.Close_data(c);

 utl_smtp.Quit(c);
EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 BEGIN
 utl_smtp.Quit(c);
 EXCEPTION
 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
 dbms_output.Put_line(' Error Encountered.'
 ||SQLERRM);
 END;

 Raise_application_error(-20000, SQLERRM);
END;

Sample Code 3 :Emails with BLOB Attachments

DECLARE
 p_to VARCHAR2(200) := 'abhishek.bajpai@xxx.co.in';
 p_from VARCHAR2(200) := 'no-reply@xxx.co.in';
 p_subject VARCHAR2(500) := 'Mail Test Subject';
 p_text_msg VARCHAR2(1000) :=
'<!doctype html> <html> <head> <title>Test HTML message</title> </head> <body> This is a HTML version of the test message. Thanks, Abhishek, </body> </html>'
 ;
 p_attach_name VARCHAR2(200) := NULL;
 --'Format of CA certificate for MSME.docx';
 p_attach_mime VARCHAR2(200) := NULL;
 --'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
 p_attach_blob BLOB := NULL;
 p_smtp_host VARCHAR2(80) := 'smtp-relay.gmail.com'; --SMTP host nam
 p_smtp_port NUMBER := 25;
 l_mail_conn utl_smtp.connection;
 l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
 l_step PLS_INTEGER := 57;
BEGIN
 /*CREATE TABLE XX_blob_table
 (
 id_num NUMBER,
 FILE_NAME VARCHAR2 (256),
 FILE_CONTENT_TYPE VARCHAR2 (256),
 blob_text BLOB
 );

 Insert into XX_blob_table
 select FILE_ID, FILE_NAME, FILE_CONTENT_TYPE, FILE_DATA
 from fnd_lobs
 WHERE FILE_NAME = 'Format of CA certificate for MSME.docx'; */
 SELECT file_name,
 file_content_type,
 blob_text
 INTO p_attach_name, p_attach_mime, p_attach_blob
 FROM xx_blob_table
 WHERE id_num = 14156456;

 l_mail_conn := utl_smtp.Open_connection(p_smtp_host, p_smtp_port);

 utl_smtp.Helo(l_mail_conn, p_smtp_host);

 utl_smtp.Mail(l_mail_conn, p_from);

 utl_smtp.Rcpt(l_mail_conn, p_to);

 utl_smtp.Open_data(l_mail_conn);

 utl_smtp.Write_data(l_mail_conn, 'Date: '
 ||
 To_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'To: '
 || p_to
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'From: '
 || p_from
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Subject: '
 || p_subject
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Reply-To: '
 || p_from
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'MIME-Version: 1.0'
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="'
 || l_boundary
 || '"'
 || utl_tcp.crlf
 || utl_tcp.crlf);

 IF p_text_msg IS NOT NULL THEN
 utl_smtp.Write_data(l_mail_conn, '--'
 || l_boundary
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn,
 'Content-Type: text/html; charset="iso-8859-1"'
 || utl_tcp.crlf
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, p_text_msg);

 utl_smtp.Write_data(l_mail_conn, utl_tcp.crlf
 || utl_tcp.crlf);
 END IF;

 IF p_attach_name IS NOT NULL THEN
 utl_smtp.Write_data(l_mail_conn, '--'
 || l_boundary
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Content-Type: '
 || p_attach_mime
 || '; name="'
 || p_attach_name
 || '"'
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn, 'Content-Transfer-Encoding: base64'
 || utl_tcp.crlf);

 utl_smtp.Write_data(l_mail_conn,
 'Content-Disposition: attachment; filename="'
 || p_attach_name
 || '"'
 || utl_tcp.crlf
 || utl_tcp.crlf);

 FOR i IN 0 .. Trunc((dbms_lob.Getlength(p_attach_blob) - 1 )/l_step) LOOP
 utl_smtp.Write_data(l_mail_conn, utl_raw.Cast_to_varchar2(
 utl_encode.Base64_encode(
 dbms_lob.Substr(p_attach_blob,
 l_step
 , i * l_step + 1)))
 || utl_tcp.crlf);
 END LOOP;

 utl_smtp.Write_data(l_mail_conn, utl_tcp.crlf);
 END IF;

 utl_smtp.Write_data(l_mail_conn, '--'
 || l_boundary
 || '--'
 || utl_tcp.crlf);

 utl_smtp.Close_data(l_mail_conn);

 utl_smtp.Quit(l_mail_conn);
END;

Oracle Apps, Oracle APEX and PL/SQL

Thursday, 17 October 2019

UTL_SMTP API to send HTML Emails from PL/SQL

No comments:

Post a Comment

About Me