Skip Headers
Oracle® Database Application Express User's Guide
Release 2.2

Part Number B28550-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

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

5 Building an Application

This section describes how to use Application Builder to build an application and application components. It includes instructions for creating an application and adding pages as well as adding components (reports, charts, or forms), page controls (buttons, items, list of values), and shared components (breadcrumbs, lists, or tabs).

This section contains the following topics:

See Also:

Creating an Application

An application is a collection of pages that share a common session state and authentication. You create a new application in Application Builder using a wizard. You delete an application from the Application Builder home page.

Topics in this section include:

About Creating an Application Using a Wizard

When you click Create on the Application Builder home page, you must choose one of the following options:

  • Create Application. Creates an application based on SQL queries or database tables. You can define blank pages or pages that contain reports, forms, tabular forms, or a report with a linked form. See "About the Create Application Wizard".

  • Create Application from Spreadsheet. Creates an application based on spreadsheet data. You can upload or paste spreadsheet data to create a table and then add a user interface. In the resulting application, users can create queries, add, insert, or update records, or analyze the data. See "About the Create Application from Spreadsheet Wizard".

  • Demonstration Application. Installs or uninstalls demonstration applications. Use demonstration applications to learn how to build applications. See "About Demonstration Applications".

See Also:

"Adding Pages to an Application" for information about adding reports and forms by creating a new page

About the Create Application Wizard

The Create Application wizard enables you to create a fully functional application based on any number of tables. You can use the Create Application Wizard to create blank pages, or pages based on SQL queries or database tables. You can create SQL queries by manually typing SQL or by using the graphical user interface of Query Builder. Applications based on tables can consist of a simple report, a form and report, or a tabular form. When creating pages on tables, you have the option to generate analysis pages. Analysis pages extend a simple report or a report on a form to include multiple drill-down reports and charts.

Topics in this section include:

Creating an Application Based on Tables or Queries

You can create an application based on a table, query, or drill-down query by selecting Create Application in the Create Application Wizard.

To create an application based on a table, query, or drill-down query:

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

  2. Click the Create button.

  3. For Method, select Create Application and click Next.

  4. For Name, enter the following application details and click Next:

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

    2. Application - Enter a unique integer value to identify the application.

    3. Create Application - Select a creation method:

      • Select From scratch to manually add all pages

      • Select Based on existing application design model to copy page definitions from a previous application model.

        Note that you will still have to define all other application attributes, or you can choose to copy some attributes by choosing to copy shared components from another application (See step 7 and "About Application Models and User Interface Defaults").

    4. Schema - Your application will obtain its privileges by parsing all SQL as a specific database schema. Identify the database schema owner.

    Next, add pages to your application.

  5. For Pages:

    1. Select the type of page you want to add. Options include:

      • Blank creates a page with no built-in functionality.

      • Report creates a page that contains the formatted result of a SQL query. You can choose to build a report based on a table you select, or based on a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement that you provide.

      • Form creates a form to update a single row in a database table.

      • Tabular Form creates a form to perform update, insert, and delete operations on multiple rows in a database table.

      • Report and Form builds a two page report and form combination. On the first page, users select a row to update. On the second page, users can add a new record or update or delete an existing record.

      Action displays the currently selected page type. For each selection, the wizard prompts you for a variety of different types of information.

      Report pages include the Include Analysis Pages check box. Select this option and follow the wizard prompts to extend a simple report or a report on a form to include multiple drill-down reports and charts.

    2. Click Add Page.

      The page (or pages) appear at the top of the page. To delete a page, click Delete icon.

    3. Repeat the previous steps until all pages have been added.

    4. Click Next.

  6. Determine whether to include tabs in your application and click Next.

  7. Determine whether to import shared components from another application. Shared components are common elements that can display or be applied on any page within an application.

    To include shared components:

    1. From Copy Shared Components from Another Application, select Yes.

    2. From Copy from Application, select the application from which you want to import shared components.

    3. From Select Components to Import, select the components to import.

    4. Click Next.

    Next, select a default authentication scheme. Authentication is the process of establishing users' identities before they can access an application. See "Establishing User Identity Through Authentication".

  8. For Authentication Scheme, select one of the following:

    • Application Express - Uses the user account credentials created and maintained with the Application Express Service Administration application. These are the accounts you use to log in to the Application Express development environment. You can also create accounts in this user account repository for end users of your applications.

    • No Authentication - Also known as database authentication, this option enables users to access your application using the account credentials stored in the modplsql DAD definition. In most cases this results in users not having to login when accessing your application. This is the quickest way to create a "public" application.

    • Database Account - Requires users logging into your application to enter a database schema name (or username) and a password in order to authenticate. This account information is managed entirely within the Oracle database.

  9. Next, select the following globalization preferences:

    1. Language - Select the primary language for this application.

      This attribute identifies the language in which an application is developed. This language is the base language from which all translations are made.

    2. User Language Preference Derived From - Specifies how the engine determines the application language. The application primary language can be static (that is, derived from the Web browser language) or determined from a user preference or item. The database language setting determines date display and sorting characteristics.

      You can alter the Language and User Language Preference Derived From attributes later on the Edit Globalization attributes page. See "Configuring Globalization Attributes".

    3. Click Next.

  10. Select a theme and click Next.

    Themes are collections of templates that can be used to define the layout and style of an entire application. See "Managing Themes".

  11. Confirm your selections and click Create.

About Application Models and User Interface Defaults

The Create Application Wizard is designed with the assumption that the developer may run it multiple times. To facilitate this iterative approach to application development, every time you run the wizard it saves the page definitions to an application model.

Consider the following example. You create a new application by running the Create Application Wizard. After viewing the application, you realize it is not quite what you wanted. Instead of altering it, you can run the wizard again and select an application model. By selecting an existing application model when you rerun the wizard, you can quickly improve your application with minimal time and effort.

Another way to increase your productivity when creating an application is to specify user interface defaults. User interface defaults are metadata that enable you to assign default user interface properties to a table, column, or view within a specified schema.

Leveraging Application Models and User Interface Defaults

You can increase your productivity when creating applications by leveraging application models and user interface defaults. Consider the following scenario:

  1. Create an application based on tables or views by running the Create Application Wizard.

  2. Run the generated application. Note any functional deficiencies.

  3. Evaluate whether to create or edit user interface defaults.

    For example, you can use user interface defaults to control how form field or report labels display. You can also utilize user interface defaults to display specific columns or have columns display in an alternate order.

  4. Navigate to the Application home page and create a new application by clicking Create.

  5. Select Create Application.

  6. When prompted to enter application details, specify the following:

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

    2. Application - Enter a unique integer value to identify the application, or accept the default.

    3. Create Application - Select Based on existing application design model.

  7. Select an application model.

    Note the pages you previously created already appear.

  8. Add pages, edit pages, or remove pages.

  9. Complete the wizard.

  10. Repeat steps 2 through 9 until the application meets your functional requirements.

About the Create Application from Spreadsheet Wizard

You can create an application based on spreadsheet data by selecting Create from Spreadsheet in the Create Application Wizard.

To create an application from spreadsheet data:

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

  2. Click the Create button.

  3. Select Create from Spreadsheet.

  4. Specify how spreadsheet data will be uploaded. Select one of the following and click Next:

    1. Upload file, comma separated (*.csv) or tab delimited. Specify the following and click Next:

      • Text File - Click Browse to locate the file to be uploaded.

      • Separator - Specify the column separator character. Use \t for tab separators.

      • Optionally Enclosed By - Enter a delimiter character. You can use this character to deliminate the starting and ending boundary of a data value. If you specify a delimiter character, the wizard ignores white space occurring before the starting and ending boundary of a data value. You can also use this option to enclose a data value with the specified delimiter character.

      • File Character Set - Choose the character set in which the text file is encoded.

    2. Copy and paste (up to 30KB). Copy and paste the spreadsheet data you wish to import and click Next.

  5. Review the preview of how your table will display and click Next. Specify the table name and column names, modify the data types, or specify which columns to include.

  6. Review the displayed Singular Name and enter a Plural Name.

    Column User Interface Defaults display default label names.

  7. (Optional) Under Column User Interface Defaults, edit the displayed Label names and click Next.

  8. For Summary By Column, select the columns for which data will be summarized in reports and charts and click Next.

  9. Select Application Options:

    1. Application Name - Enter an alphanumeric name for this application.

    2. Specify a Create Mode:

      • Read and Write includes insert and update pages.

      • Read Only does not include insert and update pages.

    3. Select a chart type.

    4. Click Next.

  10. Select a theme and click Next.

    Themes are collections of templates that can be used to define the layout and style of an entire application. See "Managing Themes".

  11. Confirm your selections and click Create.

About Demonstration Applications

Oracle Application Express installs with a number of demonstration applications. Use these applications to learn more about the different types of functionality you can include in your applications.

Accessing Demonstration Application

To access demonstration applications:

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

  2. Click the Create button.

  3. Select Demonstration Application.

    The Demonstration Applications page appears, displaying links to the following applications:

    • Sample Application offers a working demonstration that highlights basic design concepts

    • Collection Showcase demonstrates shopping cart concepts

  4. To install a demonstration application, locate the application you want to install and click Install.

  5. Follow the on-screen instructions.

    The Application home page appears.

  6. To run an installed demonstration application, click the Run Application icon.

  7. Enter the appropriate login credentials and click Login.

    • For Sample Application:

      • For User Name, enter either demo or admin

      • For Password, enter current workspace name in lowercase letters

    • For other demonstration applications, enter your workspace user name and password.

Copying an Application

To copy an application:

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

  2. Select an application.

    The Application home page appears.

  3. Click Copy this Application on the Tasks list. See "About the Tasks List".

  4. On Copy Application:

    1. Enter a new application ID.

    2. Enter a new application name.

    3. Specify whether or not to copy deployment attributes.

    4. Click Next.

  5. Click Copy Application.

Deleting an Application

You can delete an application from within Application Builder, or while editing application attributes. If you delete an application you also delete all defined components (reports, charts, or forms), page controls (buttons, items, list of values), and shared components (breadcrumbs, lists, and tabs, but not user interface defaults).

Topics in this section include:

Deleting an Application from Application Builder

To delete an application from Application Builder:

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

  2. Select an application.

  3. When Application Builder appears, verify the application ID and name at the top of the page.

  4. On the Tasks list, click Delete this Application.

  5. When prompted, click Permanently Delete Now.

Deleting an Application from the Edit Definition Page

To delete an application from the Edit Definition page:

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

  2. Select an application.

  3. Click Edit Attributes.

  4. Click Definition.

    The Edit Definition page appears.

  5. Verify the application ID and name.

  6. Click Delete at the top of the page.

  7. When prompted, click Permanently Delete Now.

Adding Pages to an Application

You can add a new page or add a component to an existing page by running the Create Page Wizard. You can access this wizard by:

Note:

You can also use the Create Page Wizard to add a component (that is, a report, chart, form, wizard, a calendar, or tree) to an existing page. When prompted, specify an existing page number.

Topics in this section include:

Creating a Page from the Application Home Page

To create a new page from the Application home page:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Click the Create button at the top of the page.

  3. Select the type of page you want to create:

    • New Page

    • Region on this page

    • Page control on this page

    • Shared Control

    Table 5-2 describes the various selections available based on the type of page you select.

    Table 5-1 Create Page Options

    Create Page Options Available Selections

    New Page

    Available page types:

    • Blank page

    • Multiple blank pages

    • Report

    • Chart

    • Form

    • Wizard

    • Calendar

    • Tree

    • Login Page

    • Access control

    Region on this page

    Regions function as containers for content. Available region types:

    • HTML

    • Report

    • Form

    • Chart

    • Breadcrumb

    • PL/SQL Dynamic Content

    • Tree

    • URL

    • Calendar

    • Multiple HTML

    • Help Text

    Page control on this page

    Page controls:

    • Item

    • Button

    • Branch

    • Computation

    • Process

    • Validation

    Shared control

    Shared component options:

    • Navigation Bar icon

    • Parent tab

    • Standard tab

    • List of values

    • List

    • Breadcrumb


  4. Follow the on-screen instructions.

Creating a Page from the Page Definition

To create a new page while viewing a Page Definition:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Click the Create button at the top of the page.

  3. Select the type of page you want to create:

    • New Page

    • Region on this page

    • Page control on this page

    • Shared Control

    Table 5-2 describes the various selections available based on the type of page you select.

    Table 5-2 Create Page Options

    Create Page Options Available Selections

    New Page

    Available page types:

    • Blank age

    • Multiple blank pages

    • Report

    • Chart

    • Form

    • Wizard

    • Calendar

    • Tree

    • Login Page

    • Access control

    Region on this page

    Regions function as containers for content. Available region types:

    • HTML

    • Report

    • Form

    • Chart

    • Breadcrumb

    • PL/SQL Dynamic Content

    • Tree

    • URL

    • Calendar

    • Multiple HTML

    • Help Text

    Page control on this page

    Page controls:

    • Item

    • Button

    • Branch

    • Computation

    • Process

    • Validation

    Shared control

    Shared component options:

    • Navigation Bar icon

    • Parent tab

    • Standard tab

    • List of values

    • List

    • Breadcrumb


  4. Follow the on-screen instructions.

Creating a Page from the Developer Toolbar

To view a rendered version of your application, you run or submit it to the Application Express engine by clicking the Run or Run Application icon.

When you run an application, the Developer toolbar appears at the bottom of the page. The Developer toolbar offers a quick way to edit the current page, create a new page, region, or page control, view session state, or turn edit links on and off. You can control whether the Developer toolbar displays by changing the Status attribute on the Edit Definition page.

See Also:

"Configuring the Application Definition" for information on the Status list

To create a new page from the Developer toolbar:

  1. Run the application. See "Running a Page or Application".

  2. On the Developer toolbar, click Create.

    The New Component Wizard appears.

  3. Select the type of component you want to create and click Next. Available options include:

    • New Page

    • Region on this page

    • Page control on this page

    • Shared control

      Table 5-2 describes the various selections available based on the type of page you select.

  4. Follow the on-screen instructions.

Copying a Page

You can copy a page from the current application or from another application. During the copy process, you can also copy shared components or change mappings to shared components in the target application.

To copy a page:

  1. Navigate to the application you want to copy to:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Click the Copy button.

  3. For Copy Page Option, select one of the following:

    • Page in this application

    • Page in another application

  4. Follow the on-screen instructions.

Running a Page or Application

The Application Express engine dynamically renders and processes pages based on data stored in database tables. To view a rendered version of your application, you run or submit it to the Application Express engine. As you create new pages, you can run them individually, or run an entire application. You can run an application by clicking the Run Application icon.

Topics in this section include:

About the Run Application and Run Page Icons

The Run Application icon resembles a large traffic light and displays on the Application home page. Clicking the Run Application icon runs an entire application.

Description of run_ico_sm.gif follows
Description of the illustration run_ico_sm.gif

The Run Page icon resembles a small, light green traffic light and displays in the upper right corner of many pages within Application Builder. Clicking the Run Page icon runs the current page.

Description of run_ico_green.gif follows
Description of the illustration run_ico_green.gif

Running an Application from the Application Builder Home Page

To run an entire application from the Application Builder home page:

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

  2. From the View list, select Details and click Go.

  3. Locate the application in the Applications list.

  4. Click the Run icon in the far right column.

Running an Application from the Application Home Page

To run an entire application from the Application home page:

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

    The Application Builder home page appears.

  2. Select on application.

  3. Click the Run Application icon at the top of the page.

Running a Page on the Application Home Page

You can control how the Application home page displays by making a selection from the View list on the navigation bar at the top of the page. Selecting Details displays each page as a line in a report. Each line includes the page number, the page name, when the page was last updated, who last updated the page, a lock icon, and a Run icon.

