PL-SQL Procedure to send email with attachment

PL-SQL Procedure to send email with attachment
How to send email using plsql
How to send email with attachment using plsql
oracle send email from database
oracle send email with attachment utl_smtp

1.  Compile the below procedure XXSEND_EMAIL in the database

CREATE OR REPLACE PROCEDURE XXSEND_EMAIL (p_filename1     IN     VARCHAR2,
                                          v_subjectline   IN     VARCHAR2,
                                          o_errbuf           OUT VARCHAR2,
                                          o_retcode          OUT NUMBER)
AS
   CONN               UTL_SMTP.connection;

   v_msg_text         VARCHAR2 (8000) := NULL;

   v_msg_from         VARCHAR2 (100);

   v_mailhost         VARCHAR2 (100);

   v_msg_subject      VARCHAR2 (100) := v_subjectline;

   v_msg_to           VARCHAR2 (1000);

   v_msg_rcpt         VARCHAR2 (1000);



   TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2 (200)
      INDEX BY BINARY_INTEGER;



   FILE_ARRAY         VARCHAR2_TABLE; /** AN ARRAY TO STORE THE FILE NAMES **/

   I                  BINARY_INTEGER;                      /** ARRAY INDEX **/

   L_FILE_HANDLE      UTL_FILE.FILE_TYPE;                 /** FILE POINTER **/

   L_DIRECTORY_NAME   VARCHAR2 (200) := fnd_profile.VALUE ('UTIL_PATH'); /** TO STORE THE PATH / DIRECTORY NAME OF THE FILE **/

   l_attachment       LONG; --    VARCHAR2(32767);                 /** TO STORE THE MESSAGE **/

   L_LINE             VARCHAR2 (1000); /** TO STORE THE CONTENTS OF THE LINE READ FROM THE FILE **/

   L_FILE_NAME        VARCHAR2 (100);            /** TO STORE THE FILENAME **/

   reply              UTL_SMTP.reply;

   i_idx              NUMBER := 0;

   v_osuser           VARCHAR2 (100);

   v_serverhost       VARCHAR2 (100);



   CURSOR cur_mail_id
   IS                           /*select recipients mail ids from table name*/
      SELECT 'example@email.com' FROM DUAL;
