Email from pl_sql with attaments using SMTP

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:

Email from pl_sql with attaments using SMTP

Post by admin »

To show how to send an E-Mail with attachments greater than 32KB using UTL_SMTP package.

When using UTL_MAIL or UTL_SMTP package to send an E-Mail with attachment, there is a limitation that the size of the attachment cannot exceed 32KB because maximum size of a variable in PL/SQL cannot be more than 32KB. This limitation applies to both binary and text attachments.

To attach a file which is more than 32KB, the code need to follow "chunking" methodology. Using this method the attachments can be added in chunks where each chunks can be maximum of 32KB until the whole file has been attached.

Download the attachment "demo_mail.sql" and modify the variables "smtp_host" and "smtp_port" in the code according to your environment and create the package. Then call the demo_mail package to send E-Mail as below.

set serveroutput on;
declare
po_err_msg varchar2(1000);
begin
demo_mail.send_email
(
P_DIRECTORY => '<plslq_directory_object_name>', --replace with your directory object
P_SENDER => '<sender_email>',
P_RECIPIENT => '<recipient_email>',
P_CC => '<cc_list>,
P_BCC => '<bcc_list>,
P_SUBJECT => '<subject_line>',
P_BODY => '<mail_body>',
P_ATTACHMENT1 => null, --replace with your attachment filename or null.
P_ATTACHMENT2 => null, --replace with your attachment filename or null
P_ATTACHMENT3 => null, --replace with your attachment filename or null
P_ATTACHMENT4 => null, --replace with your attachment filename or null
P_ERROR => po_err_msg
);
if po_err_msg <> '0' then
dbms_output.put_line('Call to demo_mail failed: '|| po_err_msg);
end if;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error while calling demo_mail package: '||sqlerrm);
end;
/

How To Send Multiple Attachments Of Size Greater Than 32 KB Using UTL_SMTP Package (Doc ID 357385.1)
demo_mail.rar
(4.03 KiB) Downloaded 335 times
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests