This discussion is archived
5 Replies Latest reply: Mar 5, 2013 12:47 AM by Marwim RSS

pl/sql procedure to send emails with attachments

992429 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    --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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points