BEGIN
   fnd_file.put_line (fnd_file.LOG, 'Sending Email...');



   v_msg_from := 'noreply@example.com';                        -- from mail id



   BEGIN
      SELECT fscpv.parameter_value smtp_host
        INTO v_mailhost
        FROM fnd_svc_comp_params_b   fscpb,
             fnd_svc_comp_param_vals fscpv,
             fnd_svc_components      fsc
       WHERE     fscpb.parameter_id = fscpv.parameter_id
             AND fscpv.component_id = fsc.component_id
             AND fscpb.parameter_name = 'OUTBOUND_SERVER'
             AND fsc.component_name = 'Workflow Notification Mailer';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_errbuf := ' SMTP HOST not found:' || SQLERRM;

         o_retcode := 1;

         RAISE;
   END;



   BEGIN
      BEGIN
         -- Open the connection

         BEGIN
            i_idx := 0;



            WHILE i_idx < 5
            LOOP
               reply := UTL_SMTP.open_connection (v_mailhost, 25, conn);



               IF reply.code BETWEEN 400 AND 599
               THEN
                  fnd_file.put_line (
                     fnd_file.LOG,
                     'Retrying to open connection: ' || TO_CHAR (i_idx + 1));

                  fnd_file.put_line (fnd_file.LOG, reply.text);
               ELSE
                  EXIT;
               END IF;



               i_idx := i_idx + 1;
            END LOOP;



            IF i_idx >= 5
            THEN
               RAISE_APPLICATION_ERROR (
                  -20010,
                     'Could not establish SMTP connection: '
                  || reply.code
                  || ' '
                  || reply.text);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               o_errbuf := 'error :' || SQLERRM;

               o_retcode := 1;

               RAISE;
         END;



         -- Check for handshake

         BEGIN
            i_idx := 0;



            WHILE i_idx < 5
            LOOP
               reply := UTL_SMTP.helo (conn, v_mailhost);



               IF reply.code BETWEEN 400 AND 599
               THEN
                  fnd_file.put_line (
                     fnd_file.LOG,
                     'Retrying helo: ' || TO_CHAR (i_idx + 1));

                  fnd_file.put_line (fnd_file.LOG, reply.text);
               ELSE
                  EXIT;
               END IF;



               i_idx := i_idx + 1;
            END LOOP;



            IF i_idx >= 5
            THEN
               RAISE_APPLICATION_ERROR (
                  -20010,
                     'Could not establish shakehand: '
                  || reply.code
                  || ' '
                  || reply.text);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               o_errbuf := 'error:' || SQLERRM;

               o_retcode := 1;

               RAISE;
         END;



         -- Add the Sender with respect to the site user's user group

         BEGIN
            i_idx := 0;



            WHILE i_idx < 5
            LOOP
               reply := UTL_SMTP.Mail (conn, v_msg_from);



               IF reply.code BETWEEN 400 AND 599
               THEN
                  fnd_file.put_line (
                     fnd_file.LOG,
                     'Retrying mail: ' || TO_CHAR (i_idx + 1));

                  fnd_file.put_line (fnd_file.LOG, reply.text);
               ELSE
                  EXIT;
               END IF;



               i_idx := i_idx + 1;
            END LOOP;



            IF i_idx >= 5
            THEN
               RAISE_APPLICATION_ERROR (
                  -20010,
                     'Could not initiate mail transaction with SMTP server: '
                  || reply.code
                  || ' '
                  || reply.text);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               o_errbuf := 'error :' || SQLERRM;

               o_retcode := 1;

               RAISE;
         END;



         -- Add the reciepents

         BEGIN
            i_idx := 0;



            WHILE i_idx < 5
            LOOP
               FOR rec_mail_id IN cur_mail_id
               LOOP
                  v_msg_rcpt := rec_mail_id.description;

                  reply := UTL_SMTP.Rcpt (conn, v_msg_rcpt);

                  v_msg_to := v_msg_to || rec_mail_id.description || '; ';
               END LOOP;



               IF reply.code BETWEEN 400 AND 599
               THEN
                  fnd_file.put_line (
                     fnd_file.LOG,
                     'Retrying rcpt: ' || TO_CHAR (i_idx + 1));

                  fnd_file.put_line (fnd_file.LOG, reply.text);
               ELSE
                  EXIT;
               END IF;



               i_idx := i_idx + 1;
            END LOOP;



            IF i_idx >= 5
            THEN
               RAISE_APPLICATION_ERROR (
                  -20010,
                     'Could not initiate Rcpt: '
                  || reply.code
                  || ' '
                  || reply.text);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               o_errbuf := 'error :' || SQLERRM;

               o_retcode := 1;

               RAISE;
         END;



         v_msg_to := RTRIM (v_msg_to, '; ');



         -- Open the data mode

         BEGIN
            i_idx := 0;



            WHILE i_idx < 5
            LOOP
               reply := UTL_SMTP.Open_data (conn);



               IF reply.code BETWEEN 400 AND 599
               THEN
                  fnd_file.put_line (
                     fnd_file.LOG,
                     'Retrying open data: ' || TO_CHAR (i_idx + 1));

                  fnd_file.put_line (fnd_file.LOG, reply.text);
               ELSE
                  EXIT;
               END IF;



               i_idx := i_idx + 1;
            END LOOP;



            IF i_idx >= 5
            THEN
               RAISE_APPLICATION_ERROR (
                  -20010,
                     'Could not send open_data to SMTP server: '
                  || reply.code
                  || ' '
                  || reply.text);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               o_errbuf := 'error :' || SQLERRM;

               o_retcode := 1;

               RAISE;
         END;



         BEGIN
            -- Subject of the email

            -- Message Text in HTML format

            v_msg_text := UTL_TCP.crlf || 'Attachment: Wire Payment Files.';

            -- Sending the header information

            UTL_SMTP.Write_data (conn,
                                 'From: ' || v_msg_from || UTL_TCP.crlf);

            UTL_SMTP.Write_data (conn, 'To: ' || v_msg_to || UTL_TCP.crlf);

            --utl_smtp.Write_data(conn,'Cc: ' ||'name@domain.com' ||utl_tcp.utl_tcp.crlf);

            UTL_SMTP.Write_data (
               conn,
               'Subject: ' || v_msg_subject || UTL_TCP.crlf);



            UTL_SMTP.Write_data (conn,
                                 'MIME-Version: ' || '1.0' || UTL_TCP.crlf);



            UTL_SMTP.Write_data (
               conn,
                  'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"'
               || UTL_TCP.crlf
               || ''
               || UTL_TCP.crlf
               || '--DMW.Boundary.605592468'
               || UTL_TCP.crlf
               || 'Content-Type: text/plain;'
               || UTL_TCP.crlf
               || 'Content-Transfer_Encoding: 7bit'
               || UTL_TCP.crlf
               || UTL_TCP.crlf);

            -- End of header information

            UTL_SMTP.Write_data (conn, v_msg_text);

            UTL_SMTP.Write_data (conn, UTL_TCP.crlf);

            UTL_SMTP.Write_data (conn, UTL_TCP.crlf);
         EXCEPTION
            WHEN OTHERS
            THEN
               o_errbuf := 'error :' || SQLERRM;

               o_retcode := 1;

               RAISE;
         END;



         /*** START ATTACHING THE FILES ***/

         BEGIN
            FILE_ARRAY (1) := p_filename1;



            FOR i IN 1 .. 1       --run the loop to attach more than one files
            LOOP
               IF file_array (i) IS NOT NULL
               THEN
                  BEGIN
                     l_file_name := FILE_ARRAY (i);

                     l_file_handle :=
                        UTL_FILE.fopen (l_directory_name, l_file_name, 'R');

                     l_attachment :=
                           UTL_TCP.crlf
                        || '--DMW.Boundary.605592468'
                        || UTL_TCP.crlf
                        || 'Content-Type: application/octet-stream; name="'
                        || l_file_name
                        || '"'
                        || UTL_TCP.crlf
                        || 'Content-Disposition: attachment; filename="'
                        || l_file_name
                        || '"'
                        || UTL_TCP.crlf
                        || 'Content-Transfer-Encoding: 7bit'
                        || UTL_TCP.crlf
                        || UTL_TCP.crlf;



                     UTL_SMTP.write_data (conn, l_attachment);



                     LOOP
                        BEGIN
                           UTL_FILE.get_line (l_file_handle, l_line);
                        EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                              EXIT;
                        END;



                        l_attachment := l_line || UTL_TCP.crlf;

                        UTL_SMTP.write_data (conn, l_attachment);
                     END LOOP;
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        fnd_file.put_line (
                           fnd_file.LOG,
                              ' Invalid Operation in Mail attempt using UTL_SMTP:'
                           || SQLERRM);
                     WHEN UTL_FILE.invalid_path
                     THEN
                        fnd_file.put_line (
                           fnd_file.LOG,
                              ' Invalid Operation in Mail attempt using UTL_SMTP:'
                           || SQLERRM);
                     WHEN OTHERS
                     THEN
                        fnd_file.put_line (
                           fnd_file.LOG,
                              ' Invalid Operation in Mail attempt using UTL_SMTP:'
                           || SQLERRM);
                  END;



                  UTL_FILE.fclose (l_file_handle);
               END IF;
            END LOOP;



            l_attachment :=
               UTL_TCP.crlf || '--DMW.Boundary.605592468--' || UTL_TCP.crlf;

            UTL_SMTP.write_data (conn, l_attachment);
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                     ' Invalid Operation in Mail attempt using UTL_SMTP:'
                  || SQLERRM);
         END;



         UTL_SMTP.Close_data (conn);

         UTL_SMTP.Quit (conn);

         COMMIT;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (
            fnd_file.LOG,
            ' Invalid Operation in Mail attempt using UTL_SMTP:' || SQLERRM);
   END;
END XXSEND_EMAIL;
/

2.  Call the procedure with below anonymous block

DECLARE
   o_errbuf    VARCHAR2 (2000);

   o_retcode   NUMBER;
BEGIN
   XXSEND_EMAIL ('testfile.pdf',
                 'Test Subject',
                 o_errbuf,
                 o_retcode);
   DBMS_OUTPUT.put_line (o_errbuf);
END;
  • Please note that the Workflow mailer should be up and running in order this procedure to work
  • Please note that the file location should be registered as util file location in the database in order to send the attachment
Related posts: Upload your own post and refer it anywhere anytime:

4 thoughts on “PL-SQL Procedure to send email with attachment”

  1. But, below query does not give any result in SYS:

    select * from dba_objects
    where OBJECT_NAME like '%FND%PROFILE%';

Leave a Reply

%d bloggers like this: