7 Replies Latest reply: Apr 25, 2012 3:32 AM by HuaMin Chen RSS

    Send an email with attacment via PL/SQL

    user590978
      Hi Guru's,
      First of all thank you some much to help me all the time.

      I need to send an email via pl/sql with attachment. Email is simple but i need to attache the file from the directory on the server. File already created from different process only i need to use that file for attachment and send an email.
      I am using utl_smtp for email.

      Could you please help me out

      Thanks in advance!

      Kind regards
        • 1. Re: Send an email with attacment via PL/SQL
          ajallen
          DESC utl_mail will show you the proc to use.

          Read up on it in the "PL/SQL Packages and Types Reference" manual.

          That will get you started pretty well. You could also Google utl_mail to see what others have done and posted.


          You will learn allot if you just RTFM (Read The Fine Manual).
          • 2. Re: Send an email with attacment via PL/SQL
            HuaMin Chen
            DECLARE
            v_From VARCHAR2(80) := 'sender@abc.com';
            v_Recipient VARCHAR2(120) := 'your_email@abc.com';
            v_Subject VARCHAR2(80) := 'test subject 2';
            v_Mail_Host VARCHAR2(30) := '10.???';-- IP of email server
            v_Mail_Conn utl_smtp.Connection;
            crlf VARCHAR2(2) := chr(13)||chr(10);
            v_dt VARCHAR2(30):=to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss');
            BEGIN
            v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
            utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
            utl_smtp.Mail(v_Mail_Conn, v_From);
            utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
            utl_smtp.Data(v_Mail_Conn,
            'Date: ' || v_dt || crlf ||
            'From: ' || v_From || crlf ||
            'Subject: '|| v_Subject || crlf ||
            'To: ' || v_Recipient || crlf ||
            crlf ||
            'some message text'|| crlf || -- Message body
            'more message text'|| crlf
            );
            utl_smtp.Quit(v_mail_conn);
            EXCEPTION
            WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
            raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
            END;
            /

            see also
            http://www.tek-tips.com/faqs.cfm?fid=5272

            Thanks & Best Regards,
            HuaMin
            • 3. Re: Send an email with attacment via PL/SQL
              Billy~Verreynne
              user590978 wrote:

              I need to send an email via pl/sql with attachment. Email is simple but i need to attache the file from the directory on the server. File already created from different process only i need to use that file for attachment and send an email.
              I am using utl_smtp for email.
              You need to read the file from the directory - and format an e-mail using MIME (<i>Multipurpose Internet Mail Extensions</i>). As part of this Mime e-mail body, your code needs to add the contents of the file.

              If the file is binary, the file's contents have to base64 encoded before adding it a Mime attachment to the e-mail body.

              The complete e-mail body is then send using the SMTP DATA command.

              You can also use UTL_MAIL - it creates a Mime body for the e-mail and supports a single (small) attachment. However, you still need to read the file from disk using UTL_FILE or DBMS_LOB.LoadFromFile() and pass the file contents to UTL_MAIL for base64 encoding and attachment.

              There is no standard Oracle PL/SQL interface that supports reading the file from disk to attach to the e-mail body - so the file reading part you need to do yourself.

              If the file is large, then UTL_MAIL (a poorly designed and written interface) will be unable to handle it. In which case you also need to write the base64 encode and generation of a Mime e-mail body with attachments.
              • 4. Re: Send an email with attacment via PL/SQL
                Billy~Verreynne
                HuaMin wrote:
                EXCEPTION
                WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
                raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
                END;
                Wrong.

                As I've indicated before to you, this is NOT how an exception handler should ever look - changing meaningful and unique exceptions into a meaningless error code that covers all these exceptions. It is wrong. It will always be wrong.
                • 5. Re: Send an email with attacment via PL/SQL
                  HuaMin Chen
                  Hi Billy,
                  The exception in my codes is to detect SMTP server up or not, like

                  ORA-29278: SMTP transient error: string
                  The ORA-29278 error may signify that, the same server where you have your Oracle database running, does not have an SMTP server up and running.

                  Why is it wrong?

                  Edited by: HuaMin on Apr 25, 2012 3:40 PM
                  • 6. Re: Send an email with attacment via PL/SQL
                    Billy~Verreynne
                    HuaMin wrote:

                    Why is it wrong?
                    The handler takes DIFFERENT exceptions (different unique errors with unique error codes) and turn that into a single GENERIC exception with a generic error code.

                    So where the original exceptions had a specific meaning, the new error code means that one of many errors happened.

                    Why turn meaningful unique exceptions into a single generic and meaningless exception? How does that provide the caller with more and detailed and meaningful details about what went wrong.

                    The basics of exception handling are explained in {message:id=9959102}.
                    • 7. Re: Send an email with attacment via PL/SQL
                      HuaMin Chen
                      Thanks. Do you mean better to use others exception, which is general?