Sending email from PL/SQL, with multiple attachments

Want to be able to send email from within Oracle, to multiple recipients, and with attachments? Well, allrightythen. The attachments will be taken from the OS, but this could be easily modified to take them from an Oracle table of your choosing. Some of this code I gathered from other places, and a bunch of it is mine. In the end, this is the most complete PL/SQL email sending package that I know of. When we’re done, we’ll have a package that looks like below, and we’ll execute it as so..

   ToList=>             '',
   Subject=>            'Test email with attachments',
   Body=>               'Here is the body',
   FromEmail=>          '',
   FromHost=>           '',
   SMTPServer=>         'localhost',
   AttachList=>         'send_email.sql,login.sql',
   Directory=>          'ATTACHMENTS');

08:25:52 DEMO (ADAM) > Desc send_email;
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILENAME                       VARCHAR2                IN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TOLIST                         VARCHAR2                IN
 SUBJECT                        VARCHAR2                IN
 BODY                           VARCHAR2                IN
 FROMEMAIL                      VARCHAR2                IN     DEFAULT
 FROMHOST                       VARCHAR2                IN     DEFAULT
 SMTPSERVER                     VARCHAR2                IN     DEFAULT
 ATTACHLIST                     VARCHAR2                IN     DEFAULT
 DIRECTORY                      VARCHAR2                IN     DEFAULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_LIST                         VARCHAR2                IN
 P_DEL                          VARCHAR2                IN     DEFAULT

Couple of prerequisites here. First of all, if you want to send attachments from the OS, you’ll need to create an Oracle DIRECTORY object to point to the OS directory. Here’s an example.

Create or replace directory ATTACHMENTS as '/home/oracle/attachments';

You’ll then reference that directory from the send_email package.

Next, we need to allow our user to contact the email server, in this case, localhost, as I’ve got a local email server running that allows connections locally. If you’re running Oracle or greater, you’ll need to create an ACL for the user, and we’re going to use a great procedure I found for this called, you guessed it, create_acl from Metalink note# 557070.1. If you don’t add an ACL, you’ll be getting to know an error like “ORA-24247: network access denied by access control list (ACL)”

This would be run as the “SYS” user, by the way.

create or replace procedure create_acl(
  aacl       varchar2,
  acomment   varchar2,
  aprincipal varchar2,
  aisgrant   boolean,
  aprivilege varchar2,
  aserver    varchar2,
  aport      number)
     dbms_output.put_line('ACL dropped.....'); 
    when others then
      dbms_output.put_line('Error dropping ACL: '||aacl);
    dbms_output.put_line('ACL created.....'); 
    when others then
      dbms_output.put_line('Error creating ACL: '||aacl);
    dbms_output.put_line('ACL assigned.....');         
    when others then
      dbms_output.put_line('Error assigning ACL: '||aacl);
  dbms_output.put_line('ACL commited.....'); 
show error

And then it’s executed via the following. This example gives the user ADAM privileges to make an outbound connection to port 25 on any server.

    'ACL for ADAM to allow SMTP outbound',

We need to create a type to support the below package

Create or replace TYPE "SPLIT_TBL"  AS TABLE OF VARCHAR2(32767);

OK then, let’s create the send_email package!

