5 Replies Latest reply: Mar 5, 2013 2:47 AM by Marwim RSS

    pl/sql procedure to send emails with attachments

    992429
      Hi,

      Daily i run some queries related to my application manually and mail those reports in the form of excel to my customers. Now i would like to automate this process.
      I have got some idea that i have to use UTL_SMTP in my procedure and DBMS_Scheduler for this requirement. I am seeking for a help to complete this requirement by providing me some links or sample pl/sql code.

      Additional Info : Database : Oracle11g


      Thanks in advance!!
        • 1. Re: pl/sql procedure to send emails with attachments
          BluShadow
          Try looking at the UTL_MAIL package.
          It's not installed by default, but it is provided with the database and adds a more 'user friendly' interface around the UTL_SMTP package to make life easier for basic email sending.

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_mail.htm#ARPLS384

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#ARPLS72235
          • 2. Re: pl/sql procedure to send emails with attachments
            nkvkashyap
            --Code:
            CREATE OR REPLACE PROCEDURE send_mail_nkv_new (
            msg_from VARCHAR2 default null,
            ----- MAIL BOX SENDING THE EMAIL
            msg_to VARCHAR2 default null,
            ----- MAIL BOX RECIEVING THE EMAIL
            msg_subject VARCHAR2 := 'Testing',
            ----- EMAIL SUBJECT
            msg_text VARCHAR2 := 'Hi All,'
            || CHR (10)
            || CHR (10)
            || 'Testing'
            || CHR (10)
            || CHR (10)
            || 'Thanks and Regards,'
            || CHR (10)
            || CHR (10)
            || 'Kashyap Varma N'
            )
            IS
            c UTL_TCP.connection;
            rc INTEGER;
            crlf VARCHAR2 (2) := CHR (13) || CHR (10);
            mesg VARCHAR2 (32767);
            v_string VARCHAR2 (32767);
            v_output1 LONG;
            v_new VARCHAR2 (32767);
            BEGIN
            v_output1 := 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO';

            FOR c1 IN (SELECT *
            FROM scott.emp)
            LOOP
            v_output1 :=
            v_output1
            || CHR (10)
            || c1.empno
            || ','
            || c1.ename
            || ','
            || c1.job
            || ','
            || c1.mgr
            || ','
            || c1.hiredate
            || ','
            || c1.sal
            || ','
            || c1.comm
            || ','
            || c1.deptno;
            END LOOP;

            c := UTL_TCP.open_connection ( 'GIVE UR OUTLOOK IP Address', 25); --GIVE UR OUTLOOK IP Address = Example 100.100.100.100
            ----- OPEN SMTP PORT CONNECTION
            rc := UTL_TCP.write_line (c, 'HELO GIVE UR OUTLOOK IP Address');
            ----- PERFORMS HANDSHAKING WITH SMTP SERVER
            DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
            rc := UTL_TCP.write_line (c, 'HELO 192.168.45.12');
            ----- PERFORMS HANDSHAKING, INCLUDING EXTRA INFORMATION
            DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
            rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || msg_from);
            ----- MAIL BOX SENDING THE EMAIL
            DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
            for c1 in (select 'lmn@gmail.com' a from dual union select 'abc@gmail.com' a from dual union select 'xyz@gmail.com' a from dual) loop
            rc := UTL_TCP.write_line (c, 'RCPT TO: ' || c1.a|| utl_tcp.CRLF);
            --rc := UTL_TCP.write_line (c, 'RCPT TO: ' || msg_to);
            v_new:=v_new||','||c1.a;
            end loop;
            v_new:=substr(v_new,2);

            --rc := UTL_TCP.write_line (c, 'RCPT TO: ' || msg_to);
            ----- MAIL BOX RECIEVING THE EMAIL
            DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
            rc := UTL_TCP.write_line (c, 'DATA'); ----- EMAIL MESSAGE BODY START
            DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
            rc :=
            UTL_TCP.write_line (c,
            'Date: '
            || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
            );
            rc :=
            UTL_TCP.write_line (c, 'From: ' || msg_from || ' <' || msg_from || '>');
            rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');
            --rc := UTL_TCP.write_line (c, 'To: ' || msg_to || ' <' || msg_to || '>');
            rc := UTL_TCP.write_line (c, 'To: ' || msg_to || ' <' || v_new || '>');
            rc := UTL_TCP.write_line (c, 'Subject: ' || msg_subject);
            rc := UTL_TCP.write_line (c, 'Content-Type: multipart/mixed;');
            ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
            rc := UTL_TCP.write_line (c, ' boundary="-----SECBOUND"');
            ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
            rc := UTL_TCP.write_line (c, '');
            ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
            rc := UTL_TCP.write_line (c, '-------SECBOUND');
            rc := UTL_TCP.write_line (c, 'Content-Type: text/plain');
            ----- 1ST BODY PART. EMAIL TEXT MESSAGE
            rc := UTL_TCP.write_line (c, 'Content-Transfer-Encoding: 7bit');
            rc := UTL_TCP.write_line (c, '');
            rc := UTL_TCP.write_line (c, msg_text); ----- TEXT OF EMAIL MESSAGE
            rc := UTL_TCP.write_line (c, '');
            rc := UTL_TCP.write_line (c, '-------SECBOUND');
            rc := UTL_TCP.write_line (c, 'Content-Type: text/plain;');
            ----- 2ND BODY PART.
            rc := UTL_TCP.write_line (c, ' name="Test.csv"');
            rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');
            rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');
            ----- INDICATES THAT THIS IS AN ATTACHMENT
            rc := UTL_TCP.write_line (c, ' filename="Test.csv"');
            --(c, ' filename="Test.txt"');
            ----- SUGGESTED FILE NAME FOR ATTACHMENT
            rc := UTL_TCP.write_line (c, '');
            rc := UTL_TCP.write_line (c, v_output1);
            rc := UTL_TCP.write_line (c, '-------SECBOUND--');
            rc := UTL_TCP.write_line (c, '');
            rc := UTL_TCP.write_line (c, '.'); ----- EMAIL MESSAGE BODY END
            DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
            rc := UTL_TCP.write_line (c, 'QUIT'); ----- ENDS EMAIL TRANSACTION
            DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
            UTL_TCP.close_connection (c); ----- CLOSE SMTP PORT CONNECTION
            EXCEPTION
            WHEN OTHERS
            THEN
            raise_application_error (-20000, SQLERRM);
            END;
            /

            The above code will send a mail to multiple users, with the file name "test.csv", in this file we have the complete data of EMP table

            Regards,
            Kashyap Varma N
            • 3. Re: pl/sql procedure to send emails with attachments
              BluShadow
              user5374501 wrote:
              The above code will send a mail to multiple users, with the file name "test.csv", in this file we have the complete data of EMP table
              The above code is horrendous.
              Using UTL_TCP to implement the SMTP protocol is pointless when Oracle already provides a UTL_SMTP package, and even more pointless when there is a UTL_MAIL package to make things even easier.
              Aside from that, the exception handling in that code is ridiculous.
              • 4. Re: pl/sql procedure to send emails with attachments
                992429
                Hi,

                Is it possible to create and write files in my local machine? does oracle have any facility to do so??
                • 5. Re: pl/sql procedure to send emails with attachments
                  Marwim
                  Hello,
                  Is it possible to create and write files in my local machine? does oracle have any facility to do so??
                  Is this question related to the original question {thread:id=2502205}? If not, then you should open a new thread.
                  And you should give us more details about your question, like what do you mean with
                  does oracle have any facility to do so??
                  The RDBMS, some frontend provided by Oracle?

                  Regards
                  Marcus