Sending email from PL/SQL, with attachments

if we want to be to send email from within Oracle, to multiple recipients, and with attachments?. 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..

Begin
   send_email.send(
   ToList=>             ‘myname@myhost.com’,
   Subject=>            ‘Test email with attachments’,
   Body=>               ‘Here is the body’,
   FromEmail=>          ‘myname@myhost.com’,
   SMTPServer=>         ‘localhost’,
   AttachList=>         ‘send_email.sql,login.sql’,
   Directory=>          ‘ATTACHMENTS’);
End;
/

You can describe by issue below command.

Desc send_email;

————————————————————————————-
FUNCTION GET_MIME_TYPE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 FILENAME                       VARCHAR2                IN
PROCEDURE SEND
 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
FUNCTION SPLIT RETURNS SPLIT_TBL
 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 ATTACHMENTS 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 11.1.0.6 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)
is
begin 
  begin
    DBMS_NETWORK_ACL_myname.DROP_ACL(aacl);
     dbms_output.put_line(‘ACL dropped…..’);
  exception
    when others then
      dbms_output.put_line(‘Error dropping ACL: ‘||aacl);
      dbms_output.put_line(sqlerrm);
  end;
  begin
    DBMS_NETWORK_ACL_myname.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
    dbms_output.put_line(‘ACL created…..’);
  exception
    when others then
      dbms_output.put_line(‘Error creating ACL: ‘||aacl);
      dbms_output.put_line(sqlerrm);
  end; 
  begin
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
    dbms_output.put_line(‘ACL assigned…..’);        
  exception
    when others then
      dbms_output.put_line(‘Error assigning ACL: ‘||aacl);
      dbms_output.put_line(sqlerrm);
  end;  
  commit;
  dbms_output.put_line(‘ACL commited…..’);
end;
/
show error

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

begin
  create_acl(
    ‘adam_smtp_acl.xml’,
    ‘ACL for myname to allow SMTP outbound’,
    ‘myname’,
    TRUE,
    ‘connect’,
    ‘*’,
    25);
end;
/

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’,
      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
   is
      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);
     
   begin
      —
      — 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/vnd.ms-pkiseccat’;
      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/vnd.ms-project’;
      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/ynd.ms-pkipko’;
      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/vnd.ms-powerpoint’;
      mime_types(‘ppm’)         := ‘image/x-portable-pixmap’;
      mime_types(‘pps’)         := ‘application/vnd.ms-powerpoint’;
      mime_types(‘ppt’)         := ‘application/vnd.ms-powerpoint’;
      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/vnd.ms-pkicertstore’;
      mime_types(‘stl’)         := ‘application/vnd.ms-pkistl’;
      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/vnd.ms-works’;
      mime_types(‘wdb’)         := ‘application/vnd.ms-works’;
      mime_types(‘wks’)         := ‘application/vnd.ms-works’;
      mime_types(‘wmf’)         := ‘application/x-msmetafile’;
      mime_types(‘wps’)         := ‘application/vnd.ms-works’;
      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/vnd.ms-excel’;
      mime_types(‘xlc’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xlm’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xls’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xlt’)         := ‘application/vnd.ms-excel’;
      mime_types(‘xlw’)         := ‘application/vnd.ms-excel’;
      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);
      else
         —
         — 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
   is
      l_idx    pls_integer;
      l_list    varchar2(32767) := p_list;
      l_value    varchar2(32767);
   begin
      loop
         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));
         else
            pipe row(l_list);
            exit;
         end if;
      end loop;
      return;
   end split;

   Procedure send (
      ToList            in      varchar2,
      Subject           in      varchar2,
      Body              in      varchar2,
      FromEmail         in      varchar2 default ‘DoNotReply@Nowhere.Com’,
      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

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

    Begin

      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’ );
      WriteLine;

      — start of boundary payloads
      WriteLine( ‘–gc0p4Jq0M2Yt08jU534c0p’ );
      WriteLine( ‘Content-Type: text/plain’ );
      WriteLine;
      WriteLine(  Body );
      WriteLine;
      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||'”‘ );
               WriteLine;
               file_handle := bfilename(Directory,i.attachment);
               pos := 1;
               total := 0;
               file_len := dbms_lob.getlength(file_handle);
               dbms_lob.open(file_handle,dbms_lob.lob_readonly);

               loop
                  if pos + 57 – 1 > file_len then
                     read_bytes := file_len – pos + 1;
                     –dbms_output.put_line(‘Last read – Start: ‘||pos);
                  else
                     –dbms_output.put_line(‘Reading – Start: ‘||pos);
                     read_bytes := 57;
                  end if;
                  total := total + read_bytes;
                  dbms_lob.read(file_handle,read_bytes,pos,data);
                  utl_smtp.write_raw_data(smtp,utl_encode.base64_encode(data));
                  –utl_smtp.write_raw_data(smtp,data);
                  pos := pos + 57;
                  if pos > file_len then
                     exit;
                  end if;
               end loop;
               dbms_output.put_line(‘Length was ‘||file_len);
               dbms_lob.close(file_handle);
               if (loopcount < att_count) then
                  WriteLine;
                  WriteLine( ‘–gc0p4Jq0M2Yt08jU534c0p’ );
                else
                  WriteLine;
                  WriteLine( ‘–gc0p4Jq0M2Yt08jU534c0p–‘ );
                  dbms_output.put_line(‘Writing end boundary’);
                end if;
            else
               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 );

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

