|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
Oracle’s UTL_SMTP package can be used to create and send e-mails from PL/SQL routines. There are several examples of how to do this that are posted on the web, including Dave Wotton’s mail_files.sql (no longer available) and Oracle’s maildemo.sql (demo_mail routine). 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 (such as in Akadia's "Read a file word by word using DBMS_LOB" tip). (I tried using the GET_RAW routine in Oracle’s UTL_FILE package, but couldn’t get it to read pdf files without getting a “file read error”.) Tom Kyte of Oracle’s Ask Tom site has several posts about using UTL_SMTP and working with BFILE’s (binary files).
The results of combining those examples and tips is my CES email_files package (in ces_email_files.sql) for Oracle 9iR2 and above. In it, you can specify any From address, so your e-mail looks like it’s coming from you instead of from your Oracle server. You 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 srea (which attaches @<localhost>), srea@uaex.edu, <srea@uaex.edu>, Steve Rea <srea@uaex.edu>, and "Steve Rea" <srea@uaex.edu>, 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 (UNIX or Windows files), or read from a character LOB (CLOB). Then, multiple binary or textual files can be attached to the e-mail of any MIME type, defaulting to text/plain. Besides coming from operating system files, the attachments can also come from character LOBs (CLOBs) or binary LOBs (BLOBs) stored in the database or created in a PL/SQL procedure. For the attachments, you will 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” or if the source is a BLOB, and is encoded as base64 in the e-mail; otherwise, the file or CLOB is just copied as-is into the e-mail as an attachment. (If you find that this is not the case for some MIME types, please let me know – I don’t know much about MIME types.) Any file in any directory accessible to the user can be attached (not just the directories listed for the utl_file_dir parameter in the init.ora file); but, see below about running from triggers and the potential for a commit being done.
The complete parameter list for the email_files procedure is shown below. There are over a dozen overloaded versions of email_files defined in the package specification, allowing you to specify various parameter types for the parameters (such as strings or CLOBs for the message parameters, or lists of files, CLOBs, or BLOBS for the attachment parameters). See the package specification for the various options that you have for calling the email_files procedure.
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 text file name or character LOB (CLOB)
for
Message field, if any (defaults to an
empty string ('') instead of
null in order to differentiate
between the text and CLOB versions)
html_message - html string or html file name or character LOB
(CLOB)
for Message field, if any (defaults
to an empty string ('') instead
of null in order to differentiate
between the text and CLOB versions)
cc_names - names and e-mail addresses for the Cc (carbon
copy) field,
if any (separated by commas or
semicolons)
bcc_names - names and e-mail addresses for the Bcc (blind
carbon copy)
field, if any (separated by commas or
semicolons)
attach - either a cesFiles list of file pathname and optional
mime type
pairs to attach, or a cesCLOBs list
of CLOB's, names, and mime types to
attach, or a cesBLOBs list of BLOB's,
names, and mime types to attach,
if any (mime types default to
'text/plain'). To specify the list of
files (but not CLOBs or BLOBs - see
those below), you can either
include or exclude the list type name
(cesFiles), such as in:
cesFiles(file1,type1,file2,type2,...)
or just:
file1,type1,file2,type2,...
where the "type" fields are optional
in both forms above.
clob_attach - cesCLOBs list of CLOB's, names, and mime types
to attach,
if any. This is used when you have
multiple types of attachments
(files, CLOB's, and BLOB's);
otherwise, just use the overloaded
"attach" parameter. To specify the
list of CLOBS, you will need to
include the type (cesCLOBs) and
object (cesCLOB) names, such as in:
cesCLOBs(cesCLOB(clob1,name1,type1),cesCLOB(clob2,name2,type2),...
blob_attach - cesBLOBs list of BLOB's, names, and mime types
to attach,
if any. This is used when you have
multiple types of attachments
(files, CLOB's, and BLOB's);
otherwise, just use the overloaded
"attach" parameter). To specify the
list of BLOBS, you will need to
include the type (cesBLOBs) and
object (cesBLOB) names, such as in:
cesBLOBs(cesBLOB(blob1,name1,type1),cesBLOB(blob2,name2,type2),...
The original version (pre-2007) of the email_files procedure, which was limited to 3 attachments and didn't handle LOBs, had the following parameters. This interface is still available through the current source file in both package and stand-alone versions to accomodate legacy code so that you don't have to edit existing code if you want to install the new packaged version of email_files.
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 three text file attachments (mime types
default to 'text/plain' when not given) and two binary file attachments. (Note:
the slash after "end;" must be the first character on it's line.)
begin
CES.email_files(
from_name => 'oracle',
to_names =>
'srea@uaex.edu',
subject => 'A test',
message => 'A test message',
html_message => '<h2>A <u><i>test</i></u> message</h2>',
attach => cesFiles('/tmp/web_ptrbdca.txt',
'/tmp/password_standards.pdf','application/pdf',
'/tmp/wb703.jpg','image/jpeg',
'/tmp/another_text_file.lis',
'/tmp/still_another_text_file.lst'));
end;
/
A sample call with CLOBs and BLOBs is shown below, which sends a text file, a CLOB text file, and two BLOB binary files (I've supplied names for the CLOB and BLOB attachments; otherwise, they default to "clob#.txt" or "blob#" respectively, where # is a number). Note: If there wasn't a text file attachment in this example for this file/clob/blob version, you would need to specify "null" for the attach parameter, since there is not a default value for it.
CES.email_files(
from_name => 'oracle',
to_names => 'srea@uaex.edu',
subject => 'Text, CLOB and BLOB
Attachments',
message => 'Text, one CLOB, and two
BLOBs attached.',
attach => cesFiles('/tmp/web_ptrbdca.txt'),
clob_attach => cesCLOBs(cesCLOB(l_clob,'testfile.txt','text/plain')),
blob_attach => cesBLOBs(cesBLOB(l_blob1,'fireworks.jpg','image/jpeg'),
cesBLOB(l_blob2,'more_fireworks.jpg','image/jpeg')));
A sample call using positional parameters in PL/SQL is shown below. Note the two placeholder null's for the cc_names and bcc_names parameters; you could also use empty strings ('') as placeholders for them as well. Also, the cesFiles type doesn't have to be included here, but, if you were passing CLOBs or BLOBS, you would have to include the cesCLOBs/cesCLOB or CESBLOBs/cesBLOB type and object names as shown in the above example.
begin
CES.email_files('oracle','srea@uaex.edu','A test','A test message',
'<h2>A <u><i>test</i></u> message</h2>',null,null,
'/tmp/web_ptrbdca.txt',
'/tmp/password_standards.pdf','application/pdf',
'/tmp/wb703.jpg','image/jpeg');
end;
/
The owner of this CES package must have
"create type" privilege, and the users running email_files may need to 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 type to scott;
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;
You may also want to create a public synonym
for this package and type definitions, from the package's owner:
create or replace public synonym CES for CES;
grant execute on CES to public;
create public synonym cesFiles for cesFiles;
create public synonym cesCLOB for cesCLOB;
create public synonym cesCLOBs for cesCLOBs;
create public synonym cesBLOB for cesBLOB;
create public synonym cesBLOBs for cesBLOBs;
If you use email_files to send e-mails with
attachments from triggers (or if you specify a file for the message text or
message html in them), you 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 doesn't find an existing Oracle directory entry for your
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 you are sending email
attachments from a PL/SQL routine that you don't want a commit done in, you 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;
You will need to have Java
installed in your database to use this. To see if you have it installed,
run the following SQL:
select count(*), object_type from
all_objects where object_type like '%JAVA%' group by object_type;
If you get around 9000 for the counts, you have Java installed. If
you get zero or a small number,
run the following SQL to install Java, making sure that you have around 1 Meg free in the system tablespace first (thanks to Belinda Taylor of George Mason University for this
Java installation information):
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
My boss says this is way cool. I hope you find it way cool, too!
This Page was Last Updated on 04/28/08
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
