Code to send E-mail from PLSQL by reading the file content

Business Case:
 This is a email program to sends email to the receiver/user by reading the text file content

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) := 'name@mydomain.com';

   v_recpnt         VARCHAR2 (255):= 'name@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;


Note:

In a typical Oracle Apps environment

1. The file that has to be read has to kept in DB Node. Meaning database server. NOT on Appl Tier. Meaning NOT on Middle tier.

2. To find out the email server, The navigation is

Sysadmin Login

Workflow (Oracle Applications Manager) > Under Configuration 'Service Components' > select 'Workflow Notification Mailer' > Edit > Inbound EMail Account (IMAP) > Note Server Name