To run a page from the Pages list:

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

  2. Select an application.

  3. From the View list, select Details and click Go.

  4. From the Pages list, locate the page you want to run and click the Run icon in the far right column.

Running a Page from the Page Definition

To run a specific page from the Page Definition:

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

  2. Select an application.

  3. Select a page.

    The Page Definition appears.

  4. Click the Run Page icon in the upper right corner of the page.

Grouping Pages

Use page groups to organize and manage the pages within an application. To use page groups, you create a group and then assign pages to the group.

Page groups do not have any function other than to help developers organize their application pages.

You can make the pages within your application easier to access by organizing them into page groups.

Topics in this section include:

Viewing the Page Group Report

The Page Group report offers a comprehensive list of which pages in an application are assigned to a group and which pages are unassigned.

Viewing Page Groups from the Application Home Page

To view the Page Group report from the Application home page:

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

  2. Select the application.

  3. On the Tasks List on the right side of the page, click Manage Page Groups.

  4. On the Tasks list, click Report Page Groups.

Note:

The Tasks list only appears if groups currently exist.
Viewing Page Groups from the Page Definition

To view page groups from the Page Definition:

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

  2. Select an application.

  3. Select a page.

    The Page Definition appears.

  4. From the View list, select Groups and click Go.

Creating a Page Group

To create a page group:

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

  2. Select an application.

  3. On the Tasks List on the right side of the page, click Manage Page Groups.

  4. On the Page Groups page, click Create.

  5. Enter a name, a description (optional), and click Create.

Assigning Pages to a Page Group

To assign pages to a page group:

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

  2. Select an application.

  3. On the Tasks List on the right side of the page, click Manage Page Groups.

  4. On the Tasks list, click Report Unassigned Pages.

    The Unassigned Pages page appears.

  5. From Page Group, select a group to which you want to assign pages.

  6. Select the pages to be assigned.

  7. Click Assigned Checked.

    Selecting the page number takes you to the Page Attributes page. Selecting the Page Name links to the Page Definition.

Locking and Unlocking a Page

You can prevent conflicts during application development by locking pages in your application. By locking a page, you prevent other developers from editing it.

Topics in this section include:

Determining If a Page Is Locked

A lock icon indicates whether a page is currently locked. If a page is unlocked, the icon appears as an open padlock. If the page is locked, the icon appears as a locked padlock. A lock icon appears on the Application home page and on the Page Definition.

To view the lock icon on the Application home page, select Details from the View list. A list of pages appears. The lock icon appears under the Lock column.

Description of lock_ico.gif follows
Description of the illustration lock_ico.gif

The lock icon also appears on the Page Definition on the far right side of the page across from the breadcrumb menu. See "Understanding the Page Definition".

Locking a Page

You can lock pages from the Page Locks page, the Pages list, and from a Page Definition.

Locking a Page from the Page Locks Page

To lock a page from the Page Locks Page:

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

  2. Select an application.

  3. On the Tasks List on the right side of the page, click Manage Page Locks.

    The Page Locks page appears.

  4. Select the appropriate pages and click Lock Checked.

  5. Enter a comment in the Comment field.

  6. Click Lock Page(s).

Locking a Page from the Pages List

To lock a page from the Pages list:

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

  2. Select an application.

  3. From the View list, select Details and click Go.

  4. In the Pages list, locate the page you want to lock and click the Lock icon.

  5. Enter a comment in the Comment field.

  6. Click Lock Page(s).

Locking a Page from the Page Definition

To lock a page from the Page Definition:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Click the Lock icon in the upper right corner above Shared Components.

  3. Select the appropriate pages and click Lock Checked.

  4. Enter a comment in the Comment field.

  5. Click Lock Page(s).

Unlocking Pages

Only the developer who locked a page can unlock it. However, a developer with administrative privileges can unlock pages locked by other developers.

Unlocking Pages from the Page Locks Page

To unlock a page from the Page Locks page:

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

  2. Select the application.

  3. On the Tasks List on the right side of the page, click Manage Page Locks.

    The Page Locks page appears.

  4. Select the appropriate pages.

  5. Click UnLock Checked.

Unlocking Pages from the from the Pages List

To unlock a page from the Pages list:

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

  2. Select an application.

  3. From the View list, select Details and click Go.

  4. In the Pages list, locate the page you want to unlock and click the Lock icon.

    The Edit Lock Comment page appears.

  5. Click UnLock.

Unlocking Pages from the Page Definition

To unlock pages from the Page Definition:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Click the Lock icon in the upper right corner above Shared Components.

    The Page Locks page appears.

  3. Select the Page you want to unlock and click Unlock Checked.

Accessing Alternative Locked Pages Views

You can access a number of different views of Locked Pages on the Locked Pages page.

To access different views of locked pages:

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

  2. Select the application.

  3. On the Tasks List on the right side of the page, click Manage Page Locks.

  4. On the Tasks list, click one of the following:

    • Show Locked Pages displays only locked pages within the current application.

    • Show All Pages displays all pages within the current application.

    • Show Unlocked Pages displays only unlocked pages within the current application.

    • Administer Locks enables an administrator to unlock any pages locked by a developer.

Deleting a Page

You can delete a page from the Page Definition or while editing page attributes.

Topics in this section include:

Deleting a Page from the Page Definition

To delete a page from the Page Definition:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Verify the page name.

  3. Click the Delete button at the top of the page.

  4. Follow the on-screen instructions.

See Also:

"Editing a Page Definition" for information about editing page attributes

Deleting a Page While Editing Page Attributes

To delete a page while editing page attributes:

  1. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Page Rendering, locate the section with the title of Page.

  3. Click the Edit page attributes icon to link to Page Attributes page. This icon resembles a small page with a pencil on top of it.

    Description of pg_def_edit_pg_att.gif follows
    Description of the illustration pg_def_edit_pg_att.gif

    The Edit Definition page appears.

  4. Verify the page number and page name.

  5. Click Delete.

  6. Follow the on-screen instructions.

Adding Developer Comments

You can add developer comments to an application, a page, or a group of pages using the Developer Comment icon. The Developer Comment icon resembles a green balloon. This icon displays beneath the Utilities tab in the upper right corner of pages in Application Builder.

Description of comment_ico.gif follows
Description of the illustration comment_ico.gif

You can use developer comments to communicate application changes, report issues, or record developer suggestions.

This section contains the following topics:

Adding Developer Comments to an Application or Page

The Developer Comment icon displays on any page in Application Builder that is related to a specific application or application page. You can add developer comments to an application, a page, or a group of pages.

To add a developer comment:

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

  2. Select an application.

  3. Click the Developer Comment icon.

    The Create Comment page displays in a separate window.

  4. In Relevant Page(s), specify the pages to which the comment applies. To enter a comment that:

    • Applies to a specific page, enter the page number.

    • Applies to multiple pages, enter a comma delimited list of pages. For example:

      1, 2, 3 
      
      
    • Does not apply to a page or group of pages, leave this field blank.

  5. In Comment, enter up to 4000 characters of text.

  6. Click Create or Create Another.

Viewing and Editing Developer Comments

To edit a developer comment:

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

  2. Select an application.

  3. Click the Developer Comment icon.

    The Create Comment page displays in a separate window.

  4. Click View Comments.

    The View Comments page appears. See "About the Navigation Bar on the View Comments Page".

  5. To edit a comment, click the Edit icon.

    The Edit Comment page appears.

    1. In Relevant Page(s), specify the pages to which the comment applies. To enter a comment that:

      • Applies to a specific page, enter the page number.

      • Applies to multiple pages, enter a comma delimited list of pages. For example:

        1, 2, 3 
        
        
      • Does not apply to a page or group of pages, leave this field blank.

    2. In Comment, enter up to 4000 characters of text.

  6. Click Apply Changes.

About the Navigation Bar on the View Comments Page

A navigation bar appears at the top of the View Comments page and contains the following controls:

  • Page. Search for a page number. Enter a page number and click Go. To view all pages, leave the field blank and click Go.

  • Comment. Search for comments. Enter a case insensitive query for comment text and click Go. To view all comments, leave the field blank and click Go.

  • Developer. Limit the display to a specific developer. Select a developer to display and click Go.

  • Display. Determine how comments display. To increase or decrease the number of comments that appear, make a selection from the Display list and click Go.

Deleting Developer Comments

You can delete developer comments on the Edit Comment page or on the Manage Comments page.

Topics in this section include:

Deleting a Specific Developer Comment

To delete a developer comment:

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

  2. Select an application.

  3. Click the Developer Comment icon.

    The Create Comment page displays in a separate window.

  4. Click View Comments.

  5. Locate the comment to be deleted. See "About the Navigation Bar on the View Comments Page".

  6. Click the Edit icon.

    The Edit Comment page appears.

  7. Click Delete.

Deleting Multiple Developer Comments

To delete multiple developer comments:

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

  2. Select an application.

  3. Click the Developer Comment icon.

    The Create Comment page displays in a separate window.

  4. Click Manage Comments.

  5. Select one of the following actions:

    • Delete all comments

    • Delete comments created by a developer

    • Delete comments by date

  6. Follow the on-screen instructions.

About the Developer Comments Report

You can also view, edit, and manage developer comments from the Developer Comments report.

Topics in this section include:

Accessing the Developer Comments Report

To access the Developer Comments report:

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

  2. Select an application.

  3. On the Application home page, click View Application Reports on the Tasks list.

  4. Click Application.

  5. Click Developer Comments.

    The Developer Comments report appears.

About the Navigation Bar on the Developer Comments Report

A navigation bar appears at the top of the page and contains the following controls:

  • Page. Search for a page number. Enter a page number and click Go. To view all pages, leave the field blank and click Go.

  • Comment. Search for comments. Enter a case insensitive query of comment text and click Go. To view all, leave the field blank and click Go.

  • Developer. Limit the display to a specific developer. Select a developer to display and click Go.

  • Date. Limit the display to a specific date. Select a date and click Go.

  • Display. Determine how comments display. To increase or decrease the number of comments that appear, make a selection from the Display list and click Go.

Editing Comments from the Developer Comments Report

To edit a comment from the Developer Comments report:

  1. Navigate to the Developer Comments report. See "Accessing the Developer Comments Report".

    The Developer Comments report appears.

  2. Locate the comment to be edited. See "About the Navigation Bar on the Developer Comments Report".

  3. Click the Edit icon.

    The Edit Comment page displays in a separate window.

    1. In Relevant Page(s), specify the pages to which the comment applies. To enter a comment that:

      • Applies to a specific page, enter the page number.

      • Applies to multiple pages, enter a comma delimited list of pages, for example:

        1, 2, 3 
        
        
      • Does not apply to a page or group of pages, leave this field blank.

    2. In Comment, enter up to 4000 characters of text.

  4. Click Apply Changes.

Deleting a Comment from the Developer Comments Report

To delete a comment from the Developer Comments report:

  1. Navigate to the Developer Comments report. See "Accessing the Developer Comments Report".

    The Developer Comments report appears.

  2. Locate the comment to be deleted. See "About the Navigation Bar on the Developer Comments Report".

  3. Click the Edit icon.

    The Edit Comment page displays in a separate window.

  4. Click Delete.

Deleting Multiple Comments from the Developer Comments Report

To delete multiple comments from the Developer Comments report:

  1. Navigate to the Developer Comments report. See "Accessing the Developer Comments Report".

    The Developer Comments report appears.

  2. Locate the comment to be deleted. See "About the Navigation Bar on the Developer Comments Report".

  3. Click the Edit icon.

    The Edit Comment page displays in a separate window.

  4. Click Manage Comments.

  5. Select one of the following actions:

    • Delete all comments

    • Delete comments created by a developer

    • Delete comments by date

  6. Follow the on-screen instructions.

  7. Click Apply Changes.

Downloading the Developer Comments Report

You can download the Developer Comments report as a comma-delimited file (.csv) file by clicking the Export link.

To download the Developer Comments report:

  1. Navigate to the Developer Comments report. See "Accessing the Developer Comments Report".

    The Developer Comments report appears.

  2. Click the Export link at the bottom of the page.

  3. Follow the on-screen instructions.

Controlling Access to Applications, Pages, and Page Components

You can control access to an application, individual pages, or page components by creating an access control list.

This section contains the following topics:

How the Access Control List Works

You create an access control list by creating a new page with a page type of Access Control. Selecting this page type prompts the Access Control Wizard to appear. The Access Control Wizard leads you through the process to create the new page named Access Control Administration. This page contains a list of application modes and an Access Control List. Once you create the Access Control Administration page, you:

  1. Run the Access Control Administration page.

  2. Select one of the following application modes:

    • Full access to all, access control list not used.

    • Restricted access. Only users defined in the access control list are allowed.

    • Public read only. Edit and administrative privileges controlled by access control list.

    • Administrative access only.

  3. Add users to the Access Control List.

In addition to creating the Access Control Administration page, the Access Control Wizard also creates two tables within the application's default schema to manage the Access Control and authorization schemes that correspond to the application mode list options and the privileges available in the Access Control List. You can control access to a specific page or page component by selecting one of these authorization schemes on the page or component attributes pages. Once you create an Access Control, you can customize the page, tables and values to suit the specific needs of your application.

Creating an Access Control List

You create an access control list by creating a new page. You can create a new page on the Application home page, while viewing a Page Definition, or by clicking Create on the Developer toolbar.

Topics in this section include:

Creating an Access Control from the Application Home Page

To create an access control list from the Application home page:

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

  2. Select an application.

    The Application home page appears.

  3. Click Create Page.

  4. For page type, select Access Control and click Next.

    The Access Control Wizard appears.

  5. Specify a page number and click Next.

  6. Select a tab option and click Next.

  7. Review the confirmation page and click Finish.

Creating an Access Control from the Page Definition

To create an access control list from the Page Definition:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Click the Create button next to the navigation bar at the top of the page.

  3. Select New page and click Next.

  4. For page type, select Access Control and click Next.

    The Access Control Wizard appears.

  5. Specify a page number and click Next.

  6. Select a tab option and click Next.

  7. Review the confirmation page and click Finish.

Creating an Access Control List from the Developer Toolbar

To create an access control list from the Developer toolbar:

  1. Run the application. See "Running a Page or Application".

  2. On the Developer toolbar, click Create.

    The New Component Wizard appears.

  3. Select New page and click Next.

  4. For page type, select Access Control and click Next.

    The Access Control Wizard appears.

  5. Specify a page number and click Next.

  6. Select a tab option and click Next.

  7. Review the confirmation page and click Finish.

Selecting an Application Mode and Adding Users

You can control access to an application by running the Access Control Administration page, selecting an application mode, and then adding users to the Access Control list.

This section contains the following topics:

Selecting an Application Mode

To select an application mode:

  1. Create an access control list. See "Creating an Access Control List".

    The wizard creates a new page named Access Control Administration.

  2. Run the Access Control Administration page. See "Running a Page or Application".

  3. Select an Application Mode. Options include:

    • Full access to all, access control list not used.

      Select this option to enable all users access to an application.

    • Restricted access. Only users defined in the access control list are allowed.

      Select this option to restrict access to users on the Access Control List. Only users on the Access Control List can view pages and components associated with an authorization scheme.

    • Public read only. Edit and administrative privileges controlled by access control list.

      Provides public access to pages and components associated with the access control - view authorization scheme.

    • Administrative access only.

      Only users with Administrator privileges can access pages or components associated with an authorization scheme.

  4. Click Set Application Mode.

    Description of access_control_1.gif follows
    Description of the illustration access_control_1.gif

    Note that the user interface of your page is dependent upon the theme you selected for your application. See "Managing Themes".

    Next, add users to the Access Control List.

Adding Users to the Access Control List and Selecting Privileges

To add users to the Access Control List:

  1. Under Access Control List, click Add User.

    A new row appears.

    Description of access_control_2.gif follows
    Description of the illustration access_control_2.gif

  2. Enter a user in the Username field.

  3. Associate a privilege with the user. Available options include:

    • Administrator

    • Edit

    • View

  4. Click Apply Changes.

