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
beyhan says
linkler bozuk malesef
Bugra Canbakal says
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.