Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple. Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips .
Thread Rating:
  • 19 Vote(s) - 3.11 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Email From Oracle PL/SQL
01-06-2013, 06:32 PM,
#1
Email From Oracle PL/SQL
The UTL_SMTP package can be used to send emails from PL/SQL.

Emails:

In it’s simplest form a single string or variable can be sent as the message body using the following procedure. In this case we have not included any header information or subject line in the message, so it is not very useful, but it is small.

CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,

p_from IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;


BEGIN


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.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);


UTL_SMTP.quit(l_mail_conn);


END;


/


The code below shows how the procedure is called.


BEGIN


send_mail(p_to => ‘me@mycompany.com’,


p_from => ‘admin@mycompany.com’,


p_message => ‘This is a test message.’,


p_smtp_host => ‘smtp.mycompany.com’);


END;


/


Multi-Line Emails


Multi-line messages can be written by expanding the UTL_SMTP.DATA command using the UTL_SMTP.WRITE_DATA command as follows. This is a better method to use as the total message size is no longer constrained by the 32K limit on a VARCHAR2 variable. In the following example the header information has been included in the message also.


CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,


p_from IN VARCHAR2,


p_subject IN VARCHAR2,


p_message IN VARCHAR2,


p_smtp_host IN VARCHAR2,


p_smtp_port IN NUMBER DEFAULT 25)


AS


l_mail_conn UTL_SMTP.connection;


BEGIN


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




UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);


UTL_SMTP.close_data(l_mail_conn);




UTL_SMTP.quit(l_mail_conn);


END;


/


The code below shows how the procedure is called.


BEGIN


send_mail(p_to => ‘me@mycompany.com’,


p_from => ‘admin@mycompany.com’,


p_subject => ‘Test Message’,


p_message => ‘This is a test message.’,


p_smtp_host => ‘smtp.mycompany.com’);


END;


/


HTML Emails


The following procedure builds on the previous version, allowing it include plain text and/or HTML versions of the email. The format of the message is explained here.


CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,


p_from IN VARCHAR2,


p_subject IN VARCHAR2,


p_text_msg IN VARCHAR2 DEFAULT NULL,


p_html_msg IN VARCHAR2 DEFAULT NULL,


p_smtp_host IN VARCHAR2,


p_smtp_port IN NUMBER DEFAULT 25)


AS


l_mail_conn UTL_SMTP.connection;


l_boundary VARCHAR2(50) := ‘—-=*#abc1234321cba#*=’;


BEGIN


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/alternative; 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/plain; 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_html_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_html_msg);


UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || 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;


/


The code below shows how the procedure is called.


DECLARE


l_html VARCHAR2(32767);


BEGIN


l_html := ‘



Test HTML message






This is a HTML version of the test message.



”Site




’;




send_mail(p_to => ‘me@mycompany.com’,


p_from => ‘admin@mycompany.com’,


p_subject => ‘Test Message’,


p_text_msg => ‘This is a test message.’,


p_html_msg => l_html,


p_smtp_host => ‘smtp.mycompany.com’);


END;


/


Emails with Attachments


Sending an email with an attachment is similar to the previous example as the message and the attachment must be separated by a boundary and identified by a name and mime type.


BLOB Attachment


Attaching a BLOB requires the binary data to be encoded and converted to text so it can be sent using SMTP.


CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,


p_from IN VARCHAR2,


p_subject IN VARCHAR2,


p_text_msg IN VARCHAR2 DEFAULT NULL,


p_attach_name IN VARCHAR2 DEFAULT NULL,


p_attach_mime IN VARCHAR2 DEFAULT NULL,


p_attach_blob IN BLOB DEFAULT NULL,


p_smtp_host IN VARCHAR2,


p_smtp_port IN NUMBER DEFAULT 25)


AS


l_mail_conn UTL_SMTP.connection;


l_boundary VARCHAR2(50) := ‘—-=*#abc1234321cba#*=’;


l_step PLS_INTEGER := 24573;


BEGIN


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/plain; 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))));


END LOOP;




UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || 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;


/


The code below shows how the procedure is called.


DECLARE