Removing Users from the Access Control List

To remove users from the Access Control List:

  1. Select the user to be removed by selecting the check box to the left of the username.

  2. Click Delete.

Controlling Access for Pages and Page Components

The Access Control Wizard creates authorization schemes that correspond to the application mode list options and the privileges available in the Access Control List.

You can control access to a specific page or page component by selecting one of the following authorization schemes on the page or component attributes pages:

  • access control administrator. Only users with Administrator privileges can view the page or component.

  • access control - edit. Users with both Edit and Administrator privileges can view the page or component. Users with View privileges cannot view the page or component.

  • access control - view. Users with Administrator, Edit, or View privileges can view the page or component.

  • Not access control administrator. Users with Administrator privileges cannot view the page or component.

  • Not access control - edit. Users with both Edit and Administrator privileges cannot view the page or component. Users with View privileges can view the page or component.

  • Not access control - view. Users with Administrator, Edit, or View privileges cannot view the page or component.

Creating Reports

In Oracle Application Express, a report is the formatted result of a SQL query. You can generate reports by selecting and running a built-in query, or by defining a report region based on a SQL query.

Topics in this section include:

Creating a Report Using a Wizard

Application Builder includes a number of built-in wizards for generating reports.

To create a report using a wizard:

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

  2. Select the application.

  3. Click Create Page.

  4. Select Report.

  5. Select a report type:

    • Wizard Report - Creates a report without requiring any SQL knowledge. Select the appropriate schema, table, columns, and result set display.

    • SQL Report - Creates a report based on a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement that you provide.

  6. Follow the on-screen instructions.

Editing Report Attributes

You can use the Report Attributes page to precisely control report layout, pagination, column sorting, error messages, export links, and column breaks.

Topics in this section include:

Accessing the Report Attributes Page

You can access the Report Attributes page by clicking the Report link next to the report region you want to edit on the Page Definition. You can also navigate to the Report Attributes page by clicking the region name and then selecting the Report Attributes tab.

Description of rprt_edit.gif follows
Description of the illustration rprt_edit.gif

To access the Report Attributes page:

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

  2. Select the application.

  3. Select a page.

    The Page Definition appears.

  4. Under Regions, click Report next to the name of the report region you want to edit.

    The Report Attributes page appears and is divided into the following sections:

  5. To learn more about a specific attribute, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  6. Click Apply Changes.

About Navigation Alternatives

The Report Attribute page is divided into seven sections: Column Attributes, Layout and Pagination, Sorting, Messages, External Processing, and Break Formatting.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page.

Description of rpt_att_nav.gif follows
Description of the illustration rpt_att_nav.gif

When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Altering Report Layout Using Column Attributes

You can use the Column Attributes section of the Report Attributes page to precisely control the report layout. For example, you can use these attributes to alter column heading text, change column positioning, hide a column, create a sum of a column, or select a sort sequence.

To access the Column Attributes:

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

  2. Select the application.

  3. Select a page.

    The Page Definition appears.

  4. Under Regions, click Report next to the name of the report region you want to edit.

    The Report Attributes page appears with the Column Attributes section at the top of the page.

    Description of rpt_att.gif follows
    Description of the illustration rpt_att.gif

    Use the Column Attributes section to control report column appearance and functionality.

    Heading Type identifies how the heading was generated for the report. The Link column indicates if a column link is currently defined. The Edit column indicates whether or not a column is currently updatable.

    Table 5-3 describes common report column edits.

    Table 5-3 Common Report Column Edits

    Description Developer Action

    Alter column display sequence.

    Click the up and down arrows to change the column display sequence.

    Alter heading alignment.

    Under Column Alignment, select a new column alignment.

    Change column heading text.

    Under Heading, enter different heading text.

    Control which columns display.

    Click Show to indicate a column should display.

    Enable a unique sort sequence.

    Click Sort and select a sequence number from Sort Sequence.

    Any number of columns can be sort enabled. However, at least one column must have a Sort Sequence defined.

    Enable the sum of a column.

    Click Sum to enable the sum of a column.


    You can further refine the attributes of a specific column on the Column Attributes page.

  5. To access the Column Attributes page, click the Edit icon next to the appropriate column Alias.

    To learn more about a specific attribute, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

Controlling Report Pagination

You control report pagination by:

  • Including a pagination substitution string in the report template

  • Making selections from the Layout and Pagination section on the Report Attributes page

You control how pagination displays by making selections from the Layout and Pagination attributes on the Report Attributes page.

Topics in this section include:

Accessing and Understanding Layout and Pagination Attributes

To access the Layout and Pagination section of the Report Attributes page:

  1. Create a report. See "Creating a Report Using a Wizard".

  2. Under Regions, click the appropriate Report attributes link.

    The Report Attributes page appears.

  3. Scroll down to Layout and Pagination.

    You use the Layout and Pagination attributes to select a pagination style, determine where pagination occurs, and specify the number of rows that display on each page. Table 5-4 describes the most commonly used Layout and Pagination attributes.

    To learn more about a specific attribute, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

Table 5-4 Layout and Pagination Attributes

Attribute Description

Report Template

Specifies a template to be applied to this report. Report templates provide control over the results of a row from your SQL query. You can choose from a number of default templates, or pick a custom build template.

Show Null Values as

Enter the text you want to display for null columns. The default value is (null).

Pagination Scheme

Specifies a pagination scheme for this report.

Pagination provides the user with information about the number of rows and the current position within the result set. Pagination also defines the style of links or buttons used to navigate to the next or previous page.

For more information, see the Help information for this attribute.

Display Position

Defines where pagination occurs.

If you choose to display pagination above a report, the selected report template needs to support that type of display.

Number of Rows

Defines the maximum number of rows to display on each page.

Number of Rows (Item)

Defines the number of rows displayed by default per page for SQL queries (obtained dynamically from an item). Identify the item in this attribute.

Maximum Row Count

Defines the maximum number of rows to query, for example, rows 1 - 10 or 456.

If you set this attribute to 200, the result would appear as follows:

rows 1 - 10 of more than 200 rows

Note that this attribute impacts performance. Counting fewer rows can improve performance and counting thousands of rows can degrade performance.

Strip HTML

Specify whether or not to remove HTML tags from the original column values for HTML expressions, column links and report data exported as CSV files.

If you select values from the database that already contain HTML tags, then those tags can cause conflicts with the HTML generated for your columns links or HTML expressions. When this option is enabled, only the actual data portion of your column value is used.


Including Pagination After the Rows in a Report

To include pagination after the rows in a report:

  1. Create a report. See "Creating a Report Using a Wizard".

    Next, select the appropriate Layout and Pagination attributes.

  2. Navigate to the Report Attributes page:

    1. Navigate to the Page Definition.

    2. Under Regions, click the appropriate Report attributes link.

      The Report Attributes page appears.

  3. Under Layout and Pagination, select the following:

    1. Report Template - Select a report template (optional).

    2. Pagination Scheme - Select a pagination scheme.

    3. Display Position - Select a display position.

    4. Number of Rows - Specify how many rows display on each page.

    5. Click Apply Changes.

  4. Edit the report template:

    1. Navigate to the Page Definition.

    2. Under Templates, select the report template name.

    3. Include the #PAGINATION# substitution string in the After Rows attribute.

    4. Click Apply Changes.

  5. Run the page.

Including Pagination Before the Rows in a Report

To include pagination before the rows in a report:

  1. Create a report. See "Creating a Report Using a Wizard".

    Next, select the appropriate Layout and Pagination attributes.

  2. Navigate to the Report Attributes page:

    1. Navigate to the Page Definition.

    2. Under Regions, click the appropriate Report attributes link.

      The Report Attributes page appears.

  3. Under Layout and Pagination:

    1. Report Template - Select a report template (optional).

    2. Pagination Scheme - Select a pagination scheme.

    3. Display Position - Select a position that contains the word top.

    4. Number of Rows - Specify how many rows display on each page.

    5. Click Apply Changes.

  4. Edit the report template.

    1. Navigate to the Page Definition.

    2. Under Templates, select the report template name.

    3. Include the #TOP_PAGINATION# substitution string in the Before Rows attribute.

    4. Click Apply Changes.

  5. Run the page.

Enabling Column Sorting

You enable column sorting on the Report Attributes page.

To enable column sorting:

  1. Navigate to the Report Attributes page. See "Editing Report Attributes".

  2. Under Report Column Attributes, select the Sort check box next to the columns to be sorted.

  3. From Sort Sequence, select a sequence number.

    Sort Sequence is optional. However, if there are one or more sort enabled columns, then at least one column needs a defined Sort Sequence.

  4. Scroll down to Sorting.

  5. Specify ascending and descending image attributes or click set defaults.

Adding a Download Link to a Report

You can create a link within a report that enables users to export the report as a comma-delimited file (.csv) file. To add a CSV link to a report you need to enable the CSV output option. When using the CSV output option, the report template is not important. You can include a CSV link with any report template that has the CSV export substitution string defined.

Enabling the CSV Output Option

To enable the Enable CSV output option:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Scroll down to Report Export.

  3. From Enable CSV output, select Yes.

  4. (Optional) In the Separator and Enclosed By fields, define the separator and delimiter.

    The default Enclosed By by characters are a double quotation marks (" "). The default delimiter is either a comma or a semicolon depending upon your current NLS settings.

  5. In the Link Label field, enter link text. This text will display in your report and enable users to invoke a download.

  6. (Optional) To specify a default export file name, enter a name in the Filename field.

    By default, the Application Express engine creates an export file name by taking the region name and adding the appropriate file name extension (.csv or .xml).

  7. Click Apply Changes.

Exporting a Report as an XML File or a CSV File

You can export a report as an XML files by selecting a report template.

To export a report as a file:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Scroll down to Layout and Pagination.

  3. From the Report Template list, select export: XML or export: CSV.

    Selecting export: XML prevents the Application Express engine from rendering the page and dumps the content to an XML file.

  4. Click Apply Changes.

Creating a Column Link

Use the Column Link attributes to create a link from a report to another page in your application or to a URL.

To create a column link to another page:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Under Report Column Attributes, locate the column to contain the link.

  3. Click the Edit icon next to the column name.

    The Column Attributes page appears.

  4. Scroll down to Column Link.

  5. To create a column link to another page:

    1. From Target, select Page in this Application.

    2. (Optional) In Link Attributes, specify additional column link attributes that will be included in the <a href= > tag (for example, a link target, classes, or styles).

    3. In Link Text, enter the text to be displayed as a link, specify an image tag, or pick from the list of default images.

    4. In Page, specify the target page number. To reset the pagination for this page, select Reset Pagination.

    5. In Request, specify the request to be used.

    6. In Clear Cache, specify the pages (that is, the page numbers) on which to clear cache. You can specify multiple pages by listing the page numbers in a comma-delimited list.

    7. Use the Name and Value fields to specify session state for a specific item.

  6. Click Apply Changes.

To create a column link to a URL:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

    The Column Attributes page appears.

  3. Scroll down to Column Link.

  4. Under Column Link, make the following selection:

    1. From Target Type, select URL.

    2. In Link Text, enter the text to be displayed as a link and select a substitution string.

    3. (Optional) In Link Attributes, specify additional column link attributes that will be included in the <a href= > tag (for example, a link target, classes, or styles).

    4. In URL, enter the appropriate address.

  5. Click Apply Changes.

Defining an Updatable Column

You can make a column updatable by editing Tabular Form Element attributes on the Column Attributes page. Note that the Application Express engine can only perform updates if:

  • A multirow update is defined

  • A PL/SQL process is implemented to process updated data

  • When using the built-in tabular form elements and display types, then the report has to be defined using the type SQL Query (updatable report)

To define updatable column attributes:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

    The Column Attributes page appears.

  3. Scroll down to Tabular Form Element.

  4. Under Tabular Form Element, make the following selections:

    1. Display As - Select a type of updatable column.

      Use this option to make a column updatable. Updates can only be performed if a multirow update is defined, or a PL/SQL process is implemented to process updated data.

    2. Date Picker Format Mask - Make a selection if you selected the Display As type of Date Picker.

    3. Element Width - Specify the width of the form item.

    4. Number of Rows - Specify the height of a form item (applicable to text areas).

    5. Element Attributes - Define a style or standard form element attribute.

    6. Element Option Attributes - Specify form element attributes for items in a radio group or check box.

    7. Primary Key Source Type - Identify the default type.

    8. Primary Key Source - Identify the default source.

      If the current column is part of the primary key defined in an MRU process, only the primary key source type and source appear.

      Otherwise, Default and Default Type appear. Use Default and Default Type to establish a relationship between two master records in a master detail form, or to set the default values for new rows.

    9. Reference Table Owner - Identify the owner of the referenced table. Use this attribute to build User Interface Defaults for reports.

    10. Reference Table Name - Identify the table or view that contains the current report column.

    11. Reference Column Name - Identify the column name that this report column references.

  5. Click Apply Changes.

Defining a Column as a List of Values

Report columns can be rendered as lists of values. For example, a column can be rendered using a select list or a popup list of values. Or, a column can be rendered as read-only text based on a list of values.

This last approach is an effective strategy when creating display lookup values and is particularly useful in regular, nonupdatable reports. This approach enables you to display the value of a column without having to write a SQL JOIN statement.

To render a report column as a list of values:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

    The Column Attributes page appears.

  3. Scroll down to List of Values.

  4. From Named LOV, make a selection from the List of Values repository.

  5. To include a null value in a list of values:

    1. In Display Null, select Yes.

    2. In Null Text, specify the value that displays.

    A column can also have a value that does not display in its list of values.

  6. To define a value that does not display in the list of values:

    1. From Display Extra Value, select Yes.

      The extra value is used if the actual column value is not part of the LOV. In that situation, the actual value is shown. If you do not display extra values, you may end up with the wrong value and unintentionally update your data incorrectly.

    2. In Null Value, specify the value that displays.

    3. If you have not selected a Named LOV, enter the query used to display a select list in the LOV Query field.

  7. If you have not selected a Named LOV, enter the query used to display a select list in LOV Query.

  8. Click Apply Changes.

Controlling When Columns Display

You can use the Authorization and Condition column attributes to control when a column displays.

Authorization enables you to control access to resources (such as a report column) based on predefined user privileges. For example, you could create an authorization scheme in which only managers can view a specific report column. Before you can select an authorization scheme, you must first create it.

A condition is a small unit of logic that enables you to control the display of a column based on a predefined condition type. The condition evaluates to true or false based on the values you enter in the Expressions fields.

To specify Authorization and Condition attributes:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

    The Column Attributes page appears.

  3. Under Authorization, make a selection from the Authorization Scheme list.

  4. Under Conditions, make a selection from the Condition Type list, and depending upon your selection, enter an expression or value in the appropriate Expression fields.

    If the authorization is successful and the condition type display evaluates to true, the column displays.

Controlling Column Breaks

You can control if a specific column repeats and how column breaks appear when printed using Break Formatting attributes. For example, suppose your report displays employee information by department number. If multiple employees are members of the same department, you can increase the readability by specifying the department number only appears once.

To create this type of column break:

  1. Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".

  2. Scroll down to Break Formatting.

  3. Make a selection from the Breaks list.

Creating Forms

You can include a variety of different types of forms in your applications. You can include forms that enable users to update just a single row in a table or multiple rows at once. Application Builder includes a number of wizards you can use to create forms automatically, or you can create forms manually.

Topics in this section include:

Creating a Form Using a Wizard

The easiest way to create a form is to use a wizard. For example, the Form on Table or View Wizard creates one item for each column in a table. It also includes the necessary buttons and processes required to insert, update, and delete rows from the table using a primary key. Each region has a defined name and display position; all other attributes are items, buttons, processes, and branches.

To create a form using a wizard:

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

  2. Select an application.

  3. Click Create Page.

  4. Select Form and click Next.

  5. Under Forms, select a type of form page as described in Table 5-5.

    Table 5-5 Forms Page Types

    Form Page Type Description

    Form on a Procedure

    Builds a form based on stored procedure arguments. Use this approach when you have implemented logic or data manipulation language (DML) in a stored procedure or package.

    Form on a SQL Query

    Creates a form based on the columns returned by a SQL query such as an EQUIJOIN.

    Form on a Table or View

    Creates a form that enables users to update a single row in a database table.

    Form on a Table with Report

    Creates two pages. One page displays a report. Each row provides a link to the second page to enable users to update each record.

    Note: This wizard does not support tables having more than 127 columns. Selecting more than 127 columns generates an error.

    Form on Web Service

    Creates a page with items based on a Web service definition. This wizard creates a user input form, a process to call the Web service, and a submit button.

    See Also: "Creating a Form on a Web Service"

    Form and Report on Web Service

    Creates a page with items based on a Web service definition. This wizard creates a user input form, a process to call the Web service, a submit button, and displays the results returned in a report.

    See Also: "Creating an Input Form and Report on a Web Service"

    Master Detail Form

    Creates a form that displays a master row and multiple detail rows within a single HTML form. With this form, users can query, insert, update, and delete values from two tables or views.

    See Also: "Creating a Master Detail Form"

    Summary Page

    Creates a read-only version of a form. Typically used to provide a confirmation page at the end of a wizard.

    Tabular Form

    Creates a form in which users can update multiple rows in a database.

    See Also: "Creating a Tabular Form"


  6. Follow the on-screen instructions.

Creating a Tabular Form

A tabular form enables users to update multiple rows in a table. The Tabular Form Wizard creates a form to perform update, insert, and delete operations on multiple rows in a database table.

To create a tabular form:

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

  2. Select an application.

  3. Click Create Page.

  4. Select Form and click Next.

  5. Select Tabular Form and click Next.

    The Tabular Form Wizard appears.

  6. For Table/View Owner:

    1. Specify the table or view owner on which you want to base your tabular form.

    2. Select the operations to be performed on the table (for example, Update, Insert and Delete).

    3. Click Next.

  7. For Table/View Name, select a table and click Next.

  8. For Displayed Columns:

    1. Select the columns (updatable and nonupdatable) to include in the form.

      Note that you can modify the column order or your SQL query after you create the page.

    2. Click Next.

  9. For Primary Key, select the Primary Key column and a secondary Primary Key column (if applicable) and click Next.

  10. For Primary Key Source, select a source type for the primary key column and click Next. Valid options include:

    • Existing trigger - Select this option if a trigger is already defined for the table. You can also select this option if you plan on specifying the primary key column source later after completing the form.

    • Custom PL/SQL function - Select this option if you wish to provide a PL/SQL function to generate returning key value.

    • Existing sequence - Select this option if you wish to pick the sequence from a list of sequences available in the selected schema.

  11. On Updatable Columns, select which columns should be updatable and click Next.

  12. On Page and Region Attributes:

    1. Specify page and region information.

    2. Select a region template.

    3. Select a report template.

    4. Click Next.

  13. On Tab, specify a tab implementation for this page and click Next.

  14. On Button Labels, enter the display text to appear for each button and click Next.

  15. On Branching, specify the pages to branch to after the user clicks the Submit and Cancel buttons and click Next.

  16. Click Finish.

Note:

Any modification of the select list of a SQL statement of a tabular form after it has been generated is not recommended. If you do modify the query, make sure the values of the updateable columns are not altered after being queried from the database by the Application Express engine.

Creating a Master Detail Form

A master detail form reflects a one-to-many relationship between two tables in a database. Typically, a master detail form displays a master row and multiple detail rows within a single HTML form. With this form, users can insert, update, and delete values from two tables or views.

To create a master detail form:

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

  2. Select an application.

  3. Click Create Page.

  4. Select Form and click Next.

  5. Select Master Detail Form and click Next.

    The Master Detail Wizard appears.

  6. On Master Table:

    1. Select a table or view owner.

    2. Select a table or view name.

      The columns in that object appear under Available Columns.

    3. Select the columns to display in the form and then click the arrow keys to move them to Displayed Columns.

    4. Click Next.

  7. On Detail Table:

    1. Specify whether to show only related tables by selecting Yes or No.

    2. Select the table or view owner.

    3. Select a table or view name.

      The columns in that object appear under Available Columns.

    4. Select the columns to display in the form and then click the arrow keys to move them to Displayed Columns.

    5. Click Next.

  8. On Primary Key Source, select the primary key column for the master table, and then select the primary key column for the detail table. Options include:

    • Existing trigger - Select this option if a trigger is already defined to populate the primary key.

    • Custom PL/SQL function - Select this option if you wish to provide a PL/SQL function to populate the primary key.

    • Existing sequence - Select this option if there is already an existing sequence you want the wizard to create the necessary trigger.

  9. On Master Options, specify whether or not to include master row navigation and click Next.

    If you include master row navigation, define navigation order columns. If a navigation order column is not defined, the master update form navigates by the primary key column.

  10. On Choose Layout, specify the layout of the master detail pages and click Next.

    You can include the master detail as a tabular form on the same page, or add the master detail on a separate page.

  11. On Page Attributes, review and edit the master page and detail page information and then click Next.

  12. On Tab, specify whether or not to include a tab set and click Next.

  13. Click Create.

Creating a Form Manually

You can also create a form manually by performing the following steps:

  • Create an HTML region (to serve as a container for your page items)

  • Create items to display in the region

  • Create processes and branches

To create a form manually by creating and HTML region:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Create an HTML region:

    1. Under Regions, click the Create icon.

    2. Select the region type HTML.

    3. Follow the on-screen instructions.

  3. Start adding items to the page:

    • Under Items, click the Create icon.

    • Follow the on-screen instructions.

Processing a Form

Once you create a form, the next step is to process the data a user types by inserting into or updating the underlying database tables or views. There are three ways to process a form:

Creating an Automatic Row (DML) Processing Process

One common way to implement a form is to manually create an Automatic Row Processing (DML) process. This approach offers three advantages. First, you are not required to provide any SQL coding. Second, Oracle Application Express performs DML processing for you. Third, this process automatically performs lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.

To implement this approach you need to:

  • Add items, define the Item Source Type as Database Column, and specify a case-sensitive column name.

  • Select the option Always overrides the cache value.

To create an Automatic Row Processing (DML) process:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Processes, click the Create icon.

  3. Select the process Data Manipulation.

  4. Select the process category Automatic Row Processing (DML).

  5. Specify the following process attributes:

    1. In the Name field, enter a name to identify the process.

    2. In the Sequence field, specify a sequence number.

    3. From the Point list, select the appropriate processing point. In most instances, select Onload - After Header.

    4. From the Type list, select Automated Row Processing (DML).

  6. Follow the on-screen instructions.

Creating a Process that Contains One or More Insert Statements

In this approach to form handling, you create one or more processes to handle insert, update, and delete actions. Instead of having the Application Express engine handling everything transparently, you are in complete control.

For example, suppose you have a form with three items:

  • P1_ID - A hidden item to store the primary key of the currently displayed row in a table.

  • P1_FIRST_NAME - A text field for user input.

  • P1_LAST_NAME - A text field for user input.

Assume also there are three buttons labeled Insert, Update, and Delete. Also assume you have a table T that contains the columns id, first_name, and last_name. The table has a trigger that automatically populates the ID column when there is no value supplied.

To process the insertion of a new row, you create a conditional process of type PL/SQL that executes when the user clicks the Insert button. For example:

BEGIN
  INSERT INTO T ( first_name, last_name )
     VALUES  (:P1_FIRST_NAME, :P1_LAST_NAME);
END; 

To process the updating of a row, you create another conditional process of type PL/SQL. For example:

BEGIN
    UPDATE T
       SET first_name = :P1_FIRST_NAME,
           last_name = :P1_LAST_NAME
    WHERE ID = :P1_ID;
END; 

To process the deletion of a row, you create a conditional process that executes when the user clicks the Delete button. For example:

BEGIN
    DELETE FROM T
    WHERE ID = :P1_ID;
END;

Using a PL/SQL API to Process Form Values

For certain types of applications, it is appropriate to centralize all access to tables in a single or a few PL/SQL packages. If you created a package to handle DML operations, you can call procedures and functions within this package from an After Submit PL/SQL process to process insert, updates, and delete requests.

Populating Forms

Oracle Application Express populates a form either on load or when the Application Express engine renders the page. You can populate a form in the following ways:

  • Create a process and define the type as Automated Row Fetch.

  • Populate the form manually by referencing a hidden session state item.

To create an Automated Row Fetch process:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Processes, click Create.

  3. Select the process type Data Manipulation.

  4. Select the process category Automatic Row Fetch.

  5. Specify the following process attributes:

    1. In the Name field, enter a name to identify the process.

    2. In the Sequence field, specify a sequence number.

    3. From the Point list, select the appropriate processing point.

    4. From the Type list, select Automated Row Fetch.

  6. Follow the on-screen instructions.

You can also populate a form manually by referencing a hidden session state item. For example, the following code in an Oracle Application Express process of type PL/SQL would set the values of ename and sal. The example also demonstrates how to manually populate a form by referencing a hidden session state item named P2_ID.

FOR C1 in (SELECT ename, sal
FROM emp WHERE ID=:P2_ID)
LOOP     
     :P2_ENAME := C1.ename;
     :P2_SAL := C1.sal;
END LOOP;

In this example:

  • C1 is an implicit cursor.

  • The value of P2_ID has already been set.

  • The process point for this process would be set to execute (or fire) on or before Onload - Before Regions.

Validating User Input in Forms

You can use validations to check data a user enters before processing. Once you create a validation and the associated error message, you can associate it with a specific item. You can choose to have validation error messages display inline (that is, on the page where the validation is performed) or on a separate error page.

Creating an inline error message involves these steps:

  • Create a new validation and specify error message text.

  • Associate the validation with a specific item.

Creating a Validation

To create a new validation:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Validations, click the Create icon.

  3. When the Create Validations Wizard appears, follow the on-screen instructions.

    Validation Types are divided into two categories:

    • Item. These validations start with the term Item and provide common checks you may want to perform on the item with which the validation is associated.

    • Code. These validations require that you provide either a piece of PL/SQL code or SQL query that defines the validation logic. Use this type of validation to perform custom validations that require verifying values of more than one item or accessing additional database tables.

  4. Follow the on-screen instructions.

Note:

Validations cannot contain more than 3,950 characters.

Associating a Validation with a Specific Item

To associate an item with a validation and specify error message text:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Validations, select the validation item you want to associate.

    The attributes page for the validation appears.

  3. Scroll down to Error Message:

    • In Error message display location, verify the display location.

    • In Associated Item, select the item you want to associate with this validation.

  4. Click Apply Changes.

About Error Message

Error message display location identifies where a validation error message displays. Validation error messages can display on an error page or inline within the existing page. Inline error messages can display in a notification area (defined as part of the page template) or within the field label.

To create a hard error that stops processes, including any remaining validations, you must display the error on an error page.

Creating Calendars

Application Builder includes a built-in wizard for generating a calendar. Once you specify the table on which the calendar is based, you can create drill-down links to information stored in specific columns. Note that Oracle Application Express supports the creation of only one calendar per page.

Topics in this section include:

About Creating Calendars

Application Builder supports two calendar types:

  • Easy Calendar creates a calendar based on schema, table, and columns you specify. The wizard prompts you to select a date column and display column.

  • SQL Calendar creates a calendar based on a SQL query you provide. The SQL SELECT statement you provide must include at least two columns: a date column and display column.

Supported Calendar Substitution Strings

Application Builder supports a number of date format substitution strings. You can view a complete list of supported substitution strings on the Calendar Templates page.

To view a list of supported substitution strings for calendars:

  1. Navigate to the appropriate calendar template.

  2. View the Substitution Stings list on the right side of the page.

Creating a New Calendar

How you create a calendar depends on if you are adding a calendar to an existing page or adding a calendar on a new page. When creating calendars remember:

  • You can only create one calendar for each page.

  • The date column determines which days on the calendar will contain entries.

  • The display column defines a specific row that will display a calendar date.

Adding a Calendar to an Existing Page

Oracle Application Express supports the creation of one calendar per page.

To add a calendar to an existing page:

  1. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Regions, click the Create icon.

    The Create Region Wizard appears.

  3. Select Calendar and click Next.

  4. Select the type of calendar you want to create and click Next:

    • Easy Calendar creates a calendar based on the date column and display column you specify.

    • SQL Calendar creates a calendar based on a SQL query you provide.

  5. Follow the on-screen instructions.

Adding a Calendar to a New Page

To create a calendar on a new page:

  1. Navigate to the Workspace home page.

  2. Click the Application Builder icon.

  3. Select an application.

  4. Click Create Page.

  5. Select Calendar and click Next.

  6. Select the type of calendar you want to create and click Next:

    • Easy Calendar creates a calendar based on the date column and display column you specify.

    • SQL Calendar creates a calendar based on a SQL query you provide.

  7. Follow the on-screen instructions.

Converting an Easy Calendar to a SQL Calendar

Creating an Easy Calendar is the simplest way to create a calendar. However, if you find the resulting calendar does not meet your needs, you can quickly convert it to a SQL Calendar.

To convert an Easy Calendar to a SQL Calendar:

  1. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Regions, click Calendar next to the region name.

    The Calendar Attributes page appears.

  3. On the Tasks list, click Convert to SQL Based calendar.

    Converting an Easy Calendar to a SQL Calendar adds a Region Source section to the Region Definition. The Region Source contains the original SQL query that creates the calendar. By accessing the Region Source, you can edit the query to meet your needs.

Editing a Calendar Title

The title that appears at the top of calendar corresponds to the region title.

To alter the region title:

  1. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Regions, select the region name.

    The Region Definition appears.

  3. Under Region, enter a new title.

  4. Click Apply Changes.

Editing Calendar Attributes

Once you create a calendar, you can alter the display by editing attributes on the Calendar Attributes page.

Topics in this section include:

Accessing the Calendar Attributes Page

To access the Calendar Attributes page:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Regions, click Calendar next to the region name.

    The Calendar Attributes page appears.

  3. Edit the appropriate attributes. To learn more about a specific item on a page, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark.

  4. Click Apply Changes.

About Navigation Alternatives

The Calendar Attribute page is divided into sections.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

About the Calendar Attributes Page

The topics that follow describe specific sections of the Calendar Attributes page. You can use these attribute to specify general calendar formatting, define the dates included in the calendar, or create a link on the column or a day in the calendar.

Calendar Display

Use Calendar Display to specify a calendar template, date columns, and general calendar formatting.

Calendar Template determines what template is used when the Application Express engine renders a calendar. Date Column defines the column from the table or query containing the dates to be placed on the calendar. Display Column defines a specific row that displays on a calendar date.

To select another Display Column:

  1. Navigate to the appropriate Calendar Attributes page.

  2. Locate the Calendar Display section.

  3. To specify another display column, make a selection from the Display Column list.

  4. Click Apply Changes.

To specify a custom Display Column:

  1. Navigate to the appropriate Calendar Attributes page.

  2. Locate the Calendar Display section.

  3. From Display Type, select Custom.

  4. In Column Format, enter a custom column format. You can use an HTML expression and supported substitution strings.

  5. Click Apply Changes.

Calendar Interval

Use Calendar Interval to define the dates that are included in the calendar.

Begin At Start Of Interval determines when the calendar should start. Selecting this option creates a calendar that spans an entire interval (such as a month). For example:

  • If Begin at start of interval is selected, the date is June 15th, and the display is monthly, the resulting calendar spans from June 1st to June 30th.

  • If Begin at start of interval is not selected, the date is June 15th, and the display is monthly, the resulting calendar spans from June 15th to June 30th.

Date Item holds the date on which the calendar is based.

The next two attributes define which items hold the calendar start date and end date. You can use these attributes to create calendars that span multiple months at a time. Item Containing Start Date points to an item that holds the start date of the calendar. Item Containing End Date points to an item that holds the end date of the calendar. Note that format of the date of either item must be YYYYMMDD.

Start of Week determines the day of the week on which the calendar starts.

Column Link

Use Column link to create a link on the column in the calendar.

To create a column link to another page:

  1. Navigate to the appropriate Calendar Attributes page.

  2. Scroll down to Column Link.

  3. From Target is a, select Page in this Application.

  4. In Page, specify the target page number. To reset the pagination for this page, select reset pagination for this page.

  5. In Request, specify the request to be used.

  6. In Clear Cache, specify the pages (that is, the page numbers) on which to clear cache. Specify multiple pages by listing the page numbers in a comma-delimited list.

    You can set session state (that is, give a listed item a value) using the next two attributes: the Set these items attribute and the With these values attribute.

  7. To set session state:

    1. Set these items - Enter a comma-delimited list of item names for which you would like to set session state.

    2. With these values - Enter a comma-delimited list of values for the items specified in the previous step.

      You can specify static values or substitution syntax (for example, &APP_ITEM_NAME.). Note that item values passed to f?p= in the URL cannot contain a colon (:). Additionally, item values cannot contain commas unless you enclose the entire value in backslashes (for example, \1234,56\).

  8. Click Apply Changes.

To create a column link to a URL:

  1. Navigate to the appropriate Calendar Attributes page.

  2. Scroll down to Column Link.

  3. From Target is a, select URL.

  4. In URL, enter the appropriate address.

  5. Click Apply Changes.

Day Link

Use Day link to create a link on a day in the calendar. This attribute creates a link on an actual number (or day) on the calendar.

To create a day link to another page:

  1. Navigate to the appropriate Calendar Attributes page.

  2. Scroll down to Day Link.

  3. From Target is a, select Page in this Application.

  4. In Page, specify the target page number.

    To reset the pagination for this page, select reset pagination for this page.

  5. In Request, specify the request to be used.

  6. In Clear Cache, specify the pages (that is, the page numbers) on which to clear cache. Specify multiple pages by listing the page numbers in a comma-delimited list.

    You can set session state (that is, give a listed item a value) using the next two attributes: Set these items and With these values.

  7. To set session state:

    1. Set these items - Enter a comma-delimited list of item names for which you would like to set session state.

    2. With these values - Enter a comma-delimited list of values for the items specified in the previous step.

      You can specify static values or substitution syntax (for example, &APP_ITEM_NAME.). Note that item values passed to f?p= in the URL cannot contain a colon (:). Additionally, item values cannot contain commas unless you enclose the entire value in backslashes (for example, \1234,56\).

  8. Click Apply Changes.

To create a day link to a URL:

  1. Navigate to the appropriate Calendar Attributes page.

  2. Scroll down to Day Link.

  3. From Target is a, select URL.

  4. In URL, enter the appropriate address.

  5. Click Apply Changes.

Creating Charts

Application Builder includes built-in wizards for generating HTML and Scalable Vector Graphics (SVG) charts. Oracle Application Express supports two types of graphical charts: HTML and SVG.

SVG is an XML-based language for Web graphics from the World Wide Web Consortium (W3C). SVG charts are defined using an embed tag. When evaluating whether or not an SVG chart is the appropriate chart type for your application, remember that:

Topics in this section include:

About SVG Plug-in Support

The Adobe SVG plug-in can handle data encoded in UTF-8, UTF-16, ISO-8859-1, and US-ASCII. Encoding of an SVG chart is determined by the database access descriptor (DAD) database character set. If the DAD character set is not UTF8, AL32UTF8, AL16UTF16, WE8ISO8859P1, or US7ASCII, SVG charts may not render properly in the Adobe SVG plug-in.

About Creating Charts

You define a chart in Application Builder using a wizard. For most chart wizards, you select a chart type and provide a SQL query using the following syntax:

SELECT link, label, value
FROM   ...

Where:

  • link is a URL.

  • label is the text that displays in the bar.

  • value is the numeric column that defines the bar size.

For example:

SELECT null, last_name, salary
FROM   employees
WHERE  DEPARTMENT_ID = :P101_DEPARTMENT_ID

To create a dial chart, select a dial chart type and provide a SQL query using the following syntax:

SELECT value , maximum_value [ ,low_value [ ,high_value] ]
FROM   ...

Where:

  • value is the starting point on the dial.

  • maximum_value is the possible highest point on the dial.

  • low_value and high_value are the historical low and high values.

For example:

SELECT dbms_random.value(500, 1200), 1300, dbms_random.value(100, 200)
FROM DUAL

Table 5-6 describes the chart types available in Application Builder.

Table 5-6 Available Chart Types

Chart Type Description

Bar (HTML)

Bar chart showing one data series with each data point represented by a bar.

HTML-based. Does not require a plug-in.

Bar, Horizontal

Single series-based bar chart oriented horizontally with each data point in the series represented by a bar.

SVG-based. Requires an SVG plug-in.

Bar, Vertical

Single series-based bar chart oriented vertically with each data point in series represented by a bar.

SVG-based. Requires an SVG plug-in.

Cluster Bar, Horizontal

Multiple series-based bar chart oriented horizontally and clustered by a common variable with each data point in the series represented by a bar (for example, Department sales total clustered by month of year).

SVG-based. Requires an SVG plug-in.

Cluster Bar, Vertical

Multiple series-based bar chart oriented vertically clustered by a common variable with each data point in series represented by a bar (for example, Department sales total clustered by month of year).

SVG-based. Requires an SVG plug-in.

Dial - Sweep

Also known as an angular gauge; this chart shows either percentage of maximum value or absolute value compared to a maximum value represented as a solid area.

SVG-based. Requires an SVG plug-in.

Dial

Also known as angular gauge; this chart shows either percentage of maximum value or absolute value compared to maximum value represented as a line.

SVG-based. Requires an SVG plug-in.

Line

Multiple series-based line chart oriented with each line representing all data points in the series.

SVG-based. Requires an SVG plug-in.

Pie

Single series-based pie chart with each slice representing a data point in the series.

SVG-based. Requires an SVG plug-in.

Stacked Bar, Horizontal

Multiple series-based bar chart oriented horizontally with each data point being an absolute value in the series representing a segment of a single bar.

SVG-based. Requires an SVG plug-in.

Stacked Bar, Vertical

Multiple series-based bar chart oriented vertically with each data point being an absolute value in the series representing a segment of a single bar.

SVG-based. Requires an SVG plug-in.

Stacked Percentage Bar, Horizontal

Multiple series-based bar chart oriented horizontally with each data point being a percentage of 100% of the series represented by a segment of a single bar.

SVG-based. Requires an SVG plug-in.

Stacked Percentage Bar, Vertical

Multiple series-based bar chart oriented vertically with each data point being a percentage of 100% of the series represented by a segment of a single bar

SVG-based. Requires an SVG plug-in.


Note:

Do not change the type of an existing chart. Instead, delete the existing chart and then re-create it.

Creating a New Chart

How you create a chart depends upon whether you are adding the chart to an existing page, or adding a chart on a new page.

Adding a Chart to an Existing Page

To add a chart to an existing page:

  1. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Regions, click the Create icon.

    The Create Region Wizard appears.

  3. Select Chart and click Next.

  4. Select the type of chart you want to create. See Table 5-6.

  5. Follow the on-screen instructions.

Adding a Chart to a New Page

To create a chart on a new page:

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

  2. Select an application.

  3. Click Create Page.

  4. Select Chart and click Next.

  5. Select the type of chart you want to create. See Table 5-6.

  6. Follow the on-screen instructions.

Editing Chart Attributes

Once you have created a chart, you can alter its display by editing chart attributes on the Chart Attributes page.

To access the Chart Attributes page:

  1. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Regions, click Chart or SVG Chart next to the name of the chart region you want to edit.

    The Chart Attributes page appears.

  3. Edit the appropriate attributes.

  4. To learn more about a specific item on a page, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  5. Click Apply Changes.

Tip:

Removing the chart title (that is, the Chart Title attribute) may negatively impact the location and display of the chart legend.

About Navigation Alternatives

The Chart Attributes page is divided into the following sections such as Chart Settings, Font Settings, CSS, Refresh, Header and Footer, and so on.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Understanding Chart Cascading Style Sheet Classes

When you create a new chart, Oracle Application Express renders it based on cascading style sheet (CSS) classes associated with the current theme. You can change the appearance of a chart by referencing another CSS or by overriding individual classes in the CSS section of the Edit Attributes page

The following sample contains the CSS classes for the dial chart in Sample Application. This example contains all the available CSS classes. Class names appear in boldface.

text{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
tspan{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
text.title{font-weight:bold;font-size:14;fill:#000000;}
text.moredatafound{font-size:12;}
rect.legend{fill:#EEEEEE;stroke:#000000;stroke-width:1;}
text.legend{font-size:10;}
#background{fill:#FFFFFF;stroke:none;}
rect.chartholderbackground{fill:#ffffff;stroke:#000000;stroke-width:1;}
#timestamp{text-anchor:start;font-size:9;}
text.tic{stroke:none;fill:#000000;font-size:12}
line.tic{stroke:#000000;stroke-width:1px;fill:none;}
#dial{stroke:#336699;stroke-width:2px;fill:#336699;fill-opacity:.5;}
#dial.alert{fill:#FF0000;fill-opacity:.5;}
#dialbackground{stroke:#000000;stroke-width:none;fill:none;filter:url(#MyFilter);}
#dialcenter{stroke:none;fill:#111111;filter:url(#MyFilter);}
#dialbackground-border{stroke:#DDDDDD;stroke-width:2px;fill:none;filter:url
(#MyFilter);}#low{stroke-width:3;stroke:#336699;}
#high{stroke-width:3;stroke:#FF0000;}
#XAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;}
#YAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;writing-mode:tb;}
.XAxisValue{font-size:8;fill:#000000;}
.YAxisValue{font-size:8;fill:#000000;text-anchor:end;}
.nodatafound{stroke:#000000;stroke-width:1;font-size:12;}
.AxisLine{stroke:#000000;stroke-width:2;fill:#FFFFFF;}
.GridLine{stroke:#000000;stroke-width:0.3;stroke-dasharray:2,4;fill:none;}
g.dataholder rect{stroke:#000000;stroke-width:0.5;}
.legenditem rect{stroke:#000000;stroke-width:0.5;}

Table 5-7 describes all supported CSS classes. Note that certain classes only apply to specific chart types.

Table 5-7 Available Chart CSS Classes

Class Description

text

Defines the appearance of text that displays in a chart.

tspan

Defines the appearance of text that displays in a chart. tspan should match the definition of text.

text.title

Overrides the default chart text. Use this class for title text.

text.moredatafound

Defines the appearance of more datafound text.

rect.legend

Creates the rectangular box that holds the chart legend.

To remove the legend border, change rect.legend to the following:

rect.legend{fill:#CCCC99;stroke:none;} 

text.legend

Defines the text that appears in the chart legend.

#background

Creates the entire background for the SVG plug-in.

For a solid white background with no border, change #background to the following:

#background{fill:#FFFFFF;stroke:#FFFFFF;stroke-width:2;}

rect.chartholderbackground

Not applicable to pie and dial charts. Creates the background of the rectangle that holds the chart data.

For a clear background, change rect.chartholderbackground to the following:

rect.chartholderbackground(display:none;)

#timestamp

Only applicable if the Asynchronous Update chart attribute is set to Yes. Controls the appearance of the update timestamp test.

To disable the display of the timestamp, use defines #timestamp as follows in the Custom CSS, Inline attribute.

"#timestamp{display:none;}" 

See Also: "Enabling Asynchronous Updates"

text.tic

Dial charts only. Defines the numbers on a dial chart.

line.tic

Dial charts only. Defines the graduation mark that displays directly beneath the number on a dial chart.

#dial

Dial charts only. Defines the value that displays on the dial chart.

#dial.alert

Dial charts only. Defines a value (called an alert value) that renders in a dial chart using a different display.

#dialbackground

Dial charts only. Creates the background of a dial chart.

#dialcenter

Dial charts only. Creates the center of the dial on a dial chart.

#dialbackground-border

Dial charts only. Works in conjunction with #dialbackground to create specific graphic effect.

#low

Dial charts only. Defines the historical low watermark of the data being displayed on a chart.

#high

Dial charts only. Defines the historical high watermark of the data being displayed on a chart.

#XAxisTitle

Defines the title that appears on the x-axis

#YAxisTitle

Defines the title that appears on the y-axis.

.XAxisValue

Defines the value that appears on the x-axis.

.YAxisValue

Defines the value that appears on the y-axis.

.AxisLabel

Similar to the axis value.

.nodatafound

Defines the text element that displays if no information is available.

.AxisLine

Indicates zero on charts that have negative values.

.GridLine

Creates the horizontal and vertical lines on the chart.

g.dataholder rect

Applies a blanket style to all data that displays in the chart.

.legenditem rect

Applies a blanket style to all rectangular items in the legend.


Referencing a Custom Cascading Style Sheet

You can reference a custom cascading style sheet for a chart using the CSS section of the Chart Attributes page. When you reference an external CSS, you can reference it entirely or simply override specific styles.

To reference a custom chart CSS:

  1. Upload the CSS to Application Builder. See "Uploading Cascading Style Sheets".

  2. Create a chart. See "Creating a New Chart".

  3. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  4. Under Regions, click Chart next to the region name.

    The Chart Attributes page appears.

  5. Scroll down to the CSS section.

  6. From Use Custom CSS, select Yes.

  7. To reference an external CSS exclusively:

    1. In Custom CSS, Link, enter a link to a custom CSS. For example:

      #IMAGE_PREFIX#themes/theme_4/svg.css
      
      
    2. Specify that the CSS should be used exclusively. In Custom CSS, Inline enter the following:

      /**/
      
      
  8. To reference a custom CSS and override specific styles:

    1. In Custom CSS, Link, enter a link to a custom style sheet. For example:

      #IMAGE_PREFIX#themes/theme_4/svg.css
      
      
    2. In Custom CSS, Inline, enter the custom CSS styles you want to override.

Specifying Custom CSS Styles Inline

You can override specific styles within the default CSS, using the Custom CSS, Inline attribute on the Chart Attributes page.

To override specific styles within the default CSS:

  1. Create a chart. See "Creating a New Chart".

  2. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  3. Under Regions, click Chart next to the region name.

    The Chart Attributes page appears.

  4. Scroll down to CSS.

  5. From Use Custom CSS, select Yes.

  6. In Custom CSS, Inline, enter the custom CSS styles you want to override.

Enabling Asynchronous Updates

You can create charts that monitor information by enabling the Asynchronous Update attribute on the Chart attributes page. Enabling this attribute updates the chart to reflect changes in the underlying data within a specified time interval.

To enable asynchronous updates:

  1. Create a chart. See Creating a New Chart.

  2. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  3. Under Regions, click Chart next to the region name.

    The Chart Attributes page appears.

  4. Scroll down to Refresh.

  5. From Asynchronous Update, select Yes.

  6. In Update Interval (Seconds), enter the interval in seconds between chart updates. For optimal performance, select an interval that is greater than 2 seconds.

When Asynchronous Update is enabled, a timestamp displays on the chart indicating the last update.

To disable the Asynchronous Update timestamp:

  1. Navigate to the Chart Attributes page.

  2. Locate the CSS section.

  3. From Use Custom CSS, select Yes.

  4. In Custom CSS, Inline edit #timestamp as follows:

    #timestamp{display:none;}
    

Displaying Charts in Other Languages

To display a chart in another language, you edit the text and tspan classes to reflect the correct language.

To display a chart in another language:

  1. Navigate to the Chart Attributes page. See "Editing Chart Attributes".

  2. Scroll down to CSS.

  3. From Use Custom CSS, select Yes.

  4. In Custom CSS, Inline, edit the text and tspan classes to reflect the correct language. The following example demonstrates how to change a chart to Korean:

    text{font-family:Batang;fill:#000000;}
    tspan{font-family:Batang;fill:#000000;}
    
    

Creating Buttons

As you design your application, you can use buttons to direct users to a specific page or URL, or to post or process information (for example, by creating Create, Cancel, Next, Previous, or Delete buttons).

Buttons can perform two different types of actions. A button can submit a page and then redirect to a URL. Alternately, a button can branch to a URL without submitting the page, such as for a Cancel button.

Topics in this section include:

About the Buttons Section of the Page Definition

You create and edit buttons on the Page Definition. The Buttons section appears in the Page Rendering area. See "Accessing a Page Definition".

Description of pg_def_buttons.gif follows
Description of the illustration pg_def_buttons.gif

You can temporarily hide all other subsections by clicking the Buttons icon. To restore the view, click Show All. The Show All icon resembles an inverted triangle.

The following icons display next to the section title:

  • Edit All. The Edit All icon resembles a small grid with a pencil on top of it. Use this icon to edit all buttons at once.

  • Copy. The Copy icon resembles two small overlapping pages. Use this icon to make a copy of an existing button.

  • Create. The Create icon resembles a plus (+) sign overlapping a small page. Click this icon to create a new button.

Buttons are organized by region. To edit a button, click the button name.

Creating a Button Using a Wizard

You create a button by running the Create Button Wizard from the Page Definition. Each button resides in a region. A region is an area on a page that serves as a container for content.

To create a new button:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. If necessary, create an HTML region. See "Understanding Regions".

  3. Under Buttons, click the Create icon.

    The Create Button Wizard appears.

  4. Select a region to contain the button and click Next.

  5. Select a position for the button and click Next:

    • Create a button displayed among this region's items - Select this option to display the button within or between page items (for example, to add a button directly to the right of a form field).

    • Create a button in a region position - Select this option to place the button in a region position. A region position is a position defined by a region template.

  6. If you select Create a button in a region position:

    1. Specify the Button Name and Label.

    2. Select a Button Type: HTML Button (Default), Image, or Template Driven

      Select Button is Reset to create an Undo button. When enabled, this type of button resets the page values to the state they were in when the page was initially rendered.

    3. Select an Action:

      • Submit page and redirect to URL submits the current page to the Application Express engine whenever a user clicks the button.

      • Redirect to URL without submitting page avoids submitting the page. Choose this action when submitting the page for processing is not necessary (for example, a Cancel button). This action avoids processing in the database and therefore reduces the load.

    4. Click Next.

  7. If you select Create a button displayed among this region's items:

    1. Specify the Button Name and Sequence.

    2. Specify if the button displays at the beginning of a new line or new field.

    3. Specify a Label.

    4. Enter the value of Request.

    5. Select the Button Style.

    6. Click Next.

  8. Follow the on-screen instructions

Creating an HTML Button

Buttons can be placed in a predefined region template position or among items in a form. To create an HTML button, select one of the following while running the Create Button Wizard:

  • Under Task, select Create a button in a region position.

  • Under Button Type, select a button type and then HTML Button (default).

Creating Multiple Buttons

You can create multiple buttons within the same region at once using the Create Multiple Buttons Wizard.

To create multiple buttons at once:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. If necessary, create an HTML region. See "Understanding Regions".

  3. Under Buttons, click the Create icon.

    The Create Button Wizard appears.

  4. Select Create Multiple Buttons at the bottom of the page.

    The Create Multiple Button Wizard appears.

  5. From Place Buttons in Region, select the region to contain the buttons.

  6. From Template, select a template.

  7. In HTML Attributes, specify HTML attributes for these buttons. This text will be added to the HTML element definition. For example, you could set the class of a text button as follows:

    class="myclass"
    
    
  8. To quickly populate the remaining fields, make a selection from the Quick Button list on the right side of the page.

  9. Click Create Buttons.

Editing Buttons

To edit a button attributes, click the button name on the Page Definition.

Topics in this section include:

Editing Button Attributes

You can edit button attributes on the Edit Pages Button page.

To edit attributes for an existing button:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Buttons, select the button name.

    The attributes page for the button appears.

  3. To learn more about a specific item on a page, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  4. Click Apply Changes.

Using the Reorder Buttons Icon

You can quickly edit a button label or change a button position within a region by clicking the Reorder Region Buttons icon on the Page Definition. The Reorder Region Buttons icon resembles a light green downward arrow and upward arrow and displays next to the region name.

Description of reorder_buttons.gif follows
Description of the illustration reorder_buttons.gif

To edit buttons using the Reorder Region Buttons icon:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Buttons, click the Reorder Region Buttons icon.

    The Reorder Buttons page appears in a separate window. Use this window to edit the button label, select a new region position, or change the button order.

  3. To edit the button label, enter a new name in the Label field.

  4. To change the region position, make a selection from the Position list.

  5. To change the order in which buttons display, click the up and down arrows in the far right column.

    Note that you can also control the order in which buttons display by editing the Sequence attribute. See "Editing Button Attributes".

  6. Click Apply Changes.

Note:

To change the region where a button resides, you must edit the button attributes. See "Editing Button Attributes".

Understanding the Relationship Between Button Names and REQUEST

The name you give a button determines the value of the built-in attribute REQUEST. You can reference the value of REQUEST from within PL/SQL using the bind variable :REQUEST. By using this bind variable, you can conditionally process, validate, or branch based on which button the user clicks. You can also create processes that execute when the user clicks a button. And you can use a more complex condition as demonstrated in the following examples:

If :REQUEST in ('EDIT','DELETE') then ...
If :REQUEST != 'DELETE' then ...

These examples assume the existence of buttons named EDIT and DELETE. You can also use this syntax in PL/SQL Expression conditions. Be aware, however, that the button name capitalization (case) is preserved. In other words, if you name a button LOGIN, then a request looking for the name Login will fail. For example:

<input type="BUTTON" value="Finish" onClick="javascript:doSubmit('Finish');">

Note that in this example Finish is the name of the REQUEST and this example is case-sensitive.

About Branching with Buttons

Each page can include any number of branches. A branch links to another page in your application or to a URL. The Application Express engine considers branching at different times during page processing. You can choose to branch before processing, before computation, before validation, and after processing. Like any other control in Application Builder, branching can be conditional. For example, you can branch when a user clicks a button. When you create a branch, you associate it with a specific button. The branch will only be considered if a user clicks the button.

Displaying Buttons Conditionally

You can choose to have a button display conditionally by editing attributes on the Edit Pages Button page.

To have a button display conditionally:

  1. Create the button. See "Creating a Button Using a Wizard".

  2. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  3. Under Buttons, select the button name.

    The attributes page for the button appears.

  4. Scroll down to Conditional Button Display.

  5. Make a selection from the Condition Type list.

  6. Enter an expression in the fields provided.

  7. Click Apply Changes.

Creating Items

An item is part of an HTML form. An item can be a text field, text area, password, select list, check box, and so on. Item attributes affect the display of items on a page. For example, these attributes can impact where a label displays, how large an item will be, and if the item will display next to or below the previous item.

There are two types of items: page items and application items. Page items are placed on a page and have associated user interface properties, such as Display As, Label and Label Template. Application items are not associated with a page and therefore have no user interface properties. You can use an application item as a global variable.

Topics in this section include:

Understanding Page-Level Items

A page-level item can be placed on a page and has associated user interface properties. Examples of page-level items include a check box, date picker, display as text, file browse field, popup list of values, select list, or a text area.

Topics in this section include:

About the Items Section of the Page Definition

You create and edit page-level items on the Page Definition. The Items section appears in the Page Rendering area.

Description of pg_def_items.gif follows
Description of the illustration pg_def_items.gif

You can temporarily hide all other subsections by clicking the Items icon. To restore the view, click Show All. The Show All icon resembles an inverted triangle.

The following icons display next to the section title:

  • Edit All. The Edit All icon resembles a small grid with a pencil on top of it. Use this icon to edit all items at once.

  • Copy. The Copy icon resembles two small overlapping pages. Use this icon to make a copy of an existing item.

  • Create. The Create icon resembles a plus (+) sign overlapping a small page. Click this icon to create a new item.

Items are organized by region. To edit an item, click the item name.

Creating a Page-Level Item

You create a page-level item by running the Create Item Wizard from the Page Definition.

To create a new page-level item:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. If necessary, create an HTML region. See "Understanding Regions".

  3. Under Items, click the Create icon.

  4. Select an item type. See "About Item Types".

  5. Follow the on-screen instructions

About Item Naming Conventions

When specifying an item name, remember the following rules. Item names must:

  • Not have quotation marks

  • Begin with a letter or a number, and subsequent characters can be letters, numbers, or underscore characters,

  • Be case-insensitive.

  • Should not exceed 30 characters.

  • Cannot contain letters outside the base ASCII character set.

About Item Types

When you create an item, you specify an item type. Once you create an item, these types appear on the Display As list on the Edit Page Item page. Table 5-8 describes available item types.

Table 5-8 Available Item Types

Item Type Description

Check box

Displayed using a list of values. A list of values is required for items displayed as check boxes. The value corresponding to a checked box is returned in a single colon-delimited string.

The following example demonstrates how to create a single check box that returns YES. This example would display both a check box and a field label.

SELECT NULL display_text, 'YES' return_value FROM DUAL;

This example includes the additional text Click to select.

SELECT 'Click to select' display_text, 'YES' return_value FROM DUAL;

See Also: "APEX_UTIL" for information about breaking up returned values

Date Picker

Displays a text field with a Calendar icon next to it. When clicked, this icon displays a small calendar where the user can select a date and a time (optional).

If the format you need is not included in the Display As list, select Date Picker (use application format mask). When using a format mask, your application looks for the format in an item called PICK_DATE_FORMAT_MASK. Note that you need to populate this item before this item type will work.

See Also: "Populating an Alternative Date Picker Format"

Display As Text

Available Display As Text subtypes include:

  • Display as Text (based on LOV, does not save state) - Displays the display value from an LOV by matching the item's source value with the LOV's return value.

  • Display as Text (based on LOV, saves state) - Same as the previous option, but also generates a form item that gets submitted with the page to pass the return value into session state.

  • Display as Text (based on PL/SQL, does not save state) - Displays the output of an anonymous PL/SQL block.

  • Display as Text (does not save state) - Displays the item's source value on the page without creating a form item.

  • Display as Text (escape special characters, does not save state) - Displays the item's source value with special characters ('<','>','&') escaped.

  • Display as Text (saves state) - Displays the item's source value and creates a form item that gets submitted with the page to pass the value into session state.

File Browse

Displays a text field with a Browse... button. This enables the user to locate a file on a local file system and upload it. Oracle Application Express provides a table for these files to be uploaded to as well as an API to retrieve the files.

See Also: "Understanding the Security Risks of File Upload Tables"

Hidden

Renders an HTML hidden form element. Session state can be assigned and referenced just like a text field.

List Managers

Based on a list of values. This item enables you to manage a list of items by selecting and adding to a list. The list of values display as a popup.

Multiple Select

Renders as a multiselect HTML form element. When submitted, selected values are returned in a single colon-delimited string. You can break up the values using the APEX_UTIL API.

See Also: "Working with a Multiple Select List Item" and "APEX_UTIL"

Password

Renders as an HTML password form element.

Popup List of Values

Renders as a text field with an icon. When the user clicks the icon, a popup window appears with a list of values represented as a series of links. When the user makes a selection from this list, the selected value will be placed in the text field. You control popup LOVs through templates. You can only specify one popup LOV template for each application.

Using a popup LOV is a good choice for lists of values that are too large to return on a single page.

There are two types of Popup LOVs: one that fetches a set of rows when the window pops up and one that does not.

Popup LOVs must be based on a query that selects two columns with different column aliases. For example:

SELECT ename name, empno id 
   FROM emp

If one of the columns is an expression, remember to use an alias. For example:

SELECT ename||' '||job display_value, empno FROM emp

Radio

Renders as an HTML radio group form element, based on a list of values. Choose Radiogroup with Submit to have the page submitted when the radio button is selected.

The following example displays employee names (ename), but returns employee numbers (empno):

SELECT ename, empno FROM emp

Select List

Displays using a list of values. A list of values is required for items displayed as a select list. Select lists are rendered using the HTML form element <select>. The values in a select list are determined using a named list of values or a list of values defined at the item level. You can specify the NULL display value and NULL return value.

The following example would return employee names (ename) and employee numbers (empno) from the emp table. Note that column aliases are not required and are included in this example for clarity.

SELECT ename display_text, empno return_value FROM emp

Oracle Application Express provides additional enhancements to a standard HTML select list:

  • Select List with Submit - Submits the page when the user changes its selected value. Upon submit, the REQUEST will be set to the name of the item that represents the select list, allowing you to execute conditional computations, validations, processes, and branches.

  • Select List with Redirect - Redirects the user back to the same page, setting ONLY the newly selected value of the select list in session state.

  • Select List Returning URL Redirect - Based on a list of values with URLs as the return values. Changing the value of the select list causes the browser to redirect to the corresponding URL.

  • Select List with Branch to Page - Based on a list of values with page numbers as return values. Changing the selected value in the select list causes the Application Express engine to branch to the corresponding page.

Note: Long select lists can cause errors. If you have a long select list that generates an error, try using a Popup List of Values instead.

Stop and Start Table

Forces the close of an HTML table using the </table> tag and starts a new HTML table. You can use this item type to reset the column width in the middle of the region.

Note that a Stop and Start Table item only displays its label. You can prevent the label from displaying at all by setting it to null. To do this, you simply remove the default label.

Text

Displays as an HTML text field containing a maximum of 30,000 bytes of text. You control the maximum length and display width by editing the Height and Width item attribute.

Available Text display options include:

  • Text Field - Renders as a text field.

  • Text Field (Disabled, does not save state) - Displays a read-only version of a display value from a list of values by using the item's value in session state to look up the corresponding display value in the associated list of values. The value displayed on the screen is not saved in session state upon submit.

  • Text Field (Disabled, saves state) - Displays a read-only version of a display value from a list of values by using the item's value in session state to look up the corresponding display value in the associated list of values.

  • Text Field (always submits page when Enter pressed) - Displays a read-only version of the value in session state. Upon submit, the value displayed is saved in session state.

  • Text Field with Calculator Popup - Renders as a text field with an icon next to it. When clicked, the icon displays a small window containing a calculator. Calculations are placed back in the text field.

Text Area

Renders as an HTML text area. There is no maximum length for an item displayed as a text area. You control the height and width by editing the Height and Width item attribute. Additional available Text Area Display As options include:

  • Text Area (auto height) - Varies the height based on the amount of text. Use this option to scale the text area to the amount of data.

  • Text Area with Counter - Includes a counter that displays the number of bytes entered in the field.

  • Text Area with Spell Checker - Provides a popup English language spell checker.

  • Text Area with HTML Editor - Provides basic text formatting controls. Note that these controls may not work in all Web browsers.


Editing Page Item Attributes

Once you create a page item, you can edit it on the Edit Page Item page.

To edit page item attributes:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Items, select the item name.

    The Edit Page Item page appears.

  3. To learn more about a specific item on a page, click the item label.

    When help is available, the item label changes to red when you pass your cursor over it, and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  4. Click Apply Changes.

About Navigation Alternatives

The Edit Page Item page is divided into the following sections: Name, Displayed, Label, Element, Source, Default, List of Values, Security, Conditions, Read Only, Help Text, Configuration, and Comments.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

About Creating a Static List of Values

You can create a static list of values that is not a named or shared list of values, but is saved along with the page item's attributes by editing a page item's List of Values definition.

To create a static list of values:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Items, select the item name.

    The Edit Page Item page appears.

  3. Under Named, specify how the item will be rendered by making a selection from the Display As list.

  4. Under List of Values, create a static list of values:

    1. From Named LOV, select Select Named LOV.

    2. In List of values definition, enter a definition using the following syntax:

      STATIC[2]:Display Value[;Return Value],Display Value[;Return Value]
      
      

      Where:

      • The first keyword may be STATIC or STATIC2.

        STATIC results in the values being sorted alphabetically by display value. STATIC2 results in the values being displayed in the order they are entered in the list.

      • A semicolon separates the display value from the return value in each entry.

      • Return Value is optional. If a Return Value is not included, the return value is the same as the display value.

  5. To learn more, see item Help. To view help on a specific item on a page, click the item label.

    When help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  6. Click Apply Changes.

    The examples that follow demonstrate syntax for three different static LOVs.

Example 1: Four Values Displayed in Alphabetical Order

In this example, the list of values has four values (Cow, Dog, Cat, and Lion) that display in alphabetical order. The return value of each entry equals the display value.

STATIC:Cow,Dog,Cat,Lion

Example 2: Ten Values Displayed in the Order Listed

In this example, the list of values has ten values that display in the order listed in the definition. The return value of each entry equals the display value.

STATIC2:10,15,20,25,50,100,200,500,1000,10000

Example 3: A List of Values with Having Both a Return and Display Value

In this example, the list of values has two values: Yes and No (the display value Yes and its return value Y, and the display value No and its return value N).

STATIC:Yes;Y,No;N

Using the Reorder Items Icon

You can quickly edit the label and position of items in a region by clicking the Reorder Region Items icon on the Page Definition. This icon resembles a light green down or up arrow.

Description of reorder_items.gif follows
Description of the illustration reorder_items.gif

To use the Reorder Region Items icon:

  1. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Items, click the Reorder Region Items icon.

    The Reorder Region Items page appears in a separate window.

    Note that items are laid out in tables. You can edit the position of an item by selecting values for New Line, New Field, Column Span, and Label Alignment attributes. Note that a graphical representation of how the items display appears at the bottom of the page.

  3. To edit an item label, enter a new title in the Label field.

  4. To change the position of an item, edit the following attributes:

    • New Line. Determines if the item displays on the same line as the previous item, or displays on the next line. Select Yes to have an item display as the first field in a new row in the table.

    • New Field. Determines if the item displays in the next column or in the same column as the previous item. Select Yes to have the label and value for the item display in a new HTML table cell. Use this attribute in combination with the New Line and Span attributes to control layout.

    • Span. Defines the value to be used for the COLSPAN attribute in the table cell. The COLSPAN attribute defines the number of columns that it spans across the table.

  5. To change label alignment, make a new selection from the Label Alignment list.

  6. To change the order in which items display, click the up and down arrows in the far right column. Clicking the arrow moves the item one row up or down.

    Note that the order you specify here translates to sequence number in the Sequence attribute on the Edit Page Item page. See "Editing Page Item Attributes".

  7. Click Apply Changes.

Note:

To change the region in which an item resides, you must edit the item attributes. See "Editing Page Item Attributes".

Referencing Item Values

You can reference item values stored in session state in regions, computations, processes, validation, and branches. Table 5-9 describes the supported syntax for referencing item values.

Table 5-9 Syntax for Referencing Item Values

Type Syntax Description

SQL

:MY_ITEM

Standard bind variable syntax for items whose names are no longer than 30 bytes. Use this syntax for references within a SQL query and within PL/SQL.

PL/SQL

V('MY_ITEM')

PL/SQL syntax referencing the item value using the V function.

See Also: "Oracle Application Express APIs"

PL/SQL

NV('MY_NUMERIC_ITEM')

Standard PL/SQL syntax referencing the numeric item value using the NV function.

See Also: "Oracle Application Express APIs"

Static Text

&MY_ITEM

Static text.

Static Text (exact)

&MY_ITEM.

Static text. Exact Substitution.


You can set the value of an item in your application using any of the following methods:

  • For page items, use the Source Attribute to set the item value.

    From the Page Definition, select the item name to view the Edit Page Item page. Scroll down to Source and edit the appropriate fields.

    You can also set the value of an item in any region based on PL/SQL or a process using the following syntax:

    BEGIN
     :MY_ITEM :=  'new value';
    END;
    
    
  • Pass the value on a URL reference using f?p syntax. For example:

    f?p=100:101:10636547268728380919::NO::MY_ITEM:ABC
    
    
  • Set the value using a computation. Computations are designed to set item values. For example:

    TO_CHAR(SYSDATE,'Day DD Month, YYYY');
    
    
  • Use the PL/SQL API to set an item value within a PL/SQL context. For example:

    APEX_UTIL.SET_SESSION_STATE('MY_ITEM',SYSDATE);
    
    

Displaying Conditional or Read-Only Page Items

You can choose to have an item display conditionally or as read-only by editing attributes on the Edit Pages Item page.

To display a conditional or read-only item:

  1. Create the item. See "Understanding Page-Level Items".

  2. Navigate to the appropriate Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  3. Under Items, select the item name.

    The Edit Page Item page appears.

  4. To display an item conditionally:

    1. Scroll down to Conditions.

    2. Make a selection from the Condition Type list.

    3. Enter an expression in the fields provided.

  5. To make an item read-only:

    1. Scroll down to Read Only Display Settings.

    2. Make a selection from the Read Only Condition Type list.

    3. Enter an expression in the fields provided.

  6. Click Apply Changes.

Working with a Multiple Select List Item

A multiple select item renders as a multiple select list form element. When submitted, selected values are returned in a single colon-delimited string. You can handle values in this format in two ways:

  • Using the INSTR function

  • Using the APEX_UTIL.STRING_TO_TABLE function

Using APEX_UTIL.STRING_TO_TABLE to Convert Selected Values

Suppose you had a report on the EMP and DEPT tables that is limited by the departments selected from a Department multiple select list. First, you create the multiple select item, P1_DEPTNO, using the following query:

SELECT dname, deptno
FROM dept

Second, you return only those employees within the selected departments as follows:

SELECT ename, job, sal, comm, dname
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND instr(':'||:P1_DEPTNO||':',':'||e.deptno||':') > 0


Next, assume you want to programmatically step through the values selected in the multiple select item, P1_DEPTNO. To accomplish this, you would convert the colon-delimited string into a PL/SQL array using the APEX_UTIL.STRING_TO_TABLE function. The following example demonstrates how to insert the selected departments into an audit table containing the date of the query.

DECLARE
    l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  --
  -- Convert the colon separated string of values into
  -- a PL/SQL array 

  l_selected := APEX_UTIL.STRING_TO_TABLE(:P1_DEPTNO);

  --
  -- Loop over array to insert department numbers and sysdate
  --

  FOR i IN 1..l_selected.count 
  LOOP
    INSERT INTO report_audit_table (report_date, selected_department)
        VALUES (sysdate, l_selected(i));
  END LOOP;
END;

Understanding Application-Level Items

Application level items do not display, but are used as global variables to the application.

Topics in this section include:

Creating an Application-level Item

To create a new application-level item:

  1. Navigate to the Workspace home page.

  2. Click the Application Builder icon.

  3. Select an application.

  4. When Application Builder appears, click Shared Components.

  5. Under Logic, select Application Items.

    The Application Items page appears.

  6. To create a new application item, click Create.

  7. Follow the on-screen instructions.

About the Application Items Page

Once you create a application item, it appears on the Application Items page. You control how the Application Items page displays by making a selection from the View list. Available options include:

  • Icons (the default) displays each application item as a large icon. To edit an application item, click the appropriate icon.

  • Details displays each application item as a line in a report. To edit an application item, click the name.

Accessing Application Item History

You can view a history of changes to application items by clicking History at the top of the Application Items page.

Editing Application-level Item Attributes

Once you create an application-level item, you can edit it on the Create/Edit Application Item page.

To edit application-level item attributes:

  1. Navigate to the Workspace home page.

  2. Click the Application Builder icon.

  3. Select an application.

  4. When Application Builder appears, click Shared Components.

  5. Under Logic, select Application Items.

    The Application Items page appears.

  6. Select an application item.

    The Create/Edit Application Item page appears.

  7. To learn more about a specific item on a page, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  8. Click Apply Changes.

About Navigation Alternatives

The Create/Edit Application Item page is divided into the following sections: Name, Security, Configuration, and Comments.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Populating an Alternative Date Picker Format

If you need to create a Date Picker item, but the format you need does not appear in the Display As list, select Date Picker (use application format mask). When an application uses this type of date picker, the Application Express engine derives the date format from an item named PICK_DATE_FORMAT_MASK. You can populate this item in two ways:

  • By defining an application substitution string named PICK_DATE_FORMAT_MASK

  • By creating an application-level item named PICK_DATE_FORMAT_MASK

Defining PICK_DATE_FORMAT_MASK as an Application Substitution String

One approach to populating PICK_DATE_FORMAT_MASK is to create an application substitution string. You define application-level substitution strings on the Edit Definition page. Remember that an application-level substitution string is a static value and cannot be altered at run time.

To define a new application substitution string named PICK_DATE_FORMAT_MASK:

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

    Application Builder appears.

  2. Select an application.

  3. Click Edit Attributes.

  4. Click Definition.

  5. Scroll down to Substitutions.

  6. Create a new static substitution string named PICK_DATE_FORMAT_MASK:

    1. In Substitution String, enter the name PICK_DATE_FORMAT_MASK.

    2. In Substitution Value, enter a value for your date format (for example, Month DD, YYYY).

Defining an Application-Level Item Named PICK_DATE_FORMAT_MASK

Another approach to populating PICK_DATE_FORMAT_MASK is to create an application-level item named PICK_DATE_FORMAT_MASK. This approach enables you to control any items rendered as Date Picker (use application format mask) by simply setting the value of this item. Plus, you can set the value of PICK_DATE_FORMAT_MASK using a computation from anywhere within your application.

If you want to provide the user with a list of date formats as preferences, you will need to create an application-level item named PICK_DATE_FORMAT_MASK and then use a computation to set the value of this item based upon the user's selection.

Creating Lists of Values

A list of values (LOV) is a static or dynamic set of values used to display a specific type of page item, such as popup lists of values, a select list, a check box, a radio group, or multiple select lists.

Topics in this section include:

See Also:

"Creating Items"

Creating Named LOVs at the Application Level

You define named (or shared) LOVs at the application level by running the Create LOV Wizard and adding them to the List of Values repository. All LOVs can be defined as static or dynamic. Static lists are based on predefined pairs of display values and return values. Dynamic lists are based on a SQL query you write that selects values from a table.

To create a named LOV:

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

  2. Select an application.

    The Application home page appears.

  3. Click the Shared Components icon.

  4. Under User Interface, select Lists of Values.

  5. To create a new LOV, click Create.

  6. Follow the on-screen instructions.

    New named LOVs are added to the List of Values repository.

About the List of Values Page

Once you create an LOV, it appears on the List of Values page. You control how the page displays by making a selection from the View list. Available options include:

  • Icons (the default) displays each LOV as a large icon. To edit an LOV, click the appropriate icon.

  • Details displays each LOV as a line in a report. To edit an LOV, click the name.

About Static LOVs

Static LOVs are based on a static list of display values and return values you specify when you run the Create LOV Wizard. To create a static LOV, you run the Create LOV Wizard and select the LOV type Static. Oracle Application Express stores the display values, return values, and sort sequence you specify in the List of Values repository. Once you add a static LOV to the repository, you can create an item and display it as a check box, radio group, select list, or popup list based on this definition.

Editing an Existing LOV

To edit an existing LOV, select the LOV on the Lists of Values page.

To edit an LOV:

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

  2. Select an application.

  3. Click Shared Components.

  4. Under User Interface, select Lists of Values.

  5. Select an LOV.

    The Edit List of Values page appears.

  6. To learn more about a specific item on a page, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  7. Click Apply Changes.

About Navigation Alternatives

The Edit List of Values page is divided into the following sections: Name, Subscription, Source, and Comments.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Bulk Edit of Static LOVs

You can edit the display values of all static LOVs by clicking the Grid Edit button on the Edit List of Values page.

To perform a bulk edit of static LOVs:

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

  2. Select an application.

  3. Click Shared Components.

  4. Under User Interface, select Lists of Values.

    By default, LOVs display as icons.

  5. Change the default display. Select Details from the View list and click Go.

  6. Locate the Static LOV and select the LOV name.

  7. Click the Grid Edit button located under Subscription.

  8. Edit the appropriate display values and click Apply Changes.

Referencing Session State Within an LOV

You can reference session state by using bind variables. Keep in mind that referencing session state makes an LOV a bit less reusable, but is still a recommended development practice. In the following example, this LOV only works if the item called my_deptno contains a valid department number.

SELECT ename, empno FROM emp WHERE deptno = :P1_DEPTNO

Referencing a Null Value in an Item Based on an LOV

LOVs have a null display value option and a null return value option. The null display value is the value the end user sees in the list indicating the no selection from the proper (non-null) values of the list will be made. When a user selects a null display value, the LOV's null return value is sent to the application when the page is submitted. If the developer has left the null return value unspecified (or empty), the actual value transmitted is not an empty string or an Oracle null, but the literal %null%. The application must be prepared to deal with this literal and treat is as the null selection.

Be aware of this behavior when writing code to evaluate submitted values. For example, suppose a page evaluates the submitted item P1_X and you need to use the PL/SQL expression replace(:P1_X,'%'||'null%',null) to prepare the item for permanent storage in session state or for passing to DML or other APIs.

To avoid problems, be aware of the appropriate way to code %null% in expressions that occur in page computations, processes, and validations. You must break up the string so that the application does not replace %null% with a null value in the page metadata when it is saved. Consider the following example:

'%'||'null%'

Accessing LOV Reports

Application Builder includes a number of reports designed to help you better manage LOVs.

To access LOV reports:

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

  2. Select an application.

  3. When Application Builder appears, click Shared Components.

  4. Under User Interface, select Lists of Values.

  5. Select one of the following tabs at the top of the page:

    • Search

    • Subscription

    • Utilization

    • History

  6. Follow the on-screen instructions.

Search

Click Search to display the Search Dynamic Lists of Values page. Use this page to search the queries that make up dynamic LOVs. Enter a query in the Query Contains field and click Go.

Subscription

Click Subscription to display the List of Values Subscription page. This page displays all subscribed LOVs in your application.

Utilization

Click Utilization to display the List of Values Utilization page. This page displays LOVs used in the current application. To edit an LOV, click the LOV name.

History

Click History to display the List of Values History page. This page displays a history of recently changed LOVs by date.

Using Shortcuts

By using shortcuts you can avoid repetitive coding of HTML or PL/SQL functions. You can use a shortcut to define a page control such as a button, HTML text, a PL/SQL procedure, or HTML. Once defined, you can invoke a shortcut using specific syntax unique to the location in which the shortcut is used. Shortcuts can be referenced many times, thus reducing code redundancy.

This section contains the following topics:

About Shortcut Types

When you create a new shortcut, you must specify the type of shortcut you want to create. Oracle Application Express supports the following shortcut types:

  • PL/SQL Function Body

  • HTML Text

  • HTML Text with Escaped Special Characters

  • Image

  • Text with JavaScript Escaped Single Quotes

  • Message

  • Message with JavaScript Escaped Special Quotes

Text with JavaScript Escaped Single Quotes

Use this type of shortcut to reference a shortcut inside of a JavaScript literal string. This shortcut defines a text string. When the shortcut is referenced, it escapes the single quotation marks required for JavaScript.

Message

Use this type of shortcut to reference a translatable message at run time. Note that since this shortcut does not have a shortcut body, the name of the shortcut must match the corresponding message name. At run time, the name of the shortcut expands to the text of the translatable message for the current language.

Message with JavaScript Escaped Single Quotes

Use this type of shortcut to reference a shortcut inside of JavaScript literal string and reference a translatable message at run time.

Defining Shortcuts

Before you can incorporate a shortcut in your application, you must define it and add it to the Shortcuts repository. You reference new shortcuts using the following syntax:

"MY_SHORTCUT"

Note that the shortcut name must be capitalized and enclosed in quotation marks.

To define a new shortcut:

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

  2. Select an application.

  3. When Application Builder appears, click Shared Components.

  4. Under User Interface, select Shortcuts.

  5. Click Create.

  6. Select one of the following creation methods:

    • From Scratch

    • As a Copy of an Existing Shortcut

  7. Follow the on-screen instructions.

New shortcuts are added to the Shortcut repository and are available for use within the following locations:

About the Shortcuts Page

Once you create a shortcut, it appears on the Shortcuts page. You control how the page displays by making a selection from the View list. Available options include:

  • Icons (the default) displays each shortcut as a large icon. To edit a shortcut, click the appropriate icon.

  • Details displays each shortcut as a line in a report. To edit a shortcut, click the name.

Editing Existing Shortcuts

Once you create a shortcut, you can alter it by editing attributes on the Edit Shortcut page.

To edit an existing shortcut:

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

  2. Select an application.

  3. Click Shared Components.

  4. Under User Interface, select Shortcuts.

  5. Select a shortcut.

    The Edit Shortcut page appears.

  6. To learn more about a specific item on a page, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  7. Click Apply Changes.

About Navigation Alternatives

The Edit Shortcut page is divided into the following sections: Name, Subscription, Source, Configuration, and Comments.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Accessing Shortcut Reports

Application Builder includes a number of reports designed to help you better manage shortcuts.

To access shortcut reports:

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

  2. Select an application.

  3. When Application Builder appears, click Shared Components.

  4. Under User Interface, select Shortcuts.

  5. Click one of the following tabs:

    • Subscription

    • History

    Note:

    The Subscription and History tabs only appear after you create a shortcut.

Subscribed Shortcuts

Click Subscription to display the Subscribed Shortcuts page. This page displays all subscribed shortcuts in your application.

Shortcut History

Click History to display the Shortcut History page. This page displays a history of recently changed shortcuts by date.

Searching for Items, Pages, Queries, Tables, or PL/SQL Code

You can search for items, pages, queries, tables, or PL/SQL code by clicking the Find icon on numerous pages within Application Builder.

This section contains the following topics:

About the Find Icon

The Find icon resembles a flashlight and often displays to the right of the Run Page and Edit Page icons as shown in the following illustration. The Find icon displays on many pages in Application Builder, including the Application Home page, the Page Definition, application attribute pages, and numerous pages for creating and managing shared components.

Description of find_icon.gif follows
Description of the illustration find_icon.gif

Searching for Items

In Application Builder, an item can be a text field, text area, password, select list, check box, and so on. You can use the Item Finder to search for items within the current application or within the schema associated with the workspace.

See Also:

"Creating Items"

To search for an item using the Item Finder:

  1. Click the Find icon.

    The Item Finder appears.

    A search bar displays at the top of the page and contains the following controls:

    • Search. Search for an item name. Enter case insensitive keywords in the Search field and click Go. To view all items, leave the Search field blank and click Go.

    • Page. Search for pages that contain items. Enter a page number in the Page field or select a page number from the list and click Go. To view all pages containing items, leave the Page field blank and click Go.

    • Display. Determine how many rows display in the resulting report. To change the number of rows that display, make a selection from the Display list and click Go.

    A Item Finder report appears, displaying the item name, label, item type, and associated page number.

    Description of item_finder.gif follows
    Description of the illustration item_finder.gif

  2. To restrict the report to display just items on a specific page, click the appropriate page number in the far right column.

    Note the page number you select appears in the Page field at the top of the page.

  3. To edit a specific item, navigate to the appropriate item. See "Editing Page Item Attributes".

Searching for Pages

A page (or Page Definition) is the basic building block of an application. You can use the Page Finder to search for pages within the current application or within the schema associated with the workspace.

To search for a page:

  1. Click the Find icon.

  2. Select the Pages tab.

    The Page Finder appears.

    A search bar displays at the top of the page and contains the following controls:

    • Search. Search for a page name. Enter case insensitive keywords in the Search field and click Go. To view all pages, leave the Search field blank and click Go.

    • Display. Determine how many rows display in the resulting report. To change the number of rows that display, make a selection from the Display list and click Go.

    The Page Finder report appears, displaying the page number, page name, page alias, page title, tab set, and counts of the number of items and regions on the page.

    Description of pages_finder.gif follows
    Description of the illustration pages_finder.gif

  3. To restrict the report to display just items on a specific page, click the appropriate page number in the far left column.

    Note the page number you select appears in the Page field at the top of the page.

Searching for Queries

You can use the Query Finder to locate a query within your application or within the schema associated with the workspace.

To search for a query using the Query Finder:

  1. Click the Find icon.

  2. Select the Queries tab.

    The Query Finder appears.

    A search bar displays at the top of the page and contains the following controls:

    • Search. Search for query statements. Enter case insensitive keywords in the Search field and click Go. To view all queries, leave the Search field blank and click Go.

    • Display. Determine how many rows display in the resulting report. To change the number of rows that display, make a selection from the Display list and click Go.

    The Query Finder report appears, displaying the page number, page name, the region containing the query, and the query source.

    Description of query_finder.gif follows
    Description of the illustration query_finder.gif

  3. To restrict the report to display queries associated with specific page, click the appropriate page number in the far right column.

    Note the page number you select appears in the Page field at the top of the page.

Searching for Tables

You can use the Table Finder to view tables within the schema associated with the workspace.

To view tables associated within the current schema:

  1. Click the Find icon.

  2. Select the Tables tab.

    The Table Finder appears.

    A search bar displays at the top of the page and contains the following controls:

    • Search. Search for a table name. Enter case insensitive keywords in the Search field and click Go. To view all tables, leave the Search field blank and click Go.

    • Display. Determine how many rows display in the resulting report. To change the number of rows that display, make a selection from the Display list and click Go.

    • Views. Select the Views checkbox and click Go to include views in the resulting report.

    The Table Finder report appears displaying the table name, the number of rows, and the object type.

  3. Select a table name.

    A table definition appears on the right side of the page.

    Description of table_finder.gif follows
    Description of the illustration table_finder.gif

    This report displays the column names, data type, length, precision, and scale as well as the SQL necessary to re-create the table appears at the bottom of the page.

Searching for PL/SQL

You can use the PL/SQL Finder to locate and view details about stored procedures, functions, and packages associated with each object within the schema associated with the workspace.

To search for PL/SQL code in the current schema:

  1. Click the Find icon.

  2. Select the PL/SQL tab.

    The PL/SQL Finder appears.

    A search bar displays at the top of the page and contains the following controls:

    • Search. Search for procedure, function, or package names. Enter case insensitive keywords in the Search field and click Go. To view all, leave the Search field blank and click Go.

    • Display. Determine how many rows display in the resulting report. To change the number of rows that display, make a selection from the Display list and click Go.

    • Procedures, Functions, or Packages. Select at least one check box and click Go to include procedures, functions, and packages in the report. You must select at least one check box to return results.

    The PL/SQL Finder report appears.

  3. To view additional details, select the procedure, function, or package name.

    Description of plsql_finder.gif follows
    Description of the illustration plsql_finder.gif

    The procedure, package, or function name appears as well as additional information including the source type, return type, argument names, data types, and IN/OUT parameters.

Incorporating JavaScript into an Application

Adding JavaScript to a Web application is a great way to add features that mimic those found in client/server applications without sacrificing all the benefits of Web deployment. Oracle Application Express includes multiple built-in interfaces especially designed for adding JavaScript.

Remember that JavaScript is not appropriate for data intensive validations. For example, to verify that a name is contained within a large database table, you would need to pull down every record to the client, creating a huge HTML document. In general, complex operations are much better suited for server-side Application Express validations instead of JavaScript.

This section contains the following topics:

Referencing Items Using JavaScript

When you reference an item, the best approach is to reference by ID. If you view the HTML source of an Oracle Application Express page in a Web browser, you would notice that all items have an id attribute. This id corresponds to the name of the item, not the item label. For example, if you create an item with the name P1_FIRST_NAME and a label of First Name, the ID will be P1_FIRST_NAME.

Knowing the item ID enables you to use the JavaScript method getElementById() to get and set item attributes and values. The following example demonstrates how to reference an item by ID and display its value in an alert box.

<script language="JavaScript1.1" type="text/javascript">
  function firstName(){    
    alert('First Name is ' + document.getElementById('P1_FIRST_NAME').value );
  }
 // or a more generic version would be
  function displayValue(id){    
    alert('The Value is ' + document.getElementById(id).value );
  }
</script>
 
  // Then add the following to the "Form Element Attributes" Attribute of the item:
  onchange="displayValue('P1_FIRST_NAME');"

Incorporating JavaScript Functions

There are two primary places to include JavaScript functions:

  • In the HTML Header attribute of the page

  • In a .js file in the page template

See Also:

"Text with JavaScript Escaped Single Quotes" for information about referencing a shortcut inside of a JavaScript literal string

Incorporating JavaScript in the HTML Header Attribute

One way to include JavaScript into your application is to add it to the HTML Header attribute of the page. This is a good approach for functions that are very specific to a page as well as a convenient way to test a function before you include it in the .js file.

You can add JavaScript functions to a page by simply entering the code into the HTML Header attribute of the Page Attributes page. For example, adding the following would make the test function accessible from anywhere on the current page.

To add JavaScript code in the HTML Header attribute:

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

  2. Select an application.

  3. Select a page.

  4. Click Edit Attributes.

  5. Scroll down to HTML Header.

  6. Enter code into HTML Header and click Apply Changes.

For example, adding the following would test a function accessible from anywhere on the current page.

<script type="text/javascript">
  function test(){
    window.alert('This is a test.');
  }
</script>

See Also:

"HTML Header"

Including JavaScript in a .js File Referenced by the Page Template

In Oracle Application Express you can reference a .js file in the page template. This approach makes all the JavaScript in that file accessible to the application. This is the most efficient approach since a .js file loads on the first page view of your application and is then cached by the browser.

The following demonstrates how to include a .js file in the header section of a page template. Note the line script src= that appears in bold.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <title>#TITLE#</title>
    #HEAD#
    <script src="http://myserver.myport/my_images/custom.js" type="text/javascript"></script>
</head>
<body #ONLOAD#>#FORM_OPEN#

See Also:

"Page Templates"

Calling JavaScript from a Button

Calling a JavaScript from a button is a great way to confirm a request. Oracle Application Express uses this technique for the delete operation of most objects. For example, when you delete a button, a JavaScript message appears asking you to confirm your request. Consider the following example:

<script type="text/javascript">
  function deleteConfirm(msg)
  {
var confDel = msg;
if(confDel ==null)
  confDel= confirm("Would you like to perform this delete action?");
else
  confDel= confirm(msg);
  
if (confDel== true)
  doSubmit('Delete');
  }
</script>

This example creates a function to confirm a delete action and then calls that function from a button. Note that the function optionally submits the page and sets the value of the internal variable :REQUEST to Delete, thus performing the delete using a process that conditionally executes based on the value of request.

Note that when you create the button you would need to select Action Redirect to URL without submitting page. Then, you would specify a URL target such as the following:

confirmDelete('Would you like to perform this delete action?');

Creating Dependent Select Lists

You can use a select list to determine the range of values of another select list on the same page. You can achieve this functionality by having a driving select list submit values to a subsequent select list. You incorporate these values in the subsequent select list as a bind variable in the WHERE clause of its query.

You can have one LOV drive another LOV by:

Consider the following example. The first LOV enables the user to pick a state.

SELECT state_name d, state_id v
FROM states

The second LOV selects the country name and country ID based on the state selected in the first LOV.

SELECT county_name d, county_id v
  FROM counties
WHERE state_id = :P1_STATE_ID

Creating a Help Page

Application Builder includes built-in attributes to create Help for your application. Creating Help for your application involves the following steps:

Help created in Application Builder displays on a dedicated Help page. To access Help, users click a link that takes them to a dedicated Help page. This Help page displays page and item Help topics specific to the page they are viewing.

Topics in this section include:

Creating a Help Page and Region

The first step in creating Help for your application is to create a dedicated page and Help Text region.

To create a new Help Text region:

  1. Create new page for your Help. See "Adding Pages to an Application".

  2. Navigate to the Page Definition of your Help page. See "Accessing a Page Definition".

  3. Under Regions, the Create icon.

  4. When prompted to select a region type, select Help Text.

  5. Follow the on-screen instructions.

Defining Help Text

You define Help text for a page or single item by editing attributes. Ideally, you would define these attributes as you create your application. For simplicity, however, the following procedures describe how to define this text after the fact.

To define page Help text:

  1. Navigate to the Page Definition for the page for which you want to add page Help.

  2. Click Edit Attributes to view the existing page attributes.

  3. Scroll down to Page Help Text.

  4. Enter your Help text in the field provided.

  5. Click Apply Changes.

Repeat the previous procedure for each page requiring page Help text.

To define item Help text for each page:

  1. Navigate to the Page Definition for the page for which you want to add item Help.

  2. Under Items, click name of the item you want to edit.

  3. Scroll down to Help Text.

  4. Enter your Help text in the field provided.

  5. Click Apply Change.

Repeat the previous procedure for each item requiring Help text.

Editing Multiple Item Help Topics at Once

If you are including item Help in your application, you can edit multiple item Help topics at once using the Bulk Edit Item Help report.

Accessing the Bulk Edit Item Help Report

To view the Bulk Edit Item Help report:

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

  2. Select an application.

  3. On the Tasks list, click View Application Reports.

  4. Click Page Components.

  5. Select Item Help Text.

    A report displays at the bottom on the page.

  6. In Bulk Item Help Report you can:

    • Update existing Help topics. Edit the Help text that appears and click Apply Changes.

    • Link to the Page Definition containing the item by clicking the page number.

    • Link to the Page Item by clicking the item name.

Seeding Item Help Topics

If you application does not yet contain item Help, you perform a mass update of default Help text.

To seed item Help topics:

  1. Access the Bulk Edit Item Help report.

  2. Click Seed Item Help Text.

  3. In Default Help Text, enter the default text to appear in all Help topics.

  4. Click Apply Changes.

Searching for Existing Item Help Topics

You can search for existing Help text, or for an item label.

To search for existing item Help topic:

  1. In Help Contains, enter keywords.

  2. Click Go.

Searching for an Item Label

To search for an item label:

  1. In Help Contains, enter keywords.

  2. Click Go.

Creating a Help Navigation Bar Entry

Once you have created your Help, the next step is to create a navigation bar entry so users can link to it.

To create a navigation bar entry:

  1. Navigate to the Page Definition:

    1. Navigate to the Workspace home page.

    2. Click the Application Builder icon.

    3. Select an application.

    4. Select a page.

      The Page Definition appears.

  2. Under Navigation Bar, click the Create icon.

  3. Specify the appropriate navigation bar entry attributes:

    • Sequence

    • Alt Tag Text

    • Icon Image Name

    • Image Height and Image Width

    • Text

    Specify the target location.

  4. To specify the target location:

    • From Target type, select Page in this application.

    • In Page, specify the page number.

    • In Request, type:

      &APP_PAGE_ID.
      
      

    By specifying substitution string &APP_PAGE_ID as the Request, you are instructing the Application Express engine to display Help text for the current page when the user clicks this icon.