This procedure uses the UTL_SMTP package to send an email message.
Up to three file names may be specified as attachments.
Parameters are:
1) from_name
(varchar2)
2) to_name (varchar2)
3) subject (varchar2)
4) message (varchar2)
5)
max_size (number)
5) filename1
(varchar2)
6) filename2
(varchar2)
7) filename3
(varchar2)
eg.
mail_files(
from_name => 'oracle' ,
to_name => 'someone@somewhere.com' ,
subject => 'A test',
message => 'A test message',
filename1 =>
'/data/oracle/dave_test1.txt',
filename2 =>
'/data/oracle/dave_test2.txt');
Most of the parameters are
self-explanatory. "message" is a varchar2 parameter, up to 32767
bytes long which contains the text of the message to be placed in the main body
of the email.
filename{1,2,3} are the names
of the files to be attached to the email. The full pathname of each file must
be specified. The files must exist in one of the directories specified in
the init. ora parameter
UTL_FILE_DIR. All filename
parameters are optional: It is not necessary to specify unused file parameters
(eg. filename3 is missing in the above example).
The max_size parameter enables you to place a
constraint on the maximum size of message, including all attachments, that the
procedure will send. If this limit is exceeded, the procedure will
truncate the message at that point with a '*** truncated ***' message.
The default is effectively unlimited. However, the text of message body is
still limited to 32Kb, as it is passed in as a varchar2.
Obviously, as with any Oracle
procedure, the parameter values can (and usually will be) PL/SQL variables,
rather than hard-coded literals, as shown here.
You may need to modify the
following variable if you don't have a local SMTP service running (particularly
relevant to Windows 2000 servers).
CREATE OR REPLACE PROCEDUREsend_mail_attachment( from_name VARCHAR2, to_name VARCHAR2, cc_name VARCHAR2 DEFAULT NULL, bcc_name VARCHAR2 DEFAULT NULL, subject VARCHAR2, message VARCHAR2, max_size NUMBER DEFAULT 9999999999, filename1 VARCHAR2 DEFAULT NULL, filename2 VARCHAR2 DEFAULT NULL, filename3 VARCHAR2 DEFAULT NULL, filename4 VARCHAR2 DEFAULT NULL, DEBUG NUMBER DEFAULT 0 ) IS v_smtp_server VARCHAR2(30) := 'MAIL_SERVER_DOMAIN_NAME'; v_smtp_server_port NUMBER := 25; -- SMTP PORT NAME (Default port 25) v_directory_name VARCHAR2(100); v_file_name VARCHAR2(100); v_line VARCHAR2(1000); crlf VARCHAR2(2):= chr(13) || chr(10); mesg VARCHAR2(32767); msg_header VARCHAR2(32767); mesg_len NUMBER; conn UTL_SMTP.CONNECTION; TYPE varchar2_table IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER; file_array varchar2_table; i BINARY_INTEGER; v_file_handle utl_file.file_type; v_slash_pos NUMBER; mesg_too_long EXCEPTION; invalid_path EXCEPTION; mesg_length_exceeded BOOLEAN := FALSE; var_length INTEGER; var_rcpt VARCHAR2(500); var_i INTEGER; var_start INTEGER; BEGIN -- first load the three filenames into an array for easier handling later ... file_array(1) := filename1; file_array(2) := filename2; file_array(3) := filename3; file_array(4) := filename4; -- Open the SMTP connection ... -- ------------------------ conn := utl_smtp.open_connection( v_smtp_server, v_smtp_server_port ); -- Initial handshaking ... -- ------------------- utl_smtp.helo( conn, v_smtp_server ); utl_smtp.mail( conn, from_name ); -- Handling multiple TO List ... -- ---------------- var_i := 1; var_length := nvl(length(to_name), 0); LOOP EXIT WHEN var_i >= var_length; var_start := var_i; LOOP EXIT WHEN var_i >= var_length OR substr(to_name, var_i+1, 1) = ','; var_i := var_i + 1; END LOOP; var_rcpt := substr(to_name, var_start, var_i-var_start+1); utl_smtp.rcpt(conn, var_rcpt); var_i := var_i + 2; END LOOP; -- Handling multiple CC List ... -- ---------------- var_i := 1; var_length := nvl(length(cc_name), 0); LOOP EXIT WHEN var_i >= var_length; var_start := var_i; LOOP EXIT WHEN var_i >= var_length or substr(cc_name, var_i+1, 1) = ','; var_i := var_i + 1; END LOOP; var_rcpt := substr(cc_name, var_start, var_i-var_start+1); utl_smtp.rcpt(conn, var_rcpt); var_i := var_i + 2; END LOOP; -- Handling multiple BCC List ... -- -------------------------- var_i := 1; var_length := nvl(length(bcc_name), 0); LOOP EXIT WHEN var_i >= var_length; var_start := var_i; LOOP EXIT WHEN var_i >= var_length OR substr(bcc_name, var_i+1, 1) = ','; var_i := var_i + 1; END LOOP; var_rcpt := substr(bcc_name,var_start,var_i-var_start+1); utl_smtp.rcpt(conn, var_rcpt); var_i := var_i + 2; END LOOP; utl_smtp.open_data ( conn ); -- build the start of the mail message ... -- ----------------------------------- msg_header := 'Date: ' || TO_CHAR( SYSDATE-1/4, 'dd Mon yy hh24:mi:ss' ) || crlf || 'From: ' || from_name || crlf || 'Subject: ' || subject || crlf || 'To: ' || to_name || crlf || 'CC: ' || cc_name || crlf || 'BCC: ' || bcc_name || crlf || 'Mime-Version: 1.0' || crlf || 'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || crlf || ''; utl_smtp.write_data ( conn, msg_header); mesg:= 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 || '--DMW.Boundary.605592468' || crlf || 'Content-Type: text/plain; charset=US-ASCII' || crlf || 'Content-Disposition: inline; ' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || '' || crlf || message || crlf ; -- To send message as attachment -- In Content-Type mesg_len := length(mesg); IF mesg_len > max_size THEN mesg_length_exceeded := TRUE; END IF; utl_smtp.write_data ( conn, mesg ); -- Append the files ... -- ---------------- FOR i IN 1..4 LOOP -- Exit if message length already exceeded ... EXIT WHEN mesg_length_exceeded; -- If the filename has been supplied ... IF file_array(i) IS NOT NULL THEN BEGIN -- locate the final '/' or '\' in the pathname ... v_slash_pos := instr(file_array(i), '/', -1 ); IF v_slash_pos = 0 THEN v_slash_pos := instr(file_array(i), '\', -1 ); END IF; -- separate the filename from the directory name ... v_directory_name := substr(file_array(i), 1, v_slash_pos - 1 ); v_file_name := substr(file_array(i), v_slash_pos + 1 ); -- open the file ... v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' ); -- generate the MIME boundary line ... mesg := crlf || '--DMW.Boundary.605592468' || crlf || 'Content-Type: application/octet-stream; name="' || v_file_name || '"' || crlf || 'Content-Disposition: attachment; filename="' || v_file_name || '"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf ; mesg_len := mesg_len + length(mesg); utl_smtp.write_data ( conn, mesg ); -- and append the file contents to the end of the message ... LOOP utl_file.get_line(v_file_handle, v_line); IF mesg_len + length(v_line) > max_size THEN mesg := '*** truncated ***' || crlf; utl_smtp.write_data ( conn, mesg ); mesg_length_exceeded := TRUE; RAISE mesg_too_long; END IF; mesg := v_line || crlf; utl_smtp.write_data ( conn, mesg ); mesg_len := mesg_len + length(mesg); END LOOP; EXCEPTION WHEN utl_file.invalid_path THEN IF DEBUG > 0 THEN dbms_output.put_line('Error in opening attachment '|| file_array(i) ); END IF; WHEN UTL_SMTP.INVALID_OPERATION THEN dbms_output.put_line(' Invalid Operation in SMTP transaction.'); WHEN UTL_SMTP.TRANSIENT_ERROR THEN dbms_output.put_line(' Temporary problems with sending email - try again later.'); WHEN UTL_SMTP.PERMANENT_ERROR THEN dbms_output.put_line(' Errors in code for SMTP transaction.'); -- All other exceptions are ignored .... WHEN OTHERS THEN NULL; END; mesg := crlf; utl_smtp.write_data ( conn, mesg ); -- close the file ... utl_file.fclose(v_file_handle); END IF; END LOOP; -- append the final boundary line ... mesg := crlf || '--DMW.Boundary.605592468--' || crlf; utl_smtp.write_data ( conn, mesg ); -- and close the SMTP connection ... utl_smtp.close_data( conn ); utl_smtp.quit( conn ); END;
--Execution Example
BEGIN
send_mail_attachment (from_name => 'abc@domain_name.com',
to_name => 'xyz@domain_name.com',
cc_name => null,
bcc_name => mill,
subject => 'Test Mail',
message => 'Hello',
filename1 => 'abc.jpg',
filename2 => 'pqr.pdf',
filename3 => null,
filename4 => null );
END; 
No comments:
Post a Comment