Skip Headers
Oracle® HTML DB User's Guide
Release 1.6

Part Number B14303-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

15 Advanced Programming Techniques

This section provides information about advanced programming techniques including establishing database links, using collections, running background SQL, utilizing Web Services and managing user preferences.

This section contains the following topics:

See Also:

"Oracle HTML DB APIs" and "Deploying an Application"

Sending E-mail from an Application

You can send an e-mail from an Oracle HTML DB application by:

Topics in this section include:

See Also:

"Configuring Oracle HTML DB to Send Mail" and "Managing E-mail" for more information on viewing the mail queue and the mail log

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.

See Also:

PL/SQL Packages and Types Reference for more information

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

See Also:

"HTMLDB_MAIL" for more information on using the HTMLDB_MAIL

Accessing Data with Database Links

Since Oracle HTML DB runs on top of an Oracle database, you have access to all distributed database capabilities. Typically, you perform distributed database operations using database links.

To create a database link:

  1. Navigate to the Workspace home page.

  2. Click SQL Workshop.

  3. Under SQL Workshop, select Create Object.

    The Create Database Object Wizard appears.

  4. Select Database Link.

  5. Follow the on-screen instructions.

    Note that Database Link names must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.

Using Collections

Collections enable you to temporarily capture one or more non-scalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user's specific session. Think of a collection as a bucket in which you can temporarily store and name rows of information.

Examples of when you might use collections include:

Topics in this section include:

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.

About Collection Naming

When you create a new collection, you must give it a name that cannot exceed 255 characters. Note that collection names are not case-sensitive and will be converted to upper case.

Once named, you can access the values in a collection by running a SQL query against the view 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.

About the Parameter p_generate_md5

Use the p_generate_md5 flag to specify if the message digest of the data of the collection member should be computed. By default, this flag is set to NO. Use this parameter to check the MD5 of the collection member (that is, compare it with another member or see if a member has changed).

See Also:

"Determining Collection Status" for more information on using the function GET_MEMBER_MD5

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 );

Deleting All Collections for the Current Application

Use the method DELETE_ALL_COLLECTIONS to delete all collections defined in the current application. For example:

HTMLDB_COLLECTION.DELETE_ALL_COLLECTIONS;

Deleting All Collections in the Current Session

Use the method DELETE_ALL_COLLECTIONS_SESSION to delete all collections defined in the current session. For example:

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);

About the Parameters p_generate_md5 and p_clob001

Use the p_generate_md5 flag to specify if the message digest of the data of the collection member should be computed. By default, this flag is set to NO. Use this parameter to check the MD5 of the collection member (that is, compare it with another member or see if a member has changed).

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

See Also:

"Determining Collection Status" for more information on using the function GET_MEMBER_MD5

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.

Table 15-1 Available Arguments for Merging Collections

Argument Description
p_c001 Array of first attribute values to be merged. Maximum length can be 4,000 characters. If the maximum length is greater, it will be truncated to 4,000 characters.

The count of elements in the P_C001 PL/SQL table is used as the total number of items across all PL/SQL tables. For example, if P_C001.count = 2 and P_C002.count = 10, only 2 members will be merged. Be aware that if P_C001 is null, an application error will be raised.

p_c0xx Attribute of XX attributes values to be merged. Maximum length can be 4,000 characters. If the maximum length is greater, it will be truncated to 4,000 characters.
p_collection_name Name of the collection.

See Also: "About Collection Naming"

p_null_index Use this argument to identify rows the merge function should ignore. This argument identifies an row as null. Null rows are automatically removed from the collection. Use p_null_index in conjunction with.
p_null_value Use this argument in conjunction with the p_null_index. Identifies the null value. If used this value cannot be null. A typical value for this argument is 0.
p_init_query Use the query defined by this argument to create a collection if the collection does not exist.

Managing Collections

You can use the following utilities to manage collections.

Obtaining a Member Count

Use COLLECTION_MEMBER_COUNT to return the total count of all members in a collection. Be aware that this count does not imply the highest sequence in the collection. For example:

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

Resequencing a Collection

