Sending email using Pl/sql

Sending email using Pl/sql

This Article is basically designed keeping in view of the 2 types of audience
1. Novice in UTL_SMTP
2. Programmer in UTL_SMTP

Abstract:

The EMAIL has become an integral part of almost every human's life. Without it, many things we do would become very tedious, perhaps impossible tasks.
This white paper we present an overview about using email with SQL/PLSQL using SMTP.

Executive Summary:

Oracle’s UTL_SMTP package can be used to create and send e-mails from PL/SQL routines. I wanted to be able to send not only text in an e-mail, but also html for those e-mail systems that could display the html equivalent of the message. I also wanted to be able to include multiple file attachments of various formats (pdf, jpeg, MS Word, etc.), which requires encoding the binary files into an ascii format that could be transmitted with the e-mail, and differentiating them from plain text files that could be copied as-is into the e-mail as attachments. Oracle’s UTL_ENCODE package has a BASE64_ENCODE routine that can be used to encode the binary data into a format that can be included in the e-mail. And, reading the binary file can be done with the READ routine in Oracle’s DBMS_LOB package.

The code written can used to specify any From address, so that our e-mail looks like it’s coming from us instead of from our Oracle server. We can also specify multiple To addresses along with multiple CC (carbon copy) addresses and multiple BCC (blind carbon copy) addresses. The e-mail addresses can be in any format, such as arun (which attaches @), arun@appsassociates.com, < arun@appsassociates.com >, Arun Kumar , and "Arun" < arun@appsassociates.com >, which are separated by commas or semicolons if there are multiple addresses listed.

Both a text message and an html message can be specified, either as a text string or as a file name to be included inline. Then, up to three binary or textual files can be attached to the e-mail of any MIME type, defaulting to text/plain. For the attachments, we need to specify the MIME type, a list of which can be seen here. The MIME types I’ve tried include text/plain, text/html, image/jpeg, image/gif, application/pdf, and application/msword. The attachment is assumed to be a binary file if the MIME type does not begin with “text”, and is encoded as base64 in the e-mail; otherwise, the file is just copied as-is into the e-mail as an attachment. Any file in any directory accessible to the user can be attached; but, see below about running from triggers and the potential for a commit being done.

The complete parameter list for the email procedure is shown below:

from_name - name and e-mail address to put in the From field
to_names - names and e-mail addresses for the To field
(separated by commas or semicolons)
subject - text string for Subject field
message - text string or unix text file name for the message, if any
html_message - html string or unix html file name for the message, if any
cc_names - names and e-mail addresses for the CC field, if any
(separated by commas or semicolons)
bcc_names - names and e-mail addresses for the BCC field, if any
(separated by commas or semicolons)
filename1 - first unix file pathname to attach, if any
filetype1 - MIME type of the first file (defaults to 'text/plain')
filename2 - second unix file pathname to attach, if any
filetype2 - MIME type of the second file (defaults to 'text/plain')
filename3 - third unix file pathname to attach, if any
filetype3 - MIME type of the third file (defaults to 'text/plain')

