1.PURPOSE AND SCOPE
We need to install utlmail and prvtmail scripts to send email through databases,with help of these script we are going to create some synonyms and packages. After installation of script we need to set smtp server parameter and grants.
2.PRACTICE
2.1. Going to connect database through sqlplus and starting utlmail.sql script.
2.1.1. Content of utlmail.sql
-bash-3.2$ cat $ORACLE_HOME/rdbms/admin/utlmail.sql REM REM $Header: plsql/admin/utlmail.sql /main/6 2009/01/06 14:20:27 traney Exp $ REM REM utlmail.sql REM REM Copyright (c) 2002, 2008, Oracle and/or its affiliates. REM All rights reserved. REM REM NAME REM utlmail.sql - PL/SQL Package for sending email messages (UTL_MAIL) REM REM DESCRIPTION REM REM REM NOTES REM PL/SQL package to send email messages. REM This package by default is not granted to anyone. Users REM who want to send email should contact their DBA REM to obtain the necessary execute privilege for this package. REM REM MODIFIED (MM/DD/YY) REM traney 12/29/08 - add reply-to REM rpang 05/04/06 - Made invoker rights routine REM lvbcheng 02/21/05 - 2801081 REM lvbcheng 12/11/03 - Remove grant of UTL_MAIL to public REM lvbcheng 02/25/03 - define priority levels REM eehrsam 10/07/02 - eehrsam_utl_mail_new_pkg REM eehrsam 05/01/02 - Created REM CREATE OR REPLACE PACKAGE utl_mail AUTHID CURRENT_USER AS ------------- -- CONSTANTS -- invalid_argument EXCEPTION; invalid_priority EXCEPTION; invalid_argument_errcode CONSTANT PLS_INTEGER:= -29261; PRAGMA EXCEPTION_INIT(invalid_argument, -29261); PRAGMA EXCEPTION_INIT(INVALID_PRIORITY, -44101); /*---------------------------------------------------------------- ** ** SEND - send an email message ** ** This procedure packages and delivers an email message to the ** SMTP server specified by the following configuration parameters: ** ** SMTP_SERVER=my_server.my_company.com ** SMTP_DOMAIN=my_company.com ** ** SEND PROCEDURE ** IN ** sender - sender address ** recipients - address(es) of 1 or more recipients, comma delimited ** cc - CC (carbon copy) recipient(s)), 1 or more addresses, ** comma delimited, default=NULL ** bcc - BCC (blind carbon copy) recipient(s), 1 or more ** addresses, comma delimited, default=NULL ** subject - subject string, default=NULL ** message - message text, default=NULL ** mime_type - mime type, default='text/plain' ** priority - message priority, default=3, valid values are [1..5] ** ** SEND_ATTACH_VARCHAR2 PROCEDURE ** IN ** sender - sender address ** recipients - address(es) of 1 or more recipients, comma delimited ** cc - CC (carbon copy) recipient(s)), 1 or more addresses, ** comma delimited, default=NULL ** bcc - BCC (blind carbon copy) recipient(s), 1 or more ** addresses, comma delimited, default=NULL ** subject - subject string, default=NULL ** message - message text, default=NULL ** mime_type - mime type, default='text/plain' ** priority - message priority, default=3, valid values are [1..5] ** att_txt_inline - boolean specifying whether the attachment is viewable ** inline with the message body text, default=TRUE ** attachment - attachment text data ** att_mime_type- attachment mime_type, default='text/plain' ** att_filename - filename to be offered as a default upon saving the ** attachment to disk ** ** SEND_ATTACH_RAW PROCEDURE ** IN ** sender - sender address ** recipients - address(es) of 1 or more recipients, comma delimited ** cc - CC (carbon copy) recipient(s)), 1 or more addresses, ** comma delimited, default=NULL ** bcc - BCC (blind carbon copy) recipient(s), 1 or more ** addresses, comma delimited, default=NULL ** subject - subject string, default=NULL ** message - message text, default=NULL ** mime_type - mime type, default='text/plain' ** priority - message priority, default=3, valid values are [1..5] ** att_raw_inline - boolean specifying whether the attachment is viewable ** inline with the message body text, default=TRUE ** attachment - attachment RAW data ** att_mime_type- attachment mime_type, default='application/octet' ** att_filename - filename to be offered as a default upon saving the ** attachment to disk ** */ PROCEDURE send(sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL); PROCEDURE send_attach_varchar2( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, attachment IN VARCHAR2 CHARACTER SET ANY_CS, att_inline IN BOOLEAN DEFAULT TRUE, att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL); PROCEDURE send_attach_raw( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, attachment IN RAW, att_inline IN BOOLEAN DEFAULT TRUE, att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'application/octet', att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL); END; / CREATE OR REPLACE PUBLIC SYNONYM utl_mail FOR sys.utl_mail;
2.1.2. Now we are going to start utlmail.sql through sqlplus, we need to connect db as sysdba.
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql Package created. Synonym created.
2.2. And now need to connect database through sqlplus and starting prvtmail.plb script. 2.2.1. Content of prvtmail.plb
-bash-3.2$ cat $ORACLE_HOME/rdbms/admin/utlmail.sql
REM
REM $Header: plsql/admin/utlmail.sql /main/6 2009/01/06 14:20:27 traney Exp $
REM
REM utlmail.sql
REM
REM Copyright (c) 2002, 2008, Oracle and/or its affiliates.
REM All rights reserved.
REM
REM NAME
REM utlmail.sql - PL/SQL Package for sending email messages (UTL_MAIL)
REM
REM DESCRIPTION
REM
REM
REM NOTES
REM PL/SQL package to send email messages.
REM This package by default is not granted to anyone. Users
REM who want to send email should contact their DBA
REM to obtain the necessary execute privilege for this package.
REM
REM MODIFIED (MM/DD/YY)
REM traney 12/29/08 - add reply-to
REM rpang 05/04/06 - Made invoker rights routine
REM lvbcheng 02/21/05 - 2801081
REM lvbcheng 12/11/03 - Remove grant of UTL_MAIL to public
REM lvbcheng 02/25/03 - define priority levels
REM eehrsam 10/07/02 - eehrsam_utl_mail_new_pkg
REM eehrsam 05/01/02 - Created
REM
CREATE OR REPLACE PACKAGE utl_mail AUTHID CURRENT_USER AS
-------------
-- CONSTANTS
--
invalid_argument EXCEPTION;
invalid_priority EXCEPTION;
invalid_argument_errcode CONSTANT PLS_INTEGER:= -29261;
PRAGMA EXCEPTION_INIT(invalid_argument, -29261);
PRAGMA EXCEPTION_INIT(INVALID_PRIORITY, -44101);
/*----------------------------------------------------------------
**
** SEND - send an email message
**
** This procedure packages and delivers an email message to the
** SMTP server specified by the following configuration parameters:
**
** SMTP_SERVER=my_server.my_company.com
** SMTP_DOMAIN=my_company.com
**
** SEND PROCEDURE
** IN
** sender - sender address
** recipients - address(es) of 1 or more recipients, comma delimited
** cc - CC (carbon copy) recipient(s)), 1 or more addresses,
** comma delimited, default=NULL
** bcc - BCC (blind carbon copy) recipient(s), 1 or more
** addresses, comma delimited, default=NULL
** subject - subject string, default=NULL
** message - message text, default=NULL
** mime_type - mime type, default='text/plain'
** priority - message priority, default=3, valid values are [1..5]
**
** SEND_ATTACH_VARCHAR2 PROCEDURE
** IN
** sender - sender address
** recipients - address(es) of 1 or more recipients, comma delimited
** cc - CC (carbon copy) recipient(s)), 1 or more addresses,
** comma delimited, default=NULL
** bcc - BCC (blind carbon copy) recipient(s), 1 or more
** addresses, comma delimited, default=NULL
** subject - subject string, default=NULL
** message - message text, default=NULL
** mime_type - mime type, default='text/plain'
** priority - message priority, default=3, valid values are [1..5]
** att_txt_inline - boolean specifying whether the attachment is viewable
** inline with the message body text, default=TRUE
** attachment - attachment text data
** att_mime_type- attachment mime_type, default='text/plain'
** att_filename - filename to be offered as a default upon saving the
** attachment to disk
**
** SEND_ATTACH_RAW PROCEDURE
** IN
** sender - sender address
** recipients - address(es) of 1 or more recipients, comma delimited
** cc - CC (carbon copy) recipient(s)), 1 or more addresses,
** comma delimited, default=NULL
** bcc - BCC (blind carbon copy) recipient(s), 1 or more
** addresses, comma delimited, default=NULL
** subject - subject string, default=NULL
** message - message text, default=NULL
** mime_type - mime type, default='text/plain'
** priority - message priority, default=3, valid values are [1..5]
** att_raw_inline - boolean specifying whether the attachment is viewable
** inline with the message body text, default=TRUE
** attachment - attachment RAW data
** att_mime_type- attachment mime_type, default='application/octet'
** att_filename - filename to be offered as a default upon saving the
** attachment to disk
**
*/
PROCEDURE send(sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type IN VARCHAR2 CHARACTER SET ANY_CS
DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT 3,
replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
PROCEDURE send_attach_varchar2(
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type IN VARCHAR2 CHARACTER SET ANY_CS
DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT 3,
attachment IN VARCHAR2 CHARACTER SET ANY_CS,
att_inline IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS
DEFAULT 'text/plain; charset=us-ascii',
att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
PROCEDURE send_attach_raw(
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type IN VARCHAR2 CHARACTER SET ANY_CS
DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT 3,
attachment IN RAW,
att_inline IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS
DEFAULT 'application/octet',
att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);
END;
/
CREATE OR REPLACE PUBLIC SYNONYM utl_mail FOR sys.utl_mail;
2.2.2. I am going to start prtmail.plb script through sqlplus as sysdba.
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb Package created. Package body created. Grant succeeded. Package body created. No errors.
2.3. I am setting my smtp server ip to smtp_out_server and grant my user to execute this package.
SQL> alter system set smtp_out_server= '172.20.100.100' scope=both; System altered. SQL> GRANT EXECUTE ON UTL_MAIL TO bcanbakal; Grant succeeded.
2.4. Lets test the script .
SQL> exec UTL_MAIL.SEND ('from@mail_address.com','to_1@mail_address.com;to_2@mail_address.com',NULL,NULL,'Subject of mail','Body of mail' , 'text/html; charset=us-ascii',NULL);
PL/SQL procedure successfully completed.
2.6. After execution of utl_mail if you see errors like ORA-06502,ORA-06512 it means that you had got problem about your smtp server.
ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.UTL_MAIL", line 654 ORA-06512: at "SYS.UTL_MAIL", line 671
2.7. While sending big mail content UTL_MAIL package sometimes unusefull.Thats the reason for big contents , i can suggest to use CES. CES package script: http://www.oracletips.info/ces_email_files.sql
Update: 29/4/2015
Because of oracletips.info page is not available ,i am sharing their code.
rem
rem Script: ces_email_files.sql; Package: CES (for Oracle 9.2 and above)
rem
rem Purpose: Send an e-mail (text and/or html, passed either as a character
rem string or from a file or from a character LOB (CLOB)) to one or more
rem recipients (including cc and/or bcc recipients), along with multiple
rem file attachments as needed (text and/or binary files, character LOB's
rem (CLOB's), and/or binary LOB's (BLOB's)). This uses the UTL_SMTP package
rem to send the e-mail, the DBMS_LOB package to read the binary file and/or
rem CLOB/BLOB attachments, and the UTL_ENCODE package to convert the binary
rem attachments to BASE64 for text (non-binary) transmission. BE AWARE THAT
rem A COMMIT MAY BE DONE BY THIS ROUTINE (but, see "HOWEVER" in the notes on
rem creating directories below).
rem
rem The complete parameter list for the email_files procedure is shown below:
rem from_name - name and e-mail address to put in the From field
rem to_names - names and e-mail addresses for the To field (separated by
rem commas or semicolons)
rem subject - text string for Subject field
rem message - text string or text file name or character LOB (CLOB) for
rem Message field, if any (defaults to an empty string ('') instead of
rem null in order to differentiate between the text and CLOB versions)
rem html_message - html string or html file name or character LOB (CLOB)
rem for Message field, if any (defaults to an empty string ('') instead
rem of null in order to differentiate between the text and CLOB versions)
rem cc_names - names and e-mail addresses for the Cc (carbon copy) field,
rem if any (separated by commas or semicolons)
rem bcc_names - names and e-mail addresses for the Bcc (blind carbon copy)
rem field, if any (separated by commas or semicolons)
rem attach - either a cesFiles list of file pathname and optional mime type
rem pairs to attach, or a cesCLOBs list of CLOB's, names, and mime types to
rem attach, or a cesBLOBs list of BLOB's, names, and mime types to attach,
rem if any (mime types default to 'text/plain'). To specify the list of
rem files (but not CLOBs or LOBs - see those below), you can either
rem include or exclude the list type name (cesFiles), such as in:
rem cesFiles(file1,type1,file2,type2,...)
rem or just:
rem file1,type1,file2,type2,...
rem where the "type" fields are optional in both forms above.
rem clob_attach - cesCLOBs list of CLOB's, names, and mime types to attach,
rem if any. This is used when you have multiple types of attachments
rem (files, CLOB's, and BLOB's); otherwise, just use the overloaded
rem "attach" parameter. To specify the list of CLOBS, you will need to
rem include the type (cesCLOBs) and object (cesCLOB) names, such as in:
rem cesCLOBs(cesCLOB(clob1,name1,type1),cesCLOB(clob2,name2,type2),...
rem blob_attach - cesBLOBs list of BLOB's, names, and mime types to attach,
rem if any. This is used when you have multiple types of attachments
rem (files, CLOB's, and BLOB's); otherwise, just use the overloaded
rem "attach" parameter). To specify the list of BLOBS, you will need to
rem include the type (cesBLOBs) and object (cesBLOB) names, such as in:
rem cesBLOBs(cesBLOB(blob1,name1,type1),cesBLOB(blob2,name2,type2),...
rem
rem Optionally, you can specify the legacy parameter list used by the original
rem version of email_files, which only allowed for up to 3 file attachments
rem (and no CLOBs or BLOBs), as shown below:
rem from_name - name and e-mail address to put in the From field
rem to_names - names and e-mail addresses for the To field (separated by
rem commas or semicolons)
rem subject - text string for Subject field
rem message - text string or text file name for Message, if any
rem html_message - html string or html file name for Message, if any
rem cc_names - names and e-mail addresses for the Cc field, if any
rem (separated by commas or semicolons)
rem bcc_names - names and e-mail addresses for the Bcc field, if any
rem (separated by commas or semicolons)
rem filename1 - first file pathname to attach, if any
rem filetype1 - mime type of first file (defaults to 'text/plain')
rem filename2 - second file pathname to attach, if any
rem filetype2 - mime type of second file (defaults to 'text/plain')
rem filename3 - third file pathname to attach, if any
rem filetype3 - mime type of third file (defaults to 'text/plain')
rem
rem Sample names and e-mail addresses are: srea (attaches @<localhost> or
rem @<mail domain>), srea@maristream.org, <srea@maristream.org>, Steve Rea <srea@maristream.org>,
rem and "Steve Rea" <srea@maristream.org> (e-mail addresses must be valid - validity
rem checking is not done on them).
rem
rem Note: There are now over a dozen ways (interfaces) to call email_files.
rem See the package specifications for those options that are available.
rem If you get "PLS-00307: too many declarations of 'EMAIL_FILES' match this
rem call", you will have to rework your parameter list to better match one of
rem those parameter list options (maybe add additional named parameters with
rem null or empty values so Oracle can match up your call to one of them).
rem
rem A sample call in PL/SQL is shown below, which sends a text and html message,
rem plus three text file attachments (mime types default to 'text/plain' when
rem not given) and two binary file attachments. (Note: the slash after "end;"
rem must be the first character on it's line.)
rem
rem begin
rem CES.email_files(
rem from_name => 'oracle',
rem to_names => 'srea@maristream.org',
rem subject => 'A test',
rem message => 'A test message',
rem html_message => '<br />
<br />
<br />
<br />
<h2>A <u><i>test</i></u> message</h2>
<p>
<p>
<p>
<p>',
rem attach => cesFiles('/tmp/web_ptrbdca.txt',
rem '/tmp/password_standards.pdf',
rem 'application/pdf',
rem '/tmp/wb703.jpg',
rem 'image/jpeg',
rem '/tmp/another_text_file.lis',
rem '/tmp/still_another_text_file.lst'));
rem end;
rem /
rem
rem A sample call with CLOBs and BLOBs is shown below, which sends a text file,
rem a CLOB text file, and two BLOB binary files (I've supplied names for the
rem CLOB and BLOB attachments). Note: If there wasn't a text file attachment
rem in this example for this file/clob/blob version, you would need to specify
rem "null" for the attach parameter, since there is not a default value for it.
rem
rem CES.email_files(
rem from_name => 'oracle',
rem to_names => 'srea@maristream.org',
rem subject => 'Text, CLOB and BLOB Attachments',
rem message => 'Text, one CLOB, and two BLOBs attached.',
rem attach => cesFiles('/tmp/web_ptrbdca.txt'),
rem clob_attach => cesCLOBs(cesCLOB(l_clob,'testfile.txt','text/plain')),
rem blob_attach => cesBLOBs(cesBLOB(l_blob1,'fireworks.jpg','image/jpeg'),
rem cesBLOB(l_blob2,'more_fireworks.jpg','image/jpeg')));
rem
rem A sample call using the legacy parameter list in PL/SQL is shown below,
rem which sends a text and html message, plus a text file attachment and two
rem binary file attachments:
rem
rem begin
rem CES.email_files(
rem from_name => 'oracle',
rem to_names => 'srea@maristream.org',
rem subject => 'A test',
rem message => 'A test message',
rem html_message => '<br />
<br />
<br />
<br />
<h2>A <u><i>test</i></u> message</h2>
<p>
<p>
<p>
<p>',
rem filename1 => '/tmp/web_ptrbdca.txt',
rem filename2 => '/tmp/password_standards.pdf',
rem filetype2 => 'application/pdf',
rem filename3 => '/tmp/wb703.jpg',
rem filetype3 => 'image/jpeg');
rem end;
rem /
rem
rem You can also use positional parameters instead of qualifying each parameter
rem with it's name as shown above, with the equivalent call shown below. Note:
rem Oracle maps this procedure call to the "attach" parameter version, not the
rem "filename/filetype" version, so, if you put a null in the list of files and
rem types, it won't attach any files listed after that null. The cesFiles cast
rem (cesFiles(...)) is not needed for calling this version of the procedure.
rem Also note that if you don't want to specify a value for message and/or
rem html_message, use an empty string ('') instead of "null" for that parameter
rem in order to differentiate between the text and CLOB versions of those
rem parameters; otherwise, you will get the error "PLS-00307: too many
rem declarations of 'EMAIL_FILES' match this call".
rem
rem A sample call using positional parameters in PL/SQL is shown below (note the
rem two placeholder null's for the cc_names and bcc_names parameters; you could
rem also use empty strings ('') as placeholders for them as well):
rem
rem begin
rem CES.email_files('oracle','srea@maristream.org','A test','A test message',
rem '<br />
<br />
<br />
<br />
<h2>A <u><i>test</i></u> message</h2>
<p>
<p>
<p>
<p>',null,null,
rem '/tmp/web_ptrbdca.txt',
rem '/tmp/password_standards.pdf','application/pdf',
rem '/tmp/wb703.jpg','image/jpeg');
rem end;
rem /
rem
rem If the message or html_message string has a file name in it (starting with
rem a forward slash for unix or starting with '<drive letter>:\' or '\\' for
rem Windows/DOS), the file is copied into the e-mail as the message or html
rem message; otherwise, the message or html_message text is copied into the
rem e-mail as-is (as would be the contents of a message or html_message CLOB).
rem You must fully-qualify all file names passed as parameters to email_files.
rem
rem For CLOB and BLOB attachments, you will need to supply a "name" for them
rem to name the attachment in the e-mail, along with the mime type. If you
rem don't supply a name for them (passing a "null" name instead), their names
rem default to "clob#.txt" or "blob#" respectively, where "#" is a number. If
rem you don't supply a mime type (passing a "null" mime type instead), their
rem mime types both default to "text/plain". (If you have another suggestion
rem for BLOB defaults, please let me know!) Also, do not pass an empty_clob
rem or empty_blob as a value to any of the lob parameters for email_files,
rem since there is no way (that I know of) to test to see if a lob is an
rem empty_clob or empty_blob. Pass a null to those lob parameters instead.
rem
rem Attachment file types (mime types) that I've tested include:
rem text/plain, text/html, image/jpeg, image/gif, application/pdf,
rem application/msword
rem A list of mime types can be seen at:
rem http://www.webmaster-toolkit.com/mime-types.shtml
rem If the mime type does not begin with "text", it is assumed to be a binary
rem file that will be encoded as base64 before transmission (as would be the
rem contents of a BLOB attachment, but not a CLOB attachment which is assumed
rem to be text).
rem
rem This was derived from several sources, including:
rem Dave Wotton (Cambridge UK) - mail_files.sql at
rem http://home.clara.net/dwotton/dba/oracle_smtp.htm
rem Oracle Technology Network - maildemo.sql (demo_mail) at
rem http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html
rem Akadia's "Read a file word by word using DBMS_LOB" Tip at
rem http://www.akadia.com/services/read_file_with_dbms_lob.html
rem Tom Kyte of Oracle's AskTom site (http://AskTom.oracle.com) -
rem answer to "File Exists which is not a BFILE" and others.
rem
rem NOTE: The owner of this CES package must have "create type" privilege, and
rem the users running email_files may need to have "create any directory" and
rem "drop any directory" privileges ("create directory" was introduced in Oracle
rem 9iR2), which must be granted from a system or dba account, such as:
rem grant create type to scott;
rem grant create any directory to scott;
rem grant drop any directory to scott;
rem then:
rem connect / as sysdba
rem grant select on dba_directories to scott;
rem or, for everyone to have directory privileges:
rem grant create any directory to public;
rem grant drop any directory to public;
rem then:
rem connect / as sysdba
rem grant select on dba_directories to public;
rem
rem Any file in any directory accessible to the user can be attached (not just
rem the directories listed for the utl_file_dir parameter in the init.ora file).
rem HOWEVER, if you are using this to send e-mail's with attached files (or
rem reading the message text or message html from a file) from a trigger or
rem from some other SQL that you can't or don't want to have a commit done,
rem you will first need to create an Oracle directory entry for the directory
rem containing the attached files and grant read access to it to public, such
rem as:
rem create directory CESDIR_COMMON as '/home/common';
rem grant read on directory CESDIR_COMMON to public;
rem
rem You may also want to create a public synonym for this package and type
rem definitions, from the package's owner:
rem create or replace public synonym CES for CES;
rem grant execute on CES to public;
rem create public synonym cesFiles for cesFiles;
rem create public synonym cesCLOB for cesCLOB;
rem create public synonym cesCLOBs for cesCLOBs;
rem create public synonym cesBLOB for cesBLOB;
rem create public synonym cesBLOBs for cesBLOBs;
rem
rem FYI: I tried using the utl_file package to read binary files:
rem utl_file.get_raw(v_file_handle,data,57);
rem but got "ORA-29284: file read error" on .pdf files, so, I switched to use
rem the dbms_lob package to read binary files:
rem dbms_lob.read(v_bfile_handle,read_bytes,v_pos,data);
rem
rem Author: Stephen Rea <srea@maristream.org>
rem Maristream, Inc.
rem Initial release: 12/21/04
rem
rem Updates:
rem 1/31/05 - Fixed for triggers and other SQL that can't have a commit by
rem checking for and using already-defined Oracle directories ("create
rem directory" is a DDL statement that does a commit).
rem 9/23/05 - Put exception handler in to handle the SMTP server being
rem down or unavailable. (Solution from Ed Siegle at Swarthmore.)
rem 7/14/06 - Additional changes to detect and handle Windows/DOS file names
rem (second and third characters ':\' for paths with drive letters, or
rem first and second characters '\\' for UNC paths).
rem 8/25/06 - Some SMTP interfaces may require angle brackets (<>) around the
rem e-mail addresses, so, add them before returning results from get_address.
rem Also, they may not attach the host if relayed through a non-localhost,
rem so, attach the mail domain part (last two components) of the relay host
rem to the address if the mail domain is not specified in the address.
rem 8/1/07 - Converted to a package to allow for parameter overloading and a
rem variable list (cesFiles) of file names and mime types to be passed,
rem entered as pairs of name and optional type strings, with the mime type
rem for the file defaulting to 'text/plain' if not given.
rem 8/10/07 - Added ability to attach CLOB (character LOB) and BLOB (binary LOB)
rem objects (in addition to text and binary files). Also, can pass CLOB's
rem for the message and html_message parameters (in addition to file names
rem and text strings). This was a major addition, resulting in over a dozen
rem interface (parameter list) options available to call email_files now.
rem 5/9/08 - Default message and html_message to an empty string ('') instead
rem of null in the legacy email_files procedure in order to match the CES
rem package procedures' differentiation between text and CLOB versions.
rem 5/16/08 - Do a lower-case pathname comparison for DOS directories in
rem DBA_DIRECTORIES.
rem
create or replace type cesFiles as table of varchar2(256);
/
show errors
drop type cesCLOBs;
create or replace type cesCLOB as object (
vclob clob,
fileName varchar2(256),
mimeType varchar2(256)
);
/
show errors
create or replace type cesCLOBs as table of cesCLOB;
/
show errors
drop type cesBLOBs;
create or replace type cesBLOB as object (
vblob blob,
fileName varchar2(256),
mimeType varchar2(256)
);
/
show errors
create or replace type cesBLOBs as table of cesBLOB;
/
show errors
create or replace package CES as
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs);
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
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');
end CES;
/
show errors
create or replace package body CES as
procedure do_email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2,
clob_message clob,
html_message varchar2,
clob_html_message clob,
cc_names varchar2,
bcc_names varchar2,
file_attach cesFiles,
clob_attach cesCLOBs,
blob_attach cesBLOBs)
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_path varchar2(256);
mime_type varchar2(256);
file_name varchar2(256);
cr varchar2(1) := chr(13);
lf varchar2(1) := chr(10);
crlf varchar2(2) := cr || lf;
mesg varchar2(32767);
conn UTL_SMTP.CONNECTION;
i binary_integer;
my_code number;
my_errm varchar2(32767);
-- 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@some-domain>
-- Someone at some domain <someone@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;
<<next_char>>
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;
i := lookup_unquoted_char(addr, '@');
IF (i = 0 and smtp_host != 'localhost') THEN
i := instr(smtp_host, '.', -1, 2);
addr := addr || '@' || substr(smtp_host, i + 1);
END IF;
addr := '<' || addr || '>';
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 the contents of a file, character LOB, or binary LOB
-- to the e-mail
PROCEDURE append_file(
directory_path IN VARCHAR2 default null,
file_name IN VARCHAR2 default null,
mime_type IN VARCHAR2,
conn IN OUT UTL_SMTP.CONNECTION,
clob_attach IN CLOB default null,
blob_attach IN BLOB default null) IS
generated_name varchar2(30) := 'CESDIR' || to_char(sysdate,'HH24MISS');
directory_name varchar2(256) := null;
file_handle utl_file.file_type;
bfile_handle bfile;
lob_len number(38) := 0;
lob_pos number(38) := 1;
read_bytes number(38);
lf_at number(38);
line varchar2(32767);
data raw(32767);
my_code number;
my_errm varchar2(32767);
begin
begin
-- If this is a file to attach, grant access to the directory, unless
-- already defined, and open the file (as a bfile for a binary file,
-- otherwise as a text file)
if directory_path is not null then
begin
line := directory_path;
if instr(directory_path,':\') = 2 then
select dd.directory_name into directory_name from dba_directories dd
where lower(dd.directory_path) = lower(line) and rownum = 1;
else
select dd.directory_name into directory_name from dba_directories dd
where dd.directory_path = line and rownum = 1;
end if;
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(mime_type,1,4) = 'text' then
file_handle := utl_file.fopen(directory_name,file_name,'r');
else
bfile_handle := bfilename(directory_name,file_name);
lob_len := dbms_lob.getlength(bfile_handle);
dbms_lob.open(bfile_handle,dbms_lob.lob_readonly);
end if;
-- If this is a CLOB or BLOB to attach, just get the length of the LOB
elsif clob_attach is not null then
lob_len := dbms_lob.getlength(clob_attach);
elsif blob_attach is not null then
lob_len := dbms_lob.getlength(blob_attach);
end if;
-- Append the file's or LOB's contents to the end of the message
loop
-- If this is a text file, append the next line to the message,
-- along with a carriage return / line feed
if directory_path is not null and substr(mime_type,1,4) = 'text' then
utl_file.get_line(file_handle,line);
utl_smtp.write_data(conn,line || crlf);
else
-- If it is a character LOB, find the next line feed, get the
-- the next line of text, and write it out, followed by a
-- carriage return / line feed
if clob_attach is not null then
lf_at := dbms_lob.instr(clob_attach,lf,lob_pos);
if lf_at = 0 then
lf_at := lob_len + 1;
end if;
read_bytes := lf_at - lob_pos;
if read_bytes > 0
and dbms_lob.substr(clob_attach,1,lf_at-1) = cr then
read_bytes := read_bytes - 1;
end if;
if read_bytes > 0 then
dbms_lob.read(clob_attach,read_bytes,lob_pos,line);
utl_smtp.write_data(conn,line);
end if;
utl_smtp.write_data(conn,crlf);
lob_pos := lf_at + 1;
-- If it is a binary file or binary LOB, 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
else
if lob_pos + 57 - 1 > lob_len then
read_bytes := lob_len - lob_pos + 1;
else
read_bytes := 57;
end if;
if blob_attach is not null then
dbms_lob.read(blob_attach,read_bytes,lob_pos,data);
else
dbms_lob.read(bfile_handle,read_bytes,lob_pos,data);
end if;
utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data));
lob_pos := lob_pos + read_bytes;
end if;
-- Exit if we've processed all of the LOB or binary file
if lob_pos > lob_len then
exit;
end if;
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;
raise_application_error(-20000,
'Failed to send mail: Error code ' || my_code || ': ' || my_errm);
end;
-- Close the file (binary or text) and drop the generated directory,
-- if any
if directory_path is not null then
if substr(mime_type,1,4) != 'text' then
dbms_lob.close(bfile_handle);
else
utl_file.fclose(file_handle);
end if;
end if;
if directory_name = generated_name then
execute immediate 'drop directory ' || directory_name;
end if;
end;
begin
-- 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;
if cc_names is not null and length(cc_names) > 0 then
recipients := cc_names;
while recipients is not null loop
utl_smtp.rcpt(conn,get_address(recipients));
end loop;
end if;
if bcc_names is not null and length(bcc_names) > 0 then
recipients := bcc_names;
while recipients is not null loop
utl_smtp.rcpt(conn,get_address(recipients));
end loop;
end if;
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 and length(cc_names) > 0 then
mesg := mesg || 'Cc: ' || cc_names || crlf;
end if;
if bcc_names is not null and length(bcc_names) > 0 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 or message CLOB, if any
if (message is not null and length(message) > 0) or
clob_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 instr(message,'/') = 1 or instr(message,':\') = 2 or
instr(message,'\\') = 1 then
split_path_name(message,directory_path,file_name);
append_file(directory_path,file_name,'text',conn);
utl_smtp.write_data(conn,crlf);
elsif message is not null and length(message) > 0 then
utl_smtp.write_data(conn,message);
if length(message) = 1 or
substr(message,length(message)-1) != crlf then
utl_smtp.write_data(conn,crlf);
end if;
elsif clob_message is not null then
append_file(null,'message.txt','text/plain',conn,clob_message);
end if;
end if;
-- Write the HTML message or message file or message CLOB, if any
if (html_message is not null and length(html_message) > 0) or
clob_html_message is not null then
mesg := '--' || boundary || crlf ||
'Content-Type: text/html; name="message.html"; charset=ISO-8859-9' ||
crlf ||
'Content-Disposition: inline; filename="message.html"' || crlf ||
'Content-Transfer-Encoding: 7bit' || crlf || crlf;
utl_smtp.write_data(conn,mesg);
if instr(html_message,'/') = 1 or instr(html_message,':\') = 2 or
instr(html_message,'\\') = 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);
elsif html_message is not null and length(html_message) > 0 then
utl_smtp.write_data(conn,html_message);
if length(html_message) = 1 or
substr(html_message,length(html_message)-1) != crlf then
utl_smtp.write_data(conn,crlf);
end if;
elsif clob_html_message is not null then
append_file(null,'message.html','text/html',conn,clob_html_message);
end if;
end if;
-- Attach the files, if any
if file_attach is not null then
for i in 1 .. file_attach.count loop
file_path := null;
mime_type := null;
-- If this is a file path parameter, get the file path and check the
-- next parameter to see if it is a file type parameter (else default
-- to text/plain).
if file_attach(i) is null or length(file_attach(i)) = 0 then
exit;
end if;
if instr(file_attach(i),'/') = 1 or instr(file_attach(i),':\') = 2 or
instr(file_attach(i),'\\') = 1 then
file_path := file_attach(i);
if i = file_attach.count then
mime_type := 'text/plain';
else
if instr(file_attach(i+1),'/') > 1 and instr(file_attach(i+1),'/',1,2) = 0 then
mime_type := file_attach(i+1);
else
mime_type := 'text/plain';
end if;
end if;
end if;
-- If this is a file path parameter ...
if file_path is not null then
split_path_name(file_path,directory_path,file_name);
-- Generate the MIME boundary line according to the file (mime) type
-- specified.
mesg := crlf || '--' || boundary || crlf;
if substr(mime_type,1,4) != 'text' then
mesg := mesg || 'Content-Type: ' || mime_type ||
'; 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,mime_type,conn);
end if;
end loop;
end if;
-- Attach the character LOB's, if any
if clob_attach is not null then
for i in 1 .. clob_attach.count loop
-- Get the name and mime type, if given, else use default values
if clob_attach(i).vclob is null then
exit;
end if;
file_name := clob_attach(i).fileName;
if file_name is null then
file_name := 'clob' || i || '.txt';
end if;
mime_type := clob_attach(i).mimeType;
if mime_type is null then
mime_type := 'text/plain';
end if;
-- Generate the MIME boundary line for this character file attachment
mesg := crlf || '--' || boundary || crlf;
mesg := mesg || 'Content-Type: application/octet-stream; name="' ||
file_name || '"' || crlf ||
'Content-Disposition: attachment; filename="' ||
file_name || '"' || crlf ||
'Content-Transfer-Encoding: 7bit' || crlf || crlf ;
utl_smtp.write_data(conn,mesg);
-- Append the CLOB contents to the end of the message
append_file(null,file_name,mime_type,conn,clob_attach=>clob_attach(i).vclob);
end loop;
end if;
-- Attach the binary LOB's, if any
if blob_attach is not null then
for i in 1 .. blob_attach.count loop
-- Get the name and mime type, if given, else use default values
if blob_attach(i).vblob is null then
exit;
end if;
file_name := blob_attach(i).fileName;
if file_name is null or length(file_name) = 0 then
file_name := 'blob' || i;
end if;
mime_type := blob_attach(i).mimeType;
if mime_type is null then
mime_type := 'text/plain'; -- but, this is a strange default!
end if;
-- Generate the MIME boundary line for this BASE64 binary attachment
mesg := crlf || '--' || boundary || crlf;
mesg := mesg || 'Content-Type: ' || mime_type ||
'; name="' || file_name || '"' || crlf ||
'Content-Disposition: attachment; filename="' ||
file_name || '"' || crlf ||
'Content-Transfer-Encoding: base64' || crlf || crlf ;
utl_smtp.write_data(conn,mesg);
-- Append the BLOB contents to the end of the message
append_file(null,file_name,mime_type,conn,blob_attach=>blob_attach(i).vblob);
end loop;
end if;
-- 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);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
my_code := SQLCODE;
my_errm := SQLERRM;
begin
utl_smtp.quit(conn);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
null;
end;
raise_application_error(-20000,
'Failed to send mail - SMTP server down or unavailable: Error code ' ||
my_code || ': ' || my_errm);
when others then
my_code := SQLCODE;
my_errm := SQLERRM;
raise_application_error(-20000,
'Failed to send mail: Error code ' || my_code || ': ' || my_errm);
end;
-- Define the various overloaded definitions (interfaces) to email_files
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null)
is
begin
do_email_files(from_name,to_names,subject,message,null,html_message,null,
cc_names,bcc_names,attach,clob_attach,blob_attach);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null)
is
begin
do_email_files(from_name,to_names,subject,null,message,html_message,null,
cc_names,bcc_names,attach,clob_attach,blob_attach);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null)
is
begin
do_email_files(from_name,to_names,subject,message,null,null,html_message,
cc_names,bcc_names,attach,clob_attach,blob_attach);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesFiles,
clob_attach cesCLOBs default null,
blob_attach cesBLOBs default null)
is
begin
do_email_files(from_name,to_names,subject,null,message,null,html_message,
cc_names,bcc_names,attach,clob_attach,blob_attach);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs)
is
begin
do_email_files(from_name,to_names,subject,message,null,html_message,null,
cc_names,bcc_names,cesFiles(null),attach,null);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs)
is
begin
do_email_files(from_name,to_names,subject,null,message,html_message,null,
cc_names,bcc_names,cesFiles(null),attach,null);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs)
is
begin
do_email_files(from_name,to_names,subject,message,null,null,html_message,
cc_names,bcc_names,cesFiles(null),attach,null);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesCLOBs)
is
begin
do_email_files(from_name,to_names,subject,null,message,null,html_message,
cc_names,bcc_names,cesFiles(null),attach,null);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs)
is
begin
do_email_files(from_name,to_names,subject,message,null,html_message,null,
cc_names,bcc_names,cesFiles(null),null,attach);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message varchar2 default '',
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs)
is
begin
do_email_files(from_name,to_names,subject,null,message,html_message,null,
cc_names,bcc_names,cesFiles(null),null,attach);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs)
is
begin
do_email_files(from_name,to_names,subject,message,null,null,html_message,
cc_names,bcc_names,cesFiles(null),null,attach);
end;
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message clob,
html_message clob,
cc_names varchar2 default null,
bcc_names varchar2 default null,
attach cesBLOBs)
is
begin
do_email_files(from_name,to_names,subject,null,message,null,html_message,
cc_names,bcc_names,cesFiles(null),null,attach);
end;
-- This overloaded version supports legacy code using the "filename/filetype"
-- parameter pairs instead of the current "attach" parameters. It is also used
-- when no file attachments are specified (since there is not a default value
-- for the "attach" parameters in the interfaces above).
procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
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
begin
do_email_files(from_name,to_names,subject,message,null,html_message,null,
cc_names,bcc_names,cesFiles(filename1,filetype1,filename2,filetype2,
filename3,filetype3),null,null);
end;
end CES;
/
show errors
rem The following procedure can be used if you have legacy code using the
rem original email_files procedure with the "filename/filetype" parameter
rem pairs, but you don't want to change your own code to use the package
rem designation (changing it from "email_files" to "CES.email_files").
rem
rem You may also want to create a public synonym for this procedure, from
rem the procedure's owner:
rem create or replace public synonym email_files for email_files;
rem grant execute on email_files to public;
create or replace procedure email_files(from_name varchar2,
to_names varchar2,
subject varchar2,
message varchar2 default '',
html_message varchar2 default '',
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
begin
CES.email_files(from_name,to_names,subject,message,html_message,
cc_names,bcc_names,filename1,filetype1,filename2,filetype2,
filename3,filetype3);
end;
/
show errors
Update: 29/4/2015: example link fixed.
Some examples about how to send mails using CES package: http://betwinx.com/Using_CES.email_files.ht
linkler bozuk malesef
Merhaba Beyhan bey, bilgilendirdiginiz icin tesekkur ederim.
CES kodlarinin paylasildigi site kapandigindan oturu, kodlari kendi sayfamda paylastim. CES orneklerini iceren linkini guncelledim , eski sayfa baska bir alana tasinmis.