end send_email;

PL/SQL Enhancements in Oracle Database 10g

Oracle 10g includes many PL/SQL enhancements including:

  • PL/SQL Native Compilation
  • FORALL Support for Non-Consecutive Indexes (Sparse Collections)
  • New IEEE Floating-Point Types
  • Improved Overloading With Numeric Types
  • Nested Table Enhancements
  • Compile-Time Warnings
  • Quoting Mechanism for String Literals
  • Implicit Conversion Between CLOB and NCLOB
  • Regular Expressions
  • Flashback Query Functions
  • UTL_COMPRESS
  • UTL_MAIL

PL/SQL Native Compilation

The process of PL/SQL native compilation has been simplified in Oracle 10g. The compiled shared libraries are now stored in the database and extracted as necessary. This means they form part of the normal backup and recovery process, require no manual maintenance and are available in Real Application Cluster (RAC) configurations. Native compliation of the package specification and body are independant of each other, meaning either one, the other or both can be natively compiled.

The PLSQL_NATIVE_LIBRARY_DIR parameter is the only one which must be set to use native compilation. All other parameters have been obsoleted. The associated compiler commands are stored in the $ORACLE_HOME/plsql/spnc_commands file which should not need to be modified.

Native compilation is switched on and off using the PLSQL_CODE_TYPE parameter which can be set at instance and session level using the ALTER SYSTEM and ALTER SESSION commands respectively. The following is an example of native PL/SQL compilation.

-- Set the PLSQL_NATIVE_LIBRARY_DIR parameter.
CONN / AS SYSDBA
ALTER SYSTEM SET PLSQL_NATIVE_LIBRARY_DIR='/u01/app/oracle/native/' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

-- Switch on native compilation and compile a procedure.
CONN scott/tiger
ALTER SESSION SET PLSQL_CODE_TYPE='NATIVE';

CREATE OR REPLACE PROCEDURE test_speed AS
  v_number  NUMBER;
BEGIN
  FOR i IN 1 .. 10000000 LOOP
    v_number := i / 1000;
  END LOOP;
END;
/

SET TIMING ON
EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.19

-- Switch off native compilation and recompile the procedure.
ALTER SESSION SET PLSQL_CODE_TYPE='INTERPRETED';
ALTER PROCEDURE test_speed COMPILE;
EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.03

-- Clean up.
DROP PROCEDURE test_speed;

FORALL Support for Non-Consecutive Indexes (Sparse Collections)

Oracle 10g introduces support for the FORALL syntax with non-consecutive indexes in collections. The INDICES OF clause allows the FORALL syntax to be used with sparse collections, while the VALUE OF clause is used for collections of indexes pointing to other collections. The following are examples of their usage.

DECLARE
  TYPE t_tab1 IS TABLE OF tab1%ROWTYPE;
  TYPE t_tab2 IS TABLE OF BINARY_INTEGER;

  l_tab1  t_tab1 := t_tab1();
  l_tab2  t_tab2 := t_tab2();
