Skip Headers
Oracle® Application Express Application Builder User's Guide
Release 4.0

Part Number E15517-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 · Mobipocket

15 Advanced Programming Techniques

This section provides information about advanced programming techniques including working with automatic data manipulation language, establishing database links, using collections, running background SQL, utilizing Web services, and managing user preferences.

Topics:

See Also:

Oracle Application Express API Reference and "Deploying an Application"

About DML Locking

When automatic data manipulation language (DML) is used in Oracle Application Express to update or delete rows of a table, a transaction is initiated to first lock the row, verify if it has changed since it was displayed on the page, and then finally issue the actual UPDATE or DELETE statement for the row.

In some environments where locking of rows is prevalent, you may want to control the DML operation and determine if the DML operation:

About APEX_DML_LOCK_WAIT_TIME

You can set the value of an application substitution string, an application item, or a page item to APEX_DML_LOCK_WAIT_TIME to control the DML operation. The following values are supported:

When set in an application, the value for APEX_DML_LOCK_WAIT_TIME applies to all UPDATE and DELETE DML operations using Automatic DML in the entire application. To control a specific Automatic DML process, update the value of APEX_DML_LOCK_WAIT_TIME before the Automatic DML process and reset it after the Automatic DML process. Note that this does not affect updates and deletes using tabular forms.

About FSP_DML_LOCK_ROW

You can also set the value of an application substitution string, an application item, or a page item to FSP_DML_LOCK_ROW to control the DML operation. The following values are supported:

Accessing Data with Database Links

Because the Workspace home page runs in 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.

Note:

By default, the CREATE DATABASE LINK system privilege is not granted to a provisioned workspace or database user. To use this feature, a DBA or administrator needs to grant this specific privilege to the database user in the user's workspace. See "Creating Database Links" in Oracle Database Administrator's Guide

To create a database link:

  1. On the Workspace home page, click SQL Workshop and then Object Browser.

    Object Browser appears.

  2. Click Create.

  3. Select Database Link and click Next.

  4. 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:

  1. On the Workspace home page, click SQL Workshop and then Object Browser.

    Object Browser appears.

  2. Select the object type Database Links at the top of the page.

See Also:

"Managing Database Objects with Object Browser" in Oracle Application Express SQL Workshop Guide and "Database Links" in Oracle Database Administrator's Guide

Sending Email from an Application

You can use the APEX_MAIL package to send an email from an Oracle Application Express application. This package is built on top of the Oracle supplied UTL_SMTP package. Because of this dependence, the UTL_SMTP package must be installed and functioning in order to use APEX_MAIL.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_SMTP package

APEX_MAIL contains three procedures. Use APEX_MAIL.SEND to send an outbound email message from your application. Use APEX_MAIL.PUSH_QUEUE to deliver mail messages stored in APEX_MAIL_QUEUE. Use APEX_MAIL.ADD_ATTACHMENT to send an outbound email message from your application as an attachment. All Application Programming Interface packages for Application Express are described in the Oracle Application Express API Reference.

Using Collections

Collections 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:

You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION. All Application Programming Interface packages for Application Express are described in the Oracle Application Express API Reference.

See Also:

"APEX_COLLECTION"in Oracle Application Express API Reference

Creating Custom Activity Reports Using APEX_ACTIVITY_LOG

The APEX_ACTIVITY_LOG view records all activity in a workspace, including developer activity and application run-time activity. You can use APEX_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 15-1 describes the columns in the APEX_ACTIVITY_LOG view.

Table 15-1 Columns in APEX_ACTIVITY_LOG

Column Type Description

time_stamp

DATE

Date and time that activity was logged at the end of the page view.

component_type

VARCHAR2(255)

Reserved for future use.

component_name

VARCHAR2(255)

Reserved for future use.

component_attribute

VARCHAR2(4000)

Title of page.

information

VARCHAR2(4000)

Reserved for future use.

elap

NUMBER

Elapsed time of page view in seconds.

num_rows

NUMBER

Number of rows processed on page.

userid

VARCHAR2(255)

User ID performing page view.

ip_address

VARCHAR2(4000)

IP address of client.

ir_report_id

NUMBER

Interactive report ID

ir_search

VARCHAR2

Interactive report search criteria entered by users.

user_agent

VARCHAR2(4000)

Web browser user agent of client.

flow_id

NUMBER

Application ID.

step_id

NUMBER

Page number.

session_id

NUMBER

Oracle Application Express session identifier.

sqlerrm

VARCHAR2(4000)

SQL Error message.

sqlerrm_component_type

VARCHAR2(255)

Reserved for future use.

sqlerrm_component_name

VARCHAR2(255)

Reserved for future use.


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 APEX_ACTIVITY_LOG
 WHERE time_stamp > (SYSDATE-1)
   AND flow_id = 9529
GROUP BY userid

Keep in mind that logging of activity in an Oracle Application Express instance is rotated between two different log tables. Because of this, logging information is only as current as the oldest available entry in the logs. If you want to persist your application specific log information for all time, you must either copy the log information into your own application table or implement logging directly in your application.

See Also:

"Name" for information on enabling logging on the Edit Definition page

Running Background PL/SQL

You can use the APEX_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.

APEX_PLSQL_JOB is a wrapper package around DBMS_JOB functionality offered in the Oracle database. Note that the APEX_PLSQL_JOB package only exposes that functionality which is necessary to run PL/SQL in the background.

Table 15-2 describes the functions available in the APEX_PLSQL_JOB package.

Table 15-2 APEX_PLSQL_JOB Package: Available Functions

Function or Procedure Description

SUBMIT_PROCESS

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.

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 the database is currently in a mode that supports submitting jobs to the APEX_PLSQL_JOB package.

PURGE_PROCESS

Call this procedure to clean up submitted jobs. Submitted jobs stay in the APEX_PLSQL_JOBS view until either Oracle Application Express cleans out those records, or you call PURGE_PROCESS to manually remove them.


You can view all jobs submitted to the APEX_PLSQL_JOB package using the APEX_PLSQL_JOB view.

All Application Programming Interface packages for Application Express are described in the Oracle Application Express API Reference.

See Also:

"APEX_PLSQL_JOB" in Oracle Application Express API Reference

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. 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.

Using a Process to Implement Background PL/SQL

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        APEX_PLSQL_JOB.UPDATE_JOB_STATUS(
006          P_JOB     => :APP_JOB,
007          P_STATUS  => i || 'rows inserted');
008      END IF;
009      APEX_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 APEX_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 APEX_PLSQL_JOB.UPDATE_JOB_STATUS procedure, however, has been implemented in such a way that the update will happen regardless of whether the job succeeds or fails. This last point is important for two reasons:

    1. 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 APEX_PLSQL_JOBS would not be affected because status updates are committed separately.

    2. 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 applications developed in Application Builder.

Web services are typically based on Simple Object Access Protocol (SOAP) or Representational State Transfer (REST) architectures. 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. RESTful Web services are resource oriented. The scope of the Web service is found in the URI and the method of the service is described by the HTTP method that is used such as GET, POST, PUT, HEAD, and DELETE.

SOAP offers two primary advantages:

Tip:

If you are running Oracle Application Express with Oracle Database 11g Release 1 (11.1), you must enable network services in order to use Web services. See "Enabling Network Services in Oracle Database 11g"

REST offers similar advantages:

Topics:

Note:

The SOAP 1.1 specification is a W3C note. SOAP Version 1.2 specification is a W3C recommendation.

For information about Simple Object Access Protocol (SOAP) see:

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

Understanding Web Service References

To utilize Web services in Oracle Application Express, you create a Web service reference using a wizard. Web service references can be based on a Web Services Description Language (WSDL) document, RESTful style or created manually by supplying information about the service.

When you create a Web service reference based on a WSDL, 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(S)

  • 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

When you create a Web service reference manually, you supply the necessary information to create a valid SOAP request, including:

  • The URL used to post the SOAP request over HTTP(S)

  • A Universal Resource Identifier (URI) identifying the SOAP HTTP request

  • The SOAP envelope for the request, including any item substitutions

  • Optionally the name of a collection to store the response from the Web service

When you create a RESTful Web service reference, you supply the necessary information about the structure of the request and response including:

  • A Universal Resource Identifier (URI) identifying the RESTful request

  • The HTTP method identifying the method of the Web service

  • HTTP Headers, if required, that are part of the request

  • The type of input expected by the Web service

  • The format of the response and how to identify the response parameters

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. On the Workspace home page, click the Application Builder icon.

  2. Select an application.

  3. Click Shared Components.

    The Shared Components page appears.

  4. Under Logic, click 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. On the Workspace home page, click the Application Builder icon.

  2. Select an application.

    Application home page appears.

  3. Click Shared Components.

  4. Under Application, click Edit Definition.

  5. Under Name, enter the proxy server in the Proxy Server field.

  6. Click Apply Changes.

Working with SSL Enabled Web Services

Secure Sockets Layer (SSL) is an industry standard protocol that uses RSA public key cryptography with symmetric key cryptography to provide authentication, encryption, and data integrity.

If the Web service that you need to interact with is SSL-enabled (that is, https displays in the URL to the Web service), you must create a wallet and configure Application Express to use the wallet. A wallet is a password-protected container that stores authentication and signing credentials (including private keys, certificates, and trusted certificates) needed by SSL.

See Also:

"Configuring Wallet Information" in Oracle Application Express Administration Guide

Creating a Web Service Reference Based on a WSDL

When you create a Web service reference based on a WSDL, you must 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

A UDDI registry is a directory where businesses register their Web services.

Topics:

Creating a Web Service Reference by Searching a UDDI Registry

To create a 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. Select Based on WSDL and click Next.

  4. When prompted to search a UDDI registry to find a WSDL, click Yes then Next.

  5. For UDDI Location, enter a URL endpoint to a UDDI registry and click Next.

  6. For Search, specify the following:

    1. Search Type - Specify whether to search for a business name or a service name. You cannot search for both.

    2. Name - Enter the business name or service name to search for. Use the percent (%) symbol as a wildcard character.

    3. Optionally indicate if the search should be case-sensitive or an exact match.

    4. Click Search.

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

    A summary page appears describing the selected Web service.

  7. Review your selection and click Next to continue.

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

  8. 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 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. Select Based on WSDL and click Next.

  4. When prompted to search a UDDI registry to find a WSDL, click No then Next.

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

  6. Click Finish.

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

Creating a Web Service Reference Manually

To create a Web service reference manually:

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

  2. Click Create.

  3. Select Manual and click Next.

    The Create/Edit Web Service page appears.

  4. In Name, enter a name to identify the reference.

  5. Under Service Description:

    1. URL - Enter the URL endpoint of the Web service.

    2. Action - Enter the action of the Web service (optional).

    3. Proxy - Enter a proxy if you want to override the application proxy for this service.

    4. SOAP Version - Select 1.1 or 1.2.

    5. Basic Authentication - Choose whether the Web service requires authentication. Select Yes or No.

  6. For SOAP Envelope, enter the SOAP envelope for this request.

    Note:

    You can reference items from session state in the SOAP envelope by using #ITEM_NAME# syntax.
  7. For Store Response in Collection, enter the name of a collection to store the response (optional).

  8. Click Create.

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

Creating a RESTful Web Service Reference

If you want to utilize a RESTful Web service from your application, you must create a RESTfull Web service reference.

To create a RESTful Web service reference:

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

  2. Click Create.

  3. Select REST and click Next.

  4. For REST Details, specify the following:

    1. Name - Enter a name to identify the reference.

    2. URL - Enter the URL endpoint of the Web service.

    3. Proxy - Enter a proxy if you want to override the application proxy for this service (optional).

    4. HTTP Method - Choose the http method used for the request to the Web service. Select GET, HEAD, POST, PUT or DELETE.

    5. Basic Authentication - Select Yes to require HTTP Basic Authentication. Otherwise, select No.

    6. HTTP Headers - Enter the names of the HTTP headers to send with the request.

    7. Click Next.

  5. For REST Input parameters, specify the following:

    1. Name - Enter the name of the input parameter expected by the method.

    2. Type - Select the input type.

    3. Click Add Parameter.

    4. Repeat steps a though c for each expected input.

    5. Click Next.

  6. For REST Output parameters, specify the following:

    1. Output Format - Select XML or Text for the response format expected from the Web service.

    2. XPath to Output Parameters (XML only) - Enter an XPath expression to the relevant part of the response.

    3. Response Namespace (XML only) - Enter the namespace corresponding to the Response XPath.

    4. Parameter Delimiter (Text only)- Enter the character or sequence that separates parameters returned from the Web service. Use \n to indicate a new line and \t to indicate a tab character.

    5. New Record Delimiter (Text only) - Enter the character or sequence that determines a new record in a text response from the Web service. Use \n to indicate a new line and \t to indicate a tab character.

    6. Name - Enter the name of the output parameter returned by the method.

    7. Type - Select the output type.

    8. Click Add Parameter.

    9. Repeat steps f though h for each returned output parameter.

  7. Click Create.

The Create Web Service Reference Success page appears. 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. On the Workspace home page, click Application Builder.

  2. Select an application.

  3. Click Shared Components.

    The Shared Components page appears.

  4. Under Logic, click Web Service References.

    The Web Service Reference page appears.

    Use the Navigation bar at the top of the page to search for Web service references or change the page display. Available options include:

    • Web Service Reference - Enter a case insensitive query and click Go. To view all Web service references, leave the field blank and click Go.

    • View - Select a display mode and click Go. 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.

      • Report displays each Web service reference as a line in a report.

  5. Select Report from the View list and click Go.

  6. In report view you can:

    • Edit a reference by clicking the reference name.

    • Test a reference by clicking the Run icon.

    • View details about a reference by clicking the View icon. Note that this option is not available for manually created or REST Web service references.

Testing a Web Service Reference Created from a WSDL

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:

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

  2. From View, select Report.

  3. 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.

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

  5. Under Input Parameters, enter the appropriate values.

  6. Click Test.

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

Testing a Web Service Reference Created Manually

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:

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

  2. From View, select Report.

  3. 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.

  4. If required, enter the username and password under Basic Authentication.

  5. In SOAP Envelope text area, optionally edit the SOAP request envelope.

  6. Click Test.

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

Testing a REST Web Service Reference

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:

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

  2. From View, select Report.

  3. 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.

  4. In the URL and Proxy Override fields, optionally edit the values for the test.

  5. If required, enter the username and password under Basic Authentication.

  6. Under HTTP Headers, enter appropriate values.

  7. Under Input Parameters, enter appropriate values.

  8. Click Test.

    The message response appears 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 from a WSDL or a RESTful style Web service, or by adding a page.

Use this wizard when you expect a nonscalar result from the Web service. The Amazon Product API 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 After Creating a Reference

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

  1. Create the Web service reference. See "Creating a Web Service Reference Based on a WSDL" and Creating a RESTful Web Service Reference.

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

  3. For Choose Service and Operation:

    1. Web Service Reference - Select the Web service reference.

    2. Operation - Select the method to be executed. For REST style Web references, select doREST.

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

  5. For Input Items:

    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. If applicable, specify the Item Names and Item Labels for basic authentication. Note that this step only appears if basic authentication was specified for this Web service reference when it was created.

  7. For Window Service Results:

    1. Temporary Result Set Name - Enter a name for the collection that stores the Web service result.

    2. Result Tree to Report On (Not displayed for REST style Web service references) - Select the portion of the resulting XML document that contains the information you want to include in the report.

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

  9. 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 Based on a WSDL", "Creating a Web Service Reference Manually", and "Creating a RESTful Web Service Reference".

  2. Create a new page. See "Managing Pages in a Database Application".

    In the Create Page Wizard:

    1. Select Form.

    2. Select Form and Report on Web Service.

  3. For Choose Service and Operation:

    1. Web Service Reference - Select the Web service reference.

    2. Operation - Select the method to be executed. For RESTful style Web references, doREST will be selected automatically. The Operation option will not appear for Manual style Web references.

  4. For 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 Input Items:

    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. If applicable, specify the Item Names and Item Labels for basic authentication. Note that this step only appears if basic authentication was specified for this Web service reference when it was created.

  7. Follow the on-screen instructions.

  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 after creating the Web service reference from a WSDL or on a RESTful style Web service, 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.

Creating a Form After Creating a Reference

To create a form after creating a Web Service Reference:

  1. Create the Web service reference. See "Creating a Web Service Reference Based on a WSDL", Creating a RESTful Web Service Reference , and Creating a Web Service Reference Manually.

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

  3. For Choose Service and Operation:

    1. Web Service Reference - Select the Web service reference.

    2. Operation - Select the method to be executed. For RESTful style Web references, doREST will be selected automatically. The Operation option will not appear for Manual style Web references.

  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 (this step is bypassed for Manual style Web references):

    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. If applicable, specify the Item Names and Item Labels for basic authentication.

    Note that this step only appears if basic authentication was specified for this Web service reference when it was created.

  8. 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 Based on a WSDL", Creating a RESTful Web Service Reference , and Creating a Web Service Reference Manually.

  2. Create a new page. See "Managing Pages in a Database Application".

    In the Create Page Wizard:

    1. Select Form.

    2. 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). For RESTful style Web references, doREST will be selected automatically. The Operation option does not appear for Manual style Web references.

  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 applicable, specify the Item Names and Item Labels for basic authentication.

      Note that this step only appears if basic authentication was specified for this Web service reference when it was created.

  6. For Items for Output Parameters (this step is bypassed for Manual style Web references):

    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 invoke a Web service as a process on the page. The process is created for you if you run one of the Create Form wizards for Web services. 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 Web Service Reference. See "Creating a Web Service Reference Based on a WSDL", "Creating a Web Service Reference Manually", or "Creating a RESTful Web Service Reference".

  2. Create a page. See "Managing Pages in a Database Application".

    In the Create Page Wizard:

    1. Select Blank Page.

    2. When prompted to use tabs, select No.

  3. Navigate to the Page Definition. See "Altering Page Attributes".

  4. Under Page Rendering or Page Processing, locate the Processes section and click Create.

    The Create Page Processes Wizard appears.

  5. For Process Type, select Web Services and click Next.

  6. For Process Attributes, specify the following:

    1. Name - Enter the process name.

    2. Sequence - Enter the sequence for this component. This specifies the order of evaluation.

    3. Point - Select the processing point.

    4. Click Next.

  7. For Process, specify the following:

    1. Web Service Reference - Select the Web service reference for this process.

      If the Web reference was created from a WSDL or is RESTful style, the Operation selection appears.

    2. Operation (WSDL or RESTful style Web service references only) - Select the Web service operation this process executes.

      The Web Service Input Parameters and Web Service Output Parameters appear.

    3. Under Web Service Input Parameters, specify inputs to the Web service process:

      • Source - Select the source type.

      • Value - Select the source value.

    4. Under Web Service Output Parameters, specify where the results are stored:

      To store the results in a collection:

      • Store Result in - Select Collection.

      • Value - Enter a name for the collection.

      To store the results in items on the page:

      • Store Result in - Select Items.

      • Value - Enter the appropriate item value.

    5. Click Next.

  8. For Messages:

    1. Success Message - Enter the message that displays on the next page when the process runs successfully.

    2. Failure Message - Enter the message that displays when an unhandled exception is raised. After any error processing stops, a rollback is issued and an error message displays.

  9. For Process Conditions:

    1. When Button Pressed - Select that button that causes the process to run.

    2. Condition Type - Select a condition type that must be met in order for this component to be processed. If the condition type requires expressions for comparison, the Expression 1 and possibly Expression 2 fields display. Enter expressions into these fields as required.

  10. 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. See "Editing a Page in Component View".

  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. Choose whether the Web reference was created manually, from a WSDL, or is RESTful style.

  6. If the Web service reference was created from a WSDL:

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

    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.

    3. 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.

  7. If the Web service reference was created manually:

    1. Select the Web service reference.

    2. Choose the SOAP style.

    3. Choose the message format.

    4. Enter the XPath expression to the node to report on.

    5. Enter the namespace for the SOAP response envelope and click Next.

    6. Enter the name of the collection where the response message is stored.

    7. Enter the names of the parameters that you want to be included in the report.

  8. If the Web service reference is REST style:

    1. Select the Web service reference.

    2. Enter the name of the collection where the response message is stored.

    3. Choose the report template and the number of rows to display per page.

    4. Select the parameters to be included in the report.

  9. Click Create SQL Report.

Editing a Web Service Process

After you create a process of type Web service on a Web service reference created from a WSDL or a RESTful style Web reference, you can change the attributes of the input and output parameters to the service.

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. 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.

  5. 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. On the Workspace home page, click the Application Builder icon.

  2. Select an application.

  3. Click Shared Components.

    The Shared Components page appears.

  4. Under Logic, click Web Service References.

  5. Click History.

Note:

The History button only appears on the Web Service Reference page after you have created a Web service reference.

Exposing a Report Region as a RESTful Web Service

RESTful Web services, that are callable with a URL and return either JSON or XML, can be exposed in report regions. In order to expose a report region as a RESTful Web service, the following steps must be performed:

  1. The instance administrator must enable the REST provider feature in instance settings. To enable RESTful access to an Application Express instance, see "Enabling RESTful Access" in the Oracle Application Express Administration Guide.

  2. A developer must enable RESTful access to a report region. See Enabling RESTful Access to a Report Region.

  3. The page that contains the report must not require authentication.

Topics:

Enabling RESTful Access to a Report Region

To enable RESTful access to a report regions, you must make the page public so no authentication is required and enable the report for RESTful access.

To make a page public:

  1. On the Workspace home page, click the Application Builder.

  2. Select an application.

  3. Select the page that contains the report you want to enable.

    The Page Definition appears.

  4. Under Page, click the edit icon.

  5. Under Security, select Page is Public from the Authentication list.

  6. Click Apply Changes.

To enable a report region for RESTful access:

  1. On the Workspace home page, click Application Builder.

  2. Select an application.

    Application Builder appears.

  3. Select the page that contains the report you want to enable.

    The Page Definition appears.

  4. Under Regions, click the name of the region that contains the report you want to enable.

  5. Under Attributes, enter a value for Static ID field. This value is used to access the report RESTfully.

  6. From the Enable RESTful Access List, select Yes.

  7. Click Apply Changes.

Accessing a RESTful Enabled Report Region from a Web Service Client

Once you enabled a report for RESTful access, you need to know the endpoint URL and parameters to pass to the RESTful Web service. The endpoint URL is similar to the URL used to access this instance of Application Express followed by the resource apex_rest.getReport. For example:

http://apex.oracle.com/apex/apex_rest.getReport

Tip:

If your client uses the POST method, you must also set the HTTP Header, Content-Type, to the value application/x-www-form-urlencoded.
Name Default Required Description
app N/A Yes The numeric ID or alias of the application that contains the RESTful enabled report.
page N/A Yes The numeric ID or alias of the page that contains the RESTful enabled report.
reportid N/A Yes The Static ID attribute of the RESTful enabled report.
parmvalues null No Values for the report parameters can be sent in a comma separated list. For example: CLERK,10
lang en No Sets the NLS environment variables prior to running the report based on the language passed in. For example: de
output xml Yes This determines whether XML or JSON will be returned to the client. Domain of possible values are: xml, json

The apex_rest service also has an operation to allow discoverability of RESTful enabled reports, given an application ID or alias. The response is an XML document with a description of all reports that can be accessed by RESTful Web services. This service is invoked with a URL similar to the following:

http://apex.oracle.com/apex/apex_rest.getServiceDescription?app=691

In the URL above, 691 is the numeric ID of an application. The document returned is similar to the following:

<?xml version="1.0"?>
<urn:getServiceDescriptionResponse xmlns:urn="urn:oasis:names:tc:wsrp:v1:types">
        <urn:requiresRegistration>false</urn:requiresRegistration>
        <urn:offeredPortlets>
                    <urn:PortletDescription>
                              <urn:portletHandle>employees</urn:portletHandle>
                              <urn:markupTypes>
                                        <urn:mimeType>application/xml</urn:mimeType>
                                        <urn:mimeType>application/json</urn:mimeType>
                              </urn:markupTypes>
                              <urn:groupID>1</urn:groupID>
                              <urn:description/>
                              <urn:title>EMP</urn:title>
                              <urn:keywords>
                                <urn:value>P1_JOB</urn:value>
                                      <urn:value>P1_DEPTNO</urn:value>
                              </urn:keywords>
                    </urn:PortletDescription>
        </urn:offeredPortlets>
</urn:getServiceDescriptionResponse

The portletHandle maps to the Static ID of the report region or the reportid parameter in the REST request. The groupID maps to the page id or the page parameter in the REST request. Finally, any parameters used by the SQL report are listed as children of the keywords node.

Example: Creating a Web Service Reference on a RESTful Style Web Service

To utilize Web services in Oracle Application Express, you create a Web service reference using a wizard. When you create the Web reference, you can follow one of these methods:

  • You supply the URL to a WSDL document. The wizard then analyzes the WSDL and collects all the necessary information to create a valid SOAP message.

    The wizard provides a step where you can locate a WSDL using the Universal Description, Discovery, and Integration (UDDI) registry. A UDDI registry is a directory where businesses register their Web services. You search for the WSDL by entering either a service or business name.

  • You supply the relevant information on how to interact with the Web service, including the SOAP request envelope, and create the Web reference manually.

  • You supply the relevant information on how to interact with a RESTful style Web service including the URL endpoint, HTTP method and input parameters or payload.

In this example, you create a Web reference to a RESTful Web service. RESTful style Web service references are created by examining the documentation for the services and supplying that information when running the Create Web Reference wizard. You then create a form and report to interact with the service and display the results.

Topics included in this section:

Note:

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. See Specifying an Application Proxy Server Address for instructions.

Note:

The following example is dependent upon the availability of the specified Web service ultimately invoked. If the Web service is unavailable, you may experience difficulties completing this exercise.

Create a Web Service Reference for a RESTfull Style Web Service

To create a new Web reference on a RESTful style Web service:

  1. Enter the following in your web browser to open the documentation for the Web service:

    https://developer.yahoo.com/maps/rest/V1/

  2. Follow the instructions to get an application ID.

  3. On your Application home page, click Shared Components.

  4. Under Logic, click Web Service References.

  5. Select REST and click Next.

  6. For REST Details, make these changes:

    1. Name - Enter Yahoo Map.

    2. URL - Enter the following: http://local.yahooapis.com/MapsService/V1/mapImage

    3. Accept all other defaults and click Next.

  7. For REST Inputs, make these changes:

    1. Name - Enter appid.

    2. Click Add Parameter.

    3. Name - Enter location.

    4. Click Next.

  8. For REST Outputs, make these changes:

    1. XPath to Output Parameters - Enter /Result

    2. Name - Enter URL

    3. Path - Enter /text()

  9. Click Create.

    The Create Web Service Reference Success page appears. The Web service reference for Yahoo Map is added to the Web Service References Repository.

Create a Form and Report of Web Service Results

Next, you create a page that contains a form and a report to interact with the Web service described by the Web Service Reference you created in the previous steps.

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

  1. On the Create Web Service Reference success page, select Create Form and Report on Web Service.

  2. For Choose Service and Operation, make these changes:

    1. Web Service Reference - Select Yahoo Map.

    2. Operation - Select doREST.

    3. Click Next.

  3. For Page and Region Attributes, make these changes:

    1. Form Region Title - Enter Address.

    2. Report Region Title - Enter Map.

    3. Accept all other defaults and click Next.

  4. For Input Items, accept all defaults and click Next.

  5. For Report Parameters, make these changes:

    1. Select URL.

    2. Click Finish.

  6. Click Run Page.

  7. If prompted to log in, enter the username and password for your workspace and click login.

    A form and report resembling Figure 15-1 appear. Notice that the Address Form at the top of the page contains data entry fields and a submit button, but the Map report does not contain any data.

    Figure 15-1 Yahoo Map Form and Report without Data

    Description of Figure 15-1 follows
    Description of "Figure 15-1 Yahoo Map Form and Report without Data"

  8. To test the form, enter the following:

    1. Appid - Enter your Yahoo! application ID you requested in previous steps.

    2. Location - Enter 500 Oracle Parkway, Redwood Shores, CA 94065.

    3. Click Submit.

      The Map report displays a long URL to an image. Next you edit the Map Report region to display the image instead of the URL.

  9. On the developer bar, click Edit Page 4.

  10. On the Edit Page for page 4, in Tree View, do the following:

    1. Under Page Rendering, right click on Regions and select Expand All.

    2. Double click URL.

    3. Under Column Formatting, for HTML Expression enter:

      <img src="#URL#" />

    4. Click Apply Changes.

  11. Click Run Page.

    The map appears in the report region as shown in Figure 15-2.

    Figure 15-2 Yahoo Map Form and Report with Data

    Description of Figure 15-2 follows
    Description of "Figure 15-2 Yahoo Map Form and Report with Data "

Example: Creating a Web Service Reference from a WSDL

To utilize Web services in Oracle Application Express, you create a Web service reference using a wizard. When you create the Web reference, you can follow one of these methods:

  • You supply the URL to a WSDL document. The wizard then analyzes the WSDL and collects all the necessary information to create a valid SOAP message.

    The wizard provides a step where you can locate a WSDL using the Universal Description, Discovery, and Integration (UDDI) registry. A UDDI registry is a directory where businesses register their Web services. You search for the WSDL by entering either a service or business name.

  • You supply the relevant information on how to interact with the Web service, including the SOAP request envelope, and create the Web reference manually.

  • You supply the relevant information on how to interact with a RESTful style Web service including the URL endpoint, HTTP method and input parameters or payload.

In this example, you create a Web service reference by supplying the location of a WSDL document to a Web service. You then test the Web service reference and create a form and report to display movie theaters and locations.

Note:

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. See Specifying an Application Proxy Server Address for instructions.

Note:

The following example is dependent upon the availability of the specified Web service ultimately invoked. If the Web service is unavailable, you may experience difficulties completing this exercise.

Create a Web Service Reference from a WSDL

To create a new Web reference by supplying the WSDL location:

  1. On the Application home page, click Shared Components.

    The Shared Components page appears.

  2. Under Logic, select Web Service References.

  3. Click Create.

  4. Choose Based on WSDL and click Next.

  5. When prompted whether to search a UDDI registry to find a WSDL, select No and click Next.

  6. In the WSDL Location field enter the following and click Next:

    http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx?wsdl

    A summary page appears describing the selected Web service.

  7. Click Create Reference.

    The Create Web Service Reference page appears. The Web service reference for MovieInformation is added to the Web Service References Repository.

Create a Form and Report of the Web Service Result

Next, you need to create a page that contains a form and report to use with your Web Service Reference.

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

  1. On the Create Web Service Reference success page, select Create Form and Report on Web Service.

  2. For Choose Service and Operation:

    1. Web Service Reference - Select MovieInformation.

    2. Operation - Select GetTheatersAndMovies.

    3. Click Next.

  3. For Page and Region Attributes:

    1. Form Region Title - Change to Theater Information.

    2. Accept the other defaults and click Next.

  4. For Input Items:

    1. For P2_ZIPCODE and P2_RADIUS, accept the default, Yes, in the Create column.

    2. For P2_ZIPCODE, change the Item Label default to ZIP Code.

    3. Click Next.

  5. For Web Service Results:

    1. Temporary Result Set Name (Collection) - Accept the default.

    2. Result Tree to Report On - Select Theater (tns:Theater).

    3. Click Next.

  6. For Result Parameters, select all the parameters and click Finish.

  7. Click Run Page.

  8. If prompted to log in, enter the user name and password for your workspace and click Login.

    A form and report resembling Figure 15-3 appear. Notice that the Theater Information Form at the top of the page contains a data entry field and a submit button, but the Results Report does not contain any data.

    Figure 15-3 Theater Information Form and Report without Data

    Description of Figure 15-3 follows
    Description of "Figure 15-3 Theater Information Form and Report without Data"

  9. To test the form, enter 43221 in the ZIP Code field and 5 in the Radius field. Then click Submit.

    The report at the bottom of the page should resemble Figure 15-4. The report lists the names and addresses of movie theaters matching the entered ZIP code and radius.

    Figure 15-4 Theater Information Report Showing Resulting Data

    Description of Figure 15-4 follows
    Description of "Figure 15-4 Theater Information Report Showing Resulting Data"

Example: Creating a Web Service Reference Manually

In this example, you create a Web reference by supplying information about the Web service and using the manual facility. Manual Web references are created by visually inspecting the WSDL document as well as using a tool to determine the SOAP envelope for the Web service request.

Topics in this section include:

Create a Web Service Reference Manually

To create a Web reference manually, you copy sections from the WSDL for a service called MovieInformation.

Please note the example settings provided in the following steps are based on the MovieInformation service at the time this document was released.

To create a manual Web reference:

  1. On the Application home page, click Shared Components.

  2. Under Logic, click Web Service References.

  3. Click Create.

  4. Select Manual and click Next.

    The Create/Edit Web Service page appears.

  5. In the Name field, enter Movie Info.

  6. Locate the endpoint of the MovieInformation service:

    1. Open the WSDL by going to:

      http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx?wsdl
      
    2. In the WSDL, find the location attribute of the soap:address element, which is a child of the port element. You can search for the following term within the code: soap:address location.

      At the time of this release, it was this attribute:

      http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx
      
  7. In the URL field on the Create/Edit Web Service page, enter the endpoint of the MovieInformation service you located. For example: http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx

  8. Locate the SOAP action for the GetTheatersAndMovies operation:

    1. If necessary, open the WSDL again. See Step 7a.

    2. In the WSDL, find the soapAction attribute of the soap:operation element, which is a child of the operation element that has a name attribute of GetTheatersAndMovies. You can search for the following term within the code: soap:operation soapAction.

      At the time of this release, it was this attribute:

      http://www.ignyte.com/whatsshowing/GetTheatersAndMovies
      
  9. In the Action field on the Create/Edit Web Service page, enter the SOAP action you located. For example: http://www.ignyte.com/whatsshowing/GetTheatersAndMovies

  10. In the SOAP Envelope field on the Create/Edit Web Reference page, enter the xml document representing the SOAP Request message. For example:

    <?xml version="1.0" encoding="UTF-8"?>
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:tns="http://www.ignyte.com/whatsshowing"
    xmlns:xs="http://www.w3.org/2001/XMLSchema">
       <soap:Body>
          <tns:GetTheatersAndMovies>
             <tns:zipCode>#ZIP#</tns:zipCode>
             <tns:radius>#RADIUS#</tns:radius>
          </tns:GetTheatersAndMovies>
       </soap:Body>
    </soap:Envelope>
    

    You can use a SOAP message generating tool, such as MindReef, to construct a valid SOAP Request for a given Web service.

  11. In the Store Response in Collection field, enter MOVIE_RESULTS. This is where the response from the Web service will be stored.

    The Create/Edit Web Service page should resemble Figure 15-5.

    Figure 15-5 Create/Edit Web Service Page

    Description of Figure 15-5 follows
    Description of "Figure 15-5 Create/Edit Web Service Page"

  12. Click Create.

    The Web Service References page appears, showing Movie Info in the list.

Test the Web Service Reference

To test the Web service reference:

  1. On the Web Service References page, click the Test icon next to the Movie Info Web reference.

    The Web Services Testing page appears.

    Note:

    View must be set to Report, otherwise the Test icon is not displayed.
  2. In the SOAP Envelope field, replace #ZIP# with 43221 and #RADIUS# with 5.

  3. Click Test.

  4. Review the Result field and note the following about the response:

    • The base node in the return envelope is called:

      GetTheatersAndMoviesResponse
      
    • The namespace for the message is:

      http://www.ignyte.com/whatsshowing
      
    • The XPath to the interesting parts of the response under the result element is:

      /GetTheatersAndMoviesResponse/GetTheatersAndMoviesResult/Theater/Movies/Movie
      
    • The interesting elements in the results are called:

      Name
      Rating
      RunningTime
      ShowTimes
      

Create a Form and Report on the Web Service Results

Next, you create a page that contains a form and report to use with the Manual Web Service Reference.

To create a form and report on a Manual Web Service Reference:

  1. On the Application home page, click Create Page.

  2. Select Form and click Next.

  3. Select Form and Report on Web Service and click Next.

  4. For Web Service Reference, select Movie Info and click Next.

  5. For Page and Region Attributes, make these changes:

    1. Form Region Title - Enter Theater Information.

    2. Accept all other defaults and click Next.

  6. For Input Items, make these changes:

    1. For ZIP and RADIUS, accept the default, Yes, in the Create column.

    2. For ZIP, change the Item label default to ZIP Code.

    3. Click Next.

  7. For Web Service Results, make these changes:

    1. SOAP Style - Select Document.

    2. Message Format - Select Literal.

    3. Result Node Path - Enter the following:

      /GetTheatersAndMoviesResponse/GetTheatersAndMoviesResult/Theater/Movies/Movie

    4. Message Namespace - Enter the following:

      http://www.ignyte.com/whatsshowing

    5. Click Next.

  8. For Parameter Names, enter Name, Rating, RunningTime, and Showtimes and click Next.

  9. For Tab Options, accept the default and click Next.

  10. Click Create Form and Report.

  11. Click Run Page.

  12. If prompted to log in, enter the user name and password for your workspace and click Login.

    A form and report resembling Figure 15-6 appears. Notice that the Theater Information Form at the top of the page contains a data entry field and a submit button, but the Results Report does not contain any data.

    Figure 15-6 Theater Information Form and Report without Data

    Description of Figure 15-6 follows
    Description of "Figure 15-6 Theater Information Form and Report without Data"

  13. To test the form, enter 43221 in the ZIP Code field and 5 in the Radius field. Then click Submit.

    The report at the bottom of the page should resemble Figure 15-7. The report lists the names and addresses of movie theaters matching the entered ZIP code and radius.

    Figure 15-7 Theater Information Report Showing Resulting Data

    Description of Figure 15-7 follows
    Description of "Figure 15-7 Theater Information Report Showing Resulting Data"

Implementing Plug-ins

This section describes how to add plug-ins to your application, and how to manage reuse and sharing. Plug-in examples are available on the Application Express Plug-in Repository on the Oracle Technology Network, and are included in the Sample Database Application.

Topics:

About Plug-ins

Plug-ins allow developers to declaratively extend the built-in types available with Application Express and to enable developers to share and reuse them.

Application Express supports a set group of item, region, dynamic action and process types. Plug-ins offer a means of augmenting these built-in types by declaratively creating and using new types in your application. Because plug-ins are designed for reuse, developers can export and import them to other workspaces and also share them with the Application Express Plug-in community by using the Plug-in Repository.

The process of implementing a plug-in involves the following:

  1. Create or import a plug-in in to your application workspace.

  2. Edit or create an item, region, process or dynamic action type to use the plug-in.

  3. Run your application to test the plug-in.

For Plug-in implementation examples, go to the Learning Library at the following location, click the All Content tab and enter search criteria for Application Express (APEX) Product OBEs:

http://apex.oracle.com/pls/apex/f?p=9830:1:0::NO

Accessing Plug-ins

To access the plug-ins:

  1. Navigate to the Plug-ins page.

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Click Shared Components.

    5. Under User Interface, select Plug-ins.

      The Plug-ins page displays with the Plug-ins tab selected by default. All available plug-ins appear.

  2. You can customize the appearance of the page using the Search bar at the top of the page. Available controls include:

    • Search icon - Resembles a magnifying glass. Click this icon to narrow your search to only specific columns. To search all columns, select All Columns.

    • Text area - Enter case insensitive search criteria (wild card characters are implied) and then click Go.

    • Go button - Executes a search or applies a filter.

    • View icons. Use this control to toggle between icon and report views. To change the view, select one of the following:

      • View Icons (default) displays each computation as a large icon.

      • View Report displays each computation as a line in a report.

    • Actions menu - Displays the Actions menu. Use this menu to customize the report view. See "Using the Actions Menu".

Editing a Plug-in

To edit a plug-in:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click the plug-in you want to edit or view.

    The Plug-in Create/Edit page appears.

  3. Make modifications. To learn more about each option, see item Help, "Creating a Plug-in", "Adding Custom Attributes to a Plug-in", "Uploading Files to Associate with a Plug-in" and "Adding Events to a Plug-in"

  4. Click Apply Changes.

Creating a Plug-in

To create a plug-in:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click Create.

    The Plug-in Create/Edit page appears.

  3. Under Name:

    1. Name (Required) - Enter name of the plug-in.

    2. Internal Name (Required) - Enter the internal name of the plug-in. This name must be unique within the current application.

      Tip:

      To insure the internal name is a globally unique name worldwide, it is recommended that your organization domain name be used as a prefix to internal plug-in names. For example, a domain name of mycompany.com prefixed to a plug-in named Slider, would result in an internal name of COM.MYCOMPANY.SLIDER.
    3. Type (Required) - Select the type of component that can use this plug-in. Settings under Callbacks and Standard Attributes will be different depending on the plug-in type selected. Refer to following sections for details.

  4. Under Subscription:

    • Reference Master Plug-in From - If you want to base this plug-in on another plug-in in this workspace, select the plug-in from the pop up list. Otherwise, leave the field blank to make this the master copy of this plug-in.

  5. For Settings:

    • File Prefix - Use the default, #PLUGIN_PREFIX#, to reference files that are stored with your plug-in definition in the database. This prefix determines the virtual path the Web server uses to point to the files of the plug-in. For improved performance, you can also store your plug-in files on your Web server and use #IMAGE_PREFIX# or any valid URL to reference them.

  6. Under Source:

    1. PL/SQL Code - Enter a PL/SQL anonymous block of code that contains the procedures for rendering, validating, executing and performing AJAX callbacks for this plug-in.

    2. Do not validate PL/SQL code (parse PL/SQL code at runtime only) - Check this option if you want to parse the PL/SQL code at runtime only. Otherwise, the code will be parsed when the plug-in is created.

  7. Under Callbacks:

    1. Render Function Name (only available for Region, Item and Dynamic Action type plug-ins) - Enter the name of the PL/SQL function called to render the plug-in.

    2. AJAX Function Name (only available for Region, Item and Dynamic Action type plug-ins) - Enter the name of the PL/SQL function used by the plug-in to load additional data with an AJAX call.

    3. Validation Function Name (only available for Item type plug-ins)- Enter the name of the PL/SQL function the plug-in can use to perform basic validations on the submitted data.

    4. Execute Function Name (only available for Process type plug-ins) - Enter the name of the PL/SQL function called to execute the plug-in.

    Note:

    All Callback function names can reference a function of the anonymous PL/SQL code block, a function within a package or a stand alone function in the database. See item help for further details and examples.
  8. Under Standard Attributes (not available for Process type plug-ins):

    For Dynamic Action type plug-ins, the following descriptions apply when the option is checked:

    1. For Item(s) - The dynamic action supports selection of items as the affected elements.

    2. For Region - The dynamic action supports selection of a region as the affected elements.

    3. For DOM Object - The dynamic action supports selection of DOM Objects as the affected elements.

    4. For jQuery Selector - The dynamic action supports specifying a jQuery selector as the affected elements.

    5. For Triggering Element - The dynamic action supports selection of the triggering element as the affected elements.

    6. For Event Source - The dynamic action supports selection of your event source as the affected elements.

    7. Affected Element Required - The dynamic action requires an affected element to be specified.

    8. Check "Fire on page load" - Defines the default value for the action's Fire on Page Load field.

    9. Has Stop Execution on Error Attribute - The Stop Execution on Error field is available for the action.

    For Item type plug-ins, check the following to enable options as described here:

    1. Is Visible Widget - The widget is visible.

    2. Session State Changeable - The value for an item can be changed in session state. The Is Visible Widget attribute must be checked.

    3. Has Read Only Attribute - The user can specify a "Read Only" condition for the item. The Is Visible Widget attribute must be checked for this attribute to be enabled.

    4. Has Escape Output Attribute - The item has the Escape special characters field in the Security section when the item is edited. The Is Visible Widget attribute must be checked.

    5. Has Quick Pick Attributes - The item has quick pick attributes. The Is Visible Widget attribute must be checked.

    6. Has Source Attributes - The item has source related attributes such as Source Used, Source Type and Format Mask.

    7. Format Mask Date Only - For items that handle date information, the Format Mask field popup restricts the date selections by displaying date values to select from. The Has Source Attributes must be checked.

    8. Format Mask Number Only - For items that handle numeric information, the Format Mask field popup restricts the numeric selections by displaying numeric values to select from.

    9. Has Element Attributes - The item has element attributes such as Horizontal/Vertical Alignment, HTML Form Element Attributes and Pre/Post Element Text.

    10. Has Width Attributes - The item width can be controlled.

    11. Has Height Attribute - The item height can be controlled.

    12. Has Element Option Attribute - The item allows specification of additional option attributes when rendering multi-selection elements such as radio groups of check boxes.

    13. Has Encrypt Session State Attribute - When editing the item, the Store value encrypted in session state displays under the Security region.

    14. Has List of Values - The item has an associated list of values. The Is Visible Widget attribute must be checked.

    15. List of Values Required - For items that handle lists of values, a list of values must be defined. The Has List of Values attribute must be checked.

    16. Has LOV Display Null Attributes - The item is associated with a list of values that is allowed to include null values. The Has List of Values attribute must be checked.

    17. Has Cascading LOV Attributes - The item has cascading LOV related attributes. The Has List of Values attribute must be checked.

    For Region type plug-ins, check the following to enable options as described here:

    1. Region Source is SQL Statement - The region source is a SQL Statement. When checked, a minimum and a maximum number of columns the query can return can be specified and SQL Examples display to help the user.

    2. Region Source is Plain Text - The region source is plain text.

    3. Region Source Required - If region source is a SQL Statement, this attribute makes this a required value.

  9. For Information:

    1. Version - Enter a string to identify the plug-in version.

    2. About URL - Enter a URL to the plug-in authors home page or to additional information about the plug-in.

  10. For Help Text, enter help text used by the user to understand how the plug-in works.

  11. For Comments, enter comments and notes that will never be displayed when the application is running.

    To learn more about each option, see item Help.

  12. Click Create.

    Now that the plug-in is created, you can specify additional custom attributes, upload files such as image, CSS and JavaScript files to associate with your plug-in and add events. See "Adding Custom Attributes to a Plug-in", "Uploading Files to Associate with a Plug-in", and "Adding Events to a Plug-in".

Adding Custom Attributes to a Plug-in

A plug-in attribute is used to prompt the developer for additional data in the Builder when the plug-in is used.

To add custom attributes to the plug-in:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click the plug-in you want to modify.

    The Plug-in Create/Edit page appears.

  3. Scroll down to Custom Attributes and click Add Attribute.

    The Edit Attribute page appears.

  4. Under name:

    1. Scope - Select Application if the attribute is only defined once for the application. Select Component if the attribute is defined each time the plug-in is referenced in a component.

    2. Attribute - Enter the sequence that correlates with the ATTRBUTE_XX columns and to the PL/SQL types defined in the apex_plugin package.

    3. Display Sequence - Enter the display sequence for this plug-in attribute in the Application Express Builder.

    4. Label - Enter the label that displays for this attribute in the Application Express Builder.

  5. For Settings:

    1. Type - Select the attribute type.

    2. Required - Select Yes if this attribute must be specified, otherwise, select No.

    3. Translatable - Select Yes if this attribute is included in the translation file, otherwise, select No.

    4. Display Width - Enter the length in characters displayed for this attribute in the Application Express Builder.

    5. Maximum Width - Enter the maximum number of characters users are allowed to enter for this attribute in the Application Express Builder.

  6. Under Default Value, specify a value to be used for this plug-in attribute when a new component is created that uses this plug-in. Use Y and N for attributes of type Yes/No.

  7. Under Condition:

    1. Depending on - Select the attribute the current attribute depends on

    2. Condition Type (only displays if dependant attribute) - Select the type of condition that must be met in order for this attribute to render.

    3. Expression (only displays if needed for condition type)- Enter expression that must meet the selected condition type. See item help for details.

  8. For Help Text, specify the help text that is displayed as context sensitive help for the attribute in the Application Express Builder.

  9. Under Comments, enter comments or notes that are never displayed when the application is running.

  10. Click Create to create the attribute and go back to the Edit page, or click Create and Create Another to create the attribute and continue to create another attribute.

Note:

If you click Create or Create and Create Another and the Return To Page checkbox on the right panel under Plug-ins is checked, this same Edit Attribute page displays.

Uploading Files to Associate with a Plug-in

Upload image, CSS and JavaScript files you want to associate with your plug-in. Use #PLUGIN_PREFIX# in uploaded CSS and JavaScript files to reference another uploaded file. See the Plug-in Files region on the right of the Upload New File page for more details.

For example, in a CSS file the reference might be:

