Want to know a way to send email when the Standard concurrent program completes, give warning or error out

Question

I'm seeking a solution for integrating email notifications into a standard concurrent program(Auto Invoice Master program) in Oracle E-Business Suite. Specifically, I want receive an email upon completion, indicating whether it succeeded, encountered an error, or produced a warning.

I'd appreciate any insights, code snippets, or pointers on how to achieve this functionality efficiently.

I tried creating an Alert but its not working.

Answer 1

I wouldn't know if anything at the application layer of Oracle E-Business Suite would provide this functionality, but as the back-end is an Oracle database if you are a programmer and can find the data you need in the base tables you have various options available to you.

1. Write a shell script initiated every minute by crontab that logs into the database, checks for completions, then uses your standard sendmail/mailx, etc. program to send the email. Advantage: mail program already ready to use. Disadvantage: you have to have access to a host, maintain code outside a database, keep your database credentials current, and incur a logon (audit trail entry) every minute.

2. Write a PL/SQL procedure scheduled by the dbms_scheduler engine to run every minute that checks for completions and uses utl_smtp to send an email. Advantage: credentials are not a worry, no login required, no need to access a host or have code outside the database. Disadvantage: you have to put your own mail program together.

3. Probably a fair amount of other ideas.

If you are interested in #2, you may adapt to your purposes code you can pull off the internet for sending email. I use this:

CREATE OR REPLACE PROCEDURE SENDMAIL_HTML(sender_name IN varchar2 :=NULL,
                                             sender_email IN varchar2,
                                             recipient_name IN varchar2 := NULL,
                                             recipient_email IN varchar2,
                                             subject_in IN varchar2 := NULL,
                                             message_in IN clob,
                                             attachment_name_1 IN varchar2 := NULL,
                                             attachment_mime_1 IN varchar2 := NULL,
                                             attachment_clob_1 IN clob := NULL,
                                             attachment_name_2 IN varchar2 := NULL,
                                             attachment_mime_2 IN varchar2 := NULL,
                                             attachment_clob_2 IN clob := NULL,
                                             attachment_name_3 IN varchar2 := NULL,
                                             attachment_mime_3 IN varchar2 := NULL,
                                             attachment_clob_3 IN clob := NULL,
                                             attachment_name_4 IN varchar2 := NULL,
                                             attachment_mime_4 IN varchar2 := NULL,
                                             attachment_clob_4 IN clob := NULL)
AS
  mail_host varchar2(30) := 'localhost';
  mail_connection utl_smtp.connection;
  message clob;
  message_piece varchar2(2000);
  boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
  step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573
  var_start_pos integer;
  
  PROCEDURE pl_add_attachment(attachment_name IN varchar2,
                              attachment_mime IN varchar2,
                              attachment_clob IN clob)
  AS
    var_unencoded_raw raw(32767); 
    var_encoded_raw raw(32767);
    var_encoded_string varchar2(32767);
  BEGIN
    utl_smtp.write_data(mail_connection, '--' || boundary || utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, 'Content-Type: ' || attachment_mime || '; name="' || attachment_name || '"' || utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, 'Content-Transfer-Encoding: base64' || utl_tcp.CRLF);    
    utl_smtp.write_data(mail_connection, 'Content-Disposition: attachment; filename="' || attachment_name || '"' || utl_tcp.CRLF || utl_tcp.CRLF);
 
    FOR i IN 0 .. TRUNC((dbms_lob.getlength(attachment_clob) - 1 )/step)
    LOOP
      var_unencoded_raw := utl_raw.cast_to_raw(dbms_lob.substr(attachment_clob, step, i * step + 1));
      var_encoded_raw := utl_encode.base64_encode(var_unencoded_raw);
      var_encoded_string := utl_raw.cast_to_varchar2(var_encoded_raw);
        
      utl_smtp.write_data(mail_connection, var_encoded_string);
    END LOOP;

    utl_smtp.write_data(mail_connection, utl_tcp.CRLF || utl_tcp.CRLF);    
  END pl_add_attachment;
BEGIN
  message := message_in;

  mail_connection := utl_smtp.open_connection(mail_host, 25);
  utl_smtp.helo(mail_connection, mail_host);
  utl_smtp.mail(mail_connection, sender_email);
  utl_smtp.rcpt(mail_connection, recipient_email);
  utl_smtp.open_data(mail_connection);
  utl_smtp.write_data(mail_connection,'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||utl_tcp.CRLF);
  utl_smtp.write_data(mail_connection,'From: '||NVL(sender_name,sender_email)||' <'||sender_email||'>'||utl_tcp.CRLF);
  utl_smtp.write_data(mail_connection,'To: '||NVL(recipient_name,recipient_email)||' <'||recipient_email||'>'||utl_tcp.CRLF);
  utl_smtp.write_data(mail_connection,'Subject: '||NVL(subject_in,' ')|| utl_tcp.CRLF);
  utl_smtp.write_data(mail_connection, 'MIME-Version: 1.0' || utl_tcp.CRLF);
  utl_smtp.write_data(mail_connection, 'Content-Type: multipart/mixed; boundary="' || boundary || '"' || utl_tcp.CRLF || utl_tcp.CRLF);

  IF dbms_lob.getlength(message) > 0
  THEN
    utl_smtp.write_data(mail_connection, '--' || boundary || utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, 'Content-Type: text/html; charset=us-ascii ' || utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, 'Content-Disposition: inline' || utl_tcp.CRLF || utl_tcp.CRLF);    
    utl_smtp.write_data(mail_connection, '<html>'|| utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, '<head>'|| utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, '<title>'||NVL(subject_in,' ')||'</title>'|| utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, '</head>'|| utl_tcp.CRLF); 
    utl_smtp.write_data(mail_connection, '<body>'|| utl_tcp.CRLF); 
    utl_smtp.write_data(mail_connection, '<pre style="font: monospace">'|| utl_tcp.CRLF); 
    

    FOR chunk IN 1..CEIL(dbms_lob.getlength(message)/1000)
    LOOP
      var_start_pos := 1+(chunk-1)*1000;
      utl_smtp.write_data(mail_connection,dbms_lob.substr(message,1000,var_start_pos));      
    END LOOP;
    
    utl_smtp.write_data(mail_connection, '</pre>'|| utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, '</body>'|| utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, '</html>'|| utl_tcp.CRLF);
    
    utl_smtp.write_data(mail_connection,utl_tcp.CRLF||utl_tcp.CRLF);   
    
    utl_smtp.write_data(mail_connection, '--' || boundary || utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, 'Content-Type: text/plain; charset="iso-8859-1"; format=flowed' || utl_tcp.CRLF);
    utl_smtp.write_data(mail_connection, 'Content-Disposition: inline' || utl_tcp.CRLF || utl_tcp.CRLF);

    FOR chunk IN 1..CEIL(dbms_lob.getlength(message)/1000)
    LOOP
      var_start_pos := 1+(chunk-1)*1000;
      utl_smtp.write_data(mail_connection,dbms_lob.substr(message,1000,var_start_pos));      
    END LOOP;
    
    utl_smtp.write_data(mail_connection,utl_tcp.CRLF||utl_tcp.CRLF);    
  END IF;
  
  IF LENGTH(attachment_clob_1) > 0
  THEN
    pl_add_attachment(attachment_name => attachment_name_1,
                      attachment_mime => attachment_mime_1,
                      attachment_clob => attachment_clob_1);
  END IF;
  
  IF LENGTH(attachment_clob_2) > 0
  THEN
    pl_add_attachment(attachment_name => attachment_name_2,
                      attachment_mime => attachment_mime_2,
                      attachment_clob => attachment_clob_2);
  END IF;

  IF LENGTH(attachment_clob_3) > 0
  THEN
    pl_add_attachment(attachment_name => attachment_name_3,
                      attachment_mime => attachment_mime_3,
                      attachment_clob => attachment_clob_3);
  END IF;

  IF LENGTH(attachment_clob_4) > 0
  THEN
    pl_add_attachment(attachment_name => attachment_name_4,
                      attachment_mime => attachment_mime_4,
                      attachment_clob => attachment_clob_4);
  END IF;
    
  utl_smtp.write_data(mail_connection, '--' || boundary || '--' || utl_tcp.crlf);  
  utl_smtp.close_data(mail_connection);
  utl_smtp.quit(mail_connection);
END;
/

I'm sure I got that off someone else years ago and adapted it further. This version supports HTML formatting and up to 9 attachments (which I typically use for sending CSV files to be opened in Excel, but it supports binary attachments too).

You also need to work with your DBA to be given network ACL rights to resolve and connect to localhost, though I usually just make it * (any host). The older way to do that was:

BEGIN
  SYS.DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('netacl.xml','Allow usage to the UTL network packages', 'MYUSER', TRUE, 'connect');
  SYS.DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'MYUSER', TRUE, 'resolve');
  SYS.DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('netacl.xml','*');
END;
/

But I think there's a newer interface now.

Once the procedure is compiled and the network ACL in place, you simply call the mail procedure from your scheduled procedure:

BEGIN
  sendmail_html(sender_name => 'My name',
                sender_email => 'myemail@myhost.mycompany.com',
                recipient_email => 'somebody@mycompany.com',
                subject_in => 'An interesting email',
                message_in => 'Blah, blah <b>blah</b> blah...',
                attachment_name_1 => 'attachment1.xls',
                attachment_mime_1 => 'text/plain',
                attachment_clob_1 => 'this,is,a,test',
                attachment_name_2 => 'attachment2.xls',
                attachment_mime_2 => 'text/plain',
                attachment_clob_2 => 'this,is,another,test');
END;