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;