A sample call in PL/SQL is shown below, which sends a text and html message, plus a text file and two binary files (note: the slash after "end;" must be the first character on it's line):

begin
email_files(from_name => 'XXX@yahoo.com' ,
to_names => 'XXXX@yahoo.com',
subject => 'A test',
message => 'A test message',
html_message => '

A test message

',
filename1 => '/tmp/web_ptrbdca.txt',
filename2 => '/tmp/password_standards.pdf',
filetype2 => 'application/pdf',
filename3 => '/tmp/wb703.jpg',
filetype3 => 'image/jpeg');
end;
/

The user running this must have "create any directory" and "drop any directory" privileges ("create directory" was introduced in Oracle 9iR2), which must be granted from a system or dba account, such as:
grant create any directory to scott;
grant drop any directory to scott;
connect / as sysdba
grant select on dba_directories to scott;
or, for everyone to have directory privileges:
grant create any directory to public;
grant drop any directory to public;
connect / as sysdba
grant select on dba_directories to public;

We may also want to create a public synonym for this procedure, from the procedure's owner:
create or replace public synonym email_files for email_files;
grant execute on email_files to public;

If we use email procedure to send e-mails with attachments from triggers, we will first have to create an Oracle directory entry, such as shown below, in order to keep from getting a commit error in the trigger. If email files procedure doesn't find an existing Oracle directory entry for our attachment file's directory, it creates a temporary one. The "create directory" command is a DDL statement, which causes an implicit commit; but, commits aren't allowed in triggers. Also, if we are sending email attachments from a PL/SQL routine that we don't want a commit done in, we will need to have an Oracle directory entry pre-created as well, such as for /home/common shown below:
create directory CESDIR_COMMON as '/home/common';
grant read on directory CESDIR_COMMON to public;

We will need to have Java installed in our database to use this. To see if we already have it installed, run the following SQL:

select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;

If we get around 9000 for the counts, we have Java installed. If we get zero or a small number, run the following SQL to install Java, making sure that we have around 1 Meg free in the system tablespace first:

connect / as sysdba
select bytes/1024/1000 Meg from dba_free_space where tablespace_name = 'SYSTEM';
shutdown
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
@$ORACLE_HOME/javavm/install/initjvm.sql
@$ORACLE_HOME/xdk/admin/initxml.sql
@$ORACLE_HOME/xdk/admin/xmlja.sql
@$ORACLE_HOME/rdbms/admin/catjava.sql
shutdown immediate
startup


The code for sending the email using UTL_SMTP is as follows:


/**************************************************************************
* PROCEDURE NAME : XX_EMAIL_FILES
*
* DESCRIPTION:
* ==========
* Sends e-mail (text and/or html, either as a string or from a file)
* to one or more recipients (including cc and/or bcc recipients), along with
* up to 3 file attachments (text and/or binary; default is text/plain), using
* the UTL_SMTP package to send the e-mail, the DBMS_LOB package to read
* binary file attachments, and the UTL_ENCODE package to convert the binary
* attachments to BASE64 for character string (non-binary) transmission.
* BE AWARE THAT A COMMIT MAY BE DONE BY THIS ROUTINE.
*
* PARAMETERS:
* ==========
* The complete parameter list for the xx_email_files procedure is shown below:

* NAME TYPE DESCRIPTION
* ----------------- -------- ---------------------------------------------
* from_name IN Name and e-mail address to put in the From field
* to_names IN Names and e-mail addresses for the To field (separated by
* commas or semicolons)
* subject IN Text string for Subject field
* message IN Text string or text file name for Message, if any
* html_message IN Html string or html file name for Message, if any
* cc_names IN Names and e-mail addresses for the Cc field, if any
* (separated by commas or semicolons)
* bcc_names IN Names and e-mail addresses for the Bcc field, if any
* (separated by commas or semicolons)
* filename1 IN First unix file pathname to attach, if any
* filetype1 IN Mime type of first file (defaults to 'text/plain')
* filename2 IN Second unix file pathname to attach, if any
* filetype2 IN Mime type of second file (defaults to 'text/plain')
* filename3 IN Third unix file pathname to attach, if any
* filetype3 IN Mime type of third file (defaults to 'text/plain')
*
* Sample names and e-mail addresses are: arun (attaches @),
* xxx@yahoo.com, , xxx , and
* "xxx" 
*
* A sample call in PL/SQL is shown below, which sends a text and html message,
* plus a text file and two binary files (note: the slash after "end;" must be
* the first character on it's line):
*
* begin
* xx_email_files(from_name => 'xxx@yahoo.com' ,
* to_names => 'xxx@yahoo.com',
* subject => 'A test',
* message => 'A TEST MESSAGE',
* html_message => '

A test message

',
* filename1 => '/ora_appl/oracle/11.5.0/data/xxx.pdf',
* filename2 => '/usr/tmp/115apug.pdf',
* filetype2 => 'application/pdf',
* filename3 => '',
* filetype3 => 'image/jpeg'
* );
* end
* /
*
* If the message or html_message string has a file name in it (starting with
* a forward slash), the text or html file is copied into the e-mail as the
* message or html message; otherwise, the message or html_message is copied
* into the e-mail as-is.
*
* Attachment file types (mime types) that I've tested include:
* text/plain,
* text/html,
* image/jpeg,
* image/gif,
* application/pdf,
* application/msword
* A list of mime types can be seen at:
* http://www.webmaster-toolkit.com/mime-types.shtml
* If the mime type does not begin with "text", it is assumed to be a binary
* file that will be encoded as base64 before transmission.
*************************************************************************/

CREATE OR REPLACE PROCEDURE xx_email_files (
from_name VARCHAR2,
to_names VARCHAR2,
subject VARCHAR2,
MESSAGE VARCHAR2 DEFAULT NULL,
html_message VARCHAR2 DEFAULT NULL,
cc_names VARCHAR2 DEFAULT NULL,
bcc_names VARCHAR2 DEFAULT NULL,
filename1 VARCHAR2 DEFAULT NULL,
filetype1 VARCHAR2 DEFAULT 'text/plain',
filename2 VARCHAR2 DEFAULT NULL,
filetype2 VARCHAR2 DEFAULT 'text/plain',
filename3 VARCHAR2 DEFAULT NULL,
filetype3 VARCHAR2 DEFAULT 'text/plain'
)
IS
-- Change the SMTP host name and port number below to your own values,
-- if not localhost on port 25:
smtp_host VARCHAR2 (256) := 'localhost';
smtp_port NUMBER := 25;
-- Change the boundary string, if needed, which demarcates boundaries of
-- parts in a multi-part email, and should not appear inside the body of
-- any part of the e-mail:
boundary CONSTANT VARCHAR2 (256) := 'CES.Boundary.DACA587499938898';
recipients VARCHAR2 (32767);
directory_path VARCHAR2 (256);
file_name VARCHAR2 (256);
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
mesg VARCHAR2 (32767);
conn UTL_SMTP.connection;
l_length NUMBER;
l_sub VARCHAR2 (32767);

TYPE varchar2_table IS TABLE OF VARCHAR2 (256)
INDEX BY BINARY_INTEGER;

file_array varchar2_table;
type_array varchar2_table;
i BINARY_INTEGER;

-- Function to return the next email address in the list of email addresses,
-- separated by either a "," or a ";". From Oracle's demo_mail. The format
-- of mailbox may be in one of these:
-- someone@some-domain
-- "Someone at some domain" 
-- Someone at some domain 
FUNCTION get_address (addr_list IN OUT VARCHAR2)
RETURN VARCHAR2
IS
addr VARCHAR2 (256);
i PLS_INTEGER;

FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2)
RETURN PLS_INTEGER
IS
c VARCHAR2 (5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;
BEGIN
inside_quote := FALSE;
i := 1;
len := LENGTH (str);

WHILE (i <= len)
LOOP
c := SUBSTR (str, i, 1);

IF (inside_quote)
THEN
IF (c = '"')
THEN
inside_quote := FALSE;
ELSIF (c = '\')
THEN
i := i + 1; -- Skip the quote character
END IF;

GOTO next_char;
END IF;

IF (c = '"')
THEN
inside_quote := TRUE;
GOTO next_char;
END IF;

IF (INSTR (chrs, c) >= 1)
THEN
RETURN i;
END IF;

<>
i := i + 1;
END LOOP;

RETURN 0;
END;
BEGIN
addr_list := LTRIM (addr_list);
i := lookup_unquoted_char (addr_list, ',;');

IF (i >= 1)
THEN
addr := SUBSTR (addr_list, 1, i - 1);
addr_list := SUBSTR (addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;

i := lookup_unquoted_char (addr, '<');

IF (i >= 1)
THEN
addr := SUBSTR (addr, i + 1);
i := INSTR (addr, '>');

IF (i >= 1)
THEN
addr := SUBSTR (addr, 1, i - 1);
END IF;
END IF;

RETURN addr;
END;

-- Procedure to split a file pathname into its directory path and file name
-- components.
PROCEDURE split_path_name (
file_path IN VARCHAR2,
directory_path OUT VARCHAR2,
file_name OUT VARCHAR2
)
IS
pos NUMBER;
BEGIN
-- Separate the filename from the directory name
pos := INSTR (file_path, '/', -1);

IF pos = 0
THEN
pos := INSTR (file_path, '\', -1);
END IF;

IF pos = 0
THEN
directory_path := NULL;
ELSE
directory_path := SUBSTR (file_path, 1, pos - 1);
END IF;

file_name := SUBSTR (file_path, pos + 1);
END;

-- Procedure to append a file's contents to the e-mail
PROCEDURE append_file (
directory_path IN VARCHAR2,
file_name IN VARCHAR2,
file_type IN VARCHAR2,
conn IN OUT UTL_SMTP.connection
)
IS
generated_name VARCHAR2 (30)
:= 'CESDIR' || TO_CHAR (SYSDATE, 'HH24MISS');
directory_name VARCHAR2 (30);
file_handle UTL_FILE.file_type;
bfile_handle BFILE;
bfile_len NUMBER;
pos NUMBER;
read_bytes NUMBER;
line VARCHAR2 (1000);
DATA RAW (200);
my_code NUMBER;
my_errm VARCHAR2 (32767);
BEGIN
BEGIN
-- Grant access to the directory, unless already defined, and open
-- the file (as a bfile for a binary file, otherwise as a text file).
BEGIN
line := directory_path;

SELECT dd.directory_name
INTO directory_name
FROM dba_directories dd
WHERE dd.directory_path = line AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
directory_name := generated_name;
END;

IF directory_name = generated_name
THEN
EXECUTE IMMEDIATE 'create or replace directory '
|| directory_name
|| ' as '''
|| directory_path
|| '''';

EXECUTE IMMEDIATE 'grant read on directory '
|| directory_name
|| ' to public';
END IF;

IF SUBSTR (file_type, 1, 4) != 'text'
THEN
bfile_handle := BFILENAME (directory_name, file_name);
bfile_len := DBMS_LOB.getlength (bfile_handle);
pos := 1;
DBMS_LOB.OPEN (bfile_handle, DBMS_LOB.lob_readonly);
ELSE
file_handle := UTL_FILE.fopen (directory_name, file_name, 'r');
END IF;

-- Append the file contents to the end of the message
LOOP
-- If it is a binary file, process it 57 bytes at a time,
-- reading them in with a LOB read, encoding them in BASE64,
-- and writing out the encoded binary string as raw data
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
IF pos + 57 - 1 > bfile_len
THEN
read_bytes := bfile_len - pos + 1;
ELSE
read_bytes := 57;
END IF;

DBMS_LOB.READ (bfile_handle, read_bytes, pos, DATA);
UTL_SMTP.write_raw_data (conn, UTL_ENCODE.base64_encode (DATA));
pos := pos + 57;

IF pos > bfile_len
THEN
EXIT;
END IF;
-- If it is a text file, get the next line of text, append a
-- carriage return / line feed to it, and write it out
ELSE
UTL_FILE.get_line (file_handle, line);
UTL_SMTP.write_data (conn, line || crlf);
END IF;
END LOOP;
-- Output any errors, except at end when no more data is found
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
my_code := SQLCODE;
my_errm := SQLERRM;
DBMS_OUTPUT.put_line ('Error code ' || my_code || ': ' || my_errm);
END;

-- Close the file (binary or text)
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
DBMS_LOB.CLOSE (bfile_handle);
ELSE
UTL_FILE.fclose (file_handle);
END IF;

IF directory_name = generated_name
THEN
EXECUTE IMMEDIATE 'drop directory ' || directory_name;
END IF;
END;
BEGIN
-- Load the three filenames and file (mime) types into an array for
-- easier handling later
file_array (1) := filename1;
file_array (2) := filename2;
file_array (3) := filename3;
type_array (1) := filetype1;
type_array (2) := filetype2;
type_array (3) := filetype3;
-- Open the SMTP connection and set the From and To e-mail addresses
conn := UTL_SMTP.open_connection (smtp_host, smtp_port);
UTL_SMTP.helo (conn, smtp_host);
recipients := from_name;
UTL_SMTP.mail (conn, get_address (recipients));
recipients := to_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, get_address (recipients));
END LOOP;

recipients := cc_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, get_address (recipients));
END LOOP;

recipients := bcc_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, get_address (recipients));
END LOOP;

UTL_SMTP.open_data (conn);
-- Build the start of the mail message
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf
|| 'From: '
|| from_name
|| crlf
|| 'Subject: '
|| subject
|| crlf
|| 'To: '
|| to_names
|| crlf;

IF cc_names IS NOT NULL
THEN
mesg := mesg || 'Cc: ' || cc_names || crlf;
END IF;

IF bcc_names IS NOT NULL
THEN
mesg := mesg || 'Bcc: ' || bcc_names || crlf;
END IF;

mesg :=
mesg
|| 'Mime-Version: 1.0'
|| crlf
|| 'Content-Type: multipart/mixed; boundary="'
|| boundary
|| '"'
|| crlf
|| crlf
|| 'This is a Mime message, which your current mail reader may not'
|| crlf
|| 'understand. Parts of the message will appear as text. If the remainder'
|| crlf
|| 'appears as random characters in the message body, instead of as'
|| crlf
|| 'attachments, then you''ll have to extract these parts and decode them'
|| crlf
|| 'manually.'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);

-- Write the text message or message file, if any
IF MESSAGE IS NOT NULL
THEN
mesg :=
'--'
|| boundary
|| crlf
|| 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII'
|| crlf
|| 'Content-Disposition: inline; filename="message.txt"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);

IF SUBSTR (MESSAGE, 1, 1) = '/'
THEN
split_path_name (MESSAGE, directory_path, file_name);
append_file (directory_path, file_name, 'text', conn);
UTL_SMTP.write_data (conn, crlf);
ELSE
UTL_SMTP.write_data (conn, MESSAGE || crlf);
END IF;
END IF;

IF html_message IS NOT NULL
THEN
mesg :=
'--'
|| boundary
|| crlf
|| 'Content-Type: text/html; name="message.html"; charset=US-ASCII'
|| crlf
|| 'Content-Disposition: inline; filename="message.html"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);

IF SUBSTR (html_message, 1, 1) = '/'
THEN
split_path_name (html_message, directory_path, file_name);
append_file (directory_path, file_name, 'text', conn);
UTL_SMTP.write_data (conn, crlf);
ELSE
UTL_SMTP.write_data (conn, html_message || crlf);
END IF;
END IF;

-- Append the files
FOR i IN 1 .. 3
LOOP
-- If the filename has been supplied ...
IF file_array (i) IS NOT NULL
THEN
split_path_name (file_array (i), directory_path, file_name);
-- Generate the MIME boundary line according to the file (mime) type
-- specified.
mesg := crlf || '--' || boundary || crlf;

SELECT INSTR (file_name, '.')
INTO l_length
FROM DUAL;

SELECT SUBSTR (file_name, 1, l_length - 1) || '.pdf'
INTO l_sub
FROM DUAL;

IF SUBSTR (type_array (i), 1, 4) != 'text'
THEN
mesg :=
mesg
|| 'Content-Type: '
|| type_array (i)
|| '; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: base64'
|| crlf
|| crlf;
ELSE
mesg :=
mesg
|| 'Content-Type: application/octet-stream; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
END IF;

UTL_SMTP.write_data (conn, mesg);
-- Append the file contents to the end of the message
append_file (directory_path, file_name, type_array (i), conn);
UTL_SMTP.write_data (conn, crlf);
END IF;
END LOOP;

-- Append the final boundary line
mesg := crlf || '--' || boundary || '--' || crlf;
UTL_SMTP.write_data (conn, mesg);
-- Close the SMTP connection
UTL_SMTP.close_data (conn);
UTL_SMTP.quit (conn);
END;
/