CREATE OR REPLACE package send_email as

   Function get_mime_type
      FileName          in      varchar2
   ) return varchar2;

   Function split
      p_list            in      varchar2,
      p_del             in      varchar2 := ','
   ) return split_tbl pipelined;

   Procedure send (
      Tolist            in      varchar2,
      Subject           in      varchar2,
      Body              in      varchar2,
      FromEmail         in      varchar2 default 'DoNotReply@Nowhere.Com',
      FromHost          in      varchar2 default utl_inaddr.get_host_name,
      SmtpServer        in      varchar2 default 'localhost',
      AttachList        in      varchar2 default null,
      Directory         in      varchar2 default null

End send_email;

Show error;

CREATE OR REPLACE package body send_email as

   Function get_mime_type
      FileName          in      varchar2
   ) return varchar2
      NewFileName               varchar2(250);
      type mime_type_arr        is table of varchar2(250) index by varchar2(20);
      mime_types                mime_type_arr;
      mime_type                 varchar2(250);
      extension                 varchar2(250);
      -- Populate the mime_types array
      mime_types('323')         := 'text/h323';
      mime_types('acx')         := 'application/internet-property-stream';
      mime_types('ai')          := 'application/postscript';
      mime_types('aif')         := 'audio/x-aiff';
      mime_types('aifc')        := 'audio/x-aiff';
      mime_types('aiff')        := 'audio/x-aiff';
      mime_types('asf')         := 'video/x-ms-asf';
      mime_types('asr')         := 'video/x-ms-asf';
      mime_types('asx')         := 'video/x-ms-asf';
      mime_types('au')          := 'audio/basic';
      mime_types('avi')         := 'video/x-msvideo';
      mime_types('axs')         := 'application/olescript';
      mime_types('bas')         := 'text/plain';
      mime_types('bcpio')       := 'application/x-bcpio';
      mime_types('bin')         := 'application/octet-stream';
      mime_types('bmp')         := 'image/bmp';
      mime_types('c')           := 'text/plain';
      mime_types('cat')         := 'application/';
      mime_types('cdf')         := 'application/x-cdf';
      mime_types('cer')         := 'application/x-x509-ca-cert';
      mime_types('class')       := 'application/octet-stream';
      mime_types('clp')         := 'application/x-msclip';
      mime_types('cmx')         := 'image/x-cmx';
      mime_types('cod')         := 'image/cis-cod';
      mime_types('cpio')        := 'application/x-cpio';
      mime_types('crd')         := 'application/x-mscardfile';
      mime_types('crl')         := 'application/pkix-crl';
      mime_types('crt')         := 'application/x-x509-ca-cert';
      mime_types('csh')         := 'application/x-csh';
      mime_types('css')         := 'text/css';
      mime_types('dcr')         := 'application/x-director';
      mime_types('der')         := 'application/x-x509-ca-cert';
      mime_types('dir')         := 'application/x-director';
      mime_types('dll')         := 'application/x-msdownload';
      mime_types('dms')         := 'application/octet-stream';
      mime_types('doc')         := 'application/msword';
      mime_types('dot')         := 'application/msword';
      mime_types('dvi')         := 'application/x-dvi';
      mime_types('dxr')         := 'application/x-director';
      mime_types('eps')         := 'application/postscript';
      mime_types('etx')         := 'text/x-setext';
      mime_types('evy')         := 'application/envoy';
      mime_types('exe')         := 'application/octet-stream';
      mime_types('fif')         := 'application/fractals';
      mime_types('flr')         := 'x-world/x-vrml';
      mime_types('gif')         := 'image/gif';
      mime_types('gtar')        := 'application/x-gtar';
      mime_types('gz')          := 'application/x-gzip';
      mime_types('h')           := 'text/plain';
      mime_types('hdf')         := 'application/x-hdf';
      mime_types('hlp')         := 'application/winhlp';
      mime_types('hqx')         := 'application/mac-binhex40';
      mime_types('hta')         := 'application/hta';
      mime_types('htc')         := 'text/x-component';
      mime_types('htm')         := 'text/html';
      mime_types('html')        := 'text/html';
      mime_types('htt')         := 'text/webviewhtml';
      mime_types('ico')         := 'image/x-icon';
      mime_types('ief')         := 'image/ief';
      mime_types('iii')         := 'application/x-iphone';
      mime_types('ins')         := 'application/x-internet-signup';
      mime_types('isp')         := 'application/x-internet-signup';
      mime_types('jfif')        := 'image/pipeg';
      mime_types('jpe')         := 'image/jpeg';
      mime_types('jpeg')        := 'image/jpeg';
      mime_types('jpg')         := 'image/jpeg';
      mime_types('js')          := 'application/x-javascript';
      mime_types('latex')       := 'application/x-latex';
      mime_types('lha')         := 'application/octet-stream';
      mime_types('lsf')         := 'video/x-la-asf';
      mime_types('lsx')         := 'video/x-la-asf';
      mime_types('lzh')         := 'application/octet-stream';
      mime_types('m13')         := 'application/x-msmediaview';
      mime_types('m14')         := 'application/x-msmediaview';
      mime_types('m3u')         := 'audio/x-mpegurl';
      mime_types('man')         := 'application/x-troff-man';
      mime_types('mdb')         := 'application/x-msaccess';
      mime_types('me')          := 'application/x-troff-me';
      mime_types('mht')         := 'message/rfc822';
      mime_types('mhtml')       := 'message/rfc822';
      mime_types('mid')         := 'audio/mid';
      mime_types('mny')         := 'application/x-msmoney';
      mime_types('mov')         := 'video/quicktime';
      mime_types('movie')       := 'video/x-sgi-movie';
      mime_types('mp2')         := 'video/mpeg';
      mime_types('mp3')         := 'audio/mpeg';
      mime_types('mpa')         := 'video/mpeg';
      mime_types('mpe')         := 'video/mpeg';
      mime_types('mpeg')        := 'video/mpeg';
      mime_types('mpg')         := 'video/mpeg';
      mime_types('mpp')         := 'application/';
      mime_types('mpv2')        := 'video/mpeg';
      mime_types('ms')          := 'application/x-troff-ms';
      mime_types('mvb')         := 'application/x-msmediaview';
      mime_types('nws')         := 'message/rfc822';
      mime_types('oda')         := 'application/oda';
      mime_types('p10')         := 'application/pkcs10';
      mime_types('p12')         := 'application/x-pkcs12';
      mime_types('p7b')         := 'application/x-pkcs7-certificates';
      mime_types('p7c')         := 'application/x-pkcs7-mime';
      mime_types('p7m')         := 'application/x-pkcs7-mime';
      mime_types('p7r')         := 'application/x-pkcs7-certreqresp';
      mime_types('p7s')         := 'application/x-pkcs7-signature';
      mime_types('pbm')         := 'image/x-portable-bitmap';
      mime_types('pdf')         := 'application/pdf';
      mime_types('pfx')         := 'application/x-pkcs12';
      mime_types('pgm')         := 'image/x-portable-graymap';
      mime_types('pko')         := 'application/';
      mime_types('pma')         := 'application/x-perfmon';
      mime_types('pmc')         := 'application/x-perfmon';
      mime_types('pml')         := 'application/x-perfmon';
      mime_types('pmr')         := 'application/x-perfmon';
      mime_types('pmw')         := 'application/x-perfmon';
      mime_types('pnm')         := 'image/x-portable-anymap';
      mime_types('pot,')        := 'application/';
      mime_types('ppm')         := 'image/x-portable-pixmap';
      mime_types('pps')         := 'application/';
      mime_types('ppt')         := 'application/';
      mime_types('prf')         := 'application/pics-rules';
      mime_types('ps')          := 'application/postscript';
      mime_types('pub')         := 'application/x-mspublisher';
      mime_types('qt')          := 'video/quicktime';
      mime_types('ra')          := 'audio/x-pn-realaudio';
      mime_types('ram')         := 'audio/x-pn-realaudio';
      mime_types('ras')         := 'image/x-cmu-raster';
      mime_types('rgb')         := 'image/x-rgb';
      mime_types('rmi')         := 'audio/mid';
      mime_types('roff')        := 'application/x-troff';
      mime_types('rtf')         := 'application/rtf';
      mime_types('rtx')         := 'text/richtext';
      mime_types('scd')         := 'application/x-msschedule';
      mime_types('sct')         := 'text/scriptlet';
      mime_types('setpay')      := 'application/set-payment-initiation';
      mime_types('setreg')      := 'application/set-registration-initiation';
      mime_types('sh')          := 'application/x-sh';
      mime_types('shar')        := 'application/x-shar';
      mime_types('sit')         := 'application/x-stuffit';
      mime_types('snd')         := 'audio/basic';
      mime_types('spc')         := 'application/x-pkcs7-certificates';
      mime_types('spl')         := 'application/futuresplash';
      mime_types('src')         := 'application/x-wais-source';
      mime_types('sst')         := 'application/';
      mime_types('stl')         := 'application/';
      mime_types('stm')         := 'text/html';
      mime_types('svg')         := 'image/svg+xml';
      mime_types('sv4cpio')     := 'application/x-sv4cpio';
      mime_types('sv4crc')      := 'application/x-sv4crc';
      mime_types('swf')         := 'application/x-shockwave-flash';
      mime_types('t')           := 'application/x-troff';
      mime_types('tar')         := 'application/x-tar';
      mime_types('tcl')         := 'application/x-tcl';
      mime_types('tex')         := 'application/x-tex';
      mime_types('texi')        := 'application/x-texinfo';
      mime_types('texinfo')     := 'application/x-texinfo';
      mime_types('tgz')         := 'application/x-compressed';
      mime_types('tif')         := 'image/tiff';
      mime_types('tiff')        := 'image/tiff';
      mime_types('tr')          := 'application/x-troff';
      mime_types('trm')         := 'application/x-msterminal';
      mime_types('tsv')         := 'text/tab-separated-values';
      mime_types('txt')         := 'text/plain';
      mime_types('uls')         := 'text/iuls';
      mime_types('ustar')       := 'application/x-ustar';
      mime_types('vcf')         := 'text/x-vcard';
      mime_types('vrml')        := 'x-world/x-vrml';
      mime_types('wav')         := 'audio/x-wav';
      mime_types('wcm')         := 'application/';
      mime_types('wdb')         := 'application/';
      mime_types('wks')         := 'application/';
      mime_types('wmf')         := 'application/x-msmetafile';
      mime_types('wps')         := 'application/';
      mime_types('wri')         := 'application/x-mswrite';
      mime_types('wrl')         := 'x-world/x-vrml';
      mime_types('wrz')         := 'x-world/x-vrml';
      mime_types('xaf')         := 'x-world/x-vrml';
      mime_types('xbm')         := 'image/x-xbitmap';
      mime_types('xla')         := 'application/';
      mime_types('xlc')         := 'application/';
      mime_types('xlm')         := 'application/';
      mime_types('xls')         := 'application/';
      mime_types('xlt')         := 'application/';
      mime_types('xlw')         := 'application/';
      mime_types('xof')         := 'x-world/x-vrml';
      mime_types('xpm')         := 'image/x-xpixmap';
      mime_types('xwd')         := 'image/x-xwindowdump';
      mime_types('z')           := 'application/x-compress';
      mime_types('zip')         := 'application/zip';
      -- Determine the file extension
      dbms_output.put_line('Splitting file '||FileName||' to get extension.');
      for i in (Select column_value as extension from table(split(FileName,'.'))) loop
         extension := i.extension;
      end loop;
      -- use the extension to determine the MIME type
      if mime_types.exists(extension) then
         mime_type := mime_types(extension);
         -- Default to 'text/plain' if not found
         mime_type := 'text/plain';
      end if;

      dbms_output.put_line('Extension is '||extension);
      dbms_output.put_line('mime_type is '||mime_type);
      return mime_type;

   end get_mime_type;

   Function split
      p_list varchar2,
      p_del varchar2 := ','
   ) return split_tbl pipelined
      l_idx    pls_integer;
      l_list    varchar2(32767) := p_list;
      l_value    varchar2(32767);
         l_idx := instr(l_list,p_del);
         if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));
            pipe row(l_list);
         end if;
      end loop;
   end split;

   Procedure send (
      ToList            in      varchar2,
      Subject           in      varchar2,
      Body              in      varchar2,
      FromEmail         in      varchar2 default 'DoNotReply@Nowhere.Com',
      FromHost          in      varchar2 default utl_inaddr.get_host_name,
      SmtpServer        in      varchar2 default 'localhost',
      AttachList        in      varchar2 default null,
      Directory         in      varchar2 default null
    ) is

      smtp            utl_smtp.CONNECTION;
      reply           utl_smtp.REPLY;
      file_handle     bfile;
      file_exists     boolean;
      block_size      number;
      file_len        number;
      pos             number;
      total           number;
      read_bytes      number;
      data            raw(200);
      my_code         number;
      my_errm         varchar2(32767);
      mime_type       varchar2(50);
      myhostname      varchar2(255);
      att_table       dbms_utility.uncl_array;
      att_count       number;
      tablen          binary_integer;
      loopcount       number;

       Procedure WriteLine(
          line          in      varchar2 default null
       ) is

          utl_smtp.Write_Data( smtp, line||utl_tcp.CRLF );


      Select sys_context('USERENV','SERVER_HOST') into myhostname from dual;

      dbms_output.put_line('ToList:     '||ToList);
      dbms_output.put_line('Subject:    '||Subject);
      dbms_output.put_line('Body:       '||Body);
      dbms_output.put_line('FromEmail:  '||FromEmail);
      dbms_output.put_line('SmtpServer: '||SmtpServer);
      dbms_output.put_line('AttachList: '||AttachList);
      dbms_output.put_line('Directory:  '||Directory);
      dbms_output.put_line('ToList:     '||ToList);
      dbms_output.put_line('myhostname: '||myhostname);

      dbms_output.put_line('Opening connection to '||SmtpServer);
      smtp := utl_smtp.OPEN_CONNECTION( SmtpServer, 25 );
      reply := utl_smtp.HELO( smtp, myhostname);
      reply := utl_smtp.MAIL( smtp, FromEmail );

      -- Split up the recipient list
      for i in (Select column_value as recipient from table(split(ToList))) loop
         dbms_output.put_line('Recipient: '||i.recipient);
         reply := utl_smtp.RCPT( smtp, i.recipient);
      end loop;

      reply := utl_smtp.open_data( smtp );

      WriteLine( 'From: '||FromEmail);

      -- Split up the recipient list (again)
      for i in (Select column_value as recipient from table(split(ToList))) loop
         WriteLine( 'To: '||i.recipient);
      end loop;

      WriteLine( 'Subject: '||Subject );
      WriteLine( 'Content-Type: multipart/mixed; boundary="gc0p4Jq0M2Yt08jU534c0p"' );
      WriteLine( 'MIME-Version: 1.0' );

      -- start of boundary payloads
      WriteLine( '--gc0p4Jq0M2Yt08jU534c0p' );
      WriteLine( 'Content-Type: text/plain' );
      WriteLine(  Body );
      WriteLine( '--gc0p4Jq0M2Yt08jU534c0p' );

      dbms_output.put_line('Starting attachment segment');
      dbms_output.put_line('Directory: '||Directory);
      dbms_output.put_line('AttachList: '||AttachList);

      -- Split up the attachment list
      loopcount := 0; 
      Select count(*) into att_count from table(split(AttachList));
      if AttachList is not null and Directory is not null then
         for i in (Select ltrim(rtrim(column_value)) as attachment from table(split(AttachList))) loop
            loopcount := loopcount +1;
            dbms_output.put_line('Attaching: '||Directory||'/'||i.attachment);
            utl_file.fgetattr(Directory, i.attachment, file_exists, file_len, block_size);
            if file_exists then
               dbms_output.put_line('Getting mime_type for the attachment');
               Select get_mime_type(i.attachment) into mime_type from dual;
               WriteLine( 'Content-Type: '||mime_type );
               WriteLine( 'Content-Transfer-Encoding: base64');
               WriteLine( 'Content-Disposition: attachment; filename="'||i.attachment||'"' );
               file_handle := bfilename(Directory,i.attachment);
               pos := 1;
               total := 0;
               file_len := dbms_lob.getlength(file_handle);

                  if pos + 57 - 1 > file_len then
                     read_bytes := file_len - pos + 1;
                     --dbms_output.put_line('Last read - Start: '||pos);
                     --dbms_output.put_line('Reading - Start: '||pos);
                     read_bytes := 57;
                  end if;
                  total := total + read_bytes;
                  pos := pos + 57;
                  if pos > file_len then
                  end if;
               end loop;
               dbms_output.put_line('Length was '||file_len);
               if (loopcount < att_count) then
                  WriteLine( '--gc0p4Jq0M2Yt08jU534c0p' );
                  WriteLine( '--gc0p4Jq0M2Yt08jU534c0p--' );
                  dbms_output.put_line('Writing end boundary');
                end if;
               dbms_output.put_line('Skipping: '||Directory||'/'||i.attachment||'Does not exist.');
            end if;
         end loop;
      end if;

      reply := utl_smtp.close_data( smtp );
      reply := utl_smtp.quit( smtp );

      when others then
         my_code := sqlcode;
         my_errm := sqlerrm;
         dbms_output.put_line('Error code ' || my_code || ': ' || my_errm);

