Skip to Main Content
  • Questions
  • email attachment but not enable to open it

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, rawan.

Asked: July 27, 2016 - 12:12 pm UTC

Last updated: August 01, 2016 - 10:48 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I am trying to send mail with attachment pdf file i recieved the mail but when am trying to open the pdf file is return error massege
please help am stuck

DECLARE
v_From VARCHAR2(80) :='test@ttt.net';
v_Recipient VARCHAR2(80) := 'roro_ad@hotmail.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.ttt.net';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||

'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/pdf;'|| crlf ||
'name="Rprtgroupleft.pdf"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
'filename="G:\RAWAN\Rprtgroupleft.pdf"'|| crlf ||
crlf ||
'pdf,file,attachement'|| crlf || -- Content of attachment
crlf ||

'-------SECBOUND--' -- End MIME mail
);

utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail', TRUE);
END;
/

and Chris said...

You've provided an attachment name, but not actually attached it!

You need to pass the PDF as a blob to your procedure. So you need to:

- Read the file from the database server
- Pass this to your procedure

You can read the file using UTL_File:
https://oracle-base.com/articles/9i/export-blob-9i

Or external tables:

https://oracle-base.com/articles/10g/external-tables-containing-lob-data

Once you've done this, modify your code to accept the blob as a parameter or read it from a table. Then write the contents of it to the message:

https://oracle-base.com/articles/misc/email-from-oracle-plsql#attachment

Note: if the PDF is on your local machine, not the database server, Oracle can't see it!

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

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here