create or replace procedure Send_Error_Mail (
  p_order_ref in integer,

  p_msg in varchar2 )

  -- This is a crude implementation. A more realistic implementation
  -- should cater for the situation where the mail cannot be sent
  -- esp 'cos the mail server is down. In this case, since the email

  -- text is generated, it would be sufficient to move the logic
  -- for sending the email into a procedure which is submitted periodically
  -- (eg from Dbms_Job) and which detects the need to send the email from the
  -- customer_orders table. If succesful it would record that in the table.
  -- Else, a subsequent submision should succeed.
  -- This is a special case of a more general "store and forward" approach

  -- to sending email.

is
  conn utl_smtp.connection;

  v_msg varchar2(32767) := '
    <b>Possible causes...</b><ul>

    <li>Node in URL doesn''t exist</li>
    <li>Node in URL exists but HTTP listener is down</li>
    <li>HTTP listener is up at existing node but URL isn''t found (eg URL misspelled or program invalid)</li>
    <li>Run time exception from vendor site (eg non-unique order_ref, customer_id sumbitted)</li>
    <li>Vendor site returned message in unexpected syntax</li>
    </ul><hr><pre>';
begin
  v_msg := v_msg || p_msg || '</pre>';


  conn := Demo_Mail.Begin_Mail(
    --
    -- Pls take special care to change this - for obvious reasons !
    --
    sender     => '<bryn.llewellyn@oracle.com>',
    recipients => '<bryn.llewellyn@oracle.com>',
    subject    => 'Error Sending Order #' || To_Char ( p_order_ref ),
    mime_type  => 'text/html');


  Demo_Mail.Write_Text(
    conn    => conn,
    message => v_msg );
  
  Demo_Mail.End_Mail( conn => conn );
end Send_Error_Mail;
/
Show Errors