BEGIN
  FOR i IN 1 .. 1000 LOOP
    l_tab1.extend;
    l_tab1(l_tab1.last).id := i;
    IF MOD(i, 100) = 0 THEN
      l_tab2.extend;
      l_tab2(l_tab2.last) := i;
    END IF;
  END LOOP;

  l_tab1.delete(301);
  l_tab1.delete(601);
  l_tab1.delete(901);

  -- This would fail due to sparse collection.
  --FORALL i IN l_tab.first .. l_tab.last
  --  INSERT INTO tab1 VALUES l_tab(i);

  -- This works fine with sparse collections.
  FORALL i IN INDICES OF l_tab1
    INSERT INTO tab1 VALUES l_tab1(i);

  -- This works fine for collections of indexes
  -- pointing to elements of another collection.
  FORALL i IN VALUES OF l_tab2
    INSERT INTO tab1 VALUES l_tab1(i);
END;
/

New IEEE Floating-Point Types

Oracle 10g introduces the new IEEE floating-point types BINARY_FLOAT and BINARY_DOUBLE. The types are extremely efficient for heavy floating point computations as the work is passed directly to the operating system. Literal assignments can be perfomed using the “f” and “d” suffixes or conversion functions TO_BINARY_FLOAT and TO_BINARY_DOUBLE.

DECLARE
  l_binary_float   BINARY_FLOAT;
  l_binary_double  BINARY_DOUBLE;
BEGIN
  l_binary_float  := 1.1f;
  l_binary_double := 1.00001d;

  l_binary_float  := TO_BINARY_FLOAT(1.1);
  l_binary_double := TO_BINARY_DOUBLE(1.00001);
END;
/

Rather than raise exceptions, the resulting values of computations may equate to the following constants that can be tested for.

  • [BINARY_FLOAT|BINARY_DOUBLE]_NAN
  • [BINARY_FLOAT|BINARY_DOUBLE]_INFINITY
  • [BINARY_FLOAT|BINARY_DOUBLE]_MAX_NORMAL
  • [BINARY_FLOAT|BINARY_DOUBLE]_MIN_NORMAL
  • [BINARY_FLOAT|BINARY_DOUBLE]_MAX_SUBNORMAL
  • [BINARY_FLOAT|BINARY_DOUBLE]_MIN_SUBNORMAL

The constants for NaN and infinity are also available in SQL.

Improved Overloading With Numeric Types

Oracle 10g includes improved overloading of numeric types like the following.

-- Create package specification.
CREATE OR REPLACE PACKAGE numeric_overload_test AS
  PROCEDURE go (p_number  NUMBER);
  PROCEDURE go (p_number  BINARY_FLOAT);
  PROCEDURE go (p_number  BINARY_DOUBLE);
END;
/

-- Create package body.
CREATE OR REPLACE PACKAGE BODY numeric_overload_test AS
  PROCEDURE go (p_number  NUMBER) AS
  BEGIN
    DBMS_OUTPUT.put_line('Using NUMBER');
  END;

  PROCEDURE go (p_number  BINARY_FLOAT) AS
  BEGIN
    DBMS_OUTPUT.put_line('Using BINARY_FLOAT');
  END;

  PROCEDURE go (p_number  BINARY_DOUBLE) AS
  BEGIN
    DBMS_OUTPUT.put_line('Using BINARY_DOUBLE');
  END;
END;
/

-- Test it.
SET SERVEROUTPUT ON
BEGIN
  numeric_overload_test.go(10);
  numeric_overload_test.go(10.1f);
  numeric_overload_test.go(10.1d);
END;
/

It is important to check that the correct overload is being used at all times. The appropriate suffix or conversion function will make the engine to pick the correct overload.

Nested Table Enhancements

Nested tables in PL/SQL now support more operations than before. Collections can be assigned directly to the value of another collection of the same type, or to the result of a set expression.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Yellow', 'Green');
  l_col_3 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_3 := l_col_1;
  display('Direct Assignment:', l_col_3);

  -- Expression assignments.
  l_col_3 := l_col_1 MULTISET UNION l_col_2;
  display('MULTISET UNION:', l_col_3);

  l_col_3 := l_col_1 MULTISET UNION DISTINCT l_col_2;
  display('MULTISET UNION DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT l_col_2;
  display('MULTISET INTERSECT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT DISTINCT l_col_2;
  display('MULTISET INTERSECT DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT l_col_2;
  display('MULTISET EXCEPT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT DISTINCT l_col_2;
  display('MULTISET EXCEPT DISTINCT:', l_col_3);
END;
/

Comparisons between collections have also improved with the addition of NULL checks, equality operators and set operations.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Green');
  l_col_3 t_colors;
BEGIN
  IF (l_col_3 IS NULL) AND (l_col_1 IS NOT NULL) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 IS NULL) AND (l_col_1 IS NOT NULL): TRUE');
  END IF;

  l_col_3 := l_col_1;

  IF (l_col_3 = l_col_1) AND (l_col_3 != l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 = l_col_1) AND (l_col_3 != l_col_2): TRUE');
  END IF;

  IF (SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2): TRUE');
  END IF;

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(l_col_2): ' || CARDINALITY(l_col_2));

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(SET(l_col_2)): ' || CARDINALITY(SET(l_col_2)) || ' - Duplicates removed');

  IF l_col_2 IS NOT A SET THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_2 IS NOT A SET: TRUE - Contains duplicates');
  END IF;

  IF l_col_3 IS NOT EMPTY THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_3 IS NOT EMPTY: TRUE');
  END IF;
END;
/

The SET function removes duplicate entries from your nested table, in a similar way to the SQL DISTINCT aggregate function.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_2 := l_col_1;
  display('Direct Assignment:', l_col_2);

  -- SET assignments.
  l_col_2 := SET(l_col_1);
  display('MULTISET UNION:', l_col_2);
END; 
/

Compile-Time Warnings

Oracle can now produce compile-time warnings when code is ambiguous or inefficient be setting the PLSQL_WARNINGS parameter at either instance or session level. The categories ALL, SEVERE, INFORMATIONAL and PERFORMANCE can be used to alter the type of warnings that are produced.

-- Instance and session level.
ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:PERFORMANCE';

-- Recompile with extra checking.
ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';

-- Set mutiple values.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','DISABLE:INFORMATIONAL';

-- Use the DBMS_WARNING package instead.
EXEC DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL' ,'SESSION');

The current settings associated with each object can be displayed using the [USER|DBA|ALL]_PLSQL_OBJECT_SETTINGS views.

To see a typical example of the warning output run the following code.

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE test_warnings AS
  l_dummy  VARCHAR2(10) := '1';
BEGIN
  IF 1=1 THEN
    SELECT '2'
    INTO   l_dummy
    FROM   dual;
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'l_dummy != 1!');
  END IF;
END;
/

SP2-0804: Procedure created with compilation warnings

SHOW ERRORS

LINE/COL ERROR
-------- ---------------------------
9/5      PLW-06002: Unreachable code

The errors can be queried using the %_ERRORS views.

Quoting Mechanism for String Literals

Oracle 10g allows you to define your own string delimiters to remove the need to double up any single quotes. Any character that is not present in the string can be used as the delimiter.

SET SERVEROUTPUT ON
BEGIN
  -- Orginal syntax.
  DBMS_OUTPUT.put_line('This is Tim''s string!');

  -- New syntax.
  DBMS_OUTPUT.put_line(q'#This is Tim's string!#');
  DBMS_OUTPUT.put_line(q'[This is Tim's string!]');
END;
/

This is Tim's string!
This is Tim's string!
This is Tim's string!

PL/SQL procedure successfully completed.

Implicit Conversion Between CLOB and NCLOB

Oracle 10g now supports implicit conversions between CLOBs and NCLOBs and vice-versa. As with all type conversions it is still better to be explicit and use the conversion functions TO_CLOB and TO_NCLOB for clarity.

Regular Expressions

Oracle 10g supports regular expressions in SQL and PL/SQL with the following functions:

  • REGEXP_INSTR – Similar to INSTRexcept it uses a regular expression rather than a literal as the search string.
  • REGEXP_LIKE – Similar to LIKEexcept it uses a regular expression as the search string.
  • REGEXP_REPLACE – Similar to REPLACEexcept it uses a regular expression as the search string.
  • REGEXP_SUBSTR – Returns the string matching the regular expression. Not really similar to SUBSTR.

The following examples show how these functions can be used with a simple regular expression ('[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}') for basic credit card validation.

SET SERVEROUTPUT ON
DECLARE
  l_text           VARCHAR2(100) := 'My credit card details are: 1234 1234 1234 1234';
  l_regular_expr   VARCHAR2(50)  := '[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}';

  l_credit_card_1  VARCHAR2(50)  := '1234 1234 1234 1234';
  l_credit_card_2  VARCHAR2(50)  := '123c 1234 1234 1234';
BEGIN
  -- REGEXP_INSTR
  IF REGEXP_INSTR(l_text, l_regular_expr) > 0 THEN
    DBMS_OUTPUT.put_line('REGEXP_INSTR: Your input contains credit card details, this is a security risk!');
  END IF;

  -- REGEXP_LIKE
  IF REGEXP_LIKE(l_credit_card_1, l_regular_expr) THEN
    DBMS_OUTPUT.put_line('REGEXP_LIKE: Good Credit Card: ' || l_credit_card_1);
  END IF;
  IF NOT REGEXP_LIKE(l_credit_card_2, l_regular_expr) THEN
    DBMS_OUTPUT.put_line('REGEXP_LIKE: Bad Credit Card : ' || l_credit_card_2);
  END IF;

  -- REGEXP_REPLACE
  DBMS_OUTPUT.put_line('REGEXP_REPLACE: Before: ' || l_text);
  DBMS_OUTPUT.put_line('REGEXP_REPLACE: After : ' || REGEXP_REPLACE(l_text, l_regular_expr, '**** **** **** ****'));

  -- REGEXP_SUBSTR
  DBMS_OUTPUT.put_line('REGEXP_SUBSTR: Matching String : ' || REGEXP_SUBSTR(l_text, l_regular_expr));
END;
/

Building regular expressions to match your requirements can get a little confusing and this is beyond the scope of this article.

Flashback Query Functions

The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations.

SELECT *
FROM   emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);

SELECT *
FROM   emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240);

DECLARE
  l_scn        NUMBER;
  l_timestamp  TIMESTAMP;
BEGIN
  l_scn       := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
  l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/

UTL_COMPRESS

The UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE). It uses the Lempel-Ziv compression algorithm which is equivalent to functionality of the gzip utility. A simple example of it’s use is shown below.

