Wednesday, May 28, 2014

How to send mail with attach using Oracle pl/sql

In a Data Warehouse is very important to use e-mail to report the results of the processing and any  errors.  Oracle provides a package UTL_MAIL (from 10g) for this task.
                              
This package, however, can not be used immediately, but must be verified a number of conditions.        
Because I love to "see" the solutions in a graphical format, you can download the solution from www.slideshare.net
 

Inside it you can see, in a single slide, the solution and all the "objects" involved.                  
After making the controls in the slide, you can try the solution immediately in this way.

               
1) Copy and paste the following script in a file, for example, OAiO1.sql    


spool OAiO1

grant connect,resource,dba to &&1 identified by &&2;

grant read,write on directory DATA_PUMP_DIR to &&1;

begin
   dbms_network_acl_admin.drop_acl(
   acl => '&&3');
   commit;
end;
/
begin   
   dbms_network_acl_admin.create_acl (
   acl         => '&&3',
   description => 'Allow mail to be send',
   principal   => '&&1',
   is_grant    => TRUE,
   privilege   => 'connect');
   commit;
end;
/
begin   
   dbms_network_acl_admin.assign_acl(
   acl  => '&&3',
   host => '&&4');
   commit;
end;
/
begin  
   dbms_network_acl_admin.add_privilege (
   acl       => '&&3',
   principal => '&&1',
   is_grant  => TRUE,
   privilege => 'resolve'
   );
   commit;
   end;
/
connect &&1/&&2

create or replace procedure p_email(
   p_sender varchar2
   ,p_recipients varchar2
   ,p_subject varchar2
   ,p_message varchar2
   ,p_dir varchar2 default null
   ,p_file varchar2 default null) is
   v_fh utl_file.file_type;
   v_rfile raw(32767);
   v_flen number;
   v_bsize number;
   v_ex boolean;
begin
   if (p_dir is null) then
      utl_mail.send(
      sender => p_sender
      ,recipients => p_recipients
      ,subject => p_subject
      ,message => p_message
      );
   else
      utl_file.fgetattr(p_dir, p_file, v_ex, v_flen, v_bsize);
      v_fh := utl_file.fopen(p_dir, p_file, 'r');
      utl_file.get_raw(v_fh,v_rfile, v_flen);
      utl_file.fclose(v_fh);
      utl_mail.send_attach_raw(
      sender => p_sender
      ,recipients => p_recipients
      ,subject => p_subject
      ,message => p_message
      ,attachment => v_rfile
      ,att_inline => FALSE
      ,att_filename => p_file
      );
   end if;
end;
/

exec p_email('&&7','&&6','OAiO1_Test','Hello world from user &&1', 'DATA_PUMP_DIR', 'dp.log');

spool off


2) The script could create a new test user. For this example we will use the "historical" user SCOTT.
3) Connect to SQL from command prompt with sqlplus / as sysdba
4) Run the command passing your personal setting:


@OAiO1 <&&1> <&&2> <&&3> <&&4> "<&&5>" "<&&6>" <&&7>

where:
&&1 = Oracle User that send the email (uppercase, e.g. SCOTT)
&&2 = Oracle User password (e.g. tiger)
&&3 = An ACL name of your choice (e.g. scott_mail.xml)
&&4 = Email Server of your Company (e.g.  Acme.ita.com)
&&5 = Oracle Home path (e.g. E:\o\s\product\11.2.0\dbhome_1)
&&6 = Email receiver (e.g. massimo_cenci@yahoo.it)
&&7 = User sender (e.g. DWH_dba)


It is all.