How to send pdf as attachment with email in oracle

[wE|rd0]

I am successfully receiving email through Oracle but opening the pdf file, I get the error:-
"Adobe Reader could not open "ExistingFile.pdf" because it is either not a supported file type or because the file has been damaged(for example, it was sent as an email attachment and wasn't correctly decoded).

Below is the code that I am using:-

create or replace PROCEDURE mail_attachments_new

AS
v_From VARCHAR2(80) := 'pay...@companyname.com';
v_Recipient VARCHAR2(80) := 'amir....@companyname.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := '193.10.10.3';
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: 3.1'|| 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: application/pdf;'|| crlf ||
' name="C:\Email\ExistingFile.pdf"'|| crlf ||
'Content-Transfer_Encoding: base64'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="ExistingFile.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: '||sqlerrm);

END mail_attachments_new;

Any ideas how to fix it?

Dont have the option of attaching a file. Otherwise, I would have attached the snapshot of the error.

[wE|rd0]

I now think its occuring because Oracle tries to create a pdf at run-time rather than sending the existing pdf in the directory.

But, I cant find the sample code for sending existing pdf file in oracle.

Others help would be highly appreciated.

[Mladen Gogala]

You should use UTL_MAIL, which has an option for adding attachments. The
procedure name is SEND_ATTACH_RAW.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Je suis Charlie

[wE|rd0]

Thanks.

[sunnych...@gmail.com]

Hello Mladen,
I tried sending the pdf attachment using UTL_MAIL.send_attach_raw . But getting this error "Adobe Reader could not open "ExistingFile.pdf" because it is either not a supported file type or because the file has been damaged(for example, it was sent as an email attachment and wasn't correctly decoded). Please help me regarding this.

[Mladen Gogala]

On Tue, 28 May 2019 04:56:10 -0700, sunnychadha.90 wrote:


> Hello Mladen,
> I tried sending the pdf attachment using UTL_MAIL.send_attach_raw . But
> getting this error "Adobe Reader could not open "ExistingFile.pdf"
> because it is either not a supported file type or because the file has
> been damaged(for example, it was sent as an email attachment and wasn't
> correctly decoded). Please help me regarding this.

UTL_MAIL is in desperate need of maintenance, it still uses the
officially deprecated RAW data type. You will need to read the file
into a RAW variable using UTL_FILE.GET_RAW. Forget everything you
learned about CLOB data type, UTL_MAIL still uses the arcane RAW data
type. I have had such a problem once and I resolved it by creating an
external process that would read the addressee, message and the
attachment and send from DBMS_PIPE and send it out using MIME::Lite,
which is a Perl module. There are other scripting languages starting
with "p" and one very popular in recent times requires you to write
the following:

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

instead of use MIME::Lite;

However, that newer monstrosity, which uses tabs like COBOL, has
become de facto standard and I had to learn it. Of course, that
doesn't mean that I don't hate it with passion. If I needed to write
the same thing today, I would still use DBMS_PIPE and an external
process to send messages, but would have to write 3 times as many
lines as with the good, aold Perl. If you are really courageous, you
can execute Perl directly from the database:

https://flylib.com/books/en/2.315.1.55/1/
https://metacpan.org/pod/release/JHORWITZ/extproc_perl-0.97/extproc_perl.pod

This is an excerpt for an excellent book "Perl for Oracle DBA" by
Jared Still and Andy Duncan. Be aware that the administrators of
the most production databases will prevent you from embedding Perl
or Python into Oracle database and will bitterly fight any attempt
to do so, with good arguments. Allowing Perl or Python scripts in
the database opens a whole can of security worms. I have been an
Oracle DBA for around a quarter of a century and despite my fondness
for Perl, I have never allowed it to be used from the database. The
problem is that Perl allows too much and can trivially be used to
compromise database security.


Also, there is another question: why do you need to send emails with
attachments from the database? There already is a package which uses
a relational database to store email data and attachments. The
package is called "Exchange" and is quite widely used. Oracle even
used to have "Oracle Office" package, but gave it up because they
couldn't dislodge Exchange.





--
Mladen Gogala
Database Consultant
http://mgogala.byethost5.com

[jo...@mail.groenveld.us]

In article <qcrq77$e8o$1...@solani.org>,

Mladen Gogala <gogala...@gmail.com> wrote:
>Jared Still and Andy Duncan. Be aware that the administrators of
>the most production databases will prevent you from embedding Perl
>or Python into Oracle database and will bitterly fight any attempt
>to do so, with good arguments. Allowing Perl or Python scripts in
>the database opens a whole can of security worms. I have been an
>Oracle DBA for around a quarter of a century and despite my fondness
>for Perl, I have never allowed it to be used from the database. The
>problem is that Perl allows too much and can trivially be used to
>compromise database security.

Just poll the database for new PDFs to spew out over email
to assuage the risk-averse DBAs.

Keep the interprocess asynchronous option in your back pocket
for when Larry buys your option pack.

ISTR that's how Oracle's text search option came to be.
John
groe...@acm.org

[John D Groenveld]

In article <qcrq77$e8o$1...@solani.org>,
Mladen Gogala <gogala...@gmail.com> wrote:

>Jared Still and Andy Duncan. Be aware that the administrators of
>the most production databases will prevent you from embedding Perl
>or Python into Oracle database and will bitterly fight any attempt
>to do so, with good arguments. Allowing Perl or Python scripts in
>the database opens a whole can of security worms. I have been an
>Oracle DBA for around a quarter of a century and despite my fondness
>for Perl, I have never allowed it to be used from the database. The
>problem is that Perl allows too much and can trivially be used to
>compromise database security.

[Mladen Gogala]

On Sat, 01 Jun 2019 16:47:31 +0000, John D Groenveld wrote:

> In article <qcrq77$e8o$1...@solani.org>,
> Mladen Gogala <gogala...@gmail.com> wrote:
>>Jared Still and Andy Duncan. Be aware that the administrators of the
>>most production databases will prevent you from embedding Perl or Python
>>into Oracle database and will bitterly fight any attempt to do so, with
>>good arguments. Allowing Perl or Python scripts in the database opens a
>>whole can of security worms. I have been an Oracle DBA for around a
>>quarter of a century and despite my fondness for Perl, I have never
>>allowed it to be used from the database. The problem is that Perl allows
>>too much and can trivially be used to compromise database security.
>
> Just poll the database for new PDFs to spew out over email to assuage
> the risk-averse DBAs.

Of course DBA personnel is risk-averse. DBA is tasked with protecting the
data and the database security. Their heads are the first on the chopping
block when there is an intrusion and data theft. In addition to that,
they do not own the data. No DBA has the right to risk exposing my SSN,
bank account, drivers license or any other piece of private information.
Many years ago, I exposed a really serious flaw of Oracle 10G and
external procedures and I took a lot of flak for it, and rightfully so.
I had no right to provide a recipe for stealing other people's data.

>
> Keep the interprocess asynchronous option in your back pocket for when
> Larry buys your option pack.

What is this supposed to mean? And what does Larry have to do with the
whole stuff?

>
> ISTR that's how Oracle's text search option came to be.
> John groe...@acm.org

Oracle text search was included into database for business reasons. If
there are documents in the database, there is a need to look for specific
terms. Regular expressions will not be of much use here. There are many
other popular text search engines, besides Oracle's, like Lucene, Sphinx,
Xapian or Elasticsearh. Other databases have text search engines too.
Presumably, they were not all bought by Larry?

[John D Groenveld]

In article <qdgpi1$k2m$1...@solani.org>,

Mladen Gogala <gogala...@gmail.com> wrote:
>> Keep the interprocess asynchronous option in your back pocket for when
>> Larry buys your option pack.
>
>What is this supposed to mean? And what does Larry have to do with the
>whole stuff?

By having your software operate in two modes you make it compelling
to two audiences: customers and Oracle M&A.

>Oracle text search was included into database for business reasons. If

Oracle Text Server (forerunner to Context) was a smart addition to
the database.

John
groe...@acm.org

[jeremy]

In article <qcrq77$e8o$1
@solani.org>,
gogala...@gmail.com says...

> Also, there is another question: why do you need to send emails with
> attachments from the database?
>

Hi Mladen, why this question of
yours? Suppose you generate a rental
contract into PDF within Oracle and
wish to send it to the customer -
there are thousands of scenarios
where you want to store a document
(whether PDF or any form) in Oracle
and deliver it to someone via email.

Sorry about the 36 char line length
- no idea what's up with my usenet
client :)


--
jeremy

[Mladen Gogala]

Sending emails is, at least in my opinion, a job for the application, not
the database. Database is a data store: place where you store the data.
It can control data consistency, return data to an application in ever
which way, but sending the data out is, IMHO, an application job.

[jeremy]

In article <qe033g$cck$1
@solani.org>,
gogala...@gmail.com says...

Supposing that application is
written in PL/SQL?


--
jeremy