15 Advanced Programming Techniques

Sending E-mail Using a Background Job

Oracle HTML DB stores unsent e-mail messages in a table named HTMLDB_MAIL_QUEUE. A DBMS_JOB background process is automatically created when you install Oracle HTML DB. This background process pushes the mail queue every 15 minutes. The package that is executed by the background process has two parameters:

• p_smtp_host is the hostname of your SMTP gateway. The default value is localhost.

• p_smtp_portno is the port number of your SMTP gatway. The default value is 25.

The most efficient approach to sending e-mail is to create a background job (using a DBMS_JOB package) to periodically send all mail messages stored in the active mail queue.

Sending E-mail Manually by Calling HTMLDB_MAIL

You can send an e-mail from an Oracle HTML DB application by calling a PL/SQL package called HTMLDB_MAIL. This package is built on top of the Oracle supplied UTL_SMTP package. Because of this dependence, in order to use HTMLDB_MAIL, the UTL_SMTP package must be installed and functioning.

HTMLDB_MAIL contains two procedures for manually sending e-mail:

• Use the HTMLDB_MAIL.SEND procedure to manually send an outbound e-mail message from your application

• Use HTMLDB_MAIL.PUSH_QUEUE to deliver mail messages stored in HTMLDB_MAIL_QUEUE

Oracle HTML DB stores unsent e-mail messages in a table named HTMLDB_MAIL_QUEUE. You can deliver mail messages stored in this queue to the specified SMTP gateway by calling the procedure HTMLDB_MAIL.PUSH_QUEUE. This procedure requires two input parameters:

• p_smtp_hostname defines the hostname of your SMTP gateway

• p_smtp_portno defines port number of your SMTP gateway (for example, 25)

Oracle HTML DB logs successfully submitted messages in the table HTMLDB_MAIL_LOG with the timestamp reflecting your server's local time.

The following example demonstrates the use of the HTMLDB_MAIL.PUSH_QUEUE procedure using a shell script. This example only applies to UNIX/LINUX installations. In this example, the SMTP gateway hostname is defined as smtp01.oracle.com and the SMTP gateway port number is 25.

SQLPLUS / <<EOF
FLOWS_010600.HTMLDB_MAIL.PUSH_QUEUE('smtp01.oracle.com','25');
DISCONNECT
EXIT
EOF

About the HTMLDB_COLLECTION API

Every collection contains a named list of data elements (or members) which can have up to 50 attributes (or columns). You insert, update, and delete collection information using the PL/SQL API HTMLDB_COLLECTION.

Creating a Collection

Every collection contains a named list of data elements (or members) which can have up to 50 attributes (or columns). Use the following methods to create a collection:

• CREATE_COLLECTION

• CREATE_OR_TRUNCATE_COLLECTION

• CREATE_COLLECTION_FROM_QUERY

CREATE_COLLECTION raises an exception if the named collection already exists. For example:

HTMLDB_COLLECTION.CREATE_COLLECTION(
    p_collection_name => collection name );

CREATE_OR_TRUNCATE_COLLECTION creates a new collection if the named collection does not exist. If the named collection already exists, this method truncates it. Truncating a collection empties it, but leaves it in place. For example:

HTMLDB_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name => collection name );
    p_generate_md5    => YES or NO );

CREATE_COLLECTION_FROM_QUERY creates a collection and then populates it with the results of a specified query. For example:

HTMLDB_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
    p_collection_name => collection name,
    p_query           => your query );
    p_generate_md5    => YES or NO );

CREATE_COLLECTION_FROM_QUERY_B also creates a collection and then populates it with the results of a specified query. For example:

HTMLDB_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
    p_collection_name => collection name,
    p_query           => your query );
   

CREATE_COLLECTION_FROM_QUERY_B offers significantly faster performance than CREATE_COLLECTION_FROM_QUERY by performing bulk SQL operations, but has the following limitations:

• No column value in the select list of the query can be more than 2,000 bytes. If a row is encountered that has a column value of more than 2,000 bytes, an error will be raised during execution.

• The MD5 checksum will not be computed for any members in the collection.

Truncating a Collection

Truncating a collection removes all members from the specified collection, but leaves the named collection in place. For example:

HTMLDB_COLLECTION.TRUNCATE_COLLECTION(
    p_collection_name => collection name );

Deleting a Collection

Deleting a collection deletes the collection and all of its members. Be aware that if you do not delete a collection, it will eventually be deleted when the session is purged. For example:

HTMLDB_COLLECTION.DELETE_COLLECTION (
    p_collection_name => collection name );

HTMLDB_COLLECTION.DELETE_ALL_COLLECTIONS;

HTMLDB_COLLECTION.DELETE_ALL_COLLECTIONS_SESSION;

Adding Members to a Collection

When data elements (or members) are added to a collection, they are assigned a unique sequence ID. As you add members to a collection, the sequence ID will change in increments of 1 with the newest members having the largest ID.

You add new member to a collection using the function ADD_MEMBER. Calling this method returns the sequence ID of the newly added member. The following example demonstrates how to use the procedure ADD_MEMBER.

HTMLDB_COLLECTION.ADD_MEMBER(
    p_collection_name => collection name,
    p_c001          => [member attribute 1],
    p_c002          => [member attribute 2],
    p_c003          => [member attribute 3],
    p_c004          => [member attribute 4],
    p_c005          => [member attribute 5],
    p_c006          => [member attribute 6],
    p_c007          => [member attribute 7],
  ...
    p_c050          => [member attribute 50]);
    p_clob001       => [CLOB member attribute 1],    p_generate_md5  => YES or NO);

The next example demonstrates how to use the function ADD_MEMBER. This function returns the sequence number assigned to the newly created member.

l_id := HTMLDB_COLLECTION.ADD_MEMBER(
    p_collection_name => collection name,
    p_c001          => [member attribute 1],
    p_c002          => [member attribute 2],
    p_c003          => [member attribute 3],
    p_c004          => [member attribute 4],
    p_c005          => [member attribute 5],
    p_c006          => [member attribute 6],
    p_c007          => [member attribute 7],
  ...
    p_c050          => [member attribute 50]);
    p_clob001       => [CLOB member attribute 1],    p_generate_md5  => YES or NO);

You can also add new members (or an array of members) to a collection using the method ADD_MEMBERS. This method raises an exception if the specified collection does not exist with the specified name of the current user and in the same session. Also any attribute exceeding 4,000 characters will be truncated to 4,000 characters. The number of members added is based on the number of elements in the first array. For example:

HTMLDB_COLLECTION.ADD_MEMBERS(
    p_collection_name => collection name,
    p_c001          => member attribute array 1,
    p_c002          => member attribute array 2,
    p_c003          => member attribute array 3,
    p_c004          => member attribute array 4,
    p_c005          => member attribute array 5,
    p_c006          => member attribute array 6,
    p_c007          => member attribute array 7,
    ...
    p_c050          => member attribute array 50);
    p_generate_md5  => YES or NO);

Updating Collection Members

You can update collection members by calling UPDATE_MEMBER and referencing the desired collection member by its sequence ID. This procedure replaces an entire collection member, not individual member attributes. This procedure raises an exception if the named collection does not exist. For example:

HTMLDB_COLLECTION.UPDATE_MEMBER (
    p_collection_name => collection name,
    p_seq             => member sequence number,
    p_c001            => member attribute 1,
    p_c002            => member attribute 2,
    p_c003            => member attribute 3,
    p_c004            => member attribute 4,
    p_c005            => member attribute 5,
    p_c006            => member attribute 6,
    p_c007            => member attribute 7,
    ...
    p_c050            => member attribute 50);
    p_clob001       => [CLOB member attribute 1],  

Use p_clob001 for collection member attributes which exceed 4,000 characters.

If you wish to update a single attribute of a collection member, use UPDATE_MEMBER_ATTRIBUTE. Calling this procedure raises an exception if the named collection does not exist. For example:

HTMLDB_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
    p_collection_name => collection name,
    p_seq             => member sequence number,
    p_c001            => member attribute 1,
    p_c002            => member attribute 2,
    p_c003            => member attribute 3,
    p_c004            => member attribute 4,
    p_c005            => member attribute 5,
    p_c006            => member attribute 6,
    p_c007            => member attribute 7,
    ...
    p_c050            => member attribute 50);
    p_clob_number     => number of CLOB attribute to be updated, <-- Only valid value for now is 1    p_clob_value      => new CLOB attribute value);

Deleting a Collection Member

You can delete a collection member by calling DELETE_MEMBER and referencing the desired collection member by its sequence ID. For example:

HTMLDB_COLLECTION.DELETE_MEMBER(
    p_collection_name => collection name,
    p_seq             => member sequence number);

Be aware that this procedure leaves a gap in the sequence IDs in the specified collection. Also, calling this procedure results in an error if the named collection does not exist.

You can also delete all members from a collection by when an attribute matches a specific value. For example:

HTMLDB_COLLECTION.DELETE_MEMBERS(
    p_collection_name => collection name,
    p_attr_number     => number of attribute used to match for the specified
                         attribute value for deletion, 
    p_attr_value      => attribute value of the member attribute used to 
                         match for deletion);

Be aware that this procedure also leaves a gap in the sequence IDs in the specified collection. Also, this procedure raises an exception if:

• The named collection does not exist

• The specified attribute number is outside the range of 1 to 50, or is in invalid

If the supplied attribute value is null, then all members of the named collection will deleted.

Determining Collection Status

The p_generate_md5 parameter determines whether the MD5 message digests are computed for each member of a collection. The collection status flag is set to FALSE immediately after you create a collection. If any operations are performed on the collection (such as add, update, truncate, and so on), this flag is set to TRUE.

You can reset this flag manually by calling RESET_COLLECTION_CHANGED. For example:

HTMLDB_COLLECTION.RESET_COLLECTION_CHANGED (
    p_collection_name => collection name)

Once this flag has been reset, you can determine if a collection has changed by calling COLLECTION_HAS_CHANGED. For example:

l_changed := HTMLDB_COLLECTION.COLLECTION_HAS_CHANGED(
  p_collection_name => collection_name);

When you add a new member to a collection, an MD5 message digest is computed against all 50 attributes and the CLOB attribute if the p_generated_md5 parameter is set to YES. You can access this value from the MD5_ORIGINAL column of the view HTMLDB_COLLECTION using the function GET_MEMBER_MD5. For example:

HTMLDB_COLLECTION.GET_MEMBER_MD5 (
    p_collection_name => collection name,
    p_seq             => member sequence number );
    RETURN VARCHAR2;

Merging Collections

You can merge members of collection with values passed in a set of arrays. By using the argument p_init_query, you can create a collection from the supplied query. For example:

HTMLDB_COLLECTION.MERGE_MEMBERS
p_collection_name => collection_name

Be aware, however, that if the collection exists, the following occurs:

• Rows in the collection (not in the arrays) will be deleted

• Rows in the collection and in the arrays will be updated

• Rows in the array and not in the collection will be inserted

Any attribute value exceeding 4,000 characters will be truncated to 4,000 characters. Table 15-1 describes the available arguments you can use when merging collections.

Managing Collections

You can use the following utilities to manage collections.

l_count := HTMLDB_COLLECTION.COLLECTION_MEMBER_COUNT (
  p_collection_name => collection name );

HTMLDB_COLLECTION.RESEQUENCE_COLLECTION (
   p_collection_name => collection name )
   

l_exists := HTMLDB_COLLECTION.COLLECTION_EXISTS  (
  p_collection_name => collection name );

HTMLDB_COLLECTION.MOVE_MEMBER_DOWN(
    p_collection_name => collection name,
    p_seq             => member sequence number);

HTMLDB_COLLECTION.SORT_MEMBERS(
    p_collection_name       => collection name,
    p_sort_on_column_number => column number to sort by);

Clearing Collection Session State

By clearing the session state of a collection, you remove the collection members. A shopping cart is a good example of when you might need to clear collection session state. When a user requests to empty his or her cart and start again, you would need to clear the session state for a collection. You can remove session state of a collection by calling the CREATE_OR_TRUNCATE_COLLECTION method or by using f?p syntax.

Calling CREATE_OR_TRUNCATE_COLLECTION deletes the existing collection and then recreates it. For example:

HTMLDB_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name       => collection name,

You can also use the sixth f?p syntax argument to clear session state. For example:

f?p=App:Page:Session::NO:1,2,3,collection name

Understanding the HTMLDB_PLSQL_JOB Package

HTMLDB_PLSQL_JOB is a wrapper package around DBMS_JOB functionality offered in the Oracle database. Be aware that the HTMLDB_PLSQL_JOB package only exposes that functionality which is necessary to run PL/SQL in the background. The following is a description of the HTMLDB_PLSQL_JOB package.

SQL> DESC HTMLDB_PLSQL_JOB
FUNCTION JOBS_ARE_ENABLED RETURNS BOOLEAN
PROCEDURE PURGE_PROCESS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
FUNCTION SUBMIT_PROCESS RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SQL                          VARCHAR2                IN
 P_WHEN                         VARCHAR2                IN     DEFAULT
 P_STATUS                       VARCHAR2                IN     DEFAULT
FUNCTION TIME_ELAPSED RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
PROCEDURE UPDATE_JOB_STATUS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_JOB                          NUMBER                  IN
 P_STATUS                       VARCHAR2                IN
 P_DESC                         

Table 15-1 describes the functions available in the HTMLDB_PLSQL_JOB package.

You can view all jobs submitted to the HTMLDB_PLSQL_JOB package using the HTMLDB_PLSQL_JOBS view. The following is the description of HTMLDB_PLSQL_JOBS view.

SQL> DESCRIBE HTMLDB_PLSQL_JOBS
 Name                              Null?    Type
 --------------------------------- -------- ----------------------------
 ID                                         NUMBER
 JOB                                        NUMBER
 FLOW_ID                                    NUMBER
 OWNER                                      VARCHAR2(30)
 ENDUSER                                    VARCHAR2(30)
 CREATED                                    DATE
 MODIFIED                                   DATE
 STATUS                                     VARCHAR2(100)
 SYSTEM_STATUS                              VARCHAR2(4000)
 SYSTEM_MODIFIED                            DATE
 SECURITY_GROUP_ID                          NUMBER

Table 15-3 describes the columns available in HTMLDB_PLSQL_JOBS view.

About System Status Updates

Submitted jobs can contain any of the following system status settings:

• SUBMITTED. Indicates the job has been submitted, but has not yet started. DBMS_JOB does not guarantee immediate starting of jobs.

• IN PROGRESS. Indicates that DBMS_JOB has started the process.

• COMPLETED. Indicates the job has finished.

• BROKEN (sqlcode) sqlerrm. Indicates there was a problem in your job that resulted in an exception. The SQL code and SQL Error Message for the exception should be included in the system status. Review this information to determine what went wrong.

Using a Process to Implement Background PL/SQL

The simplest way to implement the HTMLDB_PLSQL_JOB package is to create a page process that specifies the process type PLSQL DBMS JOB. By selecting this process type, Application Builder will submit the PL/SQL code you specify as a job. Since you are not calling the function directly, you can use the built-in substitution item APP_JOB to determine the job number of any jobs you submit.

The following example runs a PL/SQL job in the background for testing and explanation.

001  BEGIN
002    FOR i IN 1 .. 100 LOOP
003      INSERT INTO emp(a,b) VALUES (:APP_JOB,i);
004      IF MOD(i,10) = 0 THEN
005        HTMLDB_PLSQL_JOB.UPDATE_JOB_STATUS(
006          P_JOB     => :APP_JOB,
007          P_STATUS  => i || 'rows inserted');
008      END IF;
009      HTMLDB_UTIL.PAUSE(2);
010    END LOOP;
011  END;

In this example, note that:

• Lines 002 to 010 run a loop that inserts 100 records into the emp table.

• APP_JOB is referenced as a bind variable inside the VALUES clause of the INSERT, and specified as the P_JOB parameter value in the call to UPDATE_JOB_STATUS.

• APP_JOB represents the job number which will be assigned to this process as it is submitted to HTMLDB_PLSQL_JOB. By specifying this reserved item inside your process code, it will be replaced for you at execution time with the actual job number.

• Notice this example calls to UPDATE_JOB_STATUS every ten records, INSIDE the block of code. Normally, Oracle transaction rules dictate updates made inside code blocks will not be seen until the entire transaction is committed. The HTMLDB_PLSQL_JOB.UPDATE_JOB_STATUS procedure, however, has been implemented in such a way that the update will happen regardless of whether or not the job succeeds or fails. This last point is important for two reasons:

  1. Even if your status reads "100 rows inserted," it does not mean the entire operation was successful. If an exception occurred at the time the block of code tried to commit, the user_status column of HTMLDB_PLSQL_JOBS would not be affected since status updates are committed separately.

  2. These updates are performed autonomously. You can view the job status before the job has completed. This gives you the ability to display status text about ongoing operations in the background as they are happening.

Understanding Web Service References

To utilize Web services in Oracle HTML DB, you create a Web service reference using a wizard. Each Web service reference is based on a Web Services Description Language (WSDL) document that describes the target Web service. When you create a Web service reference, the wizard analyzes the WSDL and collects all the necessary information to create a valid SOAP message, including:

• The URL used to post the SOAP request over HTTP

• An Uniform Resource Identifier (URI) identifying the SOAP HTTP request

• Input parameters for each operation

• Output parameters for each operation

Creating a Web Service Reference

When you create a Web service reference you need to decide how to locate the WSDL. You can locate a WSDL in two ways:

• By searching a UDDI registry

• by entering the URL to the WSDL document

A Universal Description, Discovery, and Integration (UDDI) registry is a directory where businesses register their Web services.

Using the Web Service Reference Repository

Web service references are stored in the Web Service Reference Repository.

To access the Web Service References Repository:

1. Navigate to the Workspace home page.

2. From the Applications list, select an application.

   Application Builder appears.

3. Click Shared Components.

   The Shared Components page appears.

4. Under Logic, select Web Service References.

From the Web Service References Repository you can:

• Edit a reference by clicking the Edit icon.

• Test a reference by clicking the Run icon.

• View details about a reference, by clicking the reference name.

Testing a Web Service Reference

Once you have created a Web service reference you can test it on the Test Web Service Reference.

To test a Web service reference:

1. Navigate to the Web Service Repository. (See "Using the Web Service Reference Repository".)

2. Click Run adjacent to the Web Service reference name.

   The Test Web Service Reference page appears. The Web service name and URL endpoint display at the top of the page.

3. From Operation, select an operation (that is, the method to be executed).

4. Under Input Parameters, enter the appropriate value.

5. Click Test.

   The message request and response appear at the bottom of the page.

Creating an Input Form and Report on a Web Service

The Create Form and Report on Web Service Wizard creates an input form, a submit button, and a report for displaying results. You can execute this wizard directly after creating the Web service reference, or by adding a new page.

Use this wizard when you expect a non-scalar result from the Web service. The Amazon Web service is a good example. This Web service returns many results based on the search criteria entered in an input form.

Creating a Form on a Web Service

The Create Form on Web Service wizard creates a form and a submit button. You can execute this wizard directly after creating the Web service reference, or from the Page Definition.

Use this wizard when you expect a scalar result from the Web service. A Web service that looks up a stock price is a good example since the input is a stock symbol and the output is the scalar value price.

Invoking a Web Service as a Process

You can also implement a Web Service as a process on the page. Running the process submits the request to the service provider. You can then display the request results in report.

To invoke a Web Service as a process:

1. Create a new page. (See "Adding Additional Pages".)

   In the Create Page Wizard:

   1. Select Blank Page.

   2. When prompted whether to use tabs, select No.

2. Navigate to the Page Definition. (See "Viewing a Page Definition".)

3. Under Page Rendering, Processes, click the Create icon.

   The Create Page Processes Wizard appears.

4. From the process category, select Web Services.

5. Specify a process name, sequence, and processing point.

6. Select the Web service reference and operation (that is, the method to be executed).

7. Define the process. You can store the results in a collection or in items on the page by selecting options under Web Service Output Parameters.

   1. To store the results in a collection:

      • For Store Result in, select Collection.

      • Enter a name for the collection in the value field.

   2. To store the results in items on the page:

      • For Store Result in, select Items.

      • Enter the appropriate items value in the fields provided.

8. Click Create Process.

Editing a Web Service Process

Once you create a process of type Web service, you can map input parameters to a static value (for example to pass a key) by editing the Web service process.

To edit a Web service process:

1. Create a Web service process. (See "Invoking a Web Service as a Process".)

2. Navigate to the Page Definition containing the Web service process.

3. Select the process name.

   The Edit Page Process page appears.

4. Scroll down to Web Service Input Parameters.

5. To map an input parameter to a static value:

   1. Scroll down to Web Service Input Parameters.

   2. Enter a value in the Value field adjacent to the appropriate parameter name.

6. Click Apply Changes.

Viewing a Web Service Reference History

The Web Services History displays changes to Web service references for the current application by application ID, Web service references name, developer, and date.

To view a history of Web service reference changes:

1. Navigate to the Workspace home page.

2. From the Applications list, select an application.

   Application Builder appears.

3. Click Shared Components.

   The Shared Components page appears.

4. Under Logic, select Web Service References.

5. Click History.

Viewing User Preferences

You view user preferences for a specific user on the Session State Management page.

To view user preferences for a specific user:

1. From the Oracle HTML DB home page select the Administration tab.

2. Under Administration Services, click Manage Users and then Session State.

   The Session State Management page appears.

3. Click Report preferences for users.

4. Type a username in the field provided and click Go.

Setting User Preferences

You can set user preferences within your application through the creation of a page process, by creating a preference item, or programatically.

Topics in this section include:

• Setting User Preferences Using a Page Process

• Setting the Source of an Item Based on a User Preference

• Setting User Preferences Programatically

HTMLDB_UTIL.SET_PREFERENCE(
 p_preference=>'NAMED_PREFERENCE',
 p_value =>:ITEM_NAME);

NVL(HTMLDB_UTIL.GET_PREFERENCE('NAMED_PREFERENCE'),15)

Resetting User Preferences Manually

You can manually purge user preferences for a specific user.

To manually purge preferences for a specific user:

1. From the Oracle HTML DB home page select Administration tab.

2. Under Administration Services, click Manage Users and then Session State.

   The Session State Management page appears.

3. Click Purge preferences for a selected user.

4. Specify a user and follow the on-screen instructions.

Resetting Preferences Using a Page Process

You can reset user preferences by creating a page process and selecting the process type Reset Preferences.

To reset user preferences using a page process:

1. Navigate to the appropriate Page Definition. (See "Viewing a Page Definition".)

2. Under Page Processes, click the Create icon.

   The Create Page Computation Wizard appears.

3. Specify a process name, sequence, and processing point.

4. From Type, select Reset Preferences.

5. Follow the on-screen instructions