How to send pl/sql procedure status through an email?

Question

I have scheduled a PL/SQL procedure. I want to send the status of the PL/SQL procedure (whether it is successful or has any error messages) to my email address.

I saw some ways of sending pre-defined templates of emails using UTL_MAIL. But how can I get the status of my procedure into an email?

Answer 1

Send an e-mail at the end of the scheduled stored procedure, e.g.

create or replace procedure p_your_proc as
  l_error varchar2(300);
begin
  -- do some processing

  -- if there were no errors
  utl_mail.send(sender     => '[email protected]',
                recipients => '[email protected]',
                cc         => null,
                bcc        => null,
                subject    => 'Procedure P_YOUR_PROC completed successfully',
                message    => null);

exception
  when others then
  l_error := sqlerrm;
  utl_mail.send(sender     => '[email protected]',
                recipients => '[email protected]',
                cc         => null,
                bcc        => null,
                subject    => 'Procedure P_YOUR_PROC ended with an error',
                message    => l_error);
  raise;
end;