Skip to Main Content
  • Questions
  • Does UTL_MAIL have an attachment limit of 32k

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sonia.

Asked: October 16, 2007 - 11:01 am UTC

Last updated: November 04, 2016 - 3:36 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Tom,

We have a requirement to send email with attachments using pl/sql.
We will be querying data from the database, creating a file and attaching it to an email.

I been reading about the UTL_MAIL send_attach_varchar2 procedure to send emails, however I have read on a few sites that there is a limit of 32K for attachments. Is that a true fact and if so how would I get around the limitation.


Thanks
Sonia

and Tom said...

The interface to utl_mail for attachments accepts either a 32k RAW or 32k VARCHAR2.

You might be able to use the java mail api to do this with blobs/clobs

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255615160805

but.... My actual preference would be to

a) store the attachment in my database.
b) email a very small email with a link. the link points to my database - using a URL like I do with my files on this site (see the files tab on the home page, I upload 'attachments' into my database and then email links out - rather than emailing out a big attachment when I can)

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

e-mailing attachments > 32k

Lorenzo J. Simaika, October 18, 2007 - 7:52 am UTC

hi Tom,
Sonia,
we have successfully used UTL_MAIL but use the older UTL_SMTP to send attachments larger than 32k.
I have appreciated Oracle's MAILDEMO package as a starting point to build attachments of any size, as our data is character-based.

Regards,
Lorenzo
Oracle DBA

Large email bodies and attachments

Barry C, October 22, 2007 - 5:00 am UTC

We use a custom mail utility in my shop as opposed to UTL_MAIL... it leverages UTL_TCP instead. It started out as code I found elsewhere, but later made significant modifications. I permit CLOB as the body text and BLOB for attachment. Size is limited only by what your mail server sets as a limit and the recipients mail server sets as a limit.

Free download to whomever wants it... definitely open source so feel free to modify as desired.

You can grab from my site => http://www.myoracleportal.com or http://plnet.org .

Email with URL instead of attachment

A reader, September 28, 2009 - 10:43 am UTC

Hi Tom,

Could you please elaborate on your earlier comment on using a URL instead of sending an attachment in an email.
"email a very small email with a link. the link points to my database"

I've few files saved in the database and will be helpful to know how to send URL links in emails.

Thanks
Krishna

Tom Kyte
September 30, 2009 - 7:19 am UTC

use the same url you would use in a browser??

for example, I've uploaded files into my database - click on the files tab from the home page. Hover over a link, that is my link to retrieve my file

What you YOUR link to retrieve YOUR file. Use that

Email with URL instead of attachment

A reader, October 06, 2009 - 4:00 pm UTC

Thanks Tom.

Are you referring to using Mod_PLSQL module?
Unfortunately, it is not supported in my company and I don't know if there's another approach to achieve it. If you have any inputs on alternate ways, please share.

Tom Kyte
October 08, 2009 - 7:14 am UTC

I'm referring to

a) make the attachment available as a URL using whatever means you want, that could be
1) publish a file to a file system
2) use mod_plsql
3) use 15 tiers and j2ee
4) whatever else you want


A reader, September 17, 2010 - 11:44 am UTC

Hi Tom,

I am in the need of sending emails which will have large body and large attachment as well, I was going through this thread and i found below section.

------
We use a custom mail utility in my shop as opposed to UTL_MAIL... it leverages UTL_TCP instead. It
started out as code I found elsewhere, but later made significant modifications. I permit CLOB as
the body text and BLOB for attachment. Size is limited only by what your mail server sets as a
limit and the recipients mail server sets as a limit.

Free download to whomever wants it... definitely open source so feel free to modify as desired.

You can grab from my site => http://www.myoracleportal.com or http://plnet.org .
------
However, when i tried to download/go through the code it is not opening, can you please help me here.

Thanks in Advance.
Regards,
Kumar.
Tom Kyte
September 17, 2010 - 1:01 pm UTC

nope, I didn't have anything to do with that code. That was someone posting that three years ago.

Otto, September 21, 2010 - 3:40 pm UTC

Tom Kyte
September 21, 2010 - 4:15 pm UTC

Allentown PA - neat, I grew up in Bethlehem PA right next door...

Subject gets truncated in using utl_mail.

mfz, October 10, 2012 - 8:33 am UTC

Tom -

I am on 10g release 2 on windows 64 bit.

When I tried to send an email with subject more than 44( or so ) characters . it gets truncated.

Is there any limitation in using this package ?

I could not see any limitations specified in the documentation or in metalink.



Code:
====

declare
begin
utl_mail.send ( sender => 'sender@ru.com" ,
recipients => "powerusers@ru.com" ,
subject => "Process is completed for both Sales Order Data."
test => "Test"

end ;




Output:
-------



Subject: Process is completed for both Sales Order D

=?WINDOWS-1252?Q?ata.?=
X-Priority: 3
Content-Type: multipart/mixed;
boundary="------------4D8C24=_23F7E4A13B2357B3"

This is a multi-part message in MIME format.
--------------4D8C24=_23F7E4A13B2357B3
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Test

Tom Kyte
October 10, 2012 - 9:20 am UTC

I see some filed issues regarding this with support.

Your options would be

a) file an issue with support and see if a patch exists for your system.

b) use utl_smtp to send the mail yourself.

A reader, October 10, 2012 - 2:15 pm UTC

Thanks Tom.

Need a pl/sql which would output the sql results to excel and send it as attachment

salma, February 06, 2013 - 10:02 am UTC

Need a pl/sql which would output the sql results to excel and send it as attachment
Tom Kyte
February 06, 2013 - 2:07 pm UTC

time to start writing it then? you are a programmer right? you don't just expect to find your work done for you on the internet?

search this site for owa_sylk, it'll output something excel can read.

you already know how to send an attachment - we have it here.

so put those together and you'll have accomplished the task you were given...

Zahir, November 01, 2016 - 3:33 pm UTC

I have a package to send an email . In that package , I am using utl_mail .

Today , this job failed as the attachment is over 24 KB . My understanding is that it was 32 K . Has it been changed in 12c R1.


SQL> set serveroutput on
SQL> declare
2 begin
3 pkg_utils.send_email('testattachment.TXT', 'testattachment.TXT' , 'xxx@xx.xx.edu' , 'xxx@xx.xxx.edu' , 'test' , 'Test Body', 'REPORTS');
4 end ;
5 /
Email 4
Attachment Size: 24645 bytes
Unable to Send Email - Attachment Too Large

PL/SQL procedure successfully completed.

D:\datareports>dir te*
Volume in drive D has no label.
Volume Serial Number is 57A5-G576

Directory of D:\datareports

11/01/2016 11:25 AM 24,645 testattachment.TXT
1 File(s) 24,645 bytes
0 Dir(s) 79,806,844,928 bytes free



SQL> set linesize 1000
SQL> select * from v$version ;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

Connor McDonald
November 04, 2016 - 3:36 am UTC

If it is a binary attachment, we encode them to base64 as part of the process. This may have cause the size to exceed 32767.


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here