| | -- Script: ces_email_files.sql (for Oracle 9.2 and above) -- -- Purpose: Sends e-mail (text and/or html, either as a string or from a file) -- to one or more recipients (including cc and/or bcc recipients), along with -- up to 3 file attachments (text and/or binary; default is text/plain), using -- the UTL_SMTP package to send the e-mail, the DBMS_LOB package to read -- binary file attachments, and the UTL_ENCODE package to convert the binary -- attachments to BASE64 for character string (non-binary) transmission. -- BE AWARE THAT A COMMIT MAY BE DONE BY THIS ROUTINE (see HOWEVER... below). -- -- The complete parameter list for the email_files procedure is shown below: -- from_name - name and e-mail address to put in the From field -- to_names - names and e-mail addresses for the To field (separated by -- commas or semicolons) -- subject - text string for Subject field -- message - text string or text file name for Message, if any -- html_message - html string or html file name for Message, if any -- cc_names - names and e-mail addresses for the Cc field, if any -- (separated by commas or semicolons) -- bcc_names - names and e-mail addresses for the Bcc field, if any -- (separated by commas or semicolons) -- filename1 - first unix file pathname to attach, if any -- filetype1 - mime type of first file (defaults to 'text/plain') -- filename2 - second unix file pathname to attach, if any -- filetype2 - mime type of second file (defaults to 'text/plain') -- filename3 - third unix file pathname to attach, if any -- filetype3 - mime type of third file (defaults to 'text/plain') -- -- Sample names and e-mail addresses are: srea (attaches @<localhost>), -- srea@uaex.edu, <srea@uaex.edu>, Steve Rea <srea@uaex.edu>, and -- "Steve Rea" <srea@uaex.edu> -- -- A sample call in PL/SQL is shown below, which sends a text and html message, -- plus a text file and two binary files (note: the slash after "end;" must be -- the first character on it's line): -- -- begin -- email_files(from_name => 'oracle' , -- to_names => 'srea@uaex.edu', -- subject => 'A test', -- message => 'A test message', -- html_message => '<h2>A <u><i>test</i></u> message</h2>', -- filename1 => '/tmp/web_ptrbdca.txt', -- filename2 => '/tmp/password_standards.pdf', -- filetype2 => 'application/pdf', -- filename3 => '/tmp/wb703.jpg', -- filetype3 => 'image/jpeg'); -- end; -- / -- -- If the message or html_message string has a file name in it (starting with -- a forward slash), the text or html file is copied into the e-mail as the -- message or html message; otherwise, the message or html_message is copied -- into the e-mail as-is. -- -- Attachment file types (mime types) that I'vê tested include: -- text/plain, text/html, image/jpeg, image/gif, application/pdf, -- application/msword -- A list of mime types can be seen at: -- http://www.webmaster-toolkit.com/mime-types.shtml -- If the mime type does not begin with "text", it is assumed to be a binary -- file that will be encoded as base64 before transmission. -- -- This was derived from several sources, including: -- Dave Wotton (Cambridge UK) - mail_files.sql at -- http://home.clara.net/dwotton/dba/oracle_smtp.htm -- Oracle Technology Network - maildemo.sql (demo_mail) at -- http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html -- Akadia's "Read a file word by word using DBMS_LOB" Tip at -- http://www.akadia.com/services/read_file_with_dbms_lob.html -- Tom Kyte of Oracle's AskTom site (http://AskTom.oracle.com) - -- answer to "File Exists which is not a BFILE" and others. -- -- NOTE: The user running this must have "create any directory" and "drop any -- directory" privileges ("create directory" was introduced in Oracle 9iR2), -- which must be granted from a system or dba account, such as: -- grant create any directory to scott; -- grant drop any directory to scott; -- then: -- connect / as sysdba -- grant select on dba_directories to scott; -- or, for everyone to have directory privileges: -- grant create any directory to public; -- grant drop any directory to public; -- then: -- connect / as sysdba -- grant select on dba_directories to public; -- Any file in any directory accessible to the user can be attached (not just -- the directories listed for the utl_file_dir parameter in the init.ora file). -- HOWEVER, if you are using this to send e-mail's with attached files (or -- reading the message text or message html from a file) from a trigger or -- from some other SQL that you can't or don't want to have a commit done, -- you will first need to create an Oracle directory entry for the directory -- containing the attached files and grant read access to it to public, such -- as: -- create directory CESDIR_COMMON as '/home/common'; -- grant read on directory CESDIR_COMMON to public; -- -- You may also want to create a public synonym for this procedure, from the -- procedure's owner: -- create or replace public synonym email_files for email_files; -- grant execute on email_files to public; -- -- FYI: I tried using the utl_file package to read binary files: -- utl_file.get_raw(v_file_handle,data,57); -- but got "ORA-29284: file read error" on .pdf files, so, I switched to use -- the dbms_lob package to read binary files: -- dbms_lob.read(v_bfile_handle,read_bytes,v_pos,data); -- -- Author: Stephen Rea <srea@uaex.edu> -- University of Arkansas Cooperative Extension Service -- Initial release: 12/21/04 -- -- Updates: -- 1/31/05 - Fixed for triggers and other SQL that can't have a commit by -- checking for and using already-defined Oracle directories ("create -- directory" is a DDL statement that does a commit). -- create or replace procedure AO_SEND_MAIL( from_name varchar2, to_names varchar2, subject varchar2, message varchar2 default null, html_message varchar2 default null, cc_names varchar2 default null, bcc_names varchar2 default null, smtp_host varchar2, smtp_port number default 25, filename1 varchar2 default null, filetype1 varchar2 default 'text/plain', filename2 varchar2 default null, filetype2 varchar2 default 'text/plain', filename3 varchar2 default null, filetype3 varchar2 default 'text/plain') is -- Change the boundary string, if needed, which demarcates boundaries of -- parts in a multi-part email, and should not appear inside the body of -- any part of the e-mail: boundary constant varchar2(256) := 'CES.Boundary.DACA587499938898'; recipients varchar2(32767); directory_path varchar2(256); file_name varchar2(256); crlf varchar2(2):= chr(13) || chr(10); crlf_html varchar2(30) := '<BR>'; mesg varchar2(32767); conn UTL_SMTP.CONNECTION; type varchar2_table is table of varchar2(256) index by binary_integer; file_array varchar2_table; type_array varchar2_table; i binary_integer; addr VARCHAR2(256); html_message_complemento varchar2(2000); -- 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 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; RETURN addr; exception when others then exibe ('O erro é: ' || sqlcode); END; -- Procedure to split a file pathname into its directory path and file name -- components. PROCEDURE split_path_name(file_path IN VARCHAR2, directory_path OUT VARCHAR2, file_name OUT VARCHAR2) IS pos number; begin -- Separate the filename from the directory name pos := instr(file_path,'/',-1); if pos = 0 then pos := instr(file_path,'\',-1); end if; if pos = 0 then directory_path := null; else directory_path := substr(file_path,1,pos - 1); end if; file_name := substr(file_path,pos + 1); end; -- Procedure to append a file's contents to the e-mail PROCEDURE append_file(directory_path IN VARCHAR2, file_name IN VARCHAR2, file_type IN VARCHAR2, conn IN OUT UTL_SMTP.CONNECTION) IS generated_name varchar2(30) := 'CESDIR' || to_char(sysdate,'HH24MISS'); directory_name varchar2(100); file_handle utl_file.file_type; bfile_handle bfile; bfile_len number; pos number; read_bytes number; line varchar2(1000); data raw(200); my_code number; my_errm varchar2(32767); begin begin -- Grant access to the directory, unless already defined, and open -- the file (as a bfile for a binary file, otherwise as a text file). begin -- line := directory_path; select dd.my_path into directory_name from table_with_path dd; exception when no_data_found then directory_name := generated_name; end; if directory_name = generated_name then execute immediate 'create or replace directory ' || directory_name || ' as ''' || directory_path || ''''; execute immediate 'grant read on directory ' || directory_name || ' to public'; end if; if substr(file_type,1,4) != 'text' then bfile_handle := bfilename(directory_name,file_name); bfile_len := dbms_lob.getlength(bfile_handle); pos := 1; dbms_lob.open(bfile_handle,dbms_lob.lob_readonly); else file_handle := utl_file.fopen(directory_name,file_name,'r'); end if; -- Append the file contents to the end of the message loop -- If it is a binary file, process it 57 bytes at a time, -- reading them in with a LOB read, encoding them in BASE64, -- and writing out the encoded binary string as raw data if substr(file_type,1,4) != 'text' then if pos + 57 - 1 > bfile_len then read_bytes := bfile_len - pos + 1; else read_bytes := 57; end if; dbms_lob.read(bfile_handle,read_bytes,pos,data); utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data)); pos := pos + 57; if pos > bfile_len then exit; end if; -- If it is a text file, get the next line of text, append a -- carriage return / line feed to it, and write it out else utl_file.get_line(file_handle,line); utl_smtp.write_data(conn,line || crlf); end if; end loop; -- Output any errors, except at end when no more data is found exception when no_data_found then null; when others then my_code := SQLCODE; my_errm := SQLERRM; dbms_output.put_line('AO_SEND_MAIL: Error code ' || my_code || ': ' || my_errm); end; -- Close the file (binary or text) if substr(file_type,1,4) != 'text' then dbms_lob.close(bfile_handle); else utl_file.fclose(file_handle); end if; if directory_name = generated_name then execute immediate 'drop directory ' || directory_name; end if; end; begin -- Load the three filenames and file (mime) types into an array for -- easier handling later file_array(1) := filename1; file_array(2) := filename2; file_array(3) := filename3; type_array(1) := filetype1; type_array(2) := filetype2; type_array(3) := filetype3; -- Open the SMTP connection and set the From and To e-mail addresses conn := utl_smtp.open_connection(smtp_host,smtp_port); utl_smtp.helo(conn,smtp_host); recipients := from_name; utl_smtp.mail(conn,get_address(recipients)); recipients := to_names; <<para>> begin while recipients is not null loop utl_smtp.rcpt(conn,get_address(recipients)); end loop; exception when others then if sqlcode = -29279 then -- E-mail inexistente recipients := replace(replace(replace(recipients,';'||addr||';',';'),addr||';'),';'||addr) ; html_message_complemento := addr || crlf; goto para; end if; end; recipients := cc_names; <<copia>> begin while recipients is not null loop utl_smtp.rcpt(conn,get_address(recipients)); end loop; exception when others then if sqlcode = -29279 then -- E-mail inexistente recipients := replace(replace(replace(recipients,';'||addr||';',';'),addr||';'),';'||addr) ; html_message_complemento := addr || crlf; goto copia; end if; end; recipients := bcc_names; <<oculta>> --copia oculta begin while recipients is not null loop utl_smtp.rcpt(conn,get_address(recipients)); end loop; exception when others then if sqlcode = -29279 then -- E-mail inexistente recipients := replace(replace(replace(recipients,';'||addr||';',';'),addr||';'),';'||addr) ; html_message_complemento := addr || crlf; goto oculta; end if; end; utl_smtp.open_data(conn); -- Build the start of the mail message mesg := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || crlf || 'From: ' || from_name || crlf || 'Subject: ' || subject || crlf || 'To: ' || to_names || crlf; if cc_names is not null then mesg := mesg || 'Cc: ' || cc_names || crlf; end if; if bcc_names is not null then mesg := mesg || 'Bcc: ' || bcc_names || crlf; end if; mesg := mesg || 'Mime-Version: 1.0' || crlf || 'Content-Type: multipart/mixed; boundary="' || boundary || '"' || crlf || crlf || 'This is a Mime message, which your current mail reader may not' || crlf || 'understand. Parts of the message will appear as text. If the remainder' || crlf || 'appears as random characters in the message body, instead of as' || crlf || 'attachments, then you''ll have to extract these parts and decode them' || crlf || 'manually.' || crlf || crlf; utl_smtp.write_data(conn,mesg); -- Write the text message or message file, if any if message is not null then mesg := '--' || boundary || crlf || 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || crlf || 'Content-Disposition: inline; filename="message.txt"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf; utl_smtp.write_data(conn,mesg); if substr(message,1,1) = '/' then split_path_name(message,directory_path,file_name); append_file(directory_path,file_name,'text',conn); utl_smtp.write_data(conn,crlf); else utl_smtp.write_data(conn,message || crlf); end if; end if; -- Write the HTML message or message file, if any if html_message_complemento is not null then -- Contém e-mails inexistentes html_message_complemento := '<FONT face= "Courier New" SIZE=2 COLOR="#ff0000">' || '---------------------------------------------------------------------' || crlf_html || ' PROBLEMAS NO ENVIO DE E-MAIL ' || crlf_html || '---------------------------------------------------------------------' || crlf_html || 'A lista abaixo contem e-mails que apresentam problemas de envio.' || crlf_html || 'Estes e-mails devem ser verificados e/ou excluidos pelo aplicativo' || crlf_html || 'Manutencao de Excecoes.' || crlf_html || crlf_html || html_message_complemento || crlf_html || '---------------------------------------------------------------------' || crlf_html || '</FONT>'; html_message_complemento := nvl(html_message,'.') || crlf_html || html_message_complemento; end if; if html_message_complemento is not null then mesg := '--' || boundary || crlf || 'Content-Type: text/html; name="message.html"; charset=US-ASCII' || crlf || 'Content-Disposition: inline; filename="message.html"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf; utl_smtp.write_data(conn,mesg); if substr(html_message_complemento,1,1) = '/' then split_path_name(html_message_complemento,directory_path,file_name); append_file(directory_path,file_name,'text',conn); utl_smtp.write_data(conn,crlf); else utl_smtp.write_data(conn,html_message_complemento || crlf); end if; end if; -- Append the files for i in 1..3 loop -- If the filename has been supplied ... if file_array(i) is not null then split_path_name(file_array(i),directory_path,file_name); -- Generate the MIME boundary line according to the file (mime) type -- specified. mesg := crlf || '--' || boundary || crlf; if substr(type_array(i),1,4) != 'text' then mesg := mesg || 'Content-Type: ' || type_array(i) || '; name="' || file_name || '"' || crlf || 'Content-Disposition: attachment; filename="' || file_name || '"' || crlf || 'Content-Transfer-Encoding: base64' || crlf || crlf ; else mesg := mesg || 'Content-Type: application/octet-stream; name="' || file_name || '"' || crlf || 'Content-Disposition: attachment; filename="' || file_name || '"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf ; end if; utl_smtp.write_data(conn,mesg); -- Append the file contents to the end of the message append_file(directory_path,file_name,type_array(i),conn); utl_smtp.write_data(conn,crlf); end if; end loop; -- Append the final boundary line mesg := crlf || '--' || boundary || '--' || crlf; utl_smtp.write_data(conn,mesg); -- Close the SMTP connection utl_smtp.close_data(conn); utl_smtp.quit(conn); end; |
