Sending e-mail within an Oracle HTML DB application is accomplished by using the supplied PL/SQL package HTMLDB_MAIL. To send mail you create a PL/SQL process on a page that calls the HTMLDB_MAIL package.

This document describes how to create a process in an application to send e-mail. It also describes how to extend the demonstration application, Sample Application, to add simple workflow by sending an e-mail confirmation to the orderer and a notification e-mail to the order fulfiller.

E-mail is not sent immediately from Oracle HTML DB but rather is stored in a temporary queue until a DBMS_JOB pushes the queue. The DBMS_JOB utilizes two preferences named SMTP_HOST_ADDRESS and SMTP_HOST_PORT to send mail in the queue. By default, these preferences are set to localhost and 25. If the server where Oracle HTML DB is installed is not configured for SMTP services, you will need to change the SMTP_HOST_ADDRESS preference. Check with your Oracle HTML DB administrator to make sure the instance you are using is properly configured to send e-mail.

First you need to add yourself as a customer in the demonstration application, Sample Application. Be sure you include your valid e-mail address so you will know whether the procedure you create later works properly.

To add yourself as a customer in Sample Application:

1. Navigate to the Application Builder.
2. From the Available Applications list, select the Sample Application and click Go.
3. Click Run.
4. Click Customers tab.
5. Under Tasks, click Enter New Customer.
6. Fill in the form with at least your first name, last name and valid e-mail address.
7. Click Create.

The following is a description of the SEND procedure of the HTMLDB_MAIL package.

PROCEDURE SEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TO                           VARCHAR2                IN
 P_FROM                         VARCHAR2                IN
 P_BODY                         VARCHAR2                IN
 P_BODY_HTML                    VARCHAR2                IN     DEFAULT
 P_SUBJ                         VARCHAR2                IN     DEFAULT
 P_CC                           VARCHAR2                IN     DEFAULT
 P_BCC                          VARCHAR2                IN     DEFAULT

To create a PL/SQL procedure to send a confirmation e-mail:

1. Navigate to the Page Definition of page 206.
2. In the Page Processing area under Processes, click Create.
3. In the Create Process Wizard:
   • For the Name, enter Send Confirmation.
   • In the Sequence field, enter 20. This will ensure that the process executes after the Place the Order process.
   • From the Point list, select On Submit - After Computations and Validations.
   • From the Type list, select PL/SQL anonymous block.
   • As shown in figure 1, enter the following (or copy and paste) when prompted for the PL/SQL page process:
     

     for c1 in (select cust_email
                  from demo_customers
                 where customer_id = :P201_CUSTOMER_ID) loop
       if c1.cust_email is not null then
         HTMLDB_MAIL.SEND(
           P_TO       => c1.cust_email,
           P_FROM     => c1.cust_email,
           P_BODY     => 'Your order has been received and '||chr(10)||
             'will be processed shortly.',
           P_SUBJ     => 'Order Number '||:P201_ORDER_ID||' received.');
       end if;
     end loop;
     

   • In the Success Message field, enter:
     Confirmation sent.
   • In the Failure Message field, enter:
     Error sending confirmation.
   • From the When Button Pressed list, select P206_CONFIRM. This is the Confirm Order button that the Place Order process is conditional upon. You use the same condition for this Send Confirmation process.
   • Click Create Process.

Figure 1: Process to Send Confirmation

To create a PL/SQL procedure to send a notification e-mail to the order fulfiller:

1. Navigate to the Page Definition of page 206.
2. In the Page Processing area under Processes, click Create.
3. In the Create Process Wizard:
   • For the Name, enter Send Notification.
   • In the Sequence field, enter 30.
   • From the Point list, select On Submit - After Computations and Validations.
   • From the Type list, select PL/SQL anonymous block.
   • Enter the following (or copy and paste) when prompted for the PL/SQL page process:
     

     declare
       l_body_html varchar2(4000);
     begin
       l_body_html := '<p>A new order, number '||:P201_ORDER_ID||' has '||
         'been placed.  To review and fulfill this order, click the '||
         'link below.</p><p><a href="http://'||OWA_UTIL.GET_CGI_ENV('HTTP_HOST')||
         OWA_UTIL.GET_CGI_ENV('SCRIPT_NAME')||'/f?p='||:APP_ID||':200:::::'||
         'P200_SEARCH:'||:P201_ORDER_ID||'">View Order '||:P201_ORDER_ID||
         '</a></p>';
       HTMLDB_MAIL.SEND(
         P_TO        => 'you@yourcompany.com',
         P_FROM      => 'you@yourcompany.com',
         P_BODY      => l_body_html,
         P_BODY_HTML => l_body_html,
         P_SUBJ      => 'New Order Number '||:P201_ORDER_ID);
     end;
     

   • In the Success Message field, enter:
     Notification sent.
   • In the Failure Message field, enter:
     Error sending notification.
   • From the When Button Pressed list, select P206_CONFIRM. This is the Confirm Order button that the Place the Order process is conditional upon. You use the same condition for this Send Notification process.
   • Click Create Process.

Run the Sample Application and enter a new order. You should now receive both the confirmation and the notification e-mails.