PLSQL email code using AUTH LOGIN

In this forum Oracle Applications DBA's, System Administrators & Developers can share their knowledge/issues.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

PLSQL email code using AUTH LOGIN

Post by admin »

CREATE OR REPLACE PROCEDURE erpstuff_email (fromm VARCHAR2,
too VARCHAR2,
sub VARCHAR2,
body VARCHAR2,
port NUMBER)
IS
/*
Sample code for R&D purpose only using on PROD need proper testing and UAT hence no liability on ERPstuff.com
Verrsion 1.0 by Sikandar Hayat
info@erpstuff.com
*/
objConnection UTL_SMTP.connection;
vrData VARCHAR2 (32000);
username VARCHAR2 (200) := 'sikandar';
password VARCHAR2 (200) := 'hayat123';
BEGIN
objConnection := UTL_SMTP.open_connection ('mail.smtp2go.com', port);
UTL_SMTP.ehlo (objConnection, 'erpstuff.com');
UTL_SMTP.command (objConnection, 'AUTH LOGIN');

utl_smtp.command(objConnection,username);
utl_smtp.command(objConnection,password);

UTL_SMTP.command (
objConnection,
UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (username))));
UTL_SMTP.command (
objConnection,
UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (password))));

UTL_SMTP.mail (objConnection, fromm);
UTL_SMTP.rcpt (objConnection, too);
UTL_SMTP.open_data (objConnection);

UTL_SMTP.write_data (objConnection, 'From: ' || fromm || UTL_TCP.crlf);
UTL_SMTP.write_data (objConnection, 'To: ' || too || UTL_TCP.crlf);

UTL_SMTP.write_data (objConnection, 'Subject: ' || sub || UTL_TCP.crlf);
UTL_SMTP.write_data (objConnection,
'MIME-Version: ' || '1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data (objConnection, 'Content-Type: ' || 'text/html;');

UTL_SMTP.write_data (
objConnection,
'Content-Transfer-Encoding: ' || '"8Bit"' || UTL_TCP.crlf);
UTL_SMTP.write_data (objConnection, UTL_TCP.crlf);
UTL_SMTP.write_data (objConnection, UTL_TCP.crlf || '<HTML>');
UTL_SMTP.write_data (objConnection, UTL_TCP.crlf || '<BODY>');
UTL_SMTP.write_data (
objConnection,
UTL_TCP.crlf
|| '<FONT COLOR="red" FACE="Courier New">'
|| body
|| '</FONT>');
UTL_SMTP.write_data (objConnection, UTL_TCP.crlf || '</BODY>');
UTL_SMTP.write_data (objConnection, UTL_TCP.crlf || '</HTML>');
UTL_SMTP.close_data (objConnection);
UTL_SMTP.quit (objConnection);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
UTL_SMTP.quit (objConnection);
DBMS_OUTPUT.put_line (SQLERRM);
WHEN OTHERS
THEN
UTL_SMTP.quit (objConnection);
DBMS_OUTPUT.put_line (SQLERRM);
END erpstuff_email;
/

-- script to send 20 emails to test

DECLARE
vdate VARCHAR2 (250);
BEGIN
vdate := TO_CHAR (SYSDATE, 'dd-mon-yyyy HH:MI:SS AM');

FOR i IN 1 .. 20
LOOP
erpstuff_email (‘info@errpstuff.com',
admin@erpstuff.com',
'Oracle test',
'This is a UTL_SMTP-generated email at ' || vdate,
2525);
END LOOP ;
END ;

Reference:
How to Send an Email Using UTL_SMTP with Authenticated Mail Server. (Doc ID 885522.1)
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest