Oracle® Database Express Edition Application Express User's Guide Release 2.1 Part Number B25309-01 |
|
|
View PDF |
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:
Because the Database Home Page runs on top of an Oracle database, you have access to all distributed Oracle database capabilities. Typically, you perform distributed database operations using database links.
A database link is a schema object in one database that enables you to access objects on another database. Once you have created the database link you can access the remote objects by appending @dblink
to the table or view name where dblink
is the Database Link Name you specify in the Create Database Object Wizard.
To create a database link:
On the Database Home Page, click the Object Browser icon.
Object Browser appears.
Click Create.
Select Database Link and click Next.
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.
To view an existing a database link:
On the Database Home Page, click the Object Browser icon.
Object Browser appears.
Select the object type Database Links at the top of the page.
See Also:
"Managing Database Objects with Object Browser" and "Database Links" in Oracle Database Administrator's GuideCollections enable you to temporarily capture one or more nonscalar 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. You can think of a collection as a bucket in which you temporarily store and name rows of information.
The following are examples of when you might use collections:
When you are creating a data-entry wizard in which multiple rows of information first need to be collected within a logical transaction. You can use collections to temporarily store the contents of the multiple rows of information, before performing the final step in the wizard when both the physical and logical transactions are completed.
When your application includes an update page on which a user updates multiple detail rows on one page. The user can make many updates, apply these updates to a collection and then call a final process to apply the changes to the database.
When you are building a wizard where you are collecting an arbitrary number of attributes. At the end of the wizard, the user then performs a task that takes the information temporarily stored in the collection and applies it to the database.
Topics in this section include:
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
.
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 uppercase.
Once the collection is named, you can access the values in the collection by running a SQL query against the view HTMLDB_COLLECTIONS
.
See Also:
"Accessing a Collection"Every collection contains a named list of data elements (or members) that can have up to 50 attributes (or columns). You use the following methods to create a collection:
CREATE_COLLECTION
CREATE_OR_TRUNCATE_COLLECTION
CREATE_COLLECTION_FROM_QUERY
HTMLDB_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B
The CREATE_COLLECTION
method raises an exception if the named collection already exists, for example:
HTMLDB_COLLECTION.CREATE_COLLECTION( p_collection_name => collection name );
The CREATE_OR_TRUNCATE_COLLECTION
method 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 );
The CREATE_COLLECTION_FROM_QUERY
method 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 );
The CREATE_COLLECTION_FROM_QUERY_B
method 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 );
The CREATE_COLLECTION_FROM_QUERY_B
method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY
method 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.
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).
If you truncate a collection, you remove all members from the specified collection, but the named collection remains in place, for example:
HTMLDB_COLLECTION.TRUNCATE_COLLECTION( p_collection_name => collection name );
You can access the members of a collection by querying the database view HTMLDB_COLLECTIONS
. The HTMLDB_COLLECTIONS
view has the following definition:
COLLECTION_NAME NOT NULL VARCHAR2(255) SEQ_ID NOT NULL NUMBER C001 VARCHAR2(4000) C002 VARCHAR2(4000) C003 VARCHAR2(4000) C004 VARCHAR2(4000) C005 VARCHAR2(4000) ... C050 VARCHAR2(4000) CLOB001 CLOB MD5_ORIGINAL VARCHAR2(4000)
Use the HTMLDB_COLLECTIONS
view in an application just as you would use any other table or view in an application, for example:
SELECT c001, c002, c003 FROM htmldb_collections WHERE collection_name = 'FIREARMS'
If you delete a collection, you delete the collection and all of its members, for example:
HTMLDB_COLLECTION.DELETE_COLLECTION ( p_collection_name => collection name );
Be aware that if you do not delete a collection, it will eventually be deleted when the session is purged. For example:
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 members to a collection using the ADD_MEMBER function
. Calling this function returns the sequence ID of the newly added member. The following example demonstrates how to use the ADD_MEMBER
function.
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 ADD_MEMBERS
method, 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);
This method raises an error 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.
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.
You can update collection members by calling the UPDATE_MEMBER
procedure and referencing the desired collection member by its sequence ID, 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],
The UPDATE_MEMBER
procedure replaces an entire collection member, not individual member attributes. This procedure causes an error if the named collection does not exist. For example:
Use the p_clob001
parameter for collection member attributes which exceed 4,000 characters.
If you want to update a single attribute of a collection member, use the UPDATE_MEMBER_ATTRIBUTE procedure
, for example:
HTMLDB_COLLECTION.UPDATE_MEMBER_ATTRIBUTE( p_collection_name => collection_name, p_seq => member sequence number, p_attr_number => member attribute number, p_attr_value => member attribute value )
HTMLDB_COLLECTION.UPDATE_MEMBER_ATTRIBUTE( p_collection_name => collection_name, p_seq => member sequence number, p_clob_number => CLOB member attribute number, p_clob_value => CLOB member attribute value );
Calling the UPDATE_MEMBER_ATTRIBUTE
procedure causes an error if the named collection does not exist.
Note that the only valid value for the p_clob_number
parameter is 1.
You can delete a collection member by calling the DELETE_MEMBER
procedure 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);
Note that this procedure leaves a gap in the sequence IDs in the specified collection. In addition, calling this procedure causes 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);
Note that the DELETE_MEMBER
procedure also leaves a gap in the sequence IDs in the specified collection. This procedure causes an error if:
The named collection does not exist.
The specified attribute number is outside the range of 1 to 50, or not valid.
If the supplied attribute value is null, then all members of the named collection will deleted.
The p_generate_md5
parameter determines if 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
. You can access the MD5 message digest for the current value of a specified collection member by 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;
You can merge members of a collection with values passed in a set of arrays. By using the p_init_query
argument, you can create a collection from the supplied query, for example:
HTMLDB_COLLECTION.MERGE_MEMBERS p_collection_name => collection_name
Note 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 13-1 describes the available arguments you can use when merging collections.
Table 13-1 Available Arguments for Merging Collections
Argument | Description |
---|---|
|
Array of first attribute values to be merged. Maximum length is 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. Note that if P_C001 is null, an application error will be raised. |
|
Attribute of |
|
Name of the collection. See Also: "About Collection Naming" |
|
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 this argument in conjunction with the |
|
Use the query defined by this argument to create a collection if the collection does not exist. |
You can use the following utilities to manage collections.
Topics in this section include:
Use COLLECTION_MEMBER_COUNT
to return the total count of all members in a collection. Note that this count does not indicate the highest sequence in the collection, for example:
l_count := HTMLDB_COLLECTION.COLLECTION_MEMBER_COUNT ( p_collection_name => collection name );
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 )
Use COLLECTION_EXISTS
to determine if a collection exists, for example:
l_exists := HTMLDB_COLLECTION.COLLECTION_EXISTS ( p_collection_name => collection name );
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 ID. For example, if you were to move the ID 2 up, 2 becomes 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);
Note 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 the p_seq
sequence ID 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 if you are calling MOVE_MEMBER_UP
or MOVE_MEMBER_DOWN
).
Use the SORT_MEMBERS
method to reorder members of a collection by the column number. This method sorts the collection by a particular column number and 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 the session state of a collection removes 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 the shopping cart and start again, you 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 the CREATE_OR_TRUNCATE_COLLECTION
method 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
See Also:
"Understanding URL Syntax"The HTMLDB_ACTIVITY_LOG
view records all activity in a workspace (or database user account), including developer activity and application runtime activity. You can use HTMLDB_ACTIVITY_LOG
to view to query all activity for the current workspace. For example, you can use this view to develop monitoring reports within a specific application to provide real-time performance statistics.
Table 13-2 describes the columns in the HTMLDB_ACTIVITY_LOG
view.
Table 13-2 Columns in HTMLDB_ACTIVITY_LOG
Column | Type | Description |
---|---|---|
|
DATE |
Date and time that activity was logged at the end of the page view. |
|
VARCHAR2(255) |
Reserved for future use. |
|
VARCHAR2(255) |
Reserved for future use. |
|
VARCHAR2(4000) |
Title of page. |
|
VARCHAR2(4000) |
Reserved for future use. |
|
NUMBER |
Elapsed time of page view in seconds. |
|
NUMBER |
Number of rows processed on page. |
|
VARCHAR2(255) |
User ID performing page view. |
|
VARCHAR2(4000) |
IP address of client. |
|
VARCHAR2(4000) |
Web browser user agent of client. |
|
NUMBER |
Application ID. |
|
NUMBER |
Page ID. |
|
NUMBER |
Oracle Application Express session identifier. |
To conserve space in the activity log, only the first log entry of each unique session will contain the IP address and Web browser user agent.
The following example demonstrates how to create a report that displays the total number of page views and the average page view time in the past 24 hours for application 9529, and grouped by userid:
SELECT COUNT(*), AVG(elap), userid FROM HTMLDB_ACTIVITY_LOG WHERE time_stamp > (SYSDATE-1) AND flow_id = 9529 GROUP BY userid
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 for a user to continue working with your application.
Topics in this section include:
HTMLDB_PLSQL_JOB
is a wrapper package around DBMS_JOB
functionality offered in the Oracle database. Note 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 13-1 describes the functions available in the HTMLDB_PLSQL_JOB
package.
Table 13-3 HTMLDB_PLSQL_JOB Package: Available Functions
Function or Procedure | Description |
---|---|
|
Use this procedure to submit background PL/SQL. This procedure returns a unique job number. Because 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. |
|
Call this procedure to update the status of the currently running job. This procedure is most effective when called from the submitted PL/SQL. |
|
Use this function to determine how much time has elapsed since the job was submitted. |
|
Call this function to determine whether or not the database is currently in a mode that supports submitting jobs to the |
|
Call this procedure to clean up submitted jobs. Submitted jobs stay in the |
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 13-4 describes the columns available in HTMLDB_PLSQL_JOBS
view.
Table 13-4 HTMLDB_PLSQL_JOBS View Columns
Name | Description |
---|---|
|
An unique identifier for each row. |
|
The job number assigned to each submitted PL/SQL job. The |
|
The application from which this job was submitted. |
|
The database schema that owns the application. This identifies what schema will parse this code when |
|
The end user (that is, who logged into the application) that caused this process to be submitted. |
|
The date when the job was submitted. |
|
The date when the status was modified. |
|
The user-defined status for this job. Calling |
|
The system defined status for this job. |
|
The date when the system status was modified. |
|
The unique ID assigned to your workspace. Developers can only see jobs submitted from their own workspace. |
Submitted jobs can contain any of the following system status settings:
SUBMITTED indicates the job has been submitted, but has not yet started. The DBMS_JOB does not guarantee immediate starting of jobs.
IN PROGRESS indicates that the 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 error. The SQL code and SQL error message for the error should be included in the system status. Review this information to determine what went wrong.
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. Because you are not calling the function directly, you can use the APP_JOB
built-in substitution item 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.
Note that 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:
Even if your status shows "100 rows inserted," it does not mean the entire operation was successful. If an error occurred at the time the block of code tried to commit, the user_status
column of HTMLDB_PLSQL_JOBS
would not be affected because status updates are committed separately.
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.
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 Application Builder
Web services are based on Simple Object Access Protocol (SOAP). 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:
SOAP is based on XML, and therefore easy to use.
SOAP messages are not blocked by firewalls because this protocol uses simple transport protocols, such as HTTP.
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 information about Simple Object Access Protocol (SOAP) 1.1 see:
http://www.w3.org/TR/SOAP/
To utilize Web services in Oracle Application Express, 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
A Universal Resource Identifier (URI) identifying the SOAP HTTP request
Operations of the Web Service
Input parameters for each operation
Output parameters for each operation
You manage Web service references on the Web Service References page.
To access the Web Service References page:
On the Database Home Page, click the Application Builder icon.
Select an application.
Application Builder appears.
Click Shared Components.
The Shared Components page appears.
Under Logic, select Web Service References.
The Web Service References page appears.
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:
On the Database Home Page, click the Application Builder icon.
Select an application.
Application Builder appears.
Click Edit Attributes.
Click Edit Standard Attributes.
Under Application Definition, enter the proxy server in Proxy Server.
Click Apply Changes.
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 Universal Description, Discovery and Integration (UDDI) registry
by entering the URL to the WSDL document
AUDDI registry is a directory where businesses register their Web services.
To create a new Web service by searching a UDDI registry:
Navigate to the Web Service References page:
On the Database Home Page, click the Application Builder icon.
Select an application.
Click Shared Components.
Under Logic, select Web Service References.
The Web Service References page appears.
Click Create.
When prompted to search a UDDI registry to find a WSDL, click Yes.
For UDDI Location you can either:
Enter a URL endpoint to a UDDI registry.
Click the List icon and select a UDDI registry.
Under Search for Services, specify whether to search for a business name or a service name.
For Search Type, specify whether to search for a business name or a service name. You cannot search for both.
In Name, enter the business name or service name to search for.
Optionally indicate if the search should be case-sensitive or an exact match. Use the percent (%) symbol as a wildcard character.
Click Search.
When the search results appear, make a selection and click Next.
A summary page appears describing the selected Web service.
Review your selection and click Next to continue.
The URL to the WSDL document displays in the WSDL Location field.
Click Finish.
The Web service reference is added to the Web Service References Repository.
To create a new Web service by specifying a URL to a specific WSDL document:
Navigate to the Web Service References page:
On the Database Home Page, click the Application Builder icon.
Select an application.
Click Shared Components.
Under Logic, select Web Service References.
The Web Service References page appears.
Click Create.
When prompted to search a UDDI registry to find a WSDL, click No.
In WSDL Location, enter the URL to the WSDL document.
Click Finish.
The Web service reference is added to the Web Service References Repository.
Web service references are stored in the Web Service Reference Repository.
To access the Web Service References Repository:
On the Database Home Page, click the Application Builder icon.
Select an application.
Application Builder appears.
Click Shared Components.
The Shared Components page appears.
Under Logic, select Web Service References.
The Web Service Reference page appears.
You can change the appearance of the page by making a selection from the View list. Available options include:
Icons (the default) displays each Web service reference as a large icon. To edit a Web service reference, click the appropriate icon.
Details displays each application item as a line in a report.
In Details view 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.
After you have created a Web service reference, you can test it on the Test Web Service Reference page.
To test a Web service reference:
Navigate to the Web Service References page:
On the Database Home Page, click the Application Builder icon.
Select an application.
Click Shared Components.
Under Logic, select Web Service References.
The Web Service References page appears.
From View, select Details.
Click the Run icon 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.
From Operation, select an operation (that is, the method to be executed).
Under Input Parameters, enter the appropriate value.
Click Test.
The message request and response appear at the bottom of the page.
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 nonscalar 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.
To create a form and report after creating a Web Service Reference:
Create the Web service reference. See "Creating a Web Service Reference".
After the Web service reference has been added, select Create Form and Report on Web Service.
For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).
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.
For Items for Input Parameters:
Identify which items to add to the form. To include an item, select Yes in the Create column. Otherwise, select No.
If necessary, edit the item label.
For Base Node:
In Temporary Result Set Name, enter a name for the collection that stores the Web service result.
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.
For Result Parameters to Display, select the parameters to be included in the report.
Click Finish.
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:
Create the Web service reference. See "Creating a Web Service Reference".
Create a new page. See "Adding Pages to an Application".
In the Create Page Wizard:
Select Form.
Select Form and Report on Web Service.
For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).
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.
For Items for Input Parameters:
Identify which items to add to the form. To include an item, select Yes in the Create column. Otherwise, select No.
If necessary, edit the item label.
For Base Node:
In Temporary Result Set Name, enter a name for the collection that stores the Web service result.
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.
For Result Parameters to Display, select the parameters to be included in the report.
Click Finish.
The Create Form on Web Service Wizard creates a form and a submit button. You can execute this wizard 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 because the input is a stock symbol and the output is the scalar value price.
To create a form after creating a Web Service Reference:
Create the Web service reference. See "Creating a Web Service Reference".
After the Web service references has been added, select Create Form on Web Service.
For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).
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.
For Items for Input Parameters:
Identify which items to add. To include an item, select Yes in the Create column. Otherwise, select No.
If necessary, edit the item label.
For Items for Output Parameters:
Identify which items need to be added. To include an item, select Yes in the Create column. Otherwise, select No.
If necessary, edit the item label.
Click Finish.
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:
Create the Web service reference. See "Creating a Web Service Reference".
Create a new page. See "Adding Pages to an Application".
In the Create Page Wizard:
Select Form.
Select Form on Web Service.
For Web Service Reference and Operation, select the Web service reference and operation (that is, the method to be executed).
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.
For Items for Input Parameters:
Identify which items need to be added. To include an item, select Yes in the Create column. Otherwise, select No.
If necessary, edit the item label.
For Items for Output Parameters:
Identify which items need to be added. To include an item, select Yes in the Create column. Otherwise, select No.
If necessary, edit the item label.
Click Finish.
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:
Create a new page. See "Adding Pages to an Application".
In the Create Page Wizard:
Select Blank Page.
When prompted to use tabs, select No.
Navigate to the Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Page Rendering, Processes, click the Create icon.
From the process category, select Web Services.
Specify a process name, sequence, and processing point.
Select the Web service reference and operation (that is, the method to be executed).
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.
To store the results in a collection:
For Store Result in, select Collection.
Enter a name for the collection in the value field.
To store the results in items on the page:
For Store Result in, select Items.
Enter the appropriate items value in the fields provided.
Click Create Process.
To create a report in which to display Web Service request results:
Navigate to the Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Regions, click the Create icon.
The Create Region Wizard appears.
For the region type, select Report.
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.
For Web Service Reference and Operation, select a Web service reference and an operation (that is, the method to be executed).
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.
For Result Parameters:
In Temporary Result Set Name, enter a name for the collection that stores the Web service result.
Select and deselect the appropriate parameters.
Click Create SQL Report.
After 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:
Create a Web service process. See "Invoking a Web Service as a Process".
Navigate to the Page Definition containing the Web service process.
Select the process name.
The Edit Page Process page appears.
To map an input parameter to a static value:
Scroll down to Web Service Input Parameters.
Enter a value in the Value field, adjacent to the appropriate parameter name.
Click Apply Changes.
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:
On the Database Home Page, click the Application Builder icon.
Select an application.
Application Builder appears.
Click Shared Components.
The Shared Components page appears.
Under Logic, select Web Service References.
Click History.
Note:
The History button only appears on the Web Service Reference page after you have created a Web service reference.