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;

Advertisements

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.

Autonomous Transactions

Autonomous Transactions

Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction, and return to the calling transaction without affecting it’s state. The autonomous transaction has no link to the calling transaction, so only commited data can be shared by both transactions.

The following types of PL/SQL blocks can be defined as autonomous transactions:

  • Stored procedures and functions.
  • Local procedures and functions defined in a PL/SQL declaration block.
  • Packaged procedures and functions.
  • Type methods.
  • Top-level anonymous blocks.

The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not commited.

CREATE TABLE at_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.

SQL>

Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.

SQL>

As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.

ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

SQL>

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.

Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.

CREATE TABLE error_logs (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;

We define a procedure to log error messages as an autonomous transaction.

CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/

The following code forces an error, which is trapped and logged.

BEGIN
  INSERT INTO at_test (id, description)
  VALUES (998, 'Description for 998');

  -- Force invalid insert.
  INSERT INTO at_test (id, description)
  VALUES (999, NULL);
EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test WHERE id >= 998;

no rows selected

SELECT * FROM error_logs;

        ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
         1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")

1 row selected.

SQL>

From this we can see that the LOG_ERRORS transaction was separate to the anonymous block. If it weren’t, we would expect the first insert in the anonymous block to be preserved by the commit statement in the LOG_ERRORS procedure.

Advantages of PL/SQL

Advantages Of PL/SQL

The following are a few important advantages of PL/SQL. Moreover most of the features listed above are also advantages of PL/SQL. 

Support for SQL

PL/SQL allows you to use SQL commands, function and operators.  PL/SQL supports data types of SQL.

PL/SQL also allows SQL statements to be constructed and executed on the fly. The process of creating SQL statements on the fly is called as Dynamic SQL. This is different from writing SQL commands at the time of writing the program, which is called as Static SQL.

Starting from Oracle8i, PL/SQL support native dynamic SQL, which makes programming Dynamic SQL easier than its predecessor, where we used DBMS_SQL package. 

Better performance

PL/SQL block is sent as one unit to Oracle server. Without PL/SQL each SQL command is to be passed to Oracle server, which will increase network traffic heavily. As a collection of SQL statements is passed as a block to Oracle server, it improves performance.

Portability

Applications written in PL/SQL are portable to any platform on which Oracle runs.  Once you write a program in PL/SQL, it can be used in any environment without any change at all.

PL/SQL block

PL/SQL programs are written as blocks.  Block allows you to group logically related statements and declarations. PL/SQL block is consisting of the following three parts:

q  Declarative part

q  Executable part

q  Exception-handling part

Declarative Part

This is the area of the block where variables, cursors etc are declared.  All variables used in the block are to be declared in declarative part.

PL/SQL Datatypes

PL/SQL provides a variety of predefined datatypes, which can be divided into four categories:

Scalar Represents a single value.
Composite Is a collection of components
Reference Is a pointer that points to another item.
LOB Holds a lob locator.

The following are the datatypes in various categories

Scalar NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN
Composite  RECORD, TABLE and VARRAY.
Reference    REF CURSOR, REF Object_type
LOB    BFILE, BLOB, CLOB, and NCLOB.

 Note: There may be minor differences between PL/SQL datatypes and SQL datatypes though they have the same name. For complete information about datatypes in PL/SQL please see PL/SQL User’s guide and Reference.

Executable part

Is the area where we write SQL and PL/SQL commands that are to be executed. This is the only mandatory part of the entire block.

Exception-handling part

Is the place where we handle exceptions (errors) that are raised in executable part.  Exception handlers handle exceptions.  We will discuss more about this in later chapter. 

Comments in PL/SQL

You can give comments in PL/SQL block in two ways.

First way is by preceding the comment with two hyphens (- -).

Example:        — this is single line comment

Second way is by starting the comment with /* and ending it with */.

Example:   /* this comment can be of multiple lines */

SELECT… INTO

SQL*Plus displays the data retrieved by SELECT command. Whereas in PL/SQL SELECT command is used only to retrieve the data and storing and using data is to be done explicitly. So Oracle provided INTO clause with SELECT command that is used to specify the variable(s) into which the value(s) retrieved must be copied.

Declaring Constants

Constant is a PL/SQL variable whose value doesn’t change. Any attempt to change the value of a constant will result in error.

variable CONSTANT datatype [precision , scale] := expression;

The following declarative statement creates a constant that takes value 500.

bonus constant  number(3) :=  500;

Nesting Blocks

It is possible to define a block within another block.  When blocks are defined one within another they are said to be nested.

Scope and visibility of variables

Scope of the variable refers to the region of the program in which the variable can be used. A variable is said to be visible when it can be referred without any qualifier. 

Available functions

Most of the functions available in SQL are also available in PL/SQL. 

The functions that are NOT available in procedural statements are:

q  DECODE

AVG, COUNT, GROUPING, MIN, MAX, SUM, STDDEV, and VARIANCE

However, these functions can be used with SQL commands and those SQL commands may be used in PL/SQL.

Database Triggers

A trigger is PL/SQL code block attached and executed by an event which occurs to a database table. Triggers are implicitly invoked by DML commands. Triggers are stored as text and compiled at execute time, because of this it is wise not to include much code in them but to call out to previously stored procedures or packages as this will greatly improve performance. You may not use COMMIT, ROLLBACK and SAVEPOINT statements within trigger blocks. Remember that triggers may be executed thousands of times for a large update – they can seriously affect SQL execution performance

Triggers may be called BEFORE or AFTER the following events :-

INSERT, UPDATE and DELETE.

Triggers may be STATEMENT or ROW types. STATEMENT triggers fire BEFORE or AFTER the execution of the statement that caused the trigger to fire. ROW triggers fire BEFORE or AFTER any affected row is processed.

Packages

A package is a set of related functions and / or routines. Packages are used to group together PL/SQL code blocks which make up a common application or are attached to a single business function. Packages consist of a specification and a body. The package specification lists the public interfaces to the blocks within the package body. The package body contains the public and private PL/SQL blocks which make up the application, private blocks are not defined in the package specification and cannot be called by any routine other than one defined within the package body. The benefits of packages are that they improve the organisation of procedure and function blocks, allow you to update the blocks that make up the package body without affecting the specification (which is the object that users have rights to) and allow you to grant execute rights once instead of for each and every block.

To create a package specification we use a variation on the CREATE command, all we need put in the specification is each PL/SQL block header that will be public within the package

Procedures and Functions

A procedure or function is a named PL/SQL block – they are normally stored in the database within package specifications (which is a wrapper for a group of named blocks) but they may be stored on the database individually. The advantage of this is that when a block is placed on the database it is parsed at the time it is stored. When it is subsequently executed Oracle already has the block compiled and it is therefore much faster. It is also a good way of grouping application functionality together and exposing only function calls (not the code itself). It is possible to invoke a stored procedure or function from most Oracle tools including SQL *Plus. It is also possible to attach a block to a database or Form trigger

Benefits of using procedures and functions include :-

  1. Improved data security and integrity.
    • Control indirect access to objects from non privileged users with security privileges.
    • Ensure that related actions are performed together, or not at all, by funnelling actions for related tables through a single path.
  2. Improved performance.
    • Avoid reparsing for multiple users by exploiting shared SQL.
    • Avoid PL/SQL parsing at run time by parsing at compile time.
    • Reduce the number of calls to the database and decrease network traffic by bundling commands.

 Improved maintenance.

    • Modify routines online without interfering with other users.
    • Modify one routine to affect multiple applications. Modify one routine to eliminate duplicate testing.

Exception (error) Handling

Exceptions are identifiers in PL/SQL that are raised during the execution of a block to terminate its action. A block is always terminated when PL/SQL raises an exception but you can define your own error handler to capture exceptions and perform some final actions before quitting the block. If PL/SQL handles the exception within the block then the exception will not propagate out to an enclosing block or environment.

There are two classes of exceptions, these are :-

Predefined – Oracle predefined errors which are associated with specific error codes.

User-defined – Declared by the user and raised when specifically requested within a block. You may associate a user-defined exception with an error code if you wish.

If an error occurs within a block PL/SQL passes control to the EXCEPTION section of the block. If no EXCEPTION section exists within the block or the EXCEPTION section doesn’t handle the error that’s occurred then the block is terminated with an unhandled exception. Exceptions propagate up through nested blocks until an exception handler is found that can handle the error. If no exception handler is found in any block the error is passed out to the host environment. Exceptions occur when either an Oracle error occurs (this automatically raises an exception) or you explicitly raise an error using the RAISE statement.

The two most common errors originating from a SELECT statement occur when it returns no rows or more than one row (remember that this is not allowed).

Explicit Cursors?

SELECT statements that occur within PL/SQL blocks are known as embedded, they must return one row and may only return one row. To get around this you can define a SELECT statement as a cursor (an area of memory), run the query and then manipulate the returned rows within the cursor. Cursors are controlled via four command statements.

Implicit Cursors

Whenever a SQL statement is issued the Database server opens an area of memory in which the command is parsed and executed. This area is called a cursor. Microsoft tends to refer to cursors as datasets throughout much of their databse product documentation

When the executable part of a PL/SQL block issues a SQL command, PL/SQL creates an implicit cursor which has the identifier SQL. PL/SQL manages this cursor for you.PL/SQL provides some attributes which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements