Re: How to send pdf as attachment with email in oracle

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

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