.sidebar{
background-image:url(#PLUGIN_PREFIX#flowers.png);
}

To upload a file:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click the plug-in you want to upload to.

    The Plug-in Create/Edit page appears.

  3. Scroll down to Files and click Upload New File.

    The Upload New File page appears.

  4. For File, browse to and select the file you want to upload.

  5. Click Upload.

    The Create/Edit page appears. The name of the uploaded file appears under Files.

  6. Click Apply Changes.

Adding Events to a Plug-in

Adding events to an item, region or dynamic action type plug-in, allows them to be exposed to dynamic actions. For example, a Slider plug-in that exposes events such as Start Slide, Sliding, and Stop Slide, allows the creation of dynamic actions that can react when these events occur.

To add events to a plug-in:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click the plug-in you want to edit.

    The Plug-in Create/Edit page appears.

  3. Scroll down to Events and click Add Event.

    A new row displays under Events.

  4. Under Events, specify the following:

    1. Name - The display name under which the plug-in event should show up in the dynamic action, for example: Start Slide.

    2. Internal Name - The name of the assigned JavaScript event that triggers the dynamic action, for Example: slidestart.

  5. Click Apply Changes.

Deleting a Plug-in

You can delete a plug-in if it is not in use. If it is in use, the Delete button does not display.

To delete a plug-in:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click the plug-in you want to delete.

    The Plug-in Create/Edit page appears.

  3. Click Delete.

  4. Click OK to confirm.

Viewing the Plug-in Repository

The purpose of the Plug-in Repository is to provide a central location where developers can share and download plug-ins. The repository is located on the Oracle Technology Network.

To view the Plug-in repository:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click View Plug-in Repository.

    The Oracle Application Express Plug-in Repository displays.

Importing a Plug-in from the Plug-in Page

Use this option to import an exported plug-in to your application. Importing a plug-in can be done from the Plug-ins page under Shared Components, as described here, or from the Application Builder Home page. See "Importing Plug-ins".

To import a plug-in:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click Import.

    The import Plug-in page appears.

  3. For Specify File:

    • Import file - Select the name of the import file.

    • File Type - Select Plug-in.

    • File Character Set - Select the import file character set encoding.

  4. Click Next.

  5. For File Import Confirmation, click Next.

  6. For Install, click Install Plug-in.

Exporting a Plug-in from the Plug-in Page

Use this option to export a plug-in definition to a file. This file can be imported into any APEX application. Exporting a plug-in can be done from the Plug-ins page under Shared Components, as described here, or from the Application Builder Home page. See "Exporting Plug-ins".

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Under Tasks, click Export Plug-in.

    The Export Plug-in page appears.

  3. For Application, select the application to export the plug-in from.

  4. Click Set Application.

  5. For Export Plug-in:

    • Plug-in - Select plug-in.

    • File Format - Select file format of export plug-in.

  6. Click Export Plug-in.

  7. Select Save File, and click OK.

    Downloads complete message appears.

Resetting the Plug-in Interactive Report

To reset the interactive report:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click Reset.

View Plug-in Utilization

The Plug-in Utilization page displays which pages, components, and regions use each plug-in.

To view plug-in utilization:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click Utilization.

    The Utilization page appears.

View Plug-in History

The Plug-in History page shows the actions taken on each plug-in, the developer that performed the action and the date of each action.

To view plug-in history:

  1. Navigate to the Plug-ins page. See Accessing Plug-ins.

  2. Click History.

    The History page appears.

Implementing Dynamic Actions

This section provides an overview of dynamic actions and explains how to create and modify them.

Topics:

Understanding Dynamic Actions

Dynamic actions provide a way to define complex client-side behavior declaratively without the need for JavaScript. Using the Dynamic Action Create wizard, you can specify an action that is performed when a defined set of conditions occur. You can also specify which elements are affected by the action, and when and how they are affected.

When working with dynamic actions, you should be mindful of the fact that the more dynamic actions you add to a page, the greater your overall page size will be. This is because the dynamic action framework emits additional code to the client for each dynamic action defined, which then also needs to be downloaded and executed by the framework in the client. Please see "Debugging Dynamic Actions" for information on how to debug problems.

The process of implementing a dynamic action involves the following:

  1. Edit or create an item on a page. This item is referenced within the dynamic action in defining when it fires.

  2. Create a dynamic action from the application page that invokes the action.

  3. Run your application to test the dynamic action.

For Dynamic Action implementation examples, go to the Learning Library at the following location, click the All Content tab and enter search criteria for Application Express (APEX) Product OBEs:

http://apex.oracle.com/pls/apex/f?p=9830:1:0::NO

Creating a Dynamic Action

Creating a Dynamic Action involves specifying when the action will happen (with optional conditions), what action or actions are performed, and what elements are affected by the action.

To create a dynamic action:

  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Access the Create Dynamic Action Wizard:

    • Tree view - Under Page Rendering, locate Dynamic Actions. Right-click and select Create.

      You can also invoke the wizard by right-clicking on a specific page item or region that you want to be the trigger for the dynamic action. This makes definition faster because certain When values will be pre-filled

    • Component view - Under Page Rendering, scroll down to Dynamic Actions and click the Create icon.

  3. For Implementation, select one of the following dynamic action types and click Next.

    • Standard - Select this option to create simple actions that show, hide, enable and disable page elements when a page item's value changes.

    • Advanced - Select this option to create complex actions such as setting a value, adding a class and using a dynamic action plug-in. This option also allows the selection of different events that will trigger the dynamic action.

  4. For Identification, enter the following and click Next.

    • Name - Enter the name of the dynamic action.

    • Sequence - Enter the sequence of this component. The sequence determines the order of evaluation.

  5. For When, specify when the action is performed and click Next. Options on this page include:

    1. Event - Dynamic actions can be defined to fire based on events that happen on the page. There are three different categories of events that can be utilized, Browser events, Framework events and Component events. Here are the details of all the supported events, including the internal JavaScript event name in brackets:

      Browser Events

      • Change (change) - Fires when a control loses the input focus and its value has been modified since gaining focus. This is the default setting.

      • Click (click) - Fires when the pointing device button is clicked over the triggering element.

      • Double Click (dblclick) - Fires when the pointing device button is double clicked over the triggering element.

      • Get Focus (focusin) - Fires when the triggering element receives focus either via a pointing device or by tabbing into the element.

      • Key Down (keydown) - Fires when a key on the keyboard is pressed. Use this event when you want to capture special keystrokes such as arrow keys, after a key has been pressed.

      • Key Press (keypress) - Fires when a key on the keyboard is pressed resulting in text being entered. Use this event when you want to capture actual text entry.

      • Key Release (keyup) - Fires when a key on the keyboard is released. Use this event when you want to capture special keystrokes such as arrow keys, after a key has been released.

      • Lose Focus (focusout) - Fires when the triggering element loses focus either via the pointing device or by tabbing out of the element.

      • Mouse Button Press (mousedown) - Fires when the pointing device button is pressed over the triggering element.

      • Mouse Button Release (mouseup) - Fires when the pointing device button is released over the triggering element.

      • Mouse Enter (mouseenter) - Fires once when the pointing device is moved into the triggering element.

      • Mouse Leave (mouseleave) - Fires once when the pointing device is moved away from the triggering element.

      • Mouse Move (mousemove) - Fires when the pointing device is moved while it is over the triggering element.

      • Page Load (ready) – Fires when the page loads.

      • Page Unload (unload) - Fires when a page is unloaded.

      • Resize (resize) - Fires when the browser window is resized.

      • Resource Load (load) - When the triggering element is the window element (using a 'DOM Object' value of 'window' in the 'When' attributes) the event fires when the browser finishes loading all content within a document, including window, frames, objects and images. For other elements, it fires when the target element and all of its content has finished loading.

      • Scroll (scroll) - Fires when a scrollable triggering element is scrolled. This could be the browser window (using a 'DOM Object' value of 'window' in the 'When' attributes), scrollable frames or elements with the 'overflow' CSS property set to 'scroll' (or 'auto' when the element's explicit height is less than the height of its contents).

      • Select (select) - Fires when a user selects some text in a text field.

      Framework Events

      • After Refresh (apexafterrefresh) - Fires after the triggering element has been refreshed. The event is only valid for triggering elements that perform 'Partial Page Refresh' and fire this event, for example Interactive Reports, Classic Reports, and all item types with cascading LOV support. Plug-ins might support this event as well.

      • Before Page Submit (apexbeforepagesubmit) – Fires prior to a page being submitted.

      • Before Refresh (apexbeforerefresh) – Fires before the triggering element has been refreshed. The event is only valid for triggering elements that perform 'Partial Page Refresh' and fire this event, for example Interactive Reports, Classic Reports, and all item types with cascading LOV support. Plug-ins might support this event as well.

      Component Events

      These events will only be available when there is a component (either an item, region or dynamic action) available to your application that triggers a custom event. These events will appear in the following format Event name [Component Name], for example the Change Order event triggered by the Shuttle native item type will appear as Change Order [Shuttle]. In Application Express, these events are triggered from either native components shipped with plug-in components you have installed into your application.

      • Events triggered by native components:

        Change Order [Shuttle] (shuttlechangeorder) – Fires when the order of a value in the right hand select list is changed (either using Move Top, Move Up, Move Down or Move Bottom). There are currently no other events triggered by native components in Application Express.

      • Events triggered by plug-in components:

        These will be available when added to your current application and will be in the format Event name [Component Name]. For help related to events raised by plug-ins, please refer to help text on the plug-in configuration page, by navigating to Shared Components > Plug-ins > [plugin name] > Help Text, where the plug-in author may have included documentation.

    2. Selection Type - Select the type of page element that triggers the dynamic action. A corresponding name field displays to specify the name of the page element.

      Note:

      Only available if the event selected supports definition of a page element. Selecting any of the following events will hide this field: Page Load, Page Unload, Resize, Before Page Submit. All other event types show this field.
    3. Item(s), Region, DOM Object, or jQuery Selector - Specify the name of the page element as follows:

      • Item(s) - Enter or select one or more page item names that trigger the dynamic action. For multiple items, please separate page items with a comma.

      • Region - Select the region name that triggers the dynamic action. The region selected must use a region template that includes a container element with an ID attribute set to #REGION_STATIC_ID#. The dynamic action framework relies on this ID value to reference the region.

        The region can fire the event itself, such as in the case of Before Refresh or After Refresh events, or can be a container to catch events that fire on items or other elements in the region. Only the following subset of events support this: Change (change), Click (click), Double Click (dblclick), Get Focus (focus), Key Down (keydown), Key Press, Key Release (keyup), Lose Focus (blur), Mouse Button Press (mousedown), Mouse Button Release (mouseup), Mouse Move (mousemove).

      • DOM Object - Enter either the Document Object Model (DOM) object or the ID of a DOM object that triggers the dynamic action.

      • jQuery Selector - Enter the jQuery selector syntax to return one or more page elements that trigger the dynamic action.

    4. Condition - To conditionalize the trigger, make a selection from the Condition Type list and enter text in the Value field. Based on whether this condition is met, it is possible to define both True (when it is met) and False (when it is not) actions. If No Condition is specified, only True actions will fire. For further information, see the item level help.

  6. Specify the action that is performed when the previously specified event occurs and conditions are satisfied, then click Next.

    • For True/False Actions (Standard), specify the True Action:

      • Show - The item displays when the event is true or No Condition is specified.

      • Hide - The item does not display when the event is true or No Condition is specified.

      • Enable - The item is enabled when the event is true or No Condition is specified.

      • Disable - Disables the affected elements. By disabling the affected elements, the page item will be non-editable and does not retain the item's value when the page is submitted.

      • Create Opposite False Action - For the selected true action above, create an opposite action to occur when the event is false. The Standard branch of the wizard allows selection of either Show, Hide, Enable or Disable actions for the True Action and provides the facility to create the logical opposite of this. For example, if Show' is selected for the True Action, checking the Create Opposite False Action checkbox would also create a Hide false action.

        Note:

        Create Opposite False Action only available if a Condition has been specified.
    • For True Actions (Advanced only) - The Advanced branch of the wizard allows selection of all actions and allows selection of different True and False Actions. For both the True Action and False Action pages you specify. Depending on the selected action, additional options display. Use these options to specify additional settings and values required to perform the action.

      Component

      • Clear - Clears the affected elements.

      • Disable - Disables the affected elements. By disabling the affected elements, the page item will be non-editable and does not retain the item's value when the page is submitted.

      • Enable - Enables the affected elements.

      • Hide - Hides the affected elements. Also has the option to Hide all page items on the same line.

      • Refresh - Triggers a refresh of the affected elements. Note that not all elements support a refresh, you can use it for interactive reports, classic reports and all item types with cascading LOV support. Plug-ins might support this action as well.

      • Set Focus - Sets the focus to the affected elements. This will default to the first of the affected elements if there are multiple. This can be especially useful when used in conjunction with the Show and Enable actions to take the user straight to the appropriate item.

      • Set Value - Sets the value of the affected elements. This supports the following Set Types: Static Assignment, JavaScript Expression, SQL Statement, PL/SQL Expression, PL/SQL Function Body.

      • Show - Shows the affected elements. Also has the option to Show all the page items on the same line.

      Execute

      • Execute JavaScript Code - Allows you to define or call custom, page specific JavaScript code to use within the dynamic action framework. If you are defining JavaScript code that is specific to just one page, you can also make use of the new page level attribute 'Function and Global Variable Declaration' to define this. Functions and variables defined here can subsequently be referenced from this action.

      • Execute PL/SQL Code - Executes some PL/SQL code on the server. If an error occurs during execution, the user will be alerted.

      Notification

      • Alert - Displays an Alert.

      • Confirm - Displays a confirmation dialog. If the user chooses Cancel then the proceeding actions are not executed and the current event is canceled.

      Style

      • Add Class - Adds 1 or more CSS classes to the affected elements.

      • Confirm - Displays a confirmation dialog. If the user chooses Cancel then the proceeding actions are not executed and the current event is canceled.

      Miscellaneaous

      • Cancel Event - Cancels subsequent dynamic actions or events from firing, based on certain conditions. This could be useful in the following situations:

        If you have multiple dynamic actions on a page that are based on the same event (such as Click) and they refer to the same element on the page, the Cancel Event action can be used to prevent subsequent dynamic actions from firing, conditionally based on it's When Condition.

        If a dynamic action has many true or false actions, Cancel Event can be used to halt subsequent actions from being processed. Currently this has limited value as it is not possible to define a dynamic action's actions to fire conditionally, so this would just always prevent subsequent actions being processed, which could be useful in debugging.

        Used to cancel page submission, when used in conjunction with the Before Page Submit event of dynamic actions. For example, if you define a dynamic action that fires on the Before Page Submit even, and for the Condition define JavaScript Expression, and in the Value' some expression such as $v('P2_ENAME') == 'DO NOT SUBMIT'. Then, if you define a True Action that uses the Cancel Event action, the page will not be submitted when the ENAME field is equal to DO NOT SUBMIT.

      • Submit Page - Submits the page.

      Plug-ins

      • my_plugin_action[Plug-in] - Depending on your application configuration, you may also have additional plug-in dynamic actions available here. These will be displayed as 'my_plugin_action [Plug-in]'. Plug-in dynamic actions are installed within the application's shared components. The plug-in developer will have assigned an appropriate category for the action (Component, Execute and so forth).

    • Fire On Page Load (Advanced only) - Select this option to also trigger this action when the page loads. This may or may not be checked by default, depending on the type of Action selected.

  7. For Affected Elements (available only for actions that support affected elements), specify the elements affected by this dynamic action and how they are affected then click Next.

    The final step in creating the dynamic action is different depending on whether the action you have selected supports selection of affected elements. Some actions, such as Submit Page, do not required affected elements to be selected. Other actions, such as Show, do. If the action selected does not support selection of affected elements, the last page will just be a Confirm page where you can review details of the dynamic action. If the action selected does support selection of affected elements, options on this page include:

    • Selection Type - Select the type of page element that is affected by the dynamic action. Depending on the selected type, additional options display. Use these options to specify additional settings and values required to specify how the element is affected.

    • Item(s), Region, DOM Object, or jQuery Selector - Specify the name of the page element as follows:

      • Item(s) - Select the item name that is affected by the dynamic action.

      • Region - Enter or select the region name that is affected by the dynamic action. The region selected must use a region template that includes a container element with an ID attribute set to #REGION_STATIC_ID#. The dynamic action framework relies on this ID value in order to reference the region.

      • DOM Object - Enter either the Document Object Model (DOM) object or the ID of a DOM object that is affected by the dynamic action.

      • jQuery Selector - Enter the jQuery selector syntax to return one or more page elements that is affected by the dynamic action.

  8. Click Create.

Editing Dynamic Actions

Once you create a dynamic action, you can modify attributes defined during the creation process, specify attributes not available during the process (such as specifying an Authorization Scheme) and add additional true or false actions.

To edit a dynamic action:

  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Access the Edit Dynamic Action page:

    • Tree view - Under Page Rendering, locate Dynamic Actions. Right-click and select Edit.

      You can also go straight to editing a dynamic action's true or false actions by expanding the tree node for the dynamic action, then right-clicking and selecting Edit for the specific action you want to edit.

    • Component view - Under Page Rendering, scroll down to Dynamic Actions and click the dynamic action you want to edit.

    The Edit Dynamic Action page appears.

Adding Additional True or False Actions

Dynamic actions contain a condition that can be specified to control when True and False actions fire. For example, consider an application that displays employee information to managers. If the employee is exempt, their salary displays, otherwise their hourly wage displays.

The Dynamic Action creation wizard allows only one true and one false action to be specified. If you want to add more than one action you must use the create wizard to create the dynamic action and then edit the action to add additional true or false actions.

To add an action:

  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Access the Edit Dynamic Action page:

    • Tree view - Under Page Rendering, locate Dynamic Actions. Right-click and select Edit.

      You can also right-click directly on the True or False nodes right under the dynamic action node in the tree. Select Create here and this will take you directly to the page to create the additional true or false action. This is a simpler means of creation and eliminates the next step.

    • Component view - Under Page Rendering, scroll down to Dynamic Actions and click the dynamic action you want to edit.

    The Edit Dynamic Action page appears.

  3. If you want to add an action that executes when the conditions are met or when no condition is specified, scroll down to True Actions and click Add True Action.

    Otherwise, scroll down to False Actions and click Add False Action.

    The Create/Edit Action page appears.

  4. Under Identification:

    1. Sequence - Enter the sequence for this component. This indicates the evaluation order.

    2. Action - Select the action that triggers when the condition is true or false.

  5. Under Execution Options:

    1. Fire on Page Load - Select if you want the action to also trigger when the page loads.

    2. Stop Execution on Error - Specify whether any proceeding actions should be executed, if an error occurs while executing the current action. Only available when the selected Action has been defined to expose this attribute, for example Execute PL/SQL Code.

  6. For Affected Elements (only available if the selected action supports definition of affected elements):

    Note:

    Some or all of these fields may not be available to select, depending on the type of action selected. For example the Disable action does not support selecting a Region as the affected element.
    1. Selection Type - Select the type of page element that is affected by the dynamic action. A corresponding name field displays to specify the name of the page element.

    2. Item(s), Region, DOM Object, or jQuery Selector - Specify the name of the page element as follows:

      • Item(s) - Enter or select the item name that is affected by the dynamic action.

      • Region - Enter or select the region name that is affected by the dynamic action. The region selected must use a region template that includes a container element with an ID attribute set to #REGION_STATIC_ID#. The dynamic action framework relies on this ID value in order to reference the region.

      • DOM Object - Enter either the Document Object Model (DOM) object or the ID of a DOM object that is affected by the dynamic action.

      • jQuery Selector - Enter the jQuery selector syntax to return one or more page elements that is affected by the dynamic action.

  7. For Comments, enter developer comments or notes. These comments never display when the application is running.

  8. Click Create.

    The Edit Dynamic Action page displays with the added action listed under True Actions or False Actions.

Defining the Frequency and Scope

After creating the dynamic action, the scope of the action can be modified to trigger only once, for the lifetime of the current page, or until triggering elements are updated by a Partial Page Refresh (PPR).

To specify scope:

  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Access the Edit Dynamic Action page:

    • Tree view - Under Page Rendering, locate Dynamic Actions. Right-click and select Edit.

    • Component view - Under Page Rendering, scroll down to Dynamic Actions and click the dynamic action you want to edit.

    The Edit Dynamic Action page appears.

  3. Scroll down to Advanced and for Event Scope make one of the following selections:

    1. bind - Binds the event handler to the triggering elements for the lifetime of the current page, but will no longer be bound if the triggering elements are updated by Partial Page Refresh (PPR).

    2. live - Binds the event handler to the triggering elements for the lifetime of the current page, including any elements that are updated by Partial Page Refresh (PPR).

    3. once - Binds the event handler to the triggering elements for a once only event.

  4. Click Apply Changes.

Debugging Dynamic Actions

Debugging dynamic actions in Application Express is slightly different than other debugging, because much of the processing done with the dynamic action framework is done on the client, not on the server. In order to debug dynamic actions, we output debug information to the browser's JavaScript console, if your browser supports it (for example Firefox with Firebug installed will show the debug information in it's Console pane). The debug information will tell you when a dynamic action fires, the name of the dynamic action and also specifically which action has fired.

To debug a dynamic action:

  1. Ensure the application containing the dynamic action has Debugging enabled. See "Accessing Debugging Mode".

  2. Run the page containing the dynamic action.

  3. Open the browser's JavaScript console.From the Developer toolbar, click Debug.The page refreshes and if you have any dynamic actions that are set to fire on page load, you will see the following text output to the console: "Dynamic Action Fired: [Dynamic action name] ([specific action fired])".Leaving Debug mode switched on (as the debug information is only output when running in Debug mode), you can then further test if dynamic actions are firing when you expect them to. For example if you have defined a dynamic action that fires when a certain item's value changes, change that item's value and the console will show the debug output if the dynamic action fires.

About BLOB Support in Forms and Reports

Oracle Application Express includes declarative BLOB support to enable developers to declaratively upload files in forms, and download or display files in reports. BLOB display and download can also be authored procedurally using PL/SQL. This section describes how to upload, download and display files, and how to manage additional file attributes such as MIME type and file name that are important for proper management of files stored in BLOB columns. Using this functionality you can easily extend your Oracle Application Express applications to manage files including images, documents, videos, and so on.

Topics:

About BLOB in Forms

If you create a Form (either using the Create Application Wizard, create page of type Form - or Report and Form, or create region of type Form) or add an item to an existing form, any item whose source is a database column of type BLOB will result in an item of type File Browse. When the form is called for INSERT, the file selected by the user will be loaded into the BLOB column. When the form is called for update, a download link is displayed to the right of the Browse button. Users can use this link to download the file.

Uploading and Downloading Files into a BLOB

The defaulted BLOB support does not give you all the information a typical application needs to effectively manage a BLOB. In addition to knowing that the column is a BLOB, more information about the file will provide a better experience for the end-user. The File Browse page item has additional settings to facilitate managing this additional information completely declaratively.

See Also:

File Browse in Appendix A, "About Item Types", item level help for the File Browse settings

There are two different types of storage types available within the File Browse item type:

  • BLOB column specific in Item Source Attribute - Completely declarative approach that supports configuration of the additional settings discussed here. This will reference a BLOB in your own database table.

  • Table WWV_FLOW_FILES - Advanced option if you prefer to reference a BLOB stored in the WWV_FLOW_FILES table.

To provide this additional information, it is recommended that you add additional columns to your base table to store and track the MIME type, file name, last updated date and character set settings. You can accomplish this by extending your table. For example:

ALTER TABLE emp ADD 
   (ATTACH_MIMETYPE     VARCHAR2(255),
    ATTACH_FILENAME     VARCHAR2(255),
    ATTACH_LAST_UPDATE  DATE,
    ATTACH_CHARSET      VARCHAR2(128));

Note:

The character set of the BLOB is not automatically set on upload. If you want to store the character set value for your BLOB, you must provide an additional page item on your page which is bound to the column you use to store the character set, and where the user will be able to specify the character set for the document they are uploading.

If you manually create a form on a custom table, you can still take advantage of this feature. To do so, use the File Browse item type with a Storage Type setting of BLOB column specified in Item Source Attribute, on a page with a DML Process type of DML_PROCESS_ROW. This process determines the table name and primary key columns.

Displaying the BLOB

If the BLOB you are working with is an image, you can display it in the form as well. You can use the Display Image item type to handle this declaratively, see Appendix A, "About Item Types" for details. See "Working With BLOBs Procedurally" to handle procedurally.

Removing the Image Reference

Because there is no set to NULL when using File Browse, if you need to provide a mechanism to remove an image reference, you must include a special Remove Image button to nullify the necessary columns. Consider the following example:

UPDATE demo_product_info
   SET product_image = NULL,
       MIMETYPE = NULL,
       FILENAME = NULL,
       IMAGE_LAST_UPDATE = NULL,
       CHARSET = NULL
 WHERE product_id = :P6_PRODUCT_ID;

About BLOB Support in Reports

Oracle Application Express includes BLOB support for both classic and interactive reports. If you use a wizard to create a report and include a column of type BLOB, basic support will be included. Additional information should be added after generation to make the download capability more user friendly.

Providing a Download Link

To facilitate the inclusion of a download link in a report, the report includes the selection of the length of the BLOB (for example, dbms_lob.getlength(RESUME)). If the length is 0, the BLOB is NULL and no download link is displayed. In the same way you specify a format mask for a date or number you can format a download link. The DOWNLOAD format is more complex however then other format masks in that you are required to specify at least three parameters, for example:

DOWNLOAD:EMP:RESUME:EMPNO

The parameters of the DOWNLOAD format are described in the following table:

Position Attribute Required Description
1 DOWNLOAD Yes Identifies the DOWNLOAD report format mask.
2 Table Name Yes Case sensitive name of table containing target column of type BLOB.
3 Column containing BLOB Yes Case sensitive name of column of type BLOB.
4 Primary Key Column 1 Yes Case sensitive name of primary key column 1.
5 Primary Key Column 2 No Case sensitive name of primary key column 2.
6 MIME type Column No Case sensitive column name used to store the MIME type.
7 Filename Column No Case sensitive column name used to store the filename of the BLOB. If NULL, the column name is used as the default when a user downloads the file.
8 Last Update Column No Case sensitive column name used to store the last update date of the BLOB. If used, the HTTP header of the file download indicates the date of last modification and Web browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files.
9 Character Set Column No Case sensitive column name used to store the character set of the BLOB. Most relevant for Asian languages which may need to maintain the character set encoding.
10 Content Disposition No Specify inline or attachment. All other values ignored. If a MIME type is provided and the file is a type that can be displayed, the file will be displayed. If MIME type is not provided, or the file cannot be displayed inline, the user will be prompted to download.
11 Download Text No String used for the download link. If nothing provided, Download is used. Note that this will support substitutions (useful for translated applications).

Consider the following example:

DOWNLOAD:EMP:RESUME:EMPNO::RESUME_MIMETYPE:RESUME_FILENAME:RESUME_LAST_UPDATE::attachment:Resume

If you have a report column with a format mask that begins with DOWNLOAD:, you will see a link below the format 'BLOB Download Format Mask'. This popup assists in entering all the parameters necessary for the DOWNLOAD format.

Displaying the BLOB

If the BLOB you are working with is an image, you can display it in the report as well. To do this, you use the new report format mask of 'IMAGE'. Regardless of the MIME type, the report will always attempt to display the BLOB. If the BLOB cannot be rendered, a broken image will be displayed.

The parameters of the IMAGE format mask are described in the following table:

Position Attribute Required Description
1 IMAGE Yes Identifies the IMAGE report format mask.
2 Table Name Yes Case sensitive name of table containing target column of type BLOB.
3 Column containing BLOB Yes Case sensitive name of column of type BLOB.
4 Primary Key Column 1 Yes Case sensitive name of primary key column 1.
5 Primary Key Column 2 No Case sensitive name of primary key column 2.
6 MIME type Column No Case sensitive column name used to store the MIME type.
7 Filename Column No Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.
8 Last Update Column No Case sensitive column name used to store the last update date of the BLOB. If used, the HTTP header of the file download indicates the date of last modification and Web browsers will be able to cache the BLOB. If not specified, the browser may not be able to cache files.
9 Character Set Column No Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.
10 Content Disposition No Not used for IMAGE format but left in so that the format can easily be changed between IMAGE and DOWNLOAD.
11 Alt Text No String used for the alt tag associated with the image.

Consider the following example:

IMAGE:EMP:RESUME:EMPNO::RESUME_MIMETYPE:RESUME_FILENAME:RESUME_LAST_UPDATE::attachment:Resume

If you have a report column with a format mask that begins with 'IMAGE:', you will see a link below the format 'BLOB Download Format Mask'. This popup assists in entering all the parameters necessary for the IMAGE format.

Working With BLOBs Procedurally

As an alternative to using the built-in methods of providing a download link, you can use the APEX_UTIL.GET_BLOB_FILE_SRC function. One advantage of this approach, is the ability to more specifically format the display of the image (with height and width tags). Please note that this approach is only valid if called from a valid Oracle Application Express session. Also, this method requires that the parameters that describe the BLOB to be listed as the format of a valid item within the application. That item is then referenced by the function.

See Also:

"GET_BLOB_FILE_SRC Function" in the Oracle Application Express API Reference

About Screen Reader Mode

Sessions in APEX can now be identified as optimized for screen readers, both in the Application Express development environment, Websheet runtime and also within your own database applications. By default sessions are not flagged as running in screen reader mode.

Although this mode improves usability of Application Express with a screen reader, there may still be some areas where outstanding issues remain. For a full list of outstanding issues with workarounds where possible, please refer to the release notes.

Topics:

What Screen Reader Mode Does

Enabling Screen reader mode currently does the following, by default:

  • Application Express Flash charts are not currently accessible to screen readers, therefore when running in screen reader mode the user will get a report representation of the information conveyed in the chart. A separate report will be generated for each series of a multiple-series chart. When running in screen reader mode, these data tables contain descriptive text, in the following format:

    • Summary Text - In Websheets, the section title is used as the summary text.

    • Summary Text - In Application Builder, a combination of the chart title and chart series title are used.

    • Column Headers - In Websheets, the Data Grid report labels are used to identify the columns in the report.

    • Column Headers - In Application Builder, the column name/alias in the chart series query is used to identify the columns in the report.

      Note:

      Only Flash chart 5 supports this. For users wishing to view their Flash 3 chart data in Screen Reader mode, they must first upgrade their Flash chart 3 charts to Flash chart 5, using the Upgrade Application. Once the Flash 3 charts have been upgraded, and the user is running in Screen Reader mode, a report on the Flash Chart chart series query is displayed.
  • Radio and checkbox page items are rendered within a FIELDSET HTML tag. FIELDSET elements require a text description that is used as context for any elements within it, for example the individual radio inputs. This is done using the LEGEND HTML tag. When running in screen reader mode, this tag is generated and is given the value of the page item's Label.

  • Interactive report regions heavily rely on partial page refresh during user interaction to specify a filter, search, sort and so on. In order for a screen reader user to be notified that the page has been partially refreshed, define Interactive Report Regions as WAI-ARIA Live regions, when running in screen reader mode. This means the user is notified when the partial page refresh is complete. This uses a polite setting, meaning that the screen reader announces the update only when they are not doing anything else on the page. You must be using both a screen reader and browser that supports WAI-ARIA Live Regions. Application Express is only tested with the most recent JAWS screen reader (11.0.1430), which supports Live Regions. The current list of browsers that support this include:

    • Firefox 3.0 and later

    • IE version 8

  • Interactive report regions in Report View or Group By View use data tables to convey information. Data tables require a text description that describes what information the table contains to a screen reader user. When running in screen reader mode, these data tables now contain detailed summary text, in the following format:

    • Region - The name of the region defined by the developer.Report - The name of the current Saved Report within the Interactive Report. Default to Primary Default if no saved report is defined.View - The current view of the report (Either Report or Group By).Displayed Rows Start - The starting row of the currently displayed set of rows.Displayed Rows End - The ending row of the currently displayed set of rows.Total Rows - The total number of rows returned by the report.

  • Interactive report regions provide the ability to add Aggregates to your report data. One of the available aggregates is to Sum a column's values. When running in screen reader mode, the text Sum: is displayed as a prefix to the summed value. In standard mode only the summed value is displayed. All other aggregates display an aggregate type prefix by default, regardless of mode.

  • Item level help text is provided differently in screen reader mode. In standard mode, a dialog is displayed on your page displaying the help text for a specific item. But as these dialogs have many problems when used with screen readers, therefore the classic popup page is used to display item help text when running in screen reader mode.

  • In the Application Builder of the Application Express development environment, the Page Definition page used to manage page information contains 2 different views, Component View and Tree View. The tree used in Tree View is not accessible using a screen reader and therefore, when running this page in screen reader mode the default is Component View.

Enabling Screen Reader Mode

Please refer to "Accessibility in Oracle Application Express" in Oracle Application Express Installation Guide for details on how to enable this mode in our development environment and websheet runtime environment.

Provisioning Screen Reader Mode

There are three ways you can provision this mode to user's of your own database applications.

  1. Page template #SCREEN_READER_TOGGLE# substitution string - Add this substitution string to your page template and Application Express displays a link to the current page to turn on or off (toggle) the mode. So if you are in standard mode, this procedure generates a link to turn it on.

  2. There are also APIs that can be used to control this mode. You may want to use the APIs if you only want to render the toggle in one place and don't want to do this at page template level or if you want more control over the actual displayed link text. Please see the Oracle Application Express API Reference for more details on these APIs.

  3. Using f?p syntax REQUEST attribute to enable and disable screen reader mode. The general syntax for f?p is f?p=application:page:session:request:...

    If the request is exactly SET_SESSION_SCREEN_READER_ON or SET_SESSION_SCREEN_READER_OFF then the session is put into or out of screen reader mode. For example:

    <a href="f?p=100:1:&SESSION.:SET_SESSION_SCREEN_READER_ON">Set Screen Reader On</a>
    

Extending Screen Reader Mode

In addition to what this mode does by default as detailed in What Screen Reader Mode Does section above, you can control your own page components, such as conditionally displaying a region when running in screen reader mode. Please see the Oracle Application Express API Reference for details of the relevant database functions, APEX_UTIL.IS_SCREEN_READER_SESSION and APEX_UTIL.IS_SCREEN_READER_SESSION_YN.