l_name images.name%TYPE := ‘site_logo.gif’;


l_blob images.image%TYPE;


BEGIN


SELECT image


INTO l_blob


FROM images


WHERE name = l_name;




send_mail(p_to => ‘me@mycompany.com’,


p_from => ‘admin@mycompany.com’,


p_subject => ‘Test Message’,


p_text_msg => ‘This is a test message.’,


p_attach_name => ‘site_logo.gif’,


p_attach_mime => ‘image/gif’,


p_attach_blob => l_blob,


p_smtp_host => ‘smtp.mycompany.com’);


END;


/


CLOB Attachment


Attaching a CLOB is similar to attaching a BLOB, but we don’t have to worry about encoding the data because it is already plain text.


CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,


p_from IN VARCHAR2,


p_subject IN VARCHAR2,


p_text_msg IN VARCHAR2 DEFAULT NULL,


p_attach_name IN VARCHAR2 DEFAULT NULL,


p_attach_mime IN VARCHAR2 DEFAULT NULL,


p_attach_clob IN CLOB DEFAULT NULL,


p_smtp_host IN VARCHAR2,


p_smtp_port IN NUMBER DEFAULT 25)


AS


l_mail_conn UTL_SMTP.connection;


l_boundary VARCHAR2(50) := ‘—-=*#abc1234321cba#*=’;


l_step PLS_INTEGER := 24573;


BEGIN


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/plain; 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-Disposition: attachment; filename=”‘ || p_attach_name || ‘”‘ || UTL_TCP.crlf || UTL_TCP.crlf);




FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) – 1 )/l_step) LOOP


UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));


END LOOP;




UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || 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;


/


The code below shows how the procedure is called.


DECLARE


l_clob CLOB := ‘This is a very small CLOB!’;


BEGIN


send_mail(p_to => ‘me@mycompany.com’,


p_from => ‘admin@mycompany.com’,


p_subject => ‘Test Message’,


p_text_msg => ‘This is a test message.’,


p_attach_name => ‘test.txt’,


p_attach_mime => ‘text/plain’,


p_attach_clob => l_clob,


p_smtp_host => ‘smtp.mycompany.com’);


END;


/


Miscellaneous


For emails with multiple recipients, simply call the RCPT procedure once for each separate email address.


The UTL_SMTP package requires Jserver which can be installed by running the following scripts as SYS.


SQL> @$ORACLE_HOME/javavm/install/initjvm.sql


SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql

Reply
Thanks given by:


Possibly Related Threads…
Thread Author Replies Views Last Post
  Oracle HCM Cloud Interview Questions MM Ifan 1 50,208 06-06-2021, 12:16 AM
Last Post: zaidmd
  Oracle Implementation Project Plan admin 35 140,880 04-29-2021, 06:59 AM
Last Post: Cwahsayz
  Oracle ERP Implementation Project Schedule based on OUM Kashif Manzoor 7 25,549 03-31-2021, 11:45 PM
Last Post: haydara
  Oracle Financials - Data Flow From SLA to GL admin 8 79,306 12-17-2020, 11:26 PM
Last Post: cok3cl
  Oracle Inventory Module End User Training Material James Robert 1 29,385 08-27-2020, 08:59 PM
Last Post: shahid
  Oracle Inventory Miscellaneous Transactions Mark Donald 1 27,956 08-20-2020, 05:34 PM
Last Post: shahid
  Oracle Fusion Financials General Ledger Essentials M Irfan 5 92,145 08-20-2020, 05:32 PM
Last Post: shahid
  Oracle AIM - RD050 Business Requirement Documents admin 10 107,032 06-04-2020, 05:28 AM
Last Post: Shashi
  Oracle AIM - BR100 Documents admin 37 242,939 05-13-2020, 06:53 PM
Last Post: amjad.orafunctional
  AIM Vs OUM - Application Implementation Methodology vs Oracle Unified Methodology Kashif Manzoor 7 43,126 12-11-2019, 10:50 PM
Last Post: abuzeids



Users browsing this thread: 1 Guest(s)
Open Tech Talks Get Oracle Tutorials, Tips! Post a Question!