Sent Email from oracle 10g PL/SQL

Change the following things when you run this program:
1. v_email_server parameter value. Put your mail server details.
2. v_port parameter value. Generally, it is 25 only.
3. v_dirpath parameter value. Directory path of the file to read
4. v_filename parameter value. This is your text file name. (I put 10 lines in the text file for testing)

DECLARE

   v_file_handle    UTL_FILE.FILE_TYPE;

   v_conn           UTL_SMTP.CONNECTION;

   v_reply          UTL_SMTP.REPLY;

   v_email_server   VARCHAR2 (100):= 'lax02.lax.corp.int.gect.com';

   v_port           NUMBER        := 25;

 

   v_dirpath        VARCHAR2 (50) := '/usr/tmp';

   v_filename       VARCHAR2 (50) := 'testing123.txt';

   v_sender         VARCHAR2 (50) := 'ranjeet@mydomain.com';

   v_recpnt         VARCHAR2 (255):= 'to@senderdomain.com'; 

   v_msg            VARCHAR2 (32767);

   v_line           VARCHAR2 (1000);

   v_message        VARCHAR2 (1000);

   CRLF             VARCHAR2 (2)  := CHR (13) || CHR (10);

BEGIN

   /* Check if the file exists */

   BEGIN

      v_file_handle := UTL_FILE.FOPEN (v_dirpath, v_filename, 'R');

   EXCEPTION

      WHEN UTL_FILE.INVALID_PATH THEN

         RETURN;

      WHEN OTHERS THEN

         RETURN;

   END;

   /* Try connecting smtp server  and do handshake*/

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

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

 

IF v_reply.code != 250 THEN

       RETURN;

   END IF;

   /*UTL_SMTP.MAIL initiates a mail transaction with the server. The destination is a mailbox.*/

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

   IF v_reply.code != 250 THEN

      RETURN;

   END IF;

   /* UTL_SMTP.RCPT specifies the recipient of an e-mail message. */

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

   IF v_reply.code != 250 THEN

      RETURN;

   END IF;

   /*UTL_SMTP.OPEN_DATA sends the DATA command after which you can use WRITE_DATA and WRITE_RAW_DATA to write a portion of the e-mail message.*/

   UTL_SMTP.OPEN_DATA (v_conn);

   v_message := 'This is an auto generated mail. Please do not reply to this mail.';

   v_msg     := 'Date: ' || TO_CHAR (SYSDATE, 'Mon DD yyyy hh24:mi:ss') || CRLF ||

                'From: ' || v_sender || CRLF ||

                'Subject: ' || 'Sample file' || CRLF ||

                'To: ' || v_recpnt || CRLF

                || v_message  || CRLF || CRLF;

                 

   /*UTL_SMTP.WRITE_DATA Writes a portion of the e-mail message*/ 

   UTL_SMTP.WRITE_DATA (v_conn, v_msg);

   /*Read each line of the mail and put it in the mail*/

   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);

   /*UTL_SMTP.CLOSE_DATA call ends the e-mail message*/

   UTL_SMTP.CLOSE_DATA (v_conn);

   /*UTL_SMTP.QUIT terminates an SMTP session and disconnects from the server*/

   UTL_SMTP.QUIT (v_conn);

EXCEPTION

 when others then

   raise_application_error(-20000, SQLERRM);

END;