Sending Email Attachment Thru PL/SQL

DECLARE

v_file_handle UTL_FILE.FILE_TYPE;
v_email_server VARCHAR2 (100) := 'sgcmm232.apac.corp.eds.com';
v_conn UTL_SMTP.CONNECTION;
v_port NUMBER := 25;
v_reply UTL_SMTP.REPLY;
v_msg VARCHAR2 (32767);
v_line VARCHAR2 (1000);
v_message VARCHAR2 (1000);
b_connected BOOLEAN := FALSE;
v_sender VARCHAR2 (50) := 'adchintawar@gmail.com';
CRLF VARCHAR2 (2) := CHR (13) || CHR (10);
RECPT VARCHAR2 (255) := 'adchintawar@gmail.com';
SLP PLS_INTEGER := 300;
pdirpath varchar2 (50) := 'C:\AMIT_DOC';
pfilename varchar2 (50) := 'test.txt';
p_Stat number;

BEGIN

p_stat := 0;

/***** Check if the file exists ****/

BEGIN

v_file_handle := UTL_FILE.FOPEN (pDirPath, pFileName, 'R');

EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
p_stat := 99;
RETURN;

WHEN OTHERS
THEN
p_stat := 99;
RETURN;
END;

/***** Try to connect for three times, do sleep in between for 5 minutes *****/

FOR i IN 1 .. 3
LOOP
BEGIN

--open the connection with the smtp server and
--do the handshake

v_conn := UTL_SMTP.OPEN_CONNECTION (v_email_server, v_port);
v_reply := UTL_SMTP.HELO (v_conn, v_email_server);

IF 250 = v_reply.code
THEN
b_connected := TRUE;
EXIT;
END IF;

EXCEPTION
WHEN OTHERS
THEN
DBMS_LOCK.SLEEP (SLP);
END;

END LOOP;

IF b_connected = FALSE
THEN
p_stat := 99;
RETURN;
END IF;

v_reply := UTL_SMTP.MAIL (v_conn, v_sender);

IF 250 != v_reply.code
THEN
p_stat := 99;
RETURN;
END IF;

v_reply := UTL_SMTP.RCPT (v_conn, RECPT);

IF 250 != v_reply.code
THEN
p_stat := 99;
RETURN;
END IF;

UTL_SMTP.OPEN_DATA (v_conn);

v_message :=
'Sample Email This is an auto generated mail. Please do not reply to this mail.'
|| CHR (10);

v_msg :=
'Date: '
|| TO_CHAR (SYSDATE, 'Mon DD yy hh24:mi:ss')
|| CRLF
|| 'From: '
|| v_sender
|| CRLF
|| 'Subject: '
|| 'Sample file'
|| CRLF
|| 'To: '
|| RECPT
|| CRLF
|| 'Mime-Version: 1.0'
|| CRLF
|| 'Content-Type: multipart/mixed;
boundary="DMW.Boundary.605592468"'
|| CRLF
|| ''
|| CRLF
|| v_message
|| CRLF
|| ''
|| CRLF
|| '--DMW.Boundary.605592468'
|| CRLF
|| 'Content-Type: text/plain;
name="v_message.txt"; charset=US-ASCII'
|| CRLF
|| 'Content-Disposition: inline; filename="v_message.txt"'
|| CRLF
|| 'Content-Transfer-Encoding: 7bit'
|| CRLF
|| ''
|| CRLF
|| v_message
|| CRLF
|| CRLF
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

/***** Prepare the attachment to be sent *****/

v_Msg :=
CRLF
|| '--DMW.Boundary.605592468'
|| CRLF
|| 'Content-Type:
|| application/octet-stream; name="'
|| pFileName
|| '"'
|| CRLF
|| 'Content-Disposition: attachment; filename="'
|| pFileName
|| '"'
|| CRLF
|| 'Content-Transfer-Encoding: 7bit'
|| CRLF
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

LOOP

BEGIN

UTL_FILE.GET_LINE (v_file_handle, v_line);

EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;

END;

v_msg := '*** truncated ***'
|| CRLF;

v_msg := v_line
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

END LOOP;

UTL_FILE.FCLOSE (v_file_handle);

v_msg := CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

v_msg := CRLF
|| '--DMW.Boundary.605592468--'
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);
UTL_SMTP.CLOSE_DATA (v_conn);
UTL_SMTP.QUIT (v_conn);

EXCEPTION
WHEN OTHERS
THEN
p_stat := 99;

END;