SET SERVEROUTPUT ON
DECLARE
  l_original_blob      BLOB;
  l_compressed_blob    BLOB;
  l_uncompressed_blob  BLOB;
BEGIN
  -- Initialize both BLOBs to something.
  l_original_blob     := TO_BLOB(UTL_RAW.CAST_TO_RAW('1234567890123456789012345678901234567890'));
  l_compressed_blob   := TO_BLOB('1');
  l_uncompressed_blob := TO_BLOB('1');

  -- Compress the data.
  UTL_COMPRESS.lz_compress (src => l_original_blob,
                            dst => l_compressed_blob);

  -- Uncompress the data.
  UTL_COMPRESS.lz_uncompress (src => l_compressed_blob,
                              dst => l_uncompressed_blob);

  -- Display lengths.
  DBMS_OUTPUT.put_line('Original Length    : ' || LENGTH(l_original_blob));
  DBMS_OUTPUT.put_line('Compressed Length  : ' || LENGTH(l_compressed_blob));
  DBMS_OUTPUT.put_line('Uncompressed Length: ' || LENGTH(l_uncompressed_blob));

  -- Free temporary BLOBs.             
  DBMS_LOB.FREETEMPORARY(l_original_blob);
  DBMS_LOB.FREETEMPORARY(l_compressed_blob);
  DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/

UTL_MAIL

The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package (shown here), but this required knowledge of the SMTP protocol.

The package is loaded by running the following scripts.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

With the configuration complete we can now send a mail.

BEGIN
  UTL_MAIL.send(sender     => 'me@domain.com',
                recipients => 'person1@domain.com,person2@domain.com',
                cc         => 'person3@domain.com',
                bcc        => 'myboss@domain.com',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
END;
/

The package also supports sending mails with RAW and VARCHAR2 attachments.