4 Replies Latest reply: Nov 18, 2009 11:49 PM by Billy~Verreynne RSS

    How to send email with attachments from ORACLE DIRECTORY using pl/sql

    734944
      Hi,

      I have created a Oracle directory "dir_temp"
      create or replace directory dir_temp as 'c:\temp';

      I am able to create a csv file in this directory as "test.scv" using utl_file and some select statements.

      Now I need to send this csv file as an attachment in email.
      we have smtp installed on our server.

      Can any one help on how to acheive this?

      Thanks
        • 1. Re: How to send email with attachments from ORACLE DIRECTORY using pl/sql
          Hoek
          Hi,

          You can use UTL_MAIL for that.
          http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_mail.htm#BABDEAJF
          (don't forget to run the necessary scripts first)
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:494228200346768899
          • 2. Re: How to send email with attachments from ORACLE DIRECTORY using pl/sql
            734944
            Hi..

            Your answer is helpful in understanding the utl_mail. I am even able to send emails using utl_mail.
            But my requirement is , i should be able to send an email with attachments by just passing the
            file name and the oracle directory name. In my example these are 'test.csv' and 'dir_temp'
            Any one used such procedure before?

            thanks
            • 3. Re: How to send email with attachments from ORACLE DIRECTORY using pl/sql
              Hoek
              Hi,
              But my requirement is , i should be able to send an email with attachments by just passing the file name and the oracle directory name.
              Here's another example:
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12257624552691#tom22915468740800
              Just change the pl/sql block to a storef procedure, and adjust as you wish.

              One thing though:
              Since UTL_MAIL is restricted to 32k, you might need the javamail api:
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255615160805
              Try to ctrl+f useful examples over there.
              This link might be of use as well:
              http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html
              • 4. Re: How to send email with attachments from ORACLE DIRECTORY using pl/sql
                Billy~Verreynne
                user12225636 wrote:

                I have created a Oracle directory "dir_temp"
                create or replace directory dir_temp as 'c:\temp';

                I am able to create a csv file in this directory as "test.scv" using utl_file and some select statements.

                Now I need to send this csv file as an attachment in email.
                we have smtp installed on our server.
                You do not need a directory object. You do not need a csv file an an attachment. Nor do you need to install a SMTP server.

                UTL_MAIL in Oracle is a SMTP client. You do not need to install any SMTP software on the Oracle server to make the client part work. What you do need is access to your network/company's mail server. This means that mail server must accept client connections from your Oracle server. The firewalls in place must allow tcp traffic on port 25 through from the Oracle server to the mail server.

                As for attachments. Attachments are not files. Attachments are part and parcel of the e-mail itself. An e-mail is a MIME (Multipurpose Internet Mail Extensions) body. It contains a header part that includes the subject, Mime type, date, recipient and sender data and so on.

                After the header, there's a single black line, and then the message body. The body can be in several parts. Where a single part can be an attachment part.

                UTL_MAIL creates a MIME body for you. It allows you to specify:
                - the header (the subject, recipient, sender, etc)
                - the text body
                - a single attachment part

                Example:
                SQL> declare
                  2          CR      constant varchar2(2) := chr(10);
                  3  begin
                  4          SYS.UTL_MAIL.send_attach_varchar2(
                  5                  sender => 'noreply@oracle-server.domain',
                  6                  recipients => 'scott@tiger.com',
                  7                  subject => 'Sample Mail from UTL_MAIL',
                  8                  mime_type => 'text/plain',                      -- we're sending a plain text e-mail
                  9                  message => 'This is the e-mail message part that the mail reader will display.',
                 10                  att_mime_type => 'text/csv',                    -- we're adding a csv text attachment
                 11                  att_filename => 'sample.csv',                   -- with this as the file name
                 12                  -- and here is the attachment's contents/data:
                 13                  attachment => 'col1,col2,col3'||CR||'col1,col2,col3'||CR||'col1,col2,col3'
                 14          );
                 15  end;
                 16  /
                
                PL/SQL procedure successfully completed.
                Note: modify CR as applicable to your platform (e.g. requiring carriage return and linefeed or only one of these).


                The following MIME body is created and delivered via SMTP:
                From: noreply@oracle-server.domain
                To: scott@tiger.com
                Subject: =?ISO-8859-1?Q?Sample=20Mail=20from=20UTL=5FMAIL?=
                X-Priority: 3
                Content-Type: multipart/mixed; boundary="------------4D8C24=_23F7E4A13B2357B3"
                Return-Path: noreply@oracle-server.domain
                Date: 19 Nov 2009 07:45:15 +0200
                Mime-Version: 1.0
                
                This is a multi-part message in MIME format.
                --------------4D8C24=_23F7E4A13B2357B3
                Content-Type: text/plain
                Content-Transfer-Encoding: 7bit
                
                This is the e-mail message part that the mail reader will display.
                
                
                --------------4D8C24=_23F7E4A13B2357B3
                Content-Type: text/csv; name="sample.csv"
                Content-Transfer-Encoding: quoted-printable
                Content-Disposition: inline; filename="sample.csv"
                
                col1,col2,col3
                col1,col2,col3
                col1,col2,col3
                --------------4D8C24=_23F7E4A13B2357B3--
                As you can see, the CSV file is not a file - it is part of the MIME body and no physical file was required to be attached. That is a requirement for a mail reader like Outlook - that is not a requirement for using the SMTP protocol or constructing a MIME body.