Using Steve Rea's CES.email

Steve Rea's CES.email.files package makes it easy to send complex emails from PL/SQL. What follows is a cut-to-the-chase howto for using the package and is intended for the application programmer.

The examples here are actually modifications of Steve's original suggested test scripts for the CES.email.files Package (I have used the bknox @t ones in applications).
CES.email.files makes it easier to use Oracle’s UTL_SMTP Package to create and send e-mails from PL/SQL routines.

DBA's link on installing CES.email.files (for Oracle 9.2 and above).

Example 01 Simple Text Message with No Attachments, useful for error messages or alerts.
Example 02 Send .csv (Excel) File Attachment, useful for mailing extract files
Example 03 Simple Text Message plus PDF and JPEG File Attachments
Example 04 Simple Text and HTML Messages plus Text, PDF, and JPG File Attachments
Example 05 Simple Text Message with No Attachments using positional specification of Parameters rather than Named Parameters.
Example 06 Simple Text and HTML Messages, Text, PDF, and JPG Attachments by Positional Parameters.
Example 07 Positional Text Attachment
Example 08 CLOB Attachment
Example 09 CLOB Message
Example 10 BLOB Attachment
Example 11 BLOB Attachment
Example 12 BLOB Attachments - positional
Example 13 BLOB Attachments - unnamed
Example 14 Text, CLOB and BLOB Attachments

-- Examples:

-- Example 01 Simple Text Message with No Attachments, useful for error messages or alerts.

BEGIN
CES.email_files(from_name => 'Bruce Knox',
                  to_names =>   'bknox @t uaex.edu',
                  subject =>    'Simple Text with No Attachments',
                  message =>    'No files attached.');
END;
/

-- Example 02 Send .csv (Excel) File Attachment, useful for mailing extract files
-- Simple Text and HTML Messages plus .csv

BEGIN
email_files(from_name => 'Bruce Knox <bknox @t uaex.edu>',
              to_names => 'Mark Hubbell <appmgr @t uaex.edu>',
              bcc_names => 'Bruce Knox <bknox @t uaex.edu>',
              subject =>   'New Long Distance Accounting Extract',
              message =>   'The new Long Distance Accounting extract is attached.',
              html_message => '<html><body><br>
The New Long Distance Accounting extract is attached.
<br><br>
</body></html>',
              filename1 => '/home/common/lda_orgrep'||'.csv',
              filetype1 => 'text/plain'
              );
END;
/

-- Example 03 Simple Text Message plus PDF and JPEG File Attachments

begin
CES.email_files(
                  from_name => 'oracle',
                  to_names => 'srea @t uaex.edu',
                  subject =>   'Several Files',
                  message =>   'Several files attached.',
                  attach =>     cesFiles('/tmp/web_ptrbdca.txt',
                                         '/tmp/password_standards.pdf',
                                         'application/pdf',
                                         '/tmp/wb703.jpg',
                                         'image/jpeg'));
end;
/

-- Example 04 Simple Text and HTML Messages plus Text, PDF, and JPG File Attachments

begin
CES.email_files(
                  from_name => 'oracle',
                  to_names => 'srea @t uaex.edu',
                  subject =>   'A test',
                  message =>   'A test message',
                  html_message => '<h2>A <u><i>test</i></u> message</h2>',
                  filename1 => '/tmp/web_ptrbdca.txt',
                  filename2 => '/tmp/password_standards.pdf',
                  filetype2 => 'application/pdf',
                  filename3 => '/tmp/wb703.jpg',
                  filetype3 => 'image/jpeg');
end;
/

-- Example 05 Simple Text Message with No Attachments using positional specification of Parameters rather than Named Parameters.

begin
CES.email_files('oracle','srea @t uaex.edu','No Attachments - positional','No files attached.');
end;
/

-- Example 06 Simple Text and HTML Messages, Text, PDF, and JPG Attachments by Positional Parameters.