end send_email;

Once you’ve created the send_email package, and added an ACL for your user (if necessary), you should be ready to fire off an email. 🙂

58 thoughts on “Sending email from PL/SQL, with multiple attachments

  1. hi
    i am trying to send excel attachments using above code,my procedure execute successfully,but mail received with improper format
    please help

  2. Reaaly Amazing code. Excellent. it is working very fine. I tried all websites since 2 days i checked around 50 to 100 websites . every site has atleast one problem.
    Every u acheieved. Thank You very much,

  3. Works perfectly – almost!

    FromHost parameter missing from the package – other than that, no issues.

  4. excellent job!! keep going…

    A small change in the PL/SQL email sending package.
    there is a extra arguement FromHost=> ‘’, actually it is not required.

    Below code works perfectly.

    ToList=> ‘’,
    Subject=> ‘Test email with attachments’,
    Body=> ‘Here is the body’,
    FromEmail=> ‘’,
    SMTPServer=> ‘localhost’,
    AttachList=> ‘send_email.sql,login.sql’,
    Directory=> ‘ATTACHMENTS’);

    • Please follow the below order.
      1. Create or replace directory ATTACHMENTS as ‘/home/oracle/attachments’;

      2. Create or replace TYPE “SPLIT_TBL” AS TABLE OF VARCHAR2(32767);

      3. Create package send_email, Package Body send_email

      4. creat a send_mail package

      ToList=> ‘’,
      Subject=> ‘Test email with attachments’,
      Body=> ‘Here is the body’,
      FromEmail=> ‘’,
      SMTPServer=> ‘localhost’,
      AttachList=> ‘send_email.sql,login.sql’,
      Directory=> ‘ATTACHMENTS’);

  5. i used below code and this error occurs
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel

    ToList=> ‘’,
    Subject=> ‘dfdsafsdafsadjkfhsdkjfhdfj’,
    Body=> ‘Here is the body’,
    FromEmail=> ‘’,
    SMTPServer=> ‘localhost’,
    AttachList=> ‘send_email.sql,login.sql’,
    Directory=> ‘ATTACHMENTS’);

  6. can we please help me .. i am facing following error

    SQL> Begin
    2 send_email.send(
    3 ToList=> `’,
    4 Subject=> `Test email with attachments’,
    5 Body=> `Here is the body’,
    6 FromEmail=> `’,
    7 SMTPServer=> `’,
    8 AttachList=> `send_email.sql,exp_dca.log’,
    9 Directory=> `ATTACHMENTS’);
    10 End;
    11 /
    ORA-01756: quoted string not properly terminated


  7. i done following steps

    SQL> Create or replace TYPE “SPLIT_TBL” AS TABLE OF VARCHAR2(32767);
    2 /

    Type created.

    SQL> Create package send_email, Package Body send_email
    2 /

    Warning: Package created with compilation errors.


    Directory created.

    SQL> Begin
    2 send_email.send(
    3 ToList=> `’,
    4 Subject=> `Test email with attachments’,
    5 Body=> `Here is the body’,
    6 FromEmail=> `’,
    7 SMTPServer=> `’,
    8 AttachList=> `send_email.sql,exp_dca.log’,
    9 Directory=> `ATTACHMENTS’);
    10 End;
    11 /
    ORA-01756: quoted string not properly terminated


  8. this is perfect!!!!

    it is the best choice, it is running beautiful,

    but one thing need to be considered, if we have different forlders with respect to c:\Year\Months\XXX , it will be bad for creating a lot of DIRECTORY


    what is the way we can send email but without creating Directories ??

  9. It worked fine when I run from toad/sqlplus . but when I tried to invoke the package from ASP.NET it is not sending any emails but no error messages as well. Any idea ?

  10. Thank you very much for sharing! Added the attachment code to an existing email routine. Worked perfectly!

  11. Hi Adam
    Thank you so much for sharing this code.
    It works perfectly for the app I am working on.

    Muchly obliged.

  12. I am an exception “Error code -22813: ORA-22813: operand value exceeds system limits” while using the package. I trouble shooted for an extent and was able to say that i am getting exception when using split function.

    “for i in (Select column_value as recipient from table(split(ToList))) loop
    dbms_output.put_line(‘Recipient: ‘||i.recipient);
    reply := utl_smtp.RCPT( smtp, i.recipient);
    end loop;”

    When i commented out this piece of code, then the same exception has occurred at

    “for i in (Select column_value as recipient from table(split(ToList))) loop
    WriteLine( ‘To: ‘||i.recipient);
    end loop;”

    I am using the basic plsql block to call this.

    ToList=> ‘’,
    Subject=> ‘Test email with attachments’,
    Body=> ‘Here is the body’,
    AttachList=> ‘test.log’,
    Directory=> ‘DATA_PUMP_DIR’);

    Can you please help me out what is going wrong here.

  13. Thank you Adam , I googled a lot for this and you save my time..
    Its really great code worked for me. Keep it up!!!

    Bug found: if there is no attachment in mail. Procedure completes successfully, However mail is not delivered.

    By the way Great code.

  14. What is the 5th parameter in the send procedure…i cant understand…I want to attach RTF dynamically….how i supposed to get this?….Plz give me solution for this….

  15. This looks like great code, but I can’t get the body to compile…..getting a PLS-00323 subprogram or cursor ‘SEND’ is declared in Package specification and must be declared in the package body. I can’t figure out why this is….

    was thinking that the Procedure WriteLine had something to do with it, begin in the declaration area of the Proc Send, but not able to compile either way.

    Appreciate your help.

  16. The email functionality is working, but it says the file does not exist….

    My Oracle directory is OUTPUT_DIR and my file is definitely in there with all the permissions open.

    Have you seen this problem before?

    • Is it possible that the user sending the mail does not have permissions on the Oracle directory object? For example, if you’re trying to send as user PATRICK” ..

      Grant read on directory OUTPUT_DIR to PATRICK;

        • That all checks out….user has read & write privs on that directory.


  17. Just great!!!
    This is the only email pl-sql code that sends pdf’s I ever found
    Grettings from Costa Rica Adam;

  18. Working perfect , but there is no option for multiple recipient if i want to send this email to multiple users what can i do ??

    • You only have to concatenate them with a colon:
      ToList => ’,’
      This can be seen reading the code…

  19. Good material – A1 * i was struggling with attaching pdf. some of the attachment will open while some will not open.. I only attached this section…………..

    if pos + 57 – 1 > file_len then
    read_bytes := file_len – pos + 1;
    –dbms_output.put_line(‘Last read – Start: ‘||pos);
    –dbms_output.put_line(‘Reading – Start: ‘||pos);
    read_bytes := 57;
    end if;
    total := total + read_bytes;,read_bytes,pos,data);
    pos := pos + 57;
    if pos > file_len then
    end if;
    end loop;

  20. Thank your for your share , I can normal execute and no error output .
    But I didn’t receive mail and I can’t detect any information after trace the session .
    I can get mail through normal send mail function.
    Cloud you provide some idea to troubleshooting , thanks again
    end send_email;
    407 408 409 410 411 412 413 414
    Package body created.

    SQL> Begin
    ToList=> ‘danny@*.com’,
    Subject=> ‘Log parse check’,
    Body=> ‘Please see attachment file ‘,
    FromEmail=> ‘AUDIT@JDEDBUAT’,
    FromHost=> ‘JDEDBUAT’,
    2 3 4 5 6 7 8 SMTPServer=> ‘mail.*.com’,
    AttachList=> ‘check_log.html’,
    Directory=> ‘ATTACHMENTS’);
    / 9 10 11 12
    ToList: danny@*.com
    Subject: Log parse check
    Body: Please see attachment file
    SmtpServer: mail.*.com
    AttachList: check_log.html
    Directory: ATTACHMENTS
    ToList: danny@*.com
    myhostname: jdedb
    Opening connection to mail.*.com
    Recipient: danny@*.com
    Starting attachment segment
    Directory: ATTACHMENTS
    AttachList: check_log.html
    Attaching: ATTACHMENTS/check_log.html
    Getting mime_type for the attachment
    Splitting file check_log.html to get extension.
    Extension is html
    mime_type is text/html
    Length was 649311
    Writing end boundary

    PL/SQL procedure successfully completed.

    [oracle@JDEDB log]$ cat /u01/oracle/diag/rdbms/orcl/MINING/trace/MINING_ora_14639.trc
    Trace file /u01/oracle/diag/rdbms/orcl/MINING/trace/MINING_ora_14639.trc
    Oracle Database 12c Enterprise Edition Release – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    ORACLE_HOME = /u01/oracle/product/12101/dbhome_1
    System name: Linux
    Node name: JDEDB
    Release: 2.6.18-194.el5
    Version: #1 SMP Mon Mar 29 22:10:29 EDT 2010
    Machine: x86_64
    Instance name: MINING
    Redo thread mounted by this instance: 1
    Oracle process number: 100
    Unix process pid: 14639, image: oracle@JDEDB (TNS V1-V3)

    *** 2017-11-22 17:21:26.631
    *** SESSION ID:(125.51) 2017-11-22 17:21:26.631
    *** CLIENT ID:() 2017-11-22 17:21:26.631
    *** SERVICE NAME:(SYS$USERS) 2017-11-22 17:21:26.631
    *** MODULE NAME:(sqlplus@JDEDB (TNS V1-V3)) 2017-11-22 17:21:26.631
    *** ACTION NAME:() 2017-11-22 17:21:26.631

    Processing Oradebug command ‘setmypid ‘

    *** 2017-11-22 17:21:26.632
    Oradebug command ‘setmypid ‘ console output:

    *** 2017-11-22 17:21:27.471
    Processing Oradebug command ‘tracefile_name ‘

    *** 2017-11-22 17:21:27.471
    Oradebug command ‘tracefile_name ‘ console output:

    • Hi Danny,

      It seems like the procedure executed successfully, I’d start with looking at the mail server log for clues.


  21. Great code,
    But I could not generate PDF trough this code.
    At the output it is showing that ‘Writing end boundary’,but I’ not able to get the mail.

  22. Great Code,
    Mail sent successfully with multiple attachments but i also want CC & Bcc along with Tolist. Please add code as well. Thanks in advance

  23. Thanks for the code, however I’ve executed these two scripts—Create or replace TYPE “SPLIT_TBL” AS TABLE OF VARCHAR2(32767); and SQL>GRANT EXECUTE ON SPLIT_TBL to LIZ; from SYS profile but where I try to compile/run package specification from user LIZ schema I get the error —-PLS-00201: identifier ‘SPLIT_TBL’ must be declared. PL/SQL: Declaration ignored. Where could have gone wrong. Kindly assist……

  24. I’ve realized my mistake. I was to issue the command –SQL> GRANT CREATE TYPE TO PUBLIC; in sys schema then run the Create or replace TYPE “SPLIT_TBL” AS TABLE OF VARCHAR2(32767); from liz schema. Package specification executed fine after that.

  25. Thanks for a marvelous posting! I quite enjoyed reading it, you may be a great author.I will remember to bookmark your blog and definitely will come back later on. I want to encourage you continue your great work, have a nice holiday weekend!

  26. I’m really loving the theme/design of your blog. Do you ever run into any internet browser compatibility issues? A few of my blog visitors have complained about my site not working correctly in Explorer but looks great in Firefox. Do you have any suggestions to help fix this problem?

  27. Cool code. Thank you for sharing!
    Added mime_types(‘xlsx’) := ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’;
    and increased length of mime_type from 50 to 100.

  28. Hi Adam,

    When this package is executed for mail without any attachments, but it still attaches one attachment with ATT00001.TXT, can I know reason for this.


Leave a Reply

Your email address will not be published.