CREATE OR REPLACE PROCEDURE Pro_Ictpayslipmail(from_name VARCHAR2,
to_name VARCHAR2,
bcc_name VARCHAR2,
empname VARCHAR2,
subject VARCHAR2,
max_size NUMBER DEFAULT 99999999999,
filename1 VARCHAR2 DEFAULT NULL,
filename2 VARCHAR2 DEFAULT NULL,
filename3 VARCHAR2 DEFAULT NULL,
filename4 VARCHAR2 DEFAULT NULL,
filename5 VARCHAR2 DEFAULT NULL,
filename6 VARCHAR2 DEFAULT NULL,
filename7 VARCHAR2 DEFAULT NULL,
filename8 VARCHAR2 DEFAULT NULL,
filename9 VARCHAR2 DEFAULT NULL,
filename10 VARCHAR2 DEFAULT NULL)
IS
---Declaration Section
v_smtp_server VARCHAR2 (20) := '192.168.61.200';
v_smtp_server_port NUMBER := 25;
v_directory_name VARCHAR2 (100) := 'ICTMAIL';
v_file_name VARCHAR2 (100) := 'APPR.PDF';
cc_name VARCHAR2(100);
-- bcc_name VARCHAR2(100);
v_line VARCHAR2 (1000);
crlf VARCHAR2(2) := CHR(13) || CHR(10);
message VARCHAR2( 4000 CHAR );
message2 VARCHAR2( 4000 CHAR );
mesg VARCHAR2(32767);
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_len NUMBER;
mesg_too_long EXCEPTION;
invalid_path EXCEPTION;
mesg_length_exceeded BOOLEAN := FALSE;
mesg_body0 VARCHAR2(4000 CHAR):= ' ';
font_red VARCHAR2(100 CHAR):='';
font_blue VARCHAR2(100 CHAR):='';
font3 VARCHAR2(100 CHAR):='';
font4 VARCHAR2(100 CHAR):='';
font5 VARCHAR2(100 CHAR):='';
mesg_body1 VARCHAR2(300 CHAR):='Dear '||EMPNAME||' san ,
';
mesg_body2 VARCHAR2(300 CHAR):='Please find the attached Payslip .
';
mesg_body4 VARCHAR2(300 CHAR):='
Regards,
HR ES
';
--mesg_body5 VARCHAR2(300 CHAR):=' *** Please do not reply to this auto generated mail ***
';
--Variables for TOs
lv_to VARCHAR2 (32767);
lv_to_temp1 VARCHAR2 (32767) := to_name;
lv_to_temp2 VARCHAR2 (32767);
lv_to_check NUMBER := NULL;
--Variables for CCs
lv_cc VARCHAR2 (32767);
lv_cc_temp1 VARCHAR2 (32767) := cc_name;
lv_cc_temp2 VARCHAR2 (32767);
lv_cc_check NUMBER := NULL;
--Variables for BCCs
lv_bcc VARCHAR2 (32767);
lv_bcc_temp1 VARCHAR2 (32767) := bcc_name;
lv_bcc_temp2 VARCHAR2 (32767);
lv_bcc_check NUMBER := NULL;
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;
file_array (5) := filename5;
file_array (6) := filename6;
file_array (7) := filename7;
file_array (8) := filename8;
file_array (9) := filename9;
file_array (10) := filename10;
-- Opens 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);
-- build the start of the mail message ...
--- TOs
-- utl_smtp.rcpt (conn, to_name);
WHILE LENGTH (lv_to_temp1) IS NOT NULL LOOP
lv_to_check := INSTR (lv_to_temp1, ',', 1);
IF lv_to_check = 0 THEN
lv_to_temp2 := SUBSTR (lv_to_temp1, 1);
lv_to_temp1 := NULL;
ELSE
lv_to_temp2 := SUBSTR (lv_to_temp1, 1, INSTR (lv_to_temp1, ',', 1)-1 );
lv_to_temp1 := SUBSTR (lv_to_temp1, INSTR (lv_to_temp1, ',', 1) + 1);
END IF;
utl_smtp.rcpt (conn, lv_to_temp2);
lv_to := lv_to || lv_to_temp2 ||', ';
END LOOP;
lv_to := 'To: ' || SUBSTR (lv_to, 1, LENGTH (lv_to)-2);
--- End of TOs
--- CCs
-- utl_smtp.rcpt (conn, cc_name);
WHILE LENGTH (lv_cc_temp1) IS NOT NULL LOOP
lv_cc_check := INSTR (lv_cc_temp1, ',', 1);
IF lv_cc_check = 0 THEN
lv_cc_temp2 := SUBSTR (lv_cc_temp1, 1);
lv_cc_temp1 := NULL;
ELSE
lv_cc_temp2 := SUBSTR (lv_cc_temp1, 1, INSTR (lv_cc_temp1, ',', 1)-1 );
lv_cc_temp1 := SUBSTR (lv_cc_temp1, INSTR (lv_cc_temp1, ',', 1) + 1);
END IF;
utl_smtp.rcpt (conn, lv_cc_temp2);
lv_cc := lv_cc || lv_cc_temp2 ||', ';
END LOOP;
lv_cc := 'CC: ' || SUBSTR (lv_cc, 1, LENGTH (lv_cc)-2);
--- End of CCs
--- BCCs
-- utl_smtp.rcpt (conn, bcc_name);
WHILE LENGTH (lv_bcc_temp1) IS NOT NULL LOOP
lv_bcc_check := INSTR (lv_bcc_temp1, ',', 1);
IF lv_bcc_check = 0 THEN
lv_bcc_temp2 := SUBSTR (lv_bcc_temp1, 1);
lv_bcc_temp1 := NULL;
ELSE
lv_bcc_temp2 := SUBSTR (lv_bcc_temp1, 1, INSTR (lv_bcc_temp1, ',', 1)-1 );
lv_bcc_temp1 := SUBSTR (lv_bcc_temp1, INSTR (lv_bcc_temp1, ',', 1) + 1);
END IF;
utl_smtp.rcpt (conn, lv_bcc_temp2);
lv_bcc := lv_bcc || lv_bcc_temp2 ||', ';
END LOOP;
lv_bcc := 'BCC: ' || SUBSTR (lv_bcc, 1, LENGTH (lv_bcc)-2);
--- End of BCCs
utl_smtp.open_data (conn);
utl_smtp.write_data(conn,lv_to || utl_tcp.crlf );
utl_smtp.write_data(conn,lv_cc || utl_tcp.crlf );
utl_smtp.write_data(conn,lv_Bcc || utl_tcp.crlf );
utl_smtp.write_data(conn,'Date:'||TO_CHAR (SYSDATE, 'hh24:mi:ss dd-Mon-yyyy')||utl_tcp.crlf);
utl_smtp.write_data(conn, 'Subject: '||subject||crlf);
message:='' || crlf ||mesg_body0||font_blue||mesg_body1||font_blue||mesg_body2;
mesg:= 'Content-Transfer-Encoding: 7bit' || crlf ||
'Content-Type: multipart/mixed;
boundary="DMW.Boundary.605592468"' || crlf ||
'Mime-Version: 1.0' || crlf ||
'--DMW.Boundary.605592468' || crlf ||
'Content-Transfer-Encoding: binary'||crlf||
'Content-Type: text/html' ||crlf ||
crlf || message || crlf ;
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..10 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
-- 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 := 'ICTMAIL';
v_file_name := SUBSTR (file_array (i), v_slash_pos + 1);
-- open the file ...
BEGIN
v_file_handle := UTL_FILE.FOPEN (v_directory_name, v_file_name, 'r');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20010, 'Invalid Path or File Name');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20011, 'Invalid File Handle');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20012, 'Invalid Operation');
END;
-- 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
BEGIN
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);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
mesg := crlf;
utl_smtp.write_data (conn, mesg);
-- close the file ...
UTL_FILE.FCLOSE (v_file_handle);
END IF;
END LOOP;
message2:= crlf ||'' || crlf ||font_blue||mesg_body4;
mesg:= 'Content-Transfer-Encoding: 7bit' || crlf ||
'Content-Type: multipart/mixed;
boundary="DMW.Boundary.605592468"' || crlf ||
'Mime-Version: 1.0' || crlf ||
'--DMW.Boundary.605592468' || crlf ||
'Content-Transfer-Encoding: binary'||crlf||
'Content-Type: text/html' ||crlf ||
crlf || message2 || crlf ;
utl_smtp.write_data (conn, mesg);
-- 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);
EXCEPTION
WHEN utl_smtp.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20013, 'error');
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(v_file_handle) THEN
UTL_FILE.FCLOSE (v_file_handle);
END IF;
RAISE_APPLICATION_ERROR(-20014, 'Error in MAIL_FILES_PRO'||' '||SQLERRM);
END Pro_Ictpayslipmail;
/