begin
email_files('oracle','srea @t uaex.edu','A test - positional attachments','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;
/

-- Example 07 Positional Text Attachment

begin
CES.email_files('oracle','srea @t uaex.edu','A test - positional attachments','A test message',
'',null,null,cesFiles('/tmp/web_ptrbdca.txt'));
end;
/

-- Example 08 CLOB Attachment

declare
l_clob clob := empty_clob;
l_str varchar2 (32000);
begin
dbms_lob.createTemporary( l_clob, true );
dbms_lob.open ( l_clob, dbms_lob.lob_readwrite );
l_str := 'This is a test.' || chr(13) || chr(10);
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
CES.email_files(
                  from_name => 'oracle',
                  to_names => 'srea @t uaex.edu',
                  subject => 'CLOB Attachment',
                  message => 'CLOB attached.',
                  attach => cesCLOBs(cesCLOB(l_clob,'testfile.txt','text/plain')));
dbms_lob.close (l_clob);
dbms_lob.freeTemporary ( l_clob );
end;
/

-- Example 09 CLOB Message

declare
l_clob clob := empty_clob;
l_str varchar2 (32000);
begin
dbms_lob.createTemporary( l_clob, true );
dbms_lob.open ( l_clob, dbms_lob.lob_readwrite );
l_str := 'This is a test.' || chr(13) || chr(10);
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
CES.email_files(
                  from_name => 'oracle',
                  to_names => 'srea @t uaex.edu',
                  subject => 'CLOB message',
                  message => l_clob);
dbms_lob.close (l_clob);
dbms_lob.freeTemporary ( l_clob );
end;
/

-- Example 10 BLOB Attachment

create or replace directory tmp as '/tmp/';
declare
l_blob blob := empty_blob;
l_str varchar2 (32000);
src_file BFILE;
lob_len number(38);
begin
dbms_lob.createTemporary( l_blob, true );
dbms_lob.open ( l_blob, dbms_lob.lob_readwrite );
src_file := bfilename('TMP', 'wb703.jpg');
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lob_len := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(l_blob, src_file, lob_len);
dbms_lob.fileclose(src_file);
CES.email_files(
                  from_name => 'oracle',
                  to_names => 'srea @t uaex.edu',
                  subject => 'BLOB Attachment',
                  message => 'BLOB attached.',
                  attach => cesBLOBs(cesBLOB(l_blob,'fireworks.jpg','image/jpeg')));
dbms_lob.close (l_blob);
dbms_lob.freeTemporary ( l_blob );
end;
/

-- Example 11 BLOB Attachment

declare
l_blob blob := empty_blob;
l_str varchar2 (32000);
src_file BFILE;
lob_len number(38);
begin
dbms_lob.createTemporary( l_blob, true );
dbms_lob.open ( l_blob, dbms_lob.lob_readwrite );
src_file := bfilename('TMP', 'wb703.jpg');
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lob_len := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(l_blob, src_file, lob_len);
dbms_lob.fileclose(src_file);
CES.email_files('oracle','srea @t uaex.edu','BLOB Attachment - positional','BLOB attached.','',null,null,cesBLOBs(cesBLOB(l_blob,'fireworks.jpg','image/jpeg')));
dbms_lob.close (l_blob);
dbms_lob.freeTemporary ( l_blob );
end;
/

-- Example 12 BLOB Attachments - positional

declare
l_blob blob := empty_blob;
l_str varchar2 (32000);
src_file BFILE;
lob_len number(38);
begin
dbms_lob.createTemporary( l_blob, true );
dbms_lob.open ( l_blob, dbms_lob.lob_readwrite );
src_file := bfilename('TMP', 'wb703.jpg');
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lob_len := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(l_blob, src_file, lob_len);
dbms_lob.fileclose(src_file);
CES.email_files('oracle','srea @t uaex.edu','BLOB Attachments - positional','BLOBs attached.','',null,null,cesBLOBs(cesBLOB(l_blob,'fireworks.jpg','image/jpeg'),
cesBLOB(l_blob,'again.jpg','image/jpeg')));
dbms_lob.close (l_blob);
dbms_lob.freeTemporary ( l_blob );
end;
/

-- Example 13 BLOB Attachments - unnamed

declare
l_blob blob := empty_blob;
l_str varchar2 (32000);
src_file BFILE;
lob_len number(38);
begin
dbms_lob.createTemporary( l_blob, true );
dbms_lob.open ( l_blob, dbms_lob.lob_readwrite );
src_file := bfilename('TMP', 'wb703.jpg');
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lob_len := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(l_blob, src_file, lob_len);
dbms_lob.fileclose(src_file);
CES.email_files('oracle','srea @t uaex.edu','BLOB Attachments - unnamed','BLOBs attached.','',null,null,cesBLOBs(cesBLOB(l_blob,'fireworks.jpg','image/jpeg'),
cesBLOB(l_blob,null,'image/jpeg')));
dbms_lob.close (l_blob);
dbms_lob.freeTemporary ( l_blob );
end;
/

-- Example 14 Text, CLOB and BLOB Attachments

declare
l_clob clob := empty_clob;
l_str varchar2 (32000);
l_blob blob := empty_blob;
src_file BFILE;
lob_len number(38);
begin
dbms_lob.createTemporary( l_clob, true );
dbms_lob.open ( l_clob, dbms_lob.lob_readwrite );
l_str := 'This is a test.' || chr(13) || chr(10);
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
dbms_lob.writeappend ( l_clob, length (l_str), l_str );
dbms_lob.createTemporary( l_blob, true );
dbms_lob.open ( l_blob, dbms_lob.lob_readwrite );
src_file := bfilename('TMP', 'wb703.jpg');
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lob_len := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(l_blob, src_file, lob_len);
dbms_lob.fileclose(src_file);
CES.email_files(
                  from_name => 'oracle',
                  to_names =>   'srea @t uaex.edu',
                  subject =>    'Text, CLOB and BLOB Attachments',
                  message =>    'Text, CLOB and BLOB attached.',
                  attach =>      cesFiles('/tmp/web_ptrbdca.txt'),
                  clob_attach => cesCLOBs(cesCLOB(l_clob,'testfile.txt','text/plain')),
                  blob_attach => cesBLOBs(cesBLOB(l_blob,'fireworks.jpg','image/jpeg')));
dbms_lob.close (l_clob);
dbms_lob.freeTemporary ( l_clob );
dbms_lob.close (l_blob);
dbms_lob.freeTemporary ( l_blob );
end;
/

This is a personal site maintained by Bruce Knox ( bknox @t uaex.edu ) documenting University of Arkansas, Division of Agriculture, Cooperative Extension Service IT projects.

Disclaimer
Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

Any University of Arkansas Division of Agriculture website Disclaimer terms found in conflict with terms of this disclaimer shall over ride and replace the conflicting terms found herein.

Other personal pages/sites that I maintain:
http://www.openMosix.org the website for the openMosix (Open Source) Project hosted on .

Most of the works of art on my pages other than the Extension banner
are used by permission of J. Wilson Spence.

http://betwinx.com is my personal site. betwinx? This site is largely oriented to Oracle Reporting Solutions in the context of generating bespoken reports from SunGard Higher Education's Banner product. My university site is a more restrained version which cannot contain endorsements of products or companies.
(I maintain a number of special topic pages with links from this primary site.)

http://www.uaex.edu/bknox/ is my university work site.

Using Steve Rea's CES.email_files Package

Examples for Oracle Application Programmers - for sending email from Oracle PL/SQL

by Bruce Knox bknox @t uaex.edu Oracle Database Programmer/Analyst date last updated 05/17/10

Examples for using CES.email_files

Using Steve Rea's CES.email_files Package

Examples for Oracle Application Programmers - for sending email from Oracle PL/SQL by Bruce Knox bknox @t uaex.edu Oracle Database Programmer/Analyst date last updated 05/17/10

Examples for using CES.email_files

Examples for Oracle Application Programmers - for sending email from Oracle PL/SQL

by Bruce Knox bknox @t uaex.edu Oracle Database Programmer/Analyst date last updated 05/17/10