You can download a file from filesystem like this:
FUNCTION GetFile(FolderName IN VARCHAR2, FileName IN VARCHAR2) RETURN BLOB IS
xbfile BFILE;
xblob BLOB;
destOffset INTEGER := 1;
srcOffset INTEGER := 1;
BEGIN
xbfile := BFILENAME (FolderName, FileName);
DBMS_LOB.OPEN (xbfile);
DBMS_LOB.CREATETEMPORARY(xblob, TRUE, DBMS_LOB.SESSION);
DBMS_LOB.LOADBLOBFROMFILE(
dest_lob => xblob,
src_bfile => xbfile,
amount => DBMS_LOB.GETLENGTH(xbfile),
dest_offset => destOffset,
src_offset => srcOffset);
DBMS_LOB.CLOSE(xbfile);
RETURN xblob;
END GetFile;
In order to access filesystem you must create a DIRECTORY (if not already exists):
CREATE OR REPLACE DIRECTORY PDF_REPORTS AS 'C:\Downloads';
In my application I use this generic package to send mails:
CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
/
CREATE OR REPLACE PACKAGE Mailing AS
PRIORITY_HIGH CONSTANT INTEGER := 1;
PRIORITY_NORMAL CONSTANT INTEGER := 3;
PRIORITY_LOW CONSTANT INTEGER := 5;
MIME_MIXED CONSTANT VARCHAR2(50) := 'multipart/mixed;';
MIME_TEXT CONSTANT VARCHAR2(50) := 'text/plain;';
MIME_HTML CONSTANT VARCHAR2(50) := 'text/html;';
PROCEDURE SendMail(
Subject IN VARCHAR2,
Message IN OUT CLOB,
ToMail IN VARCHAR_TABLE_TYPE,
FromMail IN VARCHAR2, FromName IN VARCHAR2,
PRIORITY IN INTEGER DEFAULT PRIORITY_NORMAL,
FileName IN VARCHAR2 DEFAULT NULL,
MimeType IN VARCHAR2 DEFAULT MIME_TEXT, --> determines the MIME-Type of binary attachment "BinAttachment"
TxtAttachment IN CLOB DEFAULT NULL,
BinAttachment IN BLOB DEFAULT NULL);
END Mailing;
/
CREATE OR REPLACE PACKAGE BODY Mailing AS
PROCEDURE SendMail(
Subject IN VARCHAR2,
Message IN OUT CLOB,
ToMail IN VARCHAR_TABLE_TYPE,
FromMail IN VARCHAR2, FromName IN VARCHAR2,
PRIORITY IN T_MAIL_PRIORITY DEFAULT PRIORITY_NORMAL,
FileName IN VARCHAR2 DEFAULT NULL,
MimeType IN VARCHAR2 DEFAULT MIME_TEXT,
TxtAttachment IN CLOB DEFAULT NULL,
BinAttachment IN BLOB DEFAULT NULL) IS
MIME_BOUNDARY CONSTANT VARCHAR2(50) := '====Multipart.Boundary.689464861147414354====';
con UTL_SMTP.CONNECTION;
ret UTL_SMTP.REPLY;
Charset VARCHAR2(20);
Footer VARCHAR2(1000);
Recipients VARCHAR2(1000);
LobLen PLS_INTEGER;
amount PLS_INTEGER := 8192;
BUFFER VARCHAR2(16384);
OFFSET PLS_INTEGER := 1;
isHTML BOOLEAN := REGEXP_LIKE(DBMS_LOB.SUBSTR(Message, 1000, 1), '(< *html)|(< *body)', 'i');
BEGIN
SELECT UTL_I18N.MAP_CHARSET(VALUE)
INTO Charset
FROM NLS_DATABASE_PARAMETERS
WHERE parameter = 'NLS_CHARACTERSET';
-- setup mail header
con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
ret := UTL_SMTP.HELO(con, SYS_CONTEXT('USERENV', 'DB_DOMAIN'));
ret := UTL_SMTP.MAIL(con, FromMail);
FOR i IN ToMail.FIRST..ToMail.LAST LOOP
Recipients := Recipients ||ToMail(i)||',';
ret := UTL_SMTP.RCPT(con, ToMail(i));
END LOOP;
ret := UTL_SMTP.OPEN_DATA(con);
IF REGEXP_LIKE(FromName, '^[ -~]+$') THEN
UTL_SMTP.WRITE_DATA(con, 'From: "'||FromName||'" <'||FromMail||'>'||UTL_TCP.CRLF);
ELSE
UTL_SMTP.WRITE_DATA(con, 'From: =?UTF-8?B?'|| UTL_ENCODE.TEXT_ENCODE(FromName, NULL, UTL_ENCODE.BASE64) ||'?= <'||FromMail||'>'||UTL_TCP.CRLF);
END IF;
UTL_SMTP.WRITE_DATA(con, 'To: '||REGEXP_REPLACE(Recipients, ',$')||UTL_TCP.CRLF);
IF REGEXP_LIKE(Subject, '^[ -~]+$') THEN
UTL_SMTP.WRITE_DATA(con, 'Subject: '||Subject||UTL_TCP.CRLF);
ELSE
UTL_SMTP.WRITE_DATA(con, 'Subject: =?UTF-8?B?'|| REPLACE(REPLACE(UTL_ENCODE.TEXT_ENCODE(Subject, NULL, UTL_ENCODE.BASE64), CHR(13), NULL), CHR(10), NULL) ||'?='||UTL_TCP.CRLF);
END IF;
UTL_SMTP.WRITE_DATA(con, 'Date: '||TO_CHAR(CURRENT_TIMESTAMP, 'Dy, DD Mon YYYY hh24:mi:ss TZHTZM', 'NLS_DATE_LANGUAGE = American')||UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'X-Priority: '||PRIORITY||UTL_TCP.CRLF);
IF FileName IS NOT NULL THEN
UTL_SMTP.WRITE_DATA(con, 'Mime-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MIME_MIXED||' boundary="'||MIME_BOUNDARY||'"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'This is a multipart message in MIME format.' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
END IF;
-- Append common footer to mail
Footer := 'Message from '||SYS_CONTEXT('USERENV', 'DB_NAME')||' sent at '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
IF isHTML THEN
UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_HTML||' charset='||Charset || UTL_TCP.CRLF);
Message := REPLACE(message, '</body>', '<p>'||Footer||'</p></body>');
ELSE
UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_TEXT||' charset='||Charset || UTL_TCP.CRLF);
END IF;
-- Mail Body
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
LobLen := DBMS_LOB.GETLENGTH(Message);
LOOP
EXIT WHEN OFFSET > LobLen;
DBMS_LOB.READ(Message, amount, OFFSET, BUFFER);
UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
OFFSET := OFFSET + amount;
END LOOP;
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
IF NOT isHTML THEN
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, Footer);
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
END IF;
IF FileName IS NOT NULL THEN
-- Mail Attachment
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MimeType||' name="'||Filename||'"'|| UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: attachment; filename="'||Filename||'"'|| UTL_TCP.CRLF);
OFFSET := 1;
IF TxtAttachment IS NOT NULL THEN
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
LobLen := DBMS_LOB.GETLENGTH(TxtAttachment);
LOOP
EXIT WHEN OFFSET > LobLen;
DBMS_LOB.READ(TxtAttachment, amount, OFFSET, BUFFER);
UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
OFFSET := OFFSET + amount;
END LOOP;
ELSIF BinAttachment IS NOT NULL THEN
amount := 48; -- must be a whole multiple of 3
UTL_SMTP.write_data(con, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
LobLen := DBMS_LOB.GETLENGTH(BinAttachment);
LOOP
EXIT WHEN OFFSET > LobLen;
DBMS_LOB.READ(BinAttachment, amount, OFFSET, BUFFER);
UTL_SMTP.WRITE_RAW_DATA(con, UTL_ENCODE.BASE64_ENCODE(BUFFER));
OFFSET := OFFSET + amount;
END LOOP;
END IF;
UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY||'--' || UTL_TCP.CRLF);
END IF;
-- finish mail
ret := UTL_SMTP.CLOSE_DATA(con);
ret := UTL_SMTP.QUIT(con);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
UTL_SMTP.QUIT(con);
END SendMail;
END Mailing;
/
It provides some extras:
- You can optionally attach a file (txt or binary, but only one file is supported)
From and Subject may contain Non-ASCII characters like öäü (of course the mail Body may also)- Multiple recipients
- Automatically use correct character set from database
- Automatically detects plain-text or HTML mail body
- Common footer to indicate mail
Then you can call the function like
DECLARE
pdf BLOB;
Message CLOB := 'Some message text';
BEGIN
pdf := GetFile('PDF_REPORTS','abc.pdf');
Mailing.SendMail(
Subject => 'The Subject',
Message => Message,
ToMail => VARCHAR_TABLE_TYPE('[email protected]'),
FromMail => '[email protected]',
FromName => 'DB User: '||USER,
FileName => 'abc.pdf',
BinAttachment => pdf,
MimeType => 'application/pdf' );
END;
