Sample code to send Email with Attachment in Oracle

CREATE OR REPLACE PROCEDURE xx_send_email(

p_email IN VARCHAR2 -- Single User Email ID , test@test.com for Group Email ID

   ,p_path IN VARCHAR2 -- path where logfile available. 

   ,p_filename IN VARCHAR2 DEFAULT NULL -- logfile name 

   ,p_progname IN VARCHAR2 DEFAULT NULL -- Program name 

   ,p_mesgbody IN VARCHAR2 -- mail body 

   ) 

IS 

TYPE my_curs_type IS REF CURSOR; 

cursor_data my_curs_type; 

v_smtp_server VARCHAR2(100) := 'testmail.server.com'; 

v_smtp_server_port NUMBER := 25; 

v_line VARCHAR2(1000); 

crlf VARCHAR2(2) := CHR (13) || CHR (10); 

v_mesg VARCHAR2(32767); 

v_conn UTL_SMTP.CONNECTION; 

v_file_handle UTL_FILE.file_type; 

invalid_path EXCEPTION; 

v_dbname VARCHAR2(20); 

dbdir VARCHAR2(50); 

v_subject VARCHAR2(200); 

v_email VARCHAR2(80); 

v_from_name VARCHAR2(50); 

v_to_name VARCHAR2(32767); 

v_message VARCHAR2(500); 

v_stat VARCHAR2(500); 

BEGIN 

v_dbname := database_name; 

dbdir := p_path; 

v_conn := UTL_SMTP.open_connection (v_smtp_server

                                   ,v_smtp_server_port

   ); 

UTL_SMTP.helo (v_conn, v_smtp_server); 

v_from_name := 'root@test_ip_address'; 

v_subject := 'From: "SOLAD005" <XX_Server_' || v_dbname;

UTL_SMTP.mail (v_conn, v_from_name); 

--Assign Email ids to the Receiptent 

IF p_email IS NOT NULL 

   AND LOWER(p_email) != 'test@test.com' THEN 

UTL_SMTP.rcpt (v_conn, p_email); 

v_to_name := p_email; 

ELSE v_stat := 'SELECT email FROM emails where program_name=''' ||p_progname|| ''''; 

dbms_output.put_line(v_stat); 

OPEN cursor_data FOR v_stat ;-- using p_progname; 

LOOP

FETCH cursor_data INTO v_email;

EXIT WHEN cursor_data%NOTFOUND; 

UTL_SMTP.rcpt (v_conn, v_email); 

v_to_name := v_to_name || ';' || v_email; 

END LOOP; 

CLOSE cursor_data; 

END IF; 

IF p_filename IS NULL THEN 

v_message := 'Hi' || crlf || crlf || p_progname || p_mesgbody; 

ELSE 

v_message := 'Hi' || crlf || crlf || p_progname || p_mesgbody ||' and invalid records are available in attachment';

END IF; UTL_SMTP.open_data (v_conn); 

v_mesg := 'Date: ' || TO_CHAR (SYSDATE, 'DD Mon RR hh24:mi:ss') || crlf 

                   || 'From: ' || v_from_name || crlf 

   || 'Subject: ' || v_subject || crlf 

   || 'To: ' || v_to_name || crlf 

   || 'Mime-Version: 1.0 ' || crlf 

   || 'Content-Type: multipart/mixed; boundary="DMW.Boundary.123456789" ' || crlf 

   || '' || crlf || '--DMW.Boundary.123456789' || crlf 

   || 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' 

   || crlf || 'Content-Disposition: inline; filename="c:\message.txt"' || crlf 

   || 'Content-Transfer-Encoding: 7bit' || crlf || '' 

   || crlf || v_message || crlf

   ;

UTL_SMTP.write_data (v_conn, v_mesg);

IF p_filename IS NOT NULL THEN 

BEGIN 

v_file_handle := UTL_FILE.fopen (dbdir, p_filename, 'r'); 

-- generate the MIME boundary line ... 

v_mesg := crlf || '--DMW.Boundary.123456789' || crlf || 'Content-Type: application/octet-stream; name="' 

               || p_filename || '"' || crlf || 'Content-Disposition: attachment; filename="' 

   || p_filename || '"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf

   ; 

UTL_SMTP.write_data (v_conn, v_mesg); 

LOOP 

UTL_FILE.get_line (v_file_handle, v_line); 

v_mesg := v_line crlf; 

UTL_SMTP.write_data (v_conn, v_mesg); 

END LOOP; 

EXCEPTION 

WHEN UTL_FILE.invalid_path THEN 

DBMS_OUTPUT.put_line ( 'Error in opening attachment ' ||p_filename ); 

WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN 

UTL_SMTP.quit (v_conn); 

raise_application_error ( -20000, 'Failed to send mail due to the following error: ' ); 

WHEN OTHERS THEN 

NULL; 

END; 

END IF; 

v_mesg := crlf '--DMW.Boundary.123456789--' 

UTL_SMTP.close_data (v_conn); 

UTL_SMTP.quit (v_conn); 

END xx_send_email;