How-To Document

After completing this How-To, you should be able to:

• send mail in Oracle HTML DB
• build a simple workflow into Oracle HTML DB applications

Introduction

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.

Software Requirements

• Oracle HTML DB
• Netscape 7.0 or Internet Explorer 5.5 or later Web browser

Add a Customer in the Sample Application

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.

Create the Confirmation E-mail Process

The next step is to add a process to the Confirm Order page of the Enter New Order Wizard that sends a confirmation e-mail to the customer. You do this by creating a new PL/SQL process on page 206 of Sample Application to call the SEND procedure of the HTMLDB_MAIL package to send a confirmation e-mail to the customer when the Confirm Order button is clicked.

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:
   1. For the Name, enter Send Confirmation.
   2. In the Sequence field, enter 20. This will ensure that the process executes after the Place the Order process.
   3. From the Point list, select On Submit - After Computations and Validations.
   4. From the Type list, select PL/SQL anonymous block.
   5. As shown in figure 1, enter the following (or copy and paste) when prompted for the PL/SQL page process:
      Copy

      
                                         
      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;
      
      

   6. In the Success Message field, enter:

      Confirmation sent.

   7. In the Failure Message field, enter:

      Error sending confirmation.

   8. 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.
   9. Click Create Process.

Figure 1: Process to Send Confirmation

Create the Notification E-mail Process

The next step is to send an e-mail notification to the order fulfiller indicating that a new order is ready to be processed. This e-mail should include a link so the user can view the new order. Since the e-mail includes a link, the body of the email will also be passed using the parameter P_BODY_HTML. To test the process, replace the e-mail addresses in the process below with your e-mail address.

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:
   1. For the Name, enter Send Notification.
   2. In the Sequence field, enter 30.
   3. From the Point list, select On Submit - After Computations and Validations.
   4. From the Type list, select PL/SQL anonymous block.
   5. Enter the following (or copy and paste) when prompted for the PL/SQL page process:
      Copy

      
      
      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;
      
      

   6. In the Success Message field, enter:

      Notification sent.

   7. In the Failure Message field, enter:

      Error sending notification.

   8. 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.
   9. Click Create Process.

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