Search This Blog

Saturday, November 20, 2010

Send Email Attachment through Oracle Package

DECLARE
v_file_handle UTL_FILE.FILE_TYPE;
v_email_server VARCHAR2(100) := 'xxxxxx';
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) := 'temp-sandeep@xxxx.ae' ;
v_strt_day t_wk.wk_strt_day_d%TYPE;
v_end_day t_wk.wk_strt_day_d%TYPE;
CRLF VARCHAR2(2):= CHR(13) || CHR(10);
RECPT VARCHAR2(255) := 'temp-sandeep@xxxx.ae';

SLP PLS_INTEGER :=
300;
pdirpath varchar2(50) := 'c:\sample\report';
pfilename varchar2(50) := 'test.xls';
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;

1 comment:

  1. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete