DECLARE from_name VARCHAR2(2000) :='Udaykumar'; to_name VARCHAR2(2000):='bc_gkanand@tkm.co.in'; cc_name VARCHAR2(2000) :=NULL; bcc_name VARCHAR2(2000) :=NULL; subject VARCHAR2(2000) :='Message:from Uday'; message VARCHAR2(2000):='Test file'; max_size NUMBER DEFAULT 9999999999; filename1 VARCHAR2(32767) :='rose.jpeg';--DEFAULT NULL;--) IS v_smtp_server VARCHAR2 (20) := '192.168.61.200';--- SMTP SERVER IP ADDRESS v_smtp_server_port NUMBER := 25; v_directory_name VARCHAR2 (100):='ANANDMAIL\'; lv_num NUMBER; v_file_name VARCHAR2 (500); v_line VARCHAR2 (1000); crlf VARCHAR2(2) := CHR(13) || CHR(10); 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; lv_file VARCHAR2(3000); 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; --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; v_buf RAW(32767); v_buf_size CONSTANT BINARY_INTEGER := 32767; BEGIN -- lv_file:=filename1; SELECT TO_NUMBER(LENGTH(lv_file)-LENGTH(REPLACE(lv_file,','))) INTO lv_num FROM dual; FOR i IN 1..lv_num+1 LOOP v_slash_pos:= INSTR(lv_file,',',-1); file_array(i):=v_directory_name||SUBSTR(lv_file,v_slash_pos+1,LENGTH(lv_file)); lv_file:=SUBSTR(lv_file,1,v_slash_pos-1); END LOOP; -- 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); -- 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); DBMS_OUTPUT.PUT_LINE (lv_cc); mesg := 'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf || 'From: ' || from_name || crlf || 'Subject: ' || subject || crlf || lv_to || crlf || lv_cc || crlf || lv_bcc || crlf || 'Mime-Version: 1.0' || crlf || 'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || 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 || '--DMW.Boundary.605592468' || crlf || 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || crlf || 'Content-Disposition: inline; filename="message.jpeg"' || crlf || 'Content-Transfer-Encoding: 8bit' || crlf || '' || crlf || message ||crlf ||' '||crlf ||'With Regards'||crlf ||'God Team'||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..lv_num+1 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 := SUBSTR (file_array (i), 1, v_slash_pos - 1); v_file_name := SUBSTR (file_array (i), v_slash_pos + 1); -- open the file ... BEGIN UTL_FILE.FCLOSE_ALL; --v_file_handle := UTL_FILE.FOPEN (v_directory_name, v_file_name, 'R'); v_file_handle := UTL_FILE.FOPEN (v_directory_name, v_file_name, 'R',v_buf_size); 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-Type: image/jpeg; name="' || v_file_name || '"' || crlf || 'Content-Disposition: attachment; filename="' || v_file_name || '"' || crlf || --'Content-Transfer-Encoding: 7bit' || crlf || crlf; 'Content-Transfer-Encoding: base64' || crlf || crlf; --mesg := crlf || crlf --|| v_file_name || '"' || crlf -- || v_file_name || '"' || crlf --|| 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); UTL_FILE.Get_Raw (v_file_handle,v_buf,v_buf_size); IF mesg_len + LENGTH (v_buf_size) > max_size THEN mesg := '*** truncated ***' || crlf; utl_smtp.write_data (conn, mesg); mesg_length_exceeded := TRUE; RAISE mesg_too_long; END IF; mesg := v_buf_size || 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; -- 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, SQLERRM); END;