Use RESEQUENCE_COLLECTION to resequence a collection to remove any gaps in sequence IDs while maintaining the same element order. For example:

HTMLDB_COLLECTION.RESEQUENCE_COLLECTION (
   p_collection_name => collection name )
   

Verifying Whether a Collection Exists

Use COLLECTION_EXISTS to determine if a collection exists. For example:

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

Adjusting Member Sequence ID

You can adjust the sequence ID of a specific member within a collection by moving the ID up or down. When you adjust a sequence ID, the specified ID is exchanged with another one. For example, if you were to move the ID 2 up, 2 would become 3 and 3 would become 2.

Use MOVE_MEMBER_UP to adjust a member sequence ID up by one. Alternately, use MOVE_MEMBER_DOWN to adjust a member sequence ID down by one. For example:

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

Be aware that while using either of these methods an application error displays:

  • If the named collection does not exist for the current user in the current session

  • If the member specified by sequence ID p_seq does not exist

However, an application error will not be returned if the specified member already has the highest or lowest sequence ID in the collection (depending on whether you are calling MOVE_MEMBER_UP or MOVE_MEMBER_DOWN).

Sorting Collection Members

Use SORT_MEMBERS to reorder members of a collection by the column number. This method not only sorts the collection by a particular column number, but it also reassigns the sequence IDs for each member to remove gaps. For example:

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

Running Background PL/SQL

You can use the HTMLDB_PLSQL_JOB package to run PL/SQL code in the background of your application. This is an effective approach for managing long running operations that do not need to complete in order for a user to continue working with your application.

Topics in this section include:

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.

Table 15-2 HTMLDB_PLSQL_JOB Package Available Functions

Function Description
SUBMIT_PROCESS Use this procedure to submit background PL/SQL. This procedure returns a unique job number. Since you can use this job number as a reference point for other procedures and functions in this package, it may be useful to store it in your own schema.
UPDATE_JOB_STATUS Call this procedure to update the status of the currently running job. This procedure is most effective when called from the submitted PL/SQL.
TIME_ELAPSED Use this function to determine how much time has elapsed since the job was submitted.
JOBS_ARE_ENABLED Call this function to determine whether or not that database is currently in a mode which supports submitting jobs to the HTMLDB_PLSQL_JOB package.
PURGE_PROCESS Call this procedure to clean up submitted jobs. Submitted jobs stay in the HTMLDB_PLSQL_JOBS view until either Oracle HTML DB cleans out those records, or you call PURGE_PROCESS to manually remove them.

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.

Table 15-3 HTMLDB_PLSQL_JOBS View Columns

Name Description
ID An unique identifier for each row.
JOB The job number assigned to each submitted PL/SQL job. The HTMLDB_PLSQL_JOB.SUBMIT_PROCESS function returns this value. This is also the value you pass into other procedures and functions in the HTMLDB_PLSQL_JOB package.
FLOW_ID The application from which this job was submitted.
OWNER The database schema that owns the application. This identifies what schema will parse this code when DBMS_JOB runs it.
ENDUSER The end user (that is, who logged into the application) that caused this process to be submitted.
CREATED The date when the job was submitted.
MODIFIED The date when the status was modified.
STATUS The user defined status for this job. Calling HTMLDB_PLSQL_JOB.UPDATE_JOB_STATUS updates this column.
SYSTEM_STATUS The system defined status for this job.
SYSTEM_MODIFIED The date when the system status was modified.
SECURITY_GROUP_ID The unique ID assigned to your workspace. Developers can only see jobs submitted from their own workspace.

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.

Implementing Web Services

Web services enable applications to interact with one another over the Web in a platform-neutral, language independent environment. In a typical Web services scenario, a business application sends a request to a service at a given URL by using the protocol over HTTP. The service receives the request, processes it, and returns a response. You can incorporate calls with external Web services in application developed in Oracle HTML DB

Web services in Oracle HTML DB are based on SOAP (the Simple Object Access Protocol). SOAP is a World Wide Web Consortium (W3C) standard protocol for sending and receiving requests and responses across the Internet. SOAP messages can be sent back and forth between a service provider and a service user in SOAP envelopes.

SOAP offers two primary advantages:

Topics in this section include:

Note:

The SOAP 1.1 specification is a W3C note. (The W3C XML Protocol Working Group has been formed to create a standard that will supersede SOAP.)

For more information on Simple Object Access Protocol (SOAP) 1.1 see:

http://www.w3.org/TR/SOAP/

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

Accessing the Web Service References Page

You manage Web service references on the Web Service References page.

To access the Web Service References page:

  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.

    The Web Service References page appears.

Specifying an Application Proxy Server Address

If your environment requires a proxy server to access the Internet, you must specify a proxy server address on the Application Attributes page before you can create a Web service reference.

To specify a proxy address for an application:

  1. Navigate to the Workspace home page.

  2. From the Applications list, select an application.

    Application Builder appears.

  3. Click Edit Attributes.

  4. Under Application Definition, enter the proxy server in Proxy Server.

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.

Creating a Web Service Reference by Searching a UDDI Registry

To create a new Web service by searching a UDDI registry:

  1. Navigate to the Web Service References page. (See "Accessing the Web Service References Page".)

  2. Click Create.

  3. When prompted whether to search a UDDI registry to find a WSDL, click Yes.

  4. For UDDI Location you can either:

    • Enter a URL endpoint to a UDDI registry.

    • Click the List icon and select a UDDI registry.

  5. Under Search for Services, specify whether to search for a business name or a service name.

    1. For Search Type, specify whether to search for a business name or a service name. You cannot search for both.

    2. In Name, enter the business name or service name to search for.

    3. Optionally indicate whether the search should be case-sensitive or an exact match. Use the percent (%) symbol as a wildcard character.

    4. Click Search.

    5. When the search results appears, make a selection and click Next.

    A summary page appears describing the selected Web service.

  6. Review your selection and click Next to continue.

    The URL to the WSDL document displays in the WSDL Location field.

  7. Click Finish.

The Web service reference is added to the Web Service References Repository.

Creating a Web Service Reference by Specifying a WSDL Document

To create a new Web service by specifying a URL to a specific WSDL document:

  1. Navigate to the Web Service References page. (See "Accessing the Web Service References Page".)

  2. Click Create.

  3. When prompted whether to search a UDDI registry to find a WSDL, click No.

  4. In WSDL Location, enter the URL to the WSDL document.

  5. Click Finish.

The Web service reference is added to the Web Service References Repository.

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 and Report Directly After Creating a Reference

To create a form and report directly after creating a Web Service Reference:

  1. Create the Web service reference. (See "Creating a Web Service Reference".)

  2. Once the Web service references has been added, select Create Form and Report on Web Service.

  3. For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).

  4. For Identify Page and Region Attributes, review the page and region attributes. If the page you specify does not exist, the wizard creates the page for you.

  5. For Items for Input Parameters:

    1. Identify which items to add to the form. To include an item, select Yes in the Create column. Otherwise, select No.

    2. If necessary, edit the item label.

  6. For Base Node:

    1. In Temporary Result Set Name, enter a name for the collection that stores the Web service result.

    2. For Result Tree to Report On, select the portion of the resulting XML document that contains the information you want to include in the report.

  7. For Result Parameters to Display, select the parameters to be included in the report.

  8. Click Finish.

Creating a Form and Report by Adding a New Page

If you have an existing Web service reference, you can create an input form and report by adding a new page.

To create a form and report by adding a new page:

  1. Create the Web service reference. (See "Creating a Web Service Reference".)

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

    In the Create Page Wizard:

    1. Select Page with Component.

    2. For Select Component Type, select Form.

    3. For Create Page, select Form and Report on Web Service.

  3. For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).

  4. For Identify Page and Region Attributes, review the page and region attributes. If the page you specify does not exist, the wizard creates the page for you.

  5. For Items for Input Parameters:

    1. Identify which items to add to the form. To include an item, select Yes in the Create column. Otherwise, select No.

    2. If necessary, edit the item label.

  6. For Base Node:

    1. In Temporary Result Set Name, enter a name for the collection that stores the Web service result.

    2. In Result Tree to Report On, select the portion of the resulting XML document that contains the information you want to include in the report.

  7. For Result Parameters to Display, select the parameters to be included in the report.

  8. Click Finish.

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.

Creating a Form Directly After Creating a Reference

To create a form directly after creating a Web Service Reference:

  1. Create the Web service reference. (See "Creating a Web Service Reference".)

  2. Once the Web service references has been added, select Create Form on Web Service.

  3. For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).

  4. For Identify Page and Region Attributes, review the page and region attributes. If the page you specify does not exist, the wizard creates the page for you.

  5. For Items for Input Parameters:

    1. Identify which items to add. To include an item, select Yes in the Create column. Otherwise, select No.

    2. If necessary, edit the item label.

  6. For Items for Output Parameters:

    1. Identify which items need to be added. To include an item, select Yes in the Create column. Otherwise, select No.

    2. If necessary, edit the item label.

  7. Click Finish.

Creating a Form by Adding a New Page

If you have an existing Web service reference, you can create form by adding a new page.

To create a form by adding a new page:

  1. Create the Web service reference. (See "Creating a Web Service Reference".)

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

    In the Create Page Wizard:

    1. Select Page with Component.

    2. For Select Component Type, select Form.

    3. For Create Page, select Form on Web Service.

  3. For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).

  4. For Identify Page and Region Attributes, review the page and region attributes. If the page you specify does not exist, the wizard creates the page for you.

  5. For Items for Input Parameters:

    1. Identify which items need to be added. To include an item, select Yes in the Create column. Otherwise, select No.

    2. If necessary, edit the item label.

  6. For Items for Output Parameters:

    1. Identify which items need to be added. To include an item, select Yes in the Create column. Otherwise, select No.

    2. If necessary, edit the item label.

  7. Click Finish.

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.

Displaying Web Service Results in a Report

To create a report in which to display Web Service request results:

  1. Navigate to the Page Definition.

  2. Under Regions, click the Create icon.

    The Create Region Wizard appears.

  3. For the region type, select Report.

  4. For the report implementation, select Report on collection containing Web service result.On Identify Region Attributes, enter a region title and optionally edit the region attributes.

  5. For Web Service Reference and Operation, select a Web service reference and an operation (that is, the method to be executed).

  6. For Result Tree to Report On, select the portion of the resulting XML document that contains the information you want to include in the report.

  7. For Result Parameters:

    1. In Temporary Result Set Name, enter a name for the collection that stores the Web service result.

    2. Select and deselect the appropriate parameters.

  8. Click Create SQL Report.

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.

Managing User Preferences

You can use preferences to set the session state for a specific user. Once set, these preferences can only be removed by an Oracle HTML DB administrator. You can set user preferences by creating a page process, by the calculation of a preference Item Source Value, or programatically using the PL/SQL API.

Topics in this section include:

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.

See Also:

"Managing Session State and User Preferences" for more information on using the Session State Management page

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

To set user preference values by creating 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 one of the following:

    • Set Preference to value of item

    • Set Preference to value of item if item is not NULL

  5. Specify the preference value in the field provided using the format:

    PreferenceName:Item
    
    
  6. Click Page Items to see a list of available items.

  7. Follow the on-screen instructions

Setting the Source of an Item Based on a User Preference

You can set the source of an item based on a user preference by defining the item source type as Preference.

To define the source of item based on a user preference:

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

  2. Under Item, click the Create icon.

    The Create Page Computation Wizard appears.

  3. Specify the Item Name and Display Position Attributes and click Next.

  4. Specify the Item Attributes click Next.

  5. From the Item Source list, select Preferences.

  6. In Item Source Value, enter the name of the preference.

  7. Follow the on-screen instructions

Setting User Preferences Programatically

To set or reference user preferences programatically, you must use a PL/SQL API. User level caching is available programmatically. You can use the set_preference function to set a user level preference called NAMED_PREFERENCE. For example:

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

You can reference the value of a user preference using the function GET_PREFERENCES. For example:

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

In the previous example, the preference would default to the value 15 if the preference contained no value.

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.

See Also:

"Managing Session State and User Preferences" for more information on using the Session State Management page

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