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

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

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

8 Adding Application Components

This section describes how to use Application Builder to add pages to an application and add other components (reports, charts, or forms), page controls (buttons, items, list of values), and shared components (breadcrumbs, lists, or tabs).

Topics:

See Also:

"Using Application Builder", "Controlling Page Layout", and "Adding Navigation"

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:

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 and click Next.

  4. Select Report and click Next.

  5. Select a report type and click Next. Option include:

    • Interactive Report - Creates an interactive report based on a custom SQL SELECT statement you provide. For assistance in writing the SQL query, click the Query Builder button. End users can customize the report layout and data displayed by selecting options on the Actions menu.

    • Classic Report - Creates a report based on a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement that you provide. For assistance in writing the SQL query, click the Query Builder button.

    • Report on Web Service Result - Creates a report based on a Web service result. To learn more, see "Implementing Web Services".

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

  6. Follow the on-screen instructions. To learn more about a specific field, 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".

Understanding the Difference Between Interactive and Classic Reports

Oracle Application Express includes two main report types, an interactive report and a classic report. The main difference between these two report types is that interactive reports enable the user to customize the appearance of the data through a searching, filtering, sorting, column selection, highlighting, and other data manipulations.

About Interactive Reports

The following is an example of an interactive report.

Description of rpt_ir.gif follows
Description of the illustration rpt_ir.gif

Notice that the interactive report includes a Search Bar, Column Heading Menu links, and Link Column icons in the first column of each row. For a complete description of these components, see "Customizing Interactive Reports".

When using an interactive reports you can:

  • Include one interactive report per page

  • Display 100 columns using report columns. You can edit additional columns using Tree view or paginating through Report Column Attributes.

  • Include 999 rows per column heading filter (if no custom LOV is specified in the column attributes)

About Classic Reports

In contrast, a classic report does not include any of these customization features. The following is an example of a classic report built on top of the same data.

Description of rpt_classic.gif follows
Description of the illustration rpt_classic.gif

Classic reports do not include a Search Bar and the links at the top of each column only support basic sorting.

When using a classic report, you view 100 columns using report columns. You can edit additional columns using Tree view or paginating through Report Column attributes.

Customizing Interactive Reports

Interactive reports enable end users to customize reports. Users can alter the layout of report data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, different charts, group bys, and add their own computations.

Users can also set up a subscription so that an HTML version of the report will be emailed to them a designated interval. Users can create multiple variations of a report and save them as named reports, for either public or private viewing.

An interactive report can be customized in three ways: the Search bar, Actions menu and Column Heading menu.

Tip:

To learn more about controlling what options display in an interactive report, see "Editing Interactive Reports as a Developer"

Topics:

About the Search Bar

Description of ir_search_bar.gif follows
Description of the illustration ir_search_bar.gif

A search bar displays at the top of every interactive report and may include the following features:

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

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

  • Go button - Executes a search.

  • Reports - Displays alternate default and saved private or public reports.

  • View icons - Switches between icon, report and detail view of the default report (if enabled). May also include Chart and Group By View (if defined).

  • Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

Using the Select Columns Icon

A Select Columns icon displays to the left of the search bar. Clicking this icon displays a listing of all columns in the current report.

To search specific columns:

  1. Click the Select Columns icon and select a column.

    Description of ir_select_col_rpt.gif follows
    Description of the illustration ir_select_col_rpt.gif

  2. Enter keywords in the Text area and click Go.

    Description of ir_select_col.gif follows
    Description of the illustration ir_select_col.gif

    Notice in this example the filter Row text contains 'Edward' displays above the report.

  3. To disable the filter, select the Enable/Disable Filter check box.

  4. To delete the filter, click the Remove Filter icon.

Using the Actions Menu

The Actions menu appears to the right of the Go button on the Search bar. Use this menu to customize an interactive report.

Description of ir_action_menu.gif follows
Description of the illustration ir_action_menu.gif

The Actions menu contains the following options:

  • Select Columns specifies which columns to display and in what order. See "Selecting Columns to Display".

  • Filter focuses the report by adding or modifying the WHERE clause on the query. See "Adding a Filter".

  • Rows Per Page determines how many rows display in the current report. See "Specifying Rows Per Page".

  • Format contains the following submenu:

  • Flashback - Enables you to view the data as it existed at a previous point in time. See "Executing a Flashback Query".

  • Save Report - Saves the interactive report. Depending upon their user credentials, users can save four different types of reports. See "Saving a Report".

  • Reset - Enables users to reset the report back to the default report settings. See "Resetting a Report".

  • Help - Provides descriptions of how to customize interactive reports.

  • Download - Enables users to download a report. Available download formats depend upon your installation and report definition. See "Downloading a Report".

  • Subscription - Enables users to send themselves an interactive report by email. See "Subscribing to Emailed Reports"

Selecting Columns to Display

To customize a report to include specific columns, select Select Columns on the Actions menu.

To use the Select Columns option:

  1. Click the Actions menu and select Select Columns.

    The Select Columns region appears.

    Description of ir_select_column.gif follows
    Description of the illustration ir_select_column.gif

  2. Select the columns you wish to move. Click the center arrows to move a column from the Display in Report box to the Do Not Display box. To select multiple columns at once, press and hold the CTRL key.

  3. To change the order of the columns, click the Top, Up, Down, and Bottom arrows on the right.

  4. Click Apply.

    A revised report appears.

Adding a Filter

You can create a filter on an interactive report by adding or modifying the WHERE clause on the query. You can create two types of filters:

  • Column - Creates a custom column filter. Select a column (it does not need to be one that displays), select a standard Oracle operator (=, !=, not in, between), and enter an expression to compare against. Expressions are case sensitive. Use % as a wild card. For example:

    STATE_NAME like A%
    
  • Row - Creates a custom row filter. This filter creates a complex WHERE clauses using column aliases and any Oracle functions or operators. For example:

    G = 'VA' or G = 'CT'
    

    Where G is the alias for CUSTOMER_STATE.

Adding a Column Filter

To add a column filter:

  1. Click the Actions menu and select Filter.

    The Filter region appears.

  2. For Filter Type, select Column.

  3. In the Filter region, specify a column, an operator, and expression.

    Description of ir_filter_sel_col.gif follows
    Description of the illustration ir_filter_sel_col.gif

    This filter narrows the display to only customers in the city of St. Louis.

  4. Click Apply.

    Description of ir_filter.gif follows
    Description of the illustration ir_filter.gif

    Notice the filter City = 'St Louis' has been added to the Report Settings area above the report.

  5. To revise the filter:

    1. Click the filter name (in this example, City = 'St Louis').

    2. Edit your selections and click Apply.

  6. To disable the filter, select the Enable/Disable Filter check box.

  7. To delete the filter, click Remove Filter.

Adding a Row Filter

To add a row filter:

  1. Click the Actions menu and select Filter.

    The Filter region appears.

  2. For Filter Type, select Row.

  3. In the Filter region:

    1. Enter a Name.

    2. Enter expression. Select a column and function or operator at the bottom of the region.

    Description of ir_filter_sel_row.gif follows
    Description of the illustration ir_filter_sel_row.gif

  4. Click Apply.

    Description of ir_filter_sel_row_rpt.gif follows
    Description of the illustration ir_filter_sel_row_rpt.gif

    The filter K<= 1000 narrows the display to customer having credit limit that is less than or equal to 1000.

  5. To revise the filter:

    1. Click Row Filter.

    2. Edit your selections and click Apply.

  6. To disable the filter, select the Enable/Disable Filter check box.

  7. To delete the filter, click Remove Filter.

Specifying Rows Per Page

You can specify the number of rows that display on a page by selecting Rows Per Page on the Actions menu.

To specify the number of rows that display:

  1. Click the Actions menu and select Rows Per Page.

  2. From the submenu, select a number.

    Description of ir_row_per_page.gif follows
    Description of the illustration ir_row_per_page.gif

Selecting Columns and Sort Order

You can specify which columns to display and the sort order (ascending or descending) by selecting Sort on the Format submenu. You can also specify how to handle NULL values. Using the default setting always displays NULL values last or always display them first.

To sort by column:

  1. Click the Actions menu and select Format and then Sort.

    The Sort region appears.

    Description of ir_sort.gif follows
    Description of the illustration ir_sort.gif

  2. Select a column, the sort direction (Ascending or Descending), and Null Sorting behavior (Default, Nulls Always Last, or Nulls Always First).

  3. Click Apply.

Creating a Control Break

You can create a break group of one or several columns by selecting Control Break from the Actions, Format submenu. Creating a break group pulls the columns out of the interactive report and displays them as a master record.

To create a break group:

  1. Click the Actions menu and select Format and then Control Break.

    The Control Break region appears.

    Description of ir_ctrl_break.gif follows
    Description of the illustration ir_ctrl_break.gif

  2. Select a column and then a status (Enable or Disable)

  3. Click Apply.

    One or more break groups display in Report Settings area above the report.

  4. To expand the break group, click the plus (+) sign.

  5. To enable the Control Break filter, select the Enable/Disable Filter check box.

  6. To delete the filter, click Remove Filter.

Adding Highlighting

You can customize the display to highlight specific rows in a report by selecting Highlight on the Actions, Format submenu.

To add highlighting:

  1. Click the Actions menu and select Format and then Highlight.

    The Highlight region appears.

    Description of ir_highlight.gif follows
    Description of the illustration ir_highlight.gif

  2. Enter the following information:

    1. Name - Enter a name that describes this filter.

    2. Sequence - Enter numeric value to identify the sequence in which the rules are evaluated.

    3. Enabled - Select Yes.

    4. Highlight Type - Select Cell or Row. If Cell is selected, the column referenced in the Highlight Condition is highlighted.

    5. Background Color - Select a new color for the background of the highlighted area.

    6. Text Color - Select a new color for the text in the highlighted area.

    7. Highlight Condition - Select a column, an operator, and expression.

  3. Click Apply.

    Description of ir_highlight_rpt.gif follows
    Description of the illustration ir_highlight_rpt.gif

    Note a new highlight appears in the Report Settings area above the report.

  4. To revise the highlight:

    1. Click the highlight name (in this example, Top Customer).

    2. Edit your selections and click Apply.

  5. To disable the highlight, select the Enable/Disable Filter check box.

  6. To delete the highlight, click Remove Highlight.

Computing Columns

You can add computations to columns by selecting Compute from the Actions, Format submenu. These computations can be mathematical computations (for example, NBR_HOURS/24) or standard Oracle functions applied to existing columns. Note that some display as examples and others (such as TO_DATE) can also be used).

To create a computation:

  1. Click the Actions menu and select Compute.

    The Compute region appears.

  2. In the Compute region:

    1. Computation - Select New Computation.

    2. Column Heading - Enter the name of the new column to be created.

    3. Format Mask - Select an Oracle format mask to be applied to the new column. (for example, S9999).

      Next, create the computation.

    4. Create the computation:

      • Columns - Select a column or alias.

      • Keypad - Select a shortcut for commonly used keys.

      • Functions - Select the appropriate function.

      The following example, creates a new Commission column. This column calculates at 10% sales commission using the formula C*.10 where C is the order total.

      Description of ir_compute.gif follows
      Description of the illustration ir_compute.gif

  3. Click Apply.

    The revised report appears containing a new Commission column.

    Description of ir_compute_eg.gif follows
    Description of the illustration ir_compute_eg.gif

Deleting a Computation

To delete a computation:

  1. Click the Actions menu and select Format and then Compute.

    The Compute region appears.

  2. From Computation, select computation name.

  3. Click Delete.

Aggregating a Column

You can define a mathematical computation against a column (or aggregate) by selecting Aggregate from the Actions, Format menu. Aggregates are displayed after each control break and at the end of the report within the column in which they are defined.

To aggregate columns:

  1. Click the Actions menu and select Format and then Aggregate.

    The Aggregate region appears.

  2. In the Aggregate region:

    1. Aggregation - Select New Aggregation.

    2. Function - Select one of the following: Sum; Average, Count, Count Distinct, Minimum, Maximum, or Median.

    3. Column - Select a column.

      Description of ir_aggreg.gif follows
      Description of the illustration ir_aggreg.gif

      This example creates a sum or Order Total column.

  3. Click Apply.

    The computation appears at the bottom of the report.

    Description of ir_aggreg_rpt.gif follows
    Description of the illustration ir_aggreg_rpt.gif

    In this example, the aggregate shows the sum of all amounts in the Order Total column.

Removing an Aggregate

To remove aggregate columns:

  1. Click the Actions menu and select Format and then Aggregate.

    The Aggregate region appears.

  2. From Aggregation, select a previously defined aggregation.

  3. Click Delete.

Creating a Chart from the Actions Menu

To create a chart, select Chart on the Actions, Format menu. You can create one chart for each interactive report. Once defined, you can switch between the chart and report views using links on the Search bar.

To create a chart:

  1. Click the Actions menu and select Format and then Chart.

    The Chart region appears.

    Description of ir_chart.gif follows
    Description of the illustration ir_chart.gif

  2. In the Chart region, specify the following:

    1. Chart Type - Select the type of chart you wish to create (horizontal bar, vertical bar, pie, or line.).

    2. Label - Select the column to be used as the label.

    3. Axis for Title for Label - Enter title to be displayed on the axis associated with the column selected for Label (not available for pie chart).

    4. Value - Select the column to be used as the Value. If your function is a COUNT, a Value does not need to be selected.

    5. Axis Title for Value - Enter the title to display on the axis associated with the column selected for Value (not available for pie chart).

    6. Function - (Optional) Select a function to be performed on the column selected for Value.

      Click Apply and a View Chart icon appears in Search bar.

Deleting a Chart

To delete a chart:

  1. Click the Actions menu and select Format and then Chart.

    The Chart region appears.

  2. Click Delete.

Grouping Columns

Group By enables users to group the result set by one or more columns and perform mathematical computations against the columns. Once users define the group by, they can switch between the group by and report views using the View Icon on the Search bar.

To use Group By:

  1. Click the Actions menu and select Format and then Group By.

    The Group By region appears.

    Description of ir_group_by.gif follows
    Description of the illustration ir_group_by.gif

  2. From Group by Column, select the columns to display.

  3. To include a computation, select the function, column, label, and format mask.

  4. To specify sorting, select a column, sort direction, and preferences for NULL values.

    A Group By icon appears in the Search bar.

Deleting a Group By

To delete a group by:

  1. Click the Actions menu and select Format and then Group By.

    The Group By region appears.

  2. Click Delete.

Executing a Flashback Query

You can execute a flashback query by selecting Flashback from the Actions menu. A flashback query enables you to view the data as it existed at a previous point in time. The default amount of time that you can flashback is 3 hours (or 180 minutes) but the actual amount will differ for each database.

To execute a flashback query:

  1. Click the Actions menu and select Flashback.

  2. In the Flashback region, enter the number of minutes.

  3. Click Apply.

Saving a Report

Both developers and end users can save an interactive reports. However, only a developer can save the report that initially displays, the Primary Default, or create an Alternative Default report.

You can save four types of interactive reports:

  • Primary Default (Developer Only). The Primary Default is the report that initially displays. Primary Default reports cannot be renamed or deleted.

  • Alternative Report (Developer Only). Enables developers to create multiple report layouts. Only developers can save, rename, or delete an Alternative Report.

  • Public Report (End user). Can be saved, renamed, or deleted by the end user who created it. Other users can view and save the layout as another report.

  • Private Report (End user). Only the end user that created the report can view, save, rename or delete the report.

The following shows the Reports list on the Search bar of an interactive report.

Description of ir_reports_list.gif follows
Description of the illustration ir_reports_list.gif

Topics

Configuration Dependencies

The Save Report option on the Actions only displays if you:

The Reports select list on the Search Bar only displays if you select Reports Select List under Search Bar on the Report Attributes page. See "Customizing the Interactive Report Search Bar".

Note that many of these options are selected by default.

Saving a Primary or Alternative Interactive Report

Only developers can save a Primary Default interactive report. Primary Default reports cannot be renamed or deleted.

To save a default interactive report:

  1. Run the report as a developer.

  2. Customize the report (for example, hide columns, add filters, and so on). See "Using the Actions Menu".

  3. Click the Actions menu and select Save Report.

    The Save Report region appears.

    Description of ir_save_rpt.gif follows
    Description of the illustration ir_save_rpt.gif

  4. For Save, select As Default Report Settings.

  5. For Default Report Type, select either:

    • Primary - This report displays as the primary report when the page runs.

    • Alternative - This report displays as an alternative option on the Reports list on the Search bar. If you select Alternative, enter a name.

  6. Click Apply.

Saving a Public or Private Interactive Report

End users can save an interactive report and classify it as being either public or private. Public reports can be viewed by all users. Private reports can only be viewed by the user who saves the report.

Tip:

To save a public report, you must select Save Public Report must be selected under Include in Actions Menu on the Report Attributes page. in Include Actions menu list. Developers can also apply authorization scheme to enable or disable Save Public Report. See "Customizing the Interactive Report Search Bar".

To save a public or private interactive report:

  1. Go to the page containing the interactive report you wish to save.

  2. Customize the report (for example, hide columns, add filters, and so on). See "Using the Actions Menu".

  3. Click the Actions menu and select Save Report.

    The Save Report region appears.

  4. In Save Report:

    1. Save - Select As Named Report.

    2. Public- Select this check box to make the report viewable to all users. Deselect this check box to make the report private.

    3. Name - Enter a name for the report.

    4. Description - Enter an optional description.

    5. Click Apply.

Editing or Deleting Primary or Alternative Reports

To edit or delete a primary, alternative, public or private interactive report:

  1. Run the report as a developer.

  2. Customize the report (for example, hide columns, add filters, and so on). See "Using the Actions Menu".

  3. To edit the report:

    1. Click the alternative report name link.

    2. Edit the attributes (for example, enter a new name).

    3. Click Apply.

  4. To delete the report, click the Delete Report icon next to the alternative report name link.

About Exporting Interactive Reports

When you export an application, the Primary Default, Alternative Default, public reports are included by default. You can also choose to include personal reports during the export process by selecting Yes for Private Interactive Reports. See "Exporting an Application".

Resetting a Report

You can reset a report back to the default settings by selecting Reset from the Actions menu. Resetting a report removes any customizations you have made.

To reset a report:

  1. Click the Actions menu and select Reset.

  2. Click Apply.

Downloading a Report

You can download an interactive report back by selecting Download from the Actions menu. Available download formats depend upon your installation and report definition. Supported formats include comma-delimited file (CSV) format, HTML, Email, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), and Microsoft Word Rich Text Format (RTF). See "Configuring Download Options on the Actions Menu"

To download a report:

  1. Click the Actions menu and select Download.

  2. Select a report download format and follow the provided instructions.

Subscribing to Emailed Reports

End users can receive updated versions of a report by subscribing to it. To subscribe to a report, click Subscription on the Actions menu.

To utilize Subscription:

To receive updated report results by email:

  1. Click the Actions menu and select Subscription.

    The Add Subscription region appears.

  2. Under Add Subscription:

    1. Email Address - Enter the email address to receive the report.

    2. Subject - Enter text to display in the email subject line.

    3. Frequency - Select the interval at which the report is sent.

    4. Starting From - Select a start date and time.

    5. Ending - Select a end date and time. Select a day, week, month, or year.

    6. Click Apply.

Tip:

"Managing Interactive Report Subscriptions" in Oracle Application Express Administration Guide

Using the Column Heading Menu

Clicking a column heading exposes the Column Heading menu.

Description of ir_col_head_menu.gif follows
Description of the illustration ir_col_head_menu.gif

Column Heading menu options include:

  • Sort Ascending icon - Sorts the report by the column in ascending order.

  • Sort Descending icon - Sorts the report by the column in descending order.

  • Hide Column - Hides the column. Not all columns can be hidden. If a column cannot be hidden, there will be no Hide Column icon.

  • Break Column - Creates a break group on the column. This pulls the column out of the report as a master record.

  • Column Information - Displays help text about the column, if available.

  • Text Area - Used to enter a case insensitive search criteria (no need for wild cards). Entering a value reduces the list of values at the bottom of the menu. You can then select a value from the bottom. The selected value will be created as a filter using either the equal sign (=) or contains depending on the List of Values Column Filter Type. Alternatively, you can click the Flashlight icon and enter a value to be created as a filter with the LIKE modifier (for example, column LIKE '%ABC%').

  • List of Unique Values - Contains the first 500 unique values that meets your filter criteria. If the column is a date, a list of date ranges is displayed instead.

Editing Interactive Reports as a Developer

By default, an interactive report includes a Search Bar which contains a search icon, text area, Go button, and Actions menu. Users can use these controls to alter the layout of report data by selecting columns, applying filters, highlighting, and sorting. This section describes how developers can customize an interactive report by editing report attributes.

Topics:

Tip:

To learn more about using interactive reports as a user, see "Customizing Interactive Reports"

Accessing the Interactive Report Attributes Page

You customize an interactive report by editing attributes on the Interactive Report Attributes page.

To access the Report Attributes page:

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

  2. Select the application.

  3. Select the page containing the interactive report you wish to edit.

    The Page Definition appears.

  4. To access the Interactive Report Attributes page:

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

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

About Navigation Alternatives

The Report Attribute page is divided into these sections: Column Attributes, Groups, Pagination, Sorting, Search Bar, Download, Link Column, Icon View, Detail View, Advanced, and Description.

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

Description of int_rprt_nav.gif follows
Description of the illustration int_rprt_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.

Editing Interactive Report 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, or hide a column.

To access Column Attributes:

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

  2. Select the application.

  3. Select the page containing the interactive report you wish to edit.

    The Page Definition appears.

  4. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

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

    Description of int_rprt_col.gif follows
    Description of the illustration int_rprt_col.gif

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

    Heading displays the heading text to be included in the report. Type indicates the type of query column. The Link column indicates if a link column is currently defined.

    Table 8-1 describes common report column edits.

    Table 8-1 Common Report Column Edits for Interactive Reports

    Description Developer Action

    Alter column display sequence.

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

    Note that this display sequence is only used in the Single Row View if enabled. It does not affect report column display. The report column display can be changed when you run the report and save.

    Change column group assignment.

    If Column Groups are defined, individual columns can be assigned under Column Group.

    Change column heading text.

    Under Heading, enter different heading text.

    Change how columns display.

    Select an option from Display Text As.


    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.

    The Column Definition appears.

    Description of ir_col_def.gif follows
    Description of the illustration ir_col_def.gif

    Use the check boxes under Allow Users To to control how users manipulate the report. Options include: Hide, Sort, Filter, Highlight, Control Break, Aggregate, Compute, Chart, and Group By.

  6. Under Column Definition, Allow Users To, select the appropriate check boxes.

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

Creating Column Groups

Use column groups to group columns together on a single row view.

To create a column group:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Column Groups.

  3. Click Add Group.

  4. Under Column Group:

    1. Sequence - Enter the display sequence for this column group. This attribute determines the order in which the column groups display in the detail view.

    2. Group Name - Enter a column group name. This name displays in the region header in detailed view.

    3. Description - Enter an optional description for the column group. This description never displays to the end user.

  5. Click Create.

Controlling Interactive Report Pagination

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

To access the Pagination section of the Report Attributes page:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Pagination.

    You use the Pagination attributes to select a pagination style, determine where pagination occurs, specify the maximum number of rows to query, and messages to display when more than the maximum number of rows are returned or if no rows are returned.

    Table 8-2 describes the available Pagination attributes.

Table 8-2 Pagination Attributes

Attribute Description

Pagination Type

Select a pagination type. Pagination provides the user with information about the number of rows and the current position within the result set. Pagination can be displayed above or below the report (or both) and can be positioned on the left or right. Available pagination options include:

  • Row Ranges X to Y

  • Row Ranges X to Y of Z

Pagination Display Position

Defines where pagination occurs. Pagination can be display on the left side, right side, at the bottom, or above the report.

Show Null Values as

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

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.

When more than maximum row data found message

Defines the text message that displays when more than maximum row count are returned.

When No Data Found Message

Defines the text message that displays when the query does not return any rows.


Managing Interactive Report Column Sorting

You manage column sorting on the Interactive Report Attributes page.

To manage column sorting:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Under Column Attributes, click the Edit icon for the column for which you want to enable sorting.

  3. Under Column Definition, select Sort and click Apply Changes.

    The Interactive Report Attributes page appears.

  4. Scroll down to Sorting.

  5. Specify ascending and descending image attributes and click Apply Changes.

Customizing the Interactive Report Search Bar

A search bar displays at the top of every interactive report and includes a Search icon, Text area, Go button, and Actions menu.

Description of ir_search_bar.gif follows
Description of the illustration ir_search_bar.gif

You can remove or customize the search bar by selecting and deselecting options in the Search Bar section of the Interactive Reports Attributes page.

Topics:

Customizing the Search Bar

To customize the search bar:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Search Bar.

    Description of ir_att_search_bar.gif follows
    Description of the illustration ir_att_search_bar.gif

    Use these attributes to customize how the search bar displays. Table 8-3 describes Search bar attributes:

    Table 8-3 Search Bar Attributes

    Attribute Description

    Include Search Bar

    Select Yes or No. Selecting No removes the following default components:

    • Search Field - Displays a text field in the search bar in which to enter search criteria.

    • Finder Drop Down - Enables users to search on a selected column or against all columns.

    • Reports Select List - Displays report select list if the report has alternative default, shared report or personal report. See "Saving a Report".

    • Rows Per Page Selector - Displays a select list to enable users to select the number of rows to display per page.

    • Actions Menu - Enables the user to customize the report based on selected Actions.

    Include in Actions Menu

    Select and deselect functions included in the Actions menu. Available options include:

    • Select Columns - Used to hide, unhide, and order the report columns.

    • Filter - Used to add filters to the report (for example, STATE = VA).

    • Rows Per Page - Used to display rows per page menu.

    • Sort - Used to sort columns.

    • Control Break - Used to build control breaks on report columns.

    • Highlight - Used to define conditions that will highlight certain rows.

    • Compute - Used to add computed columns.

    • Aggregate - Used to include aggregate calculations on report columns.

    • Chart - Used to create a Flash chart based off the data in the report.

    • Group By - Used to create a Group By view based off the data in the report.

    • Flashback - Used to flashback the report data to some time in the past.

    • Save Report - Used to save the report settings for future use. Only available for authenticated pages.

    • Save Public Report * - Used to save Public Report for all users to view. You must select Save Report to enable this option. Only available for unauthenticated pages.

    • Reset - Used to reset the report to the default settings.

    • Help - Used to display instructions for using the Interactive report.

    • Download - Used to download the report in different formats.

    • Subscription * - Used to schedule a subscription to send the report in email. Only available for unauthenticated pages.

    See Also: "Using the Actions Menu".

    Save Report Public Authorization

    Users can save reports and classify them as being either public or private. Public reports can be viewed by all users. Private reports can only be viewed by the user who saved the report.

    In addition to enabling Save Public Report under Actions Menu, you can add additional security by defining an Authorization Scheme to determine who has rights to enable Save Public Report.

    To enable support for public reports, select an authentication method from this list.

    Button Template

    Select a template for buttons that display in the search region and in the dialogs.

    Finder Image

    Specify an image. If no image is defined, the following is used:

    /i/htmldb/builder/finder_w_dropdown.gif
    

    Actions Menu Image

    Specify an image. If no image is defined, the default button will be used.

    Search Button Label

    Specify the text to use as search button label.

    Maximum Rows Per Page

    Enter the maximum number to display in the Rows Per Page Selector.


  3. Click Apply Changes.

Removing the Search Bar

To remove the search bar:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Search Bar.

  3. From Include Search Bar, select No.

  4. Click Apply Changes.

Configuring Download Options on the Actions Menu

You can add a Download option to the Actions menu to enable users to download an interactive report as a comma-delimited file (CSV) format, HTML, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), Microsoft Word Rich Text Format (RTF), or as HTML attached to an email.

Description of download.gif follows
Description of the illustration download.gif

Enabling Download Formats

To configure download formats:

  1. Navigate to the page containing the interactive report.

  2. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  3. Scroll down to Search Bar.

    1. For Include Search Bar, select Yes and then select Actions Menu.

    2. Under Include in Actions menu, select Download.

  4. Scroll down to Download.

  5. Select download formats. Options include:

    Tip:

    The Download option only appears on the Actions menu if a file format is selected.
  6. Click Apply Changes.

Enabling CSV Download

To enable the Enable CSV download:

  1. Enable the CSV output option on the Edit Globalization Attributes page. See "Accessing the Globalization Attributes Page" and "Automatic CSV Encoding".

  2. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  3. Scroll down to Search Bar.

    1. For Include Search Bar, select Yes and then select Actions Menu.

    2. Under Include in Actions menu, select Download.

  4. Scroll down to Download.

  5. From Download Formats, select CSV.

    Tip:

    The Download option only appears on the Actions menu if a file format is selected.
  6. In the CSV Separator, define the column separator. If no value is entered, a comma or semicolon is used depending on your current NLS settings.

  7. In the CSV Enclosed By, enter delimiter character. This character is used to delineate the starting and ending boundary of a data value. Default delimiter is double quotation marks (" ").

  8. In Filename, enter a name for the downloaded document. Do not specify a file extension, the extension is added automatically. If you leave this blank, the region name is used as the file name.

  9. Click Apply Changes.

Enabling Email Download

You can configure the Download option to send static HTML report to specific email addresses.

To enable email download:

  1. An Oracle Application Express administrator must configure email at the Instance level. See "Configuring Mail" in Oracle Application Express Administration Guide

  2. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  3. Scroll down to Search Bar.

    1. For Include Search Bar, select Yes and then select Actions Menu.

    2. Under Include in Actions menu, select Download.

  4. Scroll down to Download.

  5. From Download Formats, select Email.

    Tip:

    The Download option only appears on the Actions menu if a file format is selected.
  6. Click Apply Changes.

Understanding Link Columns

A Link Column displays on the left side of an interactive report. It can link to a single row view, a custom target, or be excluded from the report. Note that a Link Column cannot be sorted, hidden, or moved by an end user and does not interact with the standard column link defined on the Column Attributes page.

Linking to a Single Row View

To link to a single row view:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Link Column.

  3. For Link Column, select Link to Single Row View.

  4. From Single Row View, select the appropriate options:

    • Allow Exclude Null Values - Excludes columns with null values.

    • Allow Displayed Columns - Displays only the columns shown in the report.

  5. For Uniquely Identify Rows by, select ROWID or Unique Column.

    If you select, Unique Column, specify the column in the Unique Column field.

  6. For Link Icon, select an icon.

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

  8. Click Apply Changes.

Linking to a Specific Page

To link to a specific page:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Link Column.

  3. For Link Column, select Link to Custom Target.

  4. For Link Icon, select an icon.

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

  6. For Target, select Page in this Application.

  7. For Page, select the target page number. To reset the pagination for this page, select Reset Pagination.

  8. For Request, specify the request to be used.

  9. For 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. See "Linking to Interactive Reports".

  10. For Name and Value, specify session state for a specific item.

  11. Click Apply Changes.

Linking to a URL

To link to a URL:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Link Column.

  3. For Link Column, select Link to Custom Target.

  4. For Link Icon, select an icon.

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

  6. From Target, select URL.

  7. In URL, enter the appropriate address.

  8. For Checksum - Select one of the following:

    • Use default

    • Application Level - Link reusable by any user

    • User Level - Link reusable by current user

    To learn more about each option, see item Help.

  9. Click Apply Changes.

Excluding a Link Column

To exclude a column link:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Link Column.

  3. For Link Column, select Exclude Link Column.

  4. Click Apply Changes.

Creating a Column Link in an Interactive Report

Use Column 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. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Under 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 to 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.

    8. For Checksum - Select one of the following:

      • Use default

      • Application Level - Link reusable by any user

      • User Level - Link reusable by current user

      To learn more about each option, see item Help.

  6. Click Apply Changes.

To create a column link to a URL:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  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, specify the following:

    1. From Target, 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 to 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.

Displaying Interactive Reports Conditionally

You can choose to have interactive reports display conditionally by editing region attributes on the Edit Region page.

To display an interactive report conditionally:

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

  2. Access the Edit Region page:

    • Component view - Under Regions, select the region name.

    • Tree view - Right-click the region name and select Edit.

    The Edit Region page appears.

  3. Scroll down to Conditions.

  4. From the Condition Type list, make a selection.

  5. Enter an expression in the fields provided.

  6. Click Apply Changes.

Tip:

Asynchronous JavaScript and XML (AJAX) is used throughout interactive reports. Because AJAX is asynchronous, the value evaluated for conditional display needs to be in the session. To display an interactive report conditionally based on specified value, use a page item instead of using a :REQUEST value. Using :REQUEST in a conditional display causes in the interactive report function incorrectly.

Defining a Column as a List of Values in an Interactive Report

In interactive reports you can use a list of values to improve the speed of built-in filter tools.

To define a report column as a list of values:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  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 Column Filter List of Values, specify how to create a list of values as a filter on the report column list. Available options include:

    • None - Disable filter on list of values.

    • Default Based on Column Type - STRING and NUMBER columns derive the list of values from distinct column values. DATE columns derive the list of values from predefined date ranges.

    • Use Defined List of Values to Filter Word Contains - Derive the list of values from a defined list of values SQL query to create a contains filter.

    • Use Named List of Values to Filter Exact Match - Derive the list of values from the named list of values to create an equal filter.

    • Use Named List of Values to Filter Word Contains - Derive the list of values from the named list of values to create a contains filter.

  5. Click Apply Changes.

Enabling Icon View

Be default, most interactive reports display as a report. You can optionally display columns as icons. When configured, a View Icons icon displays on the Search bar.

Description of ir_search_bar_icon.gif follows
Description of the illustration ir_search_bar_icon.gif

To utilize Icon view, you must identify the columns used to identify the icon, the label, and the target (that is, the link). As a best practice, set these columns to display as hidden since they are typically not useful for end users.

To add Icon view to an interactive report:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Icon View.

    Description of ir_icon_view.gif follows
    Description of the illustration ir_icon_view.gif

  3. For Icon View Enabled, select Yes.

  4. Specify the following mandatory attributes:

    • Link Column - Select the column that returns the link target of the icon. See item Help for an example.

    • Image Source Column - Identify the column that returns the image source. See item Help for an example.

    • Label Column - Select the column that returns the image label. See item Help for an example.

  5. For Columns Per Row, identify the number of icons to be displayed per row. A value of 5 would display 5 icons per row.

  6. Click Apply Changes.

Enabling Detail View

Be default, most interactive reports display as a report. You can optionally display details about each column. When configured, a View Details icon displays on the Search bar.

Description of ir_search_bar_detail.gif follows
Description of the illustration ir_search_bar_detail.gif

To add Details view to an interactive report:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Detail View.

  3. For Detail View Enabled, select Yes.

  4. Specify the following attributes:

    • Before Rows - Enter the HTML to be displayed before report rows. For example:

      <TABLE>
      
    • For Each Row (Use #COLUMN_ALIAS# substitutions) - Use substitution strings for column names and column labels. For example:

      <tr><td align="right">#ENAME_LABEL#:</td><td>#ENAME#</td></tr>
      <tr><td align="right">#JOB_LABEL#:</td><td>#JOB#</td></tr
      
    • AFter Rows - Enter the HTML to be displayed after report rows. For example:

      </TABLE>
      
  5. Click Apply Changes.

Configuring Advanced Attributes for Interactive Reports

Use the Advanced Attributes section to define the Interactive Report region alias, specify an item whose value stores the saved report id, and define items to set into session state.

To configure Advanced Attributes:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Scroll down to Advanced Attributes.

    Table 8-4 describes the available Advanced Attributes.

    Table 8-4 Advanced Attributes

    Attribute Description

    Region Alias

    Enter an alphanumeric alias for this interactive report region. The alias must be unique within the application.

    You can use an alias to reference an interactive report in an API call. For example, you can write a function to return INTERACTIVE_REPORT_ID by querying ALIAS column in the APEX_APPLICATION_PAGE_IR view). The alias must be unique within the application. Since the INTERACTIVE_REPORT_ID value can change when you export or import to different instance, referencing an interactive report region using an alias can be useful.

    Report ID Item

    This attribute enables you to send a user directly to a saved view of the report. You specify an item whose value stores the report ID of the saved view. Report ID's can be retrieved from the APEX_APPLICATION_PAGE_IR_RPT view.

    Page Items to Submit

    Enter a comma separated list of items to be set into session state when the user clicks the Go button on the search bar. Only items on the current page will be set.


  3. Click Apply Changes.

Linking to Interactive Reports

Unlike classic reports which typically use page items as report parameters, the data in an interactive report is driven by declarative filters. However, the developer may desire to set these report parameters by modifying interactive report settings for an end user. These settings only affect the end user's working report and will not affect any saved report settings.

To reset an interactive report in a link, use the string "RIR" in the Clear-Cache section of a URL. This is equivalent to the end user choosing the Reset option from the interactive report actions menu on the target page. The report is returned to the default report settings specified by the developer or saved by the user.

To create a Row text contains filter on an interactive report, use the string IR_ROWFILTER in the Item Names section of the URL. Adding this string passes the filter value in the corresponding location in the Item Values section of the URL.

To create a filter on an interactive report in a link, use the string IR<operator>_<target column alias> in the Item Names section of the URL, and pass the filter value in the corresponding location in the Item Values section of the URL.

Valid operators include:

  • EQ = Equals (this is the default)

  • NEQ = Not Equals

  • LT = Less than

  • LTE = Less then or equal to

  • GT = Greater Than

  • GTE = Greater than or equal to

  • LIKE = SQL Like operator

  • N = Null

  • NN = Not Null

  • C = Contains

  • NC = Not Contains

Consider the following examples:

f?p=...:IR_ENAME:KING

The previous example is equivalent to the end user creating a filter on the target page: ENAME = KING. Note that when an operator is not provided, EQ is used because it is the default.

f?p=...:IRGT_SAL:3000

The previous example is equivalent to the end user creating a filter on the target page: SAL > 3000.

Linking to Shared Interactive Reports

You can link to saved primary default, alternative default and public reports using IR_REPORT_[report_alias] in the request value of the URL.

Link Examples to a Primary Report

The following examples demonstrate how to link to a primary report (report_alias=12345):

This example links to a primary report:

f?p=100:1:&APP_SESSION.:IR_REPORT_12345

This example links, resets, and clears primary report settings:

f?p=100:1:&APP_SESSION.:IR_REPORT_12345::RIR,CIR:

This example links, resets, and clears primary report settings. It additionally creates a ENAME = 'KING' filter on the primary report:

f?p=100:1:&APP_SESSION.:IR_REPORT_12345::RIR,CIR:IR_ENAME:KING
Link Examples to Saved Reports

The following examples demonstrate how to link to a saved report (report_alias=12345):

This example links to a saved report:

f?p=100:1:&SESSION.:IR_REPORT_1234

This example links, resets, and clears settings for saved report 12345:

f?p=100:1:&SESSION.:IR_REPORT_12345::RIR,CIR:

This example links, resets, and clears saved report 12345 settings. It additionally creates a ENAME = 'KING' filter on saved report 12345.

f?p=100:1:&SESSION.:IR_REPORT_12345::RIR,CIR::RIR,CIR:IR_ENAME:KING

Note that if you reset, clear, or create filter using URL without the IR_REPORT_[report_alias] request value, it resets, clears, and creates filter on the primary report. For example, this link goes to the primary report:

f?p=100:1:&SESSION.:::RIR,CIR:IR_ENAME:KING
Editing the Saved Report Alias

Developers can edit the report alias or view link examples by going to Saved Reports page.

To access the Saved Reports page.

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

  2. Select the application.

  3. Go to the Page Definition containing the saved interactive report.

    The Page Definition appears.

  4. To access the Saved Reports page:

    • Tree view - Right-click the Region name and select Edit Saved Reports.

    • Component view - Under Regions, click the Saved Reports link.

      The Saved Reports page appears.

  5. In Report Alias, you can either:

    • Enter a new alias for the saved report

    • Copy and paste the link example to link to a specific saved report

  6. Click Apply Changes.

Viewing and Deleting Saved Reports

Each interactive report can have multiple default reports. A report categorized as Primary Default displays initially and cannot be renamed nor deleted. A report saved as Alternative Default can be selected from Reports list on the Search bar.

End users can also save reports and classify them as being either public or private. Public reports can be viewed by all users. Private reports can only be viewed by the user who saved the report. To learn more see, "Saving a Report".

To view a listing of Saved Reports:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. Click the Saved Reports tab.

    A report displays all saved reports by report type, report name, owner, and report alias.

    Description of ir_saved_reports.gif follows
    Description of the illustration ir_saved_reports.gif

  3. To delete a report, select it and click Delete Checked.

Viewing Saved Report Activity

Developers can view saved report activity by clicking the View Saved Report Activity link under Tasks on the Edit Report Attributes page. This link only displays if saved reports other than the primary default report exist. You can use this page to determine which reports are being used and which ones are not.

To view saved report activity:

  1. Access the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

    The Interactive Report Attributes page appears.

  2. On the Tasks list, click View Saved Report Activity.

Tip:

You can also view saved report activity from the Workspace Administration page. On the Workspace home page, click Administration and then Monitory Activity. Under Page Views, click by Interactive Report.

Editing SQL and Classic Reports

Developers can control report layout, pagination, column sorting, error messages, export links, and column break of SQL and Classic reports using the Report Attributes page.

Tip:

With the addition of interactive reports in Oracle Application Express release 3.1, all previous report types are referred to as classic reports. See "Editing Interactive Reports as a Developer".

Topics:

Accessing Classic Report Attributes

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. To access the Report Attributes page:

    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. To save your changes, click Apply Changes.

About Navigation Alternatives

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

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

Description of classic_rprt_nav.gif follows
Description of the illustration classic_rprt_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 Classic 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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    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 is generated for the report. The Link column indicates if a column link is currently defined. The Edit column indicates whether a column is currently updatable.

    Table 8-5 describes common report column edits.

    Table 8-5 Common Report Column Edits for Classic Reports

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

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. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  3. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

  4. 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 8-6 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 8-6 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.

Report Attributes Substitution

Enter a value to be in place of the #REPORT_ATTRIBUTES# substitution string. The #REPORT_ATTRIBUTES# substitution string is only replaced in the before rows attribute of the report 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.

Enable Partial Page Refresh

If enabled, this attribute refreshed the report region when paginating forward and backward in your result set.

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

Sort Nulls

For reports with column heading sorting, specify if you want null valued columns to sort first or last.

See Also: "Enabling Column Sorting in a Classic Report"


Including Pagination After the Rows in a Classic 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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    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. See "Accessing 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 Classic 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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    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. See "Accessing 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 in a Classic Report

You enable column sorting on the Report Attributes page.

To enable column sorting:

  1. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

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

  6. Click Apply Changes.

Tip:

Note that column sorting must be enabled if you want columns with null values to display at the top or end of the report. To learn more about the Sort Nulls attribute, see "Accessing and Understanding Layout and Pagination Attributes".

Adding a Download Link to a Classic 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 must 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 in a Classic Report

To use the enable the CSV output option:

  1. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

  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 Classic Report as an XML File or a CSV File

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

To export a report as a file:

  1. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

  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 in a Classic Report

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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

  2. Under 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 to 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. See "Linking to Interactive Reports".

    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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

  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, specify the following:

    1. From Target, 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 to 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 in a Classic Report

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. Access the Classic Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

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

    The Column Attributes page appears.

  3. Under Column Attributes, specify the following:

    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. Number/Date Format - 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.

  4. Tabular Form Attributes, specify the following:

    1. Default Type - Identify the default type.

    2. Default - Identify the default source.

    3. Reference Table Owner - Identify the owner of the referenced table. This attribute is used to compare references to columns across applications against User Interface Defaults. This reference is manually maintained after being initialized by a wizard, whenever possible.

    4. Reference Table Name - Specify the table or view that this column references. This attribute is used to compare references to columns across applications against User Interface Defaults. This reference is manually maintained after being initialized by a wizard, whenever possible.

    5. Reference Column Name - Specify the column name that this report column references. This attribute is used to compare references to columns across applications against User Interface Defaults. This reference is manually maintained after being initialized by a wizard, whenever possible.

  5. Click Apply Changes.

Defining a Column as a List of Values in a Classic Report

A column can be rendered as Radio Group or a static, named, or query-based LOV. 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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

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

    The Column Attributes page appears. Next, specify how the column is rendered (radio group, LOV, select list, and so on).

  3. Under Column Attributes, make a selection from the Display As list.

  4. Scroll down to List of Values.

  5. From Named LOV, make a selection from the List of Values repository. See "Creating Lists of Values at the Application Level".

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

    1. In Display Null - Select Yes.

    2. Null display value - Enter the display value for the NULL option here.

    3. Null return value - Enter the NULL value to be used for this item's list of values. If you do not specify a value, the default is %null%.

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

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

  8. If you have not selected a Named LOV, enter the query used to display a select list in List of values definition.

  9. Click Apply Changes.

Controlling When Columns Display in a Classic Report

You can use the Authorization and Condition 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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

  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 in a Classic Report

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. Access the Interactive Report Attributes page. See "Accessing Classic Report Attributes".

    The Report Attributes page appears.

  2. Scroll down to Break Formatting.

  3. Make a selection from the Breaks list.

Printing Report Regions

You can configure a classic report region to print by exporting it to a number of different formats. Oracle BI Publisher supports Adobe Portable Document Format (PDF), Microsoft Word Rich Text Format (RTF), or Microsoft Excel format (XLS), or Extensible Markup Language (XML). The supplied OC4J with Apache FOP alternative only supports PDF and XML. If you choose to use other third-party rendering engines, other output formats can also be configured.

By taking advantage of region report printing, your application users can view and print reports that have a predefined orientation, page size, column headings, and page header and footer. Interactive reports also have the ability to export to PDF, RTF, Microsoft Excel and Comma Separated Values (CSV). Note that for interactive reports, it is not possible to define a custom report layout.

Topics:

Tip:

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

About Printing Reports to PDF

When printing to a PDF, the report data is transformed using an externally defined report server. When the application end user clicks a print link, a request is sent to the Application Express engine. The Application Express engine then generates the report data in XML format and report template in XSL-FO or RTF format. The external reporting engine then transforms the data and the template into a PDF which displays to the end user using the conversion servlet that ships with BI Publisher 10.1.3.2 (formerly known as Oracle XML Publisher). Fortunately, this architectural complexity is transparent to both end users and developers. End users just click a print link, and developers just declaratively set regions to support PDF printing. Output to other formats operates in the same manner using the necessary conversion servlet.

About Report Printing Configuration Options

Your report server can be Oracle BI Publisher, OC4J with Apache FOP, or another standard XSL-FO processing engine. Oracle BI Publisher provides a higher level of functionality and facilitates "high fidelity" output requirements including master-detail reports, charts, and so on. To accommodate the difference in functionality, Oracle Application Express provides two report printing configuration options:

  • Standard Support. Enables you to print report regions and report queries using either the built-in templates (provided with a standard XSL-FO processing engine), or other XSL-FO compatible formats you provide. This setting does not support RTF.

    Standard Support provides declarative formatting of report regions and report queries with basic control over page attributes, including page orientation, page size, column heading formats, page header, and page footer.

  • Advanced Support. Requires a valid license of Oracle BI Publisher (also known as Oracle XML Publisher). This setting, provides you with all the capabilities of the Standard configuration plus the ability to define RTF-based report layouts developed using the BI Publisher Word Template Builder.

    To learn more about installing and configuring Oracle BI Publisher, see PDF Printing in Application Express 3.1. Go to:

    http://www.oracle.com/technology/products/database/application_express/html/configure_printing.html
    

Note:

To use the full functionality of report printing, your Oracle Application Express service administrator must enable it for your site. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

About Classic Report Printing Methods

There are two ways to print classic report regions:

Both report regions and report queries can be downloaded in the following formats:

  • PDF - Adobe Portable Document Format.

  • RTF - Microsoft Word Rich Text Format.

  • XLS - Microsoft Excel format. Note that this is not a true .xls file because the content is HTML-based.

  • HTML

  • XML - Extensible Markup Language.

To format either a report region or report query, you associate it with a report layout. To learn more, see "About Report Layouts".

Tip:

You can also have the output format specified by an item that determines the output format at runtime. Select the item that will hold the format value. Valid values are PDF, RTF (to open the document in Microsoft Word), XLS (to open the document in Microsot Excel) and HTM (to download the document as an HTML file).

About Report Queries

You can print a report region by defining a report query as a Shared Component. A report query identifies the data to be extracted. Unlike SQL statements contained in regions, report queries contain SQL statements that are validated when you save the query. Note that report queries must be SQL statements, not functions returning SQL statements.

You can associate a report query with a report layout and download it as a formatted document. If no report layout is selected, a generic layout is used. The generic layout is intended to be used to test and verify a report query. When using the generic layout option and multiple source queries are defined, only the first result set is included in the print document. The reports can include session state of the current application.

To make these reports available to end users, you then integrate them with an application. For example, you can associate a report query with a button, list item, branch, or other navigational component that enables you to use URLs as targets. Selecting that item then initiates the printing process.

Topics:

Creating a Report Query

Note that the availability of the report query options depends on how your service administrator configures report printing for your instance. All options described in these steps may not be available to you.

To create a report query:

  1. Navigate to the Shared Components page. See "Accessing the Shared Components Page".

  2. Under Reports, click Report Queries.

  3. Click Create.

  4. For Query, specify the following information:

    1. Report Query Name - Enter a name to identify the report query. When referencing a report query as a link target, the report query name is part of the request string.

    2. Output Format - Select the report output format. Valid options include:

      • PDF - Adobe Portable Document Format.

      • Word - Microsoft Word Rich Text Format.

      • Excel - Microsoft Excel format. Note that this is not a true .xls file because the content is HTML-based.

      • HTML

      • XML - Extensible Markup Language.

    3. Item - Select the item to hold the format value. This can be used to determine the output format at runtime.

    4. View File As - Select how the Web browser opens the report query document. Options include:

      • Attachment - Displays a File Open/Save dialog box when the user clicks the report query download button.

      • Inline - Displays the report query document inside the Browser window.

    5. Session State - Select this check box to reference session state values, such as the values of page items in the XML structure of your report.

    6. Click Next.

  5. For SQL Query, enter a SQL statement directly or click Query Builder to build a SQL statement by clicking and pointing.

    To reference applications and page items in the SQL statement, reference them as bind variables. Click Set Bind Variables to be able to enter in values for each bind variable and test the query to ensure the expected output is returned.

  6. For Download Definition, specify the following:

    1. Data Source for Report Layout - Use this option to download a report definition export file. Select XML Data or XML Schema and then click Download.

      XML Schema creates XSD file (that is, a XML schema definition of your XML structure.)

    2. Add Query - Click this button to add another query to the report query. Adding another query enables the production of complex reports with more than one output component (for example, a chart and a report based on two different queries).

    3. Create Report Query - Click this button to exit the wizard early. The Report Layout Source will default to use the generic report layout.

    4. Click Next.

  7. For Upload Report Layout, specify the following:

    1. Report Layout Source - Specify how the report layout is derived. If you choose the report based file layout then the layout name and report layout file must be provided.

    2. Layout Name - Enter a layout name (optional). The default is the report query name.

    3. Report Layout File - Click Browse and select a Rich Text Format (RTF).

    4. Click Next.

  8. For Confirm:

    1. Query Name - Identifies the query.

    2. Report Layout - Identifies the report layout you selected.

    3. Output Format - Identifies the format for this report query.

    4. Derive from Item - Identifies the item to hold the format information.

    5. URL - To integrate this report with your application, use the displayed URL as the target for a button, list item, link, or other navigational component. End users can click a button, for example, to start the printing process.

    6. Test Report - Click this to preview your report. If you have chosen to use a generic report layout then the Test Report will not produce an output.

    7. Click Finish.

      The Report Query is created and saved to Shared Components.

Editing Report Queries

To edit a report query:

  1. Navigate to the Shared Components page. See "Accessing the Shared Components Page".

  2. Under Reports, click Report Queries.

  3. On the Report Queries page, you can use the Navigation bar at the top of the page to search for a query by name or change the page display. For example, you can change the default display by making a selection from View list. Available options include:

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

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

  4. Select the appropriate report query. On the Edit Report Query page, edit the information.

  5. Click Apply Changes.

Copying Report Queries

To copy a report query:

  1. Navigate to the Shared Components page. See "Accessing the Shared Components Page".

  2. Under Reports, click Report Queries.

  3. On the Report Queries page, click Copy.

  4. On the Copy Report Query, select the query you want to copy, enter a name for the report query, and click Copy.

    The copy appears in the query list.

About Report Layouts

To format either a classic report region or report query, you associate it with a report layout. Using report layouts renders the data in a printer-friendly format. If you do not select a report layout, a default XSL-FO layout is used. The default XSL-FO layout is always used for rendering Interactive Report regions.

When creating and using report layouts, you can:

  • Take advantage of the default layouts for report regions and generic layouts for report queries provided with Oracle Application Express.

  • Utilize the built-in XSL-FO-based layouts for report regions by copying and customizing the code. You can edit a number of attributes for report regions that control page size, fonts, colors, and so on.

  • Create RTF or XSL-FO report layouts to customize the report look and feel. To use RTF report layouts, your Oracle Application Express service administrator must select the Advanced setting for your site. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

Topics:

About Report Layout Options

You can create a report layout based on one of these options:

  • Generic Columns - A generic report layout works with most query result sets. With this layout, the number of columns is automatically adjusted when generating the printable document.

    A number of report layout attributes can be defined declaratively for report regions using the built-in XSL-FO default layout. This step allows for creating customizable copies of the built-in default XSL-FO layout, if additional control over the report layout is needed.

  • Named Columns - A named column report layout is a query-specific report layout designed to work with a defined list of columns in the query result set. This type of layout is used for custom-designed layouts when precise control of the positioning of page items and query columns is required.

Note that the availability of the Report Layout options depends on how your site administrator configured the report printing settings at your site. All options described in these steps may not be available to you.

See Also:

"Configuring Report Printing" in Oracle Application Express Administration Guide.
Creating a Report Layout

To create a report layout:

  1. Navigate to the Shared Components page. See "Accessing the Shared Components Page".

  2. Under Reports, click Report Layouts.

  3. Click Create.

    The Create Report Layout wizard appears.

  4. For Report Layout Type, select an option and click Next:

    • Generic Columns (XSL-FO) - This layout is pre-populated with a default template, which you can edit to fit your needs.

    • Named Columns (RTF) - This layout is uploaded as an RTF file.

    • Named Columns (XSL-FO) - This layout is uploaded as an XSL-FO file.

  5. For Layout Source, review and edit the appropriate information. The options that appear on this page depend on the layout type you select:

    • If you selected Generic Columns:

    1. Report Layout Name - Enter a name to identify the report layout when associating it with a report query or report region.

    2. Report Layout - The report layout is the XSL-FO based definition of the page formatting. All attributes defining page size, orientation, fonts, styles, and so on, are defined in this section.

      To see a list of valid substitution strings and other information, click the item label, Report Layout. This opens a separate Help window.

    3. Report Column Heading - Defines the look of each cell in the report heading row.

    4. Report Column - Defines the look of each cell for all report rows.

    5. Report Column Width - This width is computed at runtime time or can be derived from the report column definition of a report region.

    • If you selected Named Columns:

    1. Layout Name - Enter a name to identify the report layout when associating it with a report query or report region.

    2. 8.b) Report Layout File - Upload the file containing the report layout.

Editing Report Layouts

You can edit a generic column report layout directly in Application Express. However, to edit a named column report layout, you must download the current file, edit it, and then upload it again.

To edit a report layout:

  1. Navigate to the Shared Components page. See "Accessing the Shared Components Page".

  2. Under Reports, click Report Layouts.

  3. On the Report Layouts page, select the layout you want to edit.

  4. For generic column layouts, edit the layout directly on the Edit Report Layout page and then click Apply Changes.

  5. For named column layouts, click Download and save the file to your computer.

    Edit the file and then upload the updated version as a new report layout.

Copying Report Layouts

You can copy a report layout to edit and save.

To copy a report layout:

  1. Navigate to the Shared Components page. See "Accessing the Shared Components Page".

  2. Under Reports, click Report Layouts.

  3. On the Report Layouts page, click Copy.

  4. On the Copy Report Layout page, select the layout you want to copy, enter a name for the copy, and click Copy.

    The copy appears in the layout list.

Configuring Classic Report Region Print Attributes

One approach to printing a report region is to configure Print Attributes. Once configured, these attributes only apply only to current region and cannot be used outside the context of the region.

If the printing feature is set up for your instance, you can configure a report region to print in various formats.

To configure a classic report region for printing:

  1. In your application, select the page containing the classic report region you want to print.

    The Page Definition appears.

  2. Access the Report Attributes page. See "Accessing Classic Report Attributes".

  3. Click the Print Attributes tab.

  4. Under Printing, specify the appropriate information:

    1. Enable Report Printing - Select Yes.

    2. Link Label - Enter the text for the link that starts the printing process.

    3. Response Header - Specify whether the response header generated by Oracle Application Express is based on your Report Settings or the Print Server. The response header is the information the server sends back to the Web browser in response to receiving an HTTP request. It contains the date, size and type of file that the server is sending back to the client and also data about the server itself. To specify content disposition (using the attribute View File As) and file name, the response header needs to be based on Report Settings.

    4. View File As - Select how the Web browser opens the report query document. Options include:

      • Attachment - Displays a File Open/Save dialog box when the user clicks the report query download button.

      • Inline - Displays the report query document inside the Browser window.

    5. (Optional) File Name - Enter a name for the downloaded file. If you leave this blank, the region name is used as the file name.

    6. Output Format - Select an output option.

    7. Item - Only enabled, if you select the Output Format Derived from Item. If enabled, select the application or page item that holds format value.

    8. Report Layout - Select Default Report Layout to use the built-in generic report layout, or an available report layout you or your administrator created. See item Help for a listing of the supported substitution strings available when building custom XSL-FO report layouts.

    9. Print Server Overwrite - Specify an alternate print server. A print server is typically configured centrally for all workspaces in Oracle Application Express Administration Services. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

      Configuring an alternative print server at the report level makes debugging and diagnosis of potential printing problems easier and supports the use of specific custom built end points to perform custom XSLT transformations.

    10. Print URL - Report regions can be printed using the standard print link or using a page button. To use a button, remove the Link Label, create a button on your page, and use the URL shown in this attribute as the button target.

  5. In the remaining sections, define page size, paper orientation, page headers and footers, fonts, text color, and background color.

  6. Under Report Columns, specify the width for each column.

    The default is to make each column the same width. The Total Width is a display only field that shows the sum of all column width values. When changing the width for an individual column the Total Width is automatically recalculated. If Column Width Units is set to Percentage, rather than Points, then the total column width must be less than or equal to 100.

    Use the Reset Column Width button to reset the Column Width Units to Percentage and change all column widths back to their default value. The Recalculate button maintains the current ratios between column widths but reduce all column widths such that the Total Width is less than or equal to 100%.

  7. Click Apply Changes.

Configuring Interactive Report Region Print Attributes

Unlike classic reports, the Interactive Report Print Attributes can only utilize the default XSL-FO layout and is initiated from the Report Attributes, Download section rather than directly from this screen. Once configured, these attributes only apply only to the current region and cannot be used outside the context of the region.

If the printing feature is set up for your instance, you can configure a report region to print in various formats

To configure a interactive report region for printing:

  1. In your application, select the page containing the interactive report region you want to print.

    The Page Definition appears.

  2. Under Regions, click Interactive Report next to the region you want to print.

  3. Click the Print Attributes tab.

  4. Under Printing, specify the appropriate information:

    1. Response Header - Specify whether the response header generated by Oracle Application Express is based on your Report Settings or the Print Server. The response header is the information the server sends back to the Web browser in response to receiving an HTTP request. It contains the date, size and type of file that the server is sending back to the client and also data about the server itself. To specify content disposition (using the attribute View File As) and file name, the response header needs to be based on Report Settings.

    2. Content Disposition - Select how the Web browser opens the report query document. Options include:

      • Attachment - Displays a File Open/Save dialog box when the user clicks the report query download button.

      • Inline - Displays the report query document inside the Browser window.

    3. Print Server Overwrite - Specify an alternate print server. A print server is typically configured centrally for all workspaces in Oracle Application Express Administration Services. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

      Configuring an alternative print server at the report level allows for easier debugging and diagnosis of potential printing problems and supports the use of specific custom built end points to perform custom XSLT transformations.

  5. In the remaining sections, define page size, paper orientation, page headers and footers, fonts, text color, and background color.

  6. Click Apply Changes.

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:

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

    Table 8-7 Form 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 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.

    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"

    Tabular Form

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

    See Also: "Creating a Tabular Form"

    Form on a SQL Query

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

    Summary Page

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

    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"


  6. Follow the on-screen instructions. To learn more about a specific field, 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".

Creating a Tabular Form

A tabular form enables users to update multiple rows in a table at once from a single page. If created using a wizard, a tabular forms enables you to perform update, insert, and delete operations on multiple rows in a database table. This form includes a built-in multiple row update process that performs optimistic locking behind the scenes to maintain the data integrity.

Topics:

About Tabular Forms

There are two primary methods for creating tabular forms:

  • Create Application Wizard

  • Create Page Wizard

Tabular forms created using these wizards include automatic DML processes. These wizards create a form that performs update, insert, and delete operations on multiple rows in a database table. Alternatively, you can build a tabular form manually either by creating a updatable report region or standard report region with updatable columns in conjunction with custom PL/SQL page processes to process the tabular form data.

Note:

Any modification of the select list of a SQL statement of a tabular form after it has been generated using a wizard 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.
Running a Tabular Form

When running a tabular form, data is rendered as a report having updateable columns shown using various form elements (including text fields, text areas, date pickers, select lists, radio groups, and so on).

Description of rprt_tabular_form.gif follows
Description of the illustration rprt_tabular_form.gif

By default, date columns are created using the Date Picker item type. If user interface defaults are defined, other columns can be automatically created as select lists based on lists of values, or numeric fields and large text areas.

See Also:

"Managing User Interface Defaults" in Oracle Application Express SQL Workshop Guide

For numeric and date fields, you can also pre-define date and number format masks, or apply those format masks after generating the initial form.

Each report also includes a check box column (called a row selector). Users use this row selector to select one or more columns and update or delete data. To add a row, users click Add Row.

Tip:

You can control the appearance of a specific column by editing column attributes. See "Altering Classic Report Layout Using Column Attributes" and "Editing SQL and Classic Reports".

How Tabular Forms Work

Tabular forms are based on a SQL query in which the query columns are tied to the underlying table columns. Unlike a single record from, tabular forms do not store data in session state. When a tabular form page is submitted, the tabular form data is processed using built-in data manipulation language (DML), or a custom PL/SQL page processes.

A tabular form with automatic DML has two processes for multi-row operations:

  • A Multi Row Update (MRU) process performs create and update operations

  • Multi Row Delete (MRD) process deletes requests on one or more rows

MRU and MRD processes reference the underlying table name and the primary key columns. The primary key columns must be part of the tabular form SQL query, and the report columns must be either hidden or display-only (save state) in order for the DML processes to be able to identify the correct records. For new records, the primary key values must be NULL in order to be identified as new records. Delete operations are performed by referencing row number of the row to be deleted. Users can check one or more rows on the current page and when they press the Delete button, the row numbers are used to identify the corresponding primary key value(s) and the matching rows are deleted.

Wizard generated tabular forms built-in DML, include a checksum column to prevent lost updates. Using a method called optimistic locking, a checksum is computed for each row when rendering a tabular form. This checksum is stored in an extra hidden tabular form column. Upon submission of the form for update, an additional set of checksums is computed on the then current data stored in the database. The new checksums are compared to those built when the tabular form was first rendered, and if there are any mismatches, the update is rejected, because the data in the database was either modified by another user or anther process in the time between the tabular form was rendered and the time the data was submitted.

Wizard generated tabular forms also automatically create validations for some columns. Validations are created for columns that are set to NOT NULL in the underlying table and columns of type NUMBER, DATE or TIMESTAMP. Note that validations are not created for columns if the column is set to read-only, either based on the user's selection, or defined user interface defaults.

Creating a Tabular Form Using the Create Application Wizard

The Create Application Wizard is designed to quickly create a basic Oracle Application Express application consisting of multiple pages and components. For that reason, this wizard uses general best practices to generate the application and does not include several advanced options included in the Create Page Wizard.

To create a tabular form using the Create Application Wizard:

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

  2. Click the Create button.

  3. For Application Type, select Database.

  4. For Method, select From Scratch.

  5. For Name, enter the following 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 11).

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

    Next, select a page type and table on which to build the tabular form.

  6. For Pages:

    1. Select Page Type - Select Tabular Form.

    2. Table Name - Select a table.

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

    3. Click Add Page.

      The new page appears at the top of the page.

    4. Click Next.

  7. On Tabs, specify a tab implementation and click Next.

  8. For Shared Components, 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, select the following:

    1. Copy Shared Components from Another Application - Select Yes.

    2. Copy from Application - Select the application from which you want to import shared components.

    3. Select Components to Import - Select the components to import.

    4. Click Next.

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

  9. 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 user name) and a password in order to authenticate. This account information is managed entirely within the Oracle database.

  10. Next, specify the following attributes:

    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. Date Format - Specifies the date format to be used in the application.

      This date format is used to alter the NLS_DATE_FORMAT database session setting before showing or submitting any page within the application. This value can be a literal string containing a valid Oracle date format mask or an item reference via substitution syntax. If no value is specified, the default date format is derived from the database session at runtime. If supplied, this is also used as the date format for any items resulting from columns of type DATE.

    4. Click Next.

  11. For User Interface, 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".

  12. Confirm your selections and click Create.

See Also:

"Managing User Interface Defaults" in Oracle Application Express SQL Workshop Guide

Creating a Tabular Form Using the Create Page Wizard

Use the Create Page Wizard to add a tabular form to an existing application. Once you provide a table name on which to build the tabular form, the wizard reads the table columns from the database data dictionary. You then choose which columns to include in the tabular form, either as display-only columns or as updatable columns. Unlike the Create Application Wizard, you can choose between three different primary key source types: trigger, PL/SQL function, and sequence.

To create a tabular form using the Create Page 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. Select Tabular Form and click Next.

    The Create 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 a before-insert trigger populates the primary key. 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 want to provide a PL/SQL function to generate returning key value.

    • Existing sequence - Select this option if you want 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 a page name and region information.

    2. Specify a region title.

    3. Select a region template.

    4. Select a report template.

    5. Determine whether to include breadcrumbs.

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

    A success message appears.

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.

Topics:

About Master Detail Forms

When running a master detail form, data is rendered as a report using various form elements (including text fields, text areas, date pickers, select lists, radio groups, and so on).

Description of rprt_master_detail.gif follows
Description of the illustration rprt_master_detail.gif

By default, date columns are created using the Date Picker item type. If user interface defaults are defined, other columns can be automatically created as select lists based on lists of values, or numeric fields and large text areas.

Click Create to add a new row. Click the Edit icon to edit row details.

Description of rprt_master_detail2.gif follows
Description of the illustration rprt_master_detail2.gif

On the Master Detail, the master record displays as a standard form and the detail records display in a tabular form at the bottom of the page.

See Also:

"Managing User Interface Defaults" in Oracle Application Express SQL Workshop Guide

For numeric and date fields, you can also pre-define date and number format masks, or apply those format masks after generating the initial form. Wizard generated master detail forms also automatically create validations for some columns. Validations are created for columns that are set to NOT NULL in the underlying table and columns of type NUMBER, DATE or TIMESTAMP. Note that validations are not created for columns if the column is set to read-only, either based on the user's selection, or defined user interface defaults.

Tip:

You can control the appearance of a specific column by editing column attributes. See "Altering Classic Report Layout Using Column Attributes" and "Editing SQL and Classic Reports".

Creating a Master Details form Using the Create Application Wizard

The Create Application Wizard is designed to quickly create a basic Oracle Application Express application consisting of multiple pages and components. This wizard uses general best practices to generate the application and does not include several advanced options included in the Create Page Wizard.

To create a tabular form using the Create Application Wizard:

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

  2. Click the Create button.

  3. For Application Type, select Database.

  4. For Method, select From Scratch.

  5. For Name, enter the following 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 11).

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

    Next, select a page type and table on which to build the tabular form.

  6. For Pages:

    1. Select Page Type - Select Master Detail.

    2. Master Table Name - Select a table.

      This table will be used to create a report page and used as the master form on the master detail page.

    3. Detail Table Name - Select a table.

      Only tables and views that are associated to your selected Master Table via a foreign key are displayed.

    4. Click Add Page.

      The new page appears at the top of the page.

    5. Click Next.

  7. On Tabs, specify a tab implementation and click Next.

  8. For Shared Components, 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, select the following:

    1. Copy Shared Components from Another Application - Select Yes.

    2. Copy from Application - Select the application from which you want to import shared components.

    3. Select Components to Import - Select the components to import.

    4. Click Next.

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

  9. 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 user name) and a password in order to authenticate. This account information is managed entirely within the Oracle database.

  10. Next, specify the following attributes:

    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. Date Format - Specifies the date format to be used in the application.

      This date format is used to alter the NLS_DATE_FORMAT database session setting before showing or submitting any page within the application. This value can be a literal string containing a valid Oracle date format mask or an item reference via substitution syntax. If no value is specified, the default date format is derived from the database session at runtime. If supplied, this is also used as the date format for any items resulting from columns of type DATE.

    4. Click Next.

  11. For User Interface, 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".

  12. Confirm your selections and click Create.

Create a Master Detail Form Using the Create Page Wizard

The Create Page Wizard provides support for creating a master detail from two tables that do not have foreign key relationship defined in the database. Although Oracle recommends the use of foreign keys and properly defined relationships in the underlying database, developers do not always have the ability to modify their database model.

When created using a wizard, the master form on a master detail form page includes buttons that enable the user to move forward and backward with in the master result set. While running the wizard, you choose which columns determine the navigation order. You also have the option to choose whether to create a two-page or three-page master detail form, with the detail records shown in a report with single-record editing on a separate page.

To create a master detail form using the Create Page 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. Select Master Detail Form and click Next.

    The Master Detail Wizard appears.

  6. On Master Table:

    1. Select the 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 click Next. Then select the primary key column for the detail table and click Next. Options include:

    • Existing trigger - Select this option if a trigger is defined to populate the primary key, or if the user will be expected to enter the primary key value manually. Detail tables do not support user entered primary key values.

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

    • Existing sequence - Select this option if an existing sequence will be used to generate the primary key.

  9. On Master Options, specify whether 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 Layout, specify how to build the master detail and click Next. Options include:

    • Edit detail as tabular form on same page creates a two page master detail. Edit detail on separate page creates a three page master detail.

    • Edit detail on separate page creates a three page master detail.

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

  12. On Tab, specify whether 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. See "Accessing the Page Definition".

  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. See "Accessing the Page Definition"

  2. Access the Create Page Process Wizard:

    • Tree view - Under the appropriate location, right-click and click Create Process.

    • Component view - Under Processes, click the Create icon.

  3. Under Processes, click the Create icon.

  4. Select the process Data Manipulation.

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

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

  7. 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. See "Accessing the Page Definition".

  2. Access the Create Page Process Wizard:

    • Tree view - Under the appropriate location, right-click and click Create Process.

    • Component view - Under Processes, click the Create icon.

  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 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 validation and specify error message text.

  • Associate the validation with a specific item.

Creating a Validation

To create a validation:

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

  2. Access the Create Validation Wizard:

    • Tree view - Under Page Processing, right-click Validating and click Create Validation.

    • Component view - Under Processes, click the Create icon.

    The Create Validations Wizard appear

  3. For Level, select the validation level:

    • Item Level Validation

    • Tabular Form Validation

    • Page Level Validation

  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. See "Accessing the Page Definition".

  2. Navigate to the Edit Page Validations page:

    • Tree view - Right-click the validation name and click Edit.

    • Component view - Under Validations, select the validation item you want to associate.

    The attributes page for the validation appears.

  3. Scroll down to Error Message:

    • Error message display location - Verify the display location.

    • 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 with monthly, weekly, and daily views. 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:

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 calendar includes daily, weekly, and monthly views.

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

Topics:

Tip:

In Oracle Application Express release 3.1, the Create Calendar Wizard includes a new Display Type option. Standard submits the page along with the associated request. Partial Page Refresh only refreshes the Calendar region. Once the calendar is created, the display type cannot be changed. To change the display type, you must delete the calendar and then recreate it.

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. See "Accessing the Page Definition".

  2. On the Page Definition, create a new Calendar region:

    • Tree view- Under Page Rendering, right-click Regions and select Create.

    • Component view - Under Region, 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.

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. See "Accessing the Page Definition".

  2. Access the Region Definition:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

    The Region Definition appears.

  3. Under Identification, edit the Title attribute.

  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.

Note that to disable a view of a calendar, you must delete the Monthly, Weekly, or Daily buttons on the calendar page.

Topics:

Accessing the Calendar Attributes Page

To access the Calendar Attributes page:

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

  2. Access the Calendar Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. Click Calendar Attributes.

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

  5. 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. Table 8-8 describes Calendar Display attributes.

Table 8-8 Calendar Display Attributes

Attribute Description

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.

Date Item

Defines the item which holds the date on which the calendar is based

End Date Item

Specifies the item which holds the end date on which the calendar of type Custom is based

Calendar Type Item

Specifies the item which holds the calendar type.

Display Type

Defines the display type. Options include:

  • Column

  • Custom

  • No Display Value

If the Display Type is Column, use the Display Column attribute to select the column to display in the day cell of the monthly calendar.

If the Display Type is Custom, in Column Format enter a custom column format using HTML expressions and supported substitution strings.

Display Column

Defines a specific row that displays on a calendar date.

Column Format

Enter HTML expressions to be shown in this column. Use #COLUMN# syntax to show column values in HTML. For example:

#EMPLOYEE_NAME#

This following example demonstrates how to display images in a column.

<img src="#IMAGE_NAME#">

Display Attributes

Use Display Attributes to define the dates that are included in the calendar. Table 8-9 describes Display attributes.

Table 8-9 Display Attributes

Attribute Description

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.

Item Containing Start Date

Item Containing End Date

These 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. Note that the format of the date of either item must be YYYYMMDD:

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

Start of Week for Monthly Calendar

Determines the day on which the calendar starts for the monthly view.

Start Day for Weekly Calendar

Determines the day on which the calendar starts for the weekly view.

End Day for Weekly Calendar

Determines the day on which the calendar ends for the weekly view.

Time Format determines if the time displays in a 12-hour or 24-hour format.

Start Time and End Time determine the start and end times to display in the weekly and daily calendar views.

Time Format

Determines if the time displays in a 12-hour or 24-hour format.

Start Time

Determines the start times to display in the weekly and daily calendar views.

End Time

Determines the end times to display in the weekly and daily calendar views.


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.

Upgrading a Calendar Created in a Previous Releases

By default, calendars you create in Oracle Application Express 3.0 include daily, weekly, and monthly views. To update calendars created in a previous release to include these views, you can either:

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. See "Accessing the Page Definition".

  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.

Creating Maps

Application Builder includes built-in wizards for generating Flash maps. How you create a flash map depends upon whether you are adding the map to an existing page, or adding a map on a new page.

Topics:

About Flash Map Support

Flash map support in Oracle Application Express is based on the AnyChart AnyMap Interactive Maps Component. AnyMap is a flexible Macromedia Flash-based solution that enables developers to visualize geographical related data. Flash maps are rendered by a browser and require Flash Player 9 or later. For more information about AnyChart, go to

http://www.anychart.com

AnyChart stores map data in files with a *.amap extension, and supports 300 map files for the United States of America, Europe, Asia, Europe, Africa, Oceania, North America, and South America. To render a desired map, you select the map source in the wizard (for example, Germany) and the map XML automatically references the desired map source .amap file, germany.amap. To browse through the available maps, see "Map Reference" in AnyChart User's Guide:

http://anychart.com/products/anychart/docs/users-guide/

Adding a Flash Map to an Existing Page

To add a flash map to an existing page.

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

  2. On the Page Definition, create a new region:

    • Tree view- Under Page Rendering, right-click Regions and select Create.

    • Component view - Under Region, click the Create icon.

    The Create Region Wizard appears.

  3. Select Map and click Next.

  4. For Map Type, select a map type (for example, Europe), and click Next.

  5. For Map Source, a Tree lists the map source files for the selected Map Type. Click the Map Source you wish to use (for example, Denmark).

  6. For Display Attributes:

    1. Specify the following:

      • Title

      • Region Template

      • Parent Region

      • Display Point

      • Sequence

      • Column

    2. Click Next.

  7. For Display Attributes, select the appropriate map attributes and click Next.

    To learn more about a specific attribute, click the item label. See "About Field-Level Help".

  8. For Source:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Maps".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant attributes. To view map reference information associated with your selected Map Source, expand the Map Region Information region at the bottom on of the page.

      In order to associate data with geographical locations, you must incorporate information from the Region Name or Region ID columns into the Map query. See "About Creating SQL Queries for Maps".

  9. Click Create Region.

Adding Flash Maps to a New Page

To create an flash 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 Map and click Next.

  5. For Map Type, select a map type (for example, Europe), and click Next.

  6. For Map Source, a Tree lists the map source files for the selected Map Type. Click the Map Source you wish to use (for example, Denmark).

  7. For Page Attributes:

    1. Specify the following:

      • Page Number

      • Page Name

      • Region Template

      • Region Name

      • Breadcrumb

      To learn more, 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".

    2. Click Next.

  8. Specify whether to include tabs and click Next.

  9. For Map Attributes, select the appropriate attributes and click Next.

    To learn more about a specific attribute, click the item label. See "About Field-Level Help".

  10. For Query:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Maps".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant attributes. To view map reference information associated with your selected Map Source, expand the Map Region Information region at the bottom on of the page.

      In order to associate data with geographical locations, you must incorporate information from the Region Name or Region ID columns into the Map query. See "About Creating SQL Queries for Maps".

    3. Click Next.

  11. Click Finish.

About Creating SQL Queries for Maps

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

SELECT link, label, value
FROM   ...

Where:

  • link is a URL.

  • label is the text that identifies the point on the map that you wish to associate data with. The Region ID or Region Name of the map should be used as the label.

  • value is the numeric column that defines the data to be associated with a point on the map.

Consider the following example when creating a new page:

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

  2. Select an application.

  3. Click Create Page.

  4. Select Map and click Next.

  5. For Map Type, select United States of America and click Next.

  6. For Map Source, expand Country Maps and select States.

    Description of maps_source1.gif follows
    Description of the illustration maps_source1.gif

  7. For Page Attributes specify the appropriate attributes and click Next.

  8. Specify whether to include tabs and click Next.

  9. For Map Attributes, select the appropriate attributes and click Next.

    The Query page appears.

  10. Expand and review the Map Region Information region at the bottom of the page.

    Description of maps_source2.gif follows
    Description of the illustration maps_source2.gif

  11. For Query,

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Maps".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. When you create your query, you must incorporate the region name information. The following example includes information for Florida and Alaska:

      SELECT null link, region label, value Populations
      FROM (
      SELECT 'Florida' region, 18328340 value FROM dual
      UNION ALL
      SELECT 'Alaska' region, 686293 value FROM dual)
      

      Note:

      If you are referencing geographical information stored in a database table, the information must correspond with the Region information associated with the map source. For more information, see AnyChart User's Guide:
      http://anychart.com/products/anymap/docs/
      
    3. Click Next.

  12. Click Finish.

Editing Flash Map Attributes

Once you create a map, you can alter its display by editing map attributes on the Map Attributes page.

To access the Map Attributes page:

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

  2. Access the Chart Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. Click Map Attributes.

  4. 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. See "About Field-Level Help".

  5. Click Apply Changes.

About Navigation Alternatives

The Map Attributes 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.

Using Custom XML with Flash Maps

There are additional map settings that cannot be controlled using the standard attributes on the Map Attributes page. To further control the look and feel of a chart, you can use custom XML.

To use custom XML:

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

  2. Access the Map Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. Click Map Attributes.

  4. Scroll down to Map XML.

  5. From Use Custom XML, select Yes.

    Note that when you select to use custom XML, regions with attributes that no longer appear are hidden. To display these regions again, select No for Use Custom XML.

  6. Edit the XML.

  7. Click Apply Changes.

Tip:

For more information on supported XML format for maps, see the Online Map XML Reference at:
http://www.anychart.com/

Enabling Asynchronous Updates

You can create Flash maps that monitor information by enabling the Asynchronous Update attribute on the Map Attributes page. Enabling this attribute updates the map to reflect changes in the underlying data within a specified time interval.

To enable asynchronous updates:

  1. Create a Flash map. See "About Flash Map Support" and "About Flash Map Support".

  2. Navigate to the Page Definition. See "Accessing the Page Definition".

  3. Access the Map Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  4. Click Map Attributes.

  5. Scroll down to Refresh.

  6. From Asynchronous Update, select Yes.

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

  8. Click Apply Changes.

Creating Charts

Application Builder includes built-in wizards for generating HTML and Flash charts, and supports the editing of SVG charts. Oracle Application Express supports only those three types of graphical charts.

Topics:

About Creating SQL Queries for 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

Dial Chart Syntax

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

Multiple Series Syntax (Flash only)

For column charts and line Flash charts, you can define multiple series in one SQL query. The series names for these chart types are derived from the corresponding column aliases in the query. To define a multiple series Flash chart, use the following syntax:

SELECT link, label, series_1_value [, series_2_value [, ...]]
FROM   ...

Range Chart Syntax (Flash only)

Range charts require two values for each bar. To create a range chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, label, low_value, high_value 
FROM   ...

Scatter Chart Syntax (Flash only)

Scatter charts require an x value and y value for each point. To create a range chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, label, x_value, y_value 
FROM   ...

Candlestick Chart Syntax (Flash only)

Candlestick charts require open, low, high, and close values for each candlestick. To create a candlestick chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, label, open, low, high, close
FROM   ...

Gantt Chart Syntax

Project Gantt charts require a task name, task id, parent task id, actual start date, actual_end_date, and progress value for each task. Two optional values for planned start date and planned end date can also be used. To create a Project gantt chart, create a Flash chart and provide a SQL query using one of the following syntax:

SELECT link, task_name, task_id, parent_task_id, actual_start_date, actual_end_date, progress
FROM   ...

Resource Gantt charts require a resource id, resource name, parent resource id, actual start date, and actual end date value for each task. To create a Resource gantt chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, resource_id, resource_name, resource_parent_id, actual_start_date, actual_end_date
FROM   ...

Creating an HTML Chart

How you create a chart depends upon whether you are adding the chart to an existing page, or adding a chart on a page. This chart type creates a bar chart showing one data series with each data point represented by a bar.

Topics:

Adding an HTML Chart to an Existing Page

To add an HTML chart to an existing page:

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

  2. On the Page Definition, create a new region:

    • Tree view- Under Page Rendering, right-click Regions and select Create.

    • Component view - Under Region, click the Create icon.

    The Create Region Wizard appears.

  3. Select Chart and click Next.

  4. For Region, select HTML Chart and click Next.

  5. For Display Attributes:

    1. Specify the following:

      • Title

      • Region Template

      • Parent Region

      • Display Point

      • Sequence

      • Column

      To learn more, 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".

    2. Click Next.

  6. For Source:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant chart attributes. To learn more, click the item label.

  7. Click Create Region.

Adding an HTML Chart to a New Page

To create an HTML 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. For Region, select HTML Chart and click Next.

  6. For Page Attributes:

    1. Specify the following:

      • Title

      • Region Template

      • Parent Region

      • Display Point

      • Sequence

      • Column

      To learn more, 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".

    2. Click Next.

  7. Specify whether to include tabs and click Next.

  8. For Chart Definition:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant chart attributes. To learn more, click the item label.

    3. Click Next.

  9. Click Finish.

Creating a Flash Chart

Flash chart support in Oracle Application Express is based on the Anychart Flash Chart Component.

Topics:

About Flash Charts

Flash chart support in Oracle Application Express is based on the Anychart Flash Chart Component. Anychart is a flexible Macromedia Flash-based solution that enables developers to create animated, compact, interactive flash charts. Flash charts are rendered by a browser and require Flash player 9 or later.

http://www.anychart.com

Adding a Flash Chart to an Existing Page

To add a flash chart to an existing page:

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

  2. On the Page Definition, create a new region:

    • Tree view- Under Page Rendering, right-click Regions and select Create.

    • Component view - Under Region, click the Create icon.

    The Create Region Wizard appears.

  3. Select Chart and click Next.

  4. Select Flash Chart and click Next.

  5. For Chart Type:

    1. Select a chart style (for example, Column) and click Next.

    2. Select a specific chart type (for example, 3D Column) and click Next.

  6. For Display Attributes

    1. Specify the following:

      • Title

      • Region Template

      • Parent Region

      • Display Point

      • Sequence

      • Column

      To learn more, 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".

    2. Click Next.

  7. For Display Attributes, select the appropriate attributes and click Next.

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

  8. For Source:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant chart attributes. To learn more, click the item label.

  9. Click Create Region.

Adding a Flash Chart to a New Page

To create a flash 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 Flash Chart and click Next.

  6. For Chart Type:

    1. Select a chart style (for example, Column) and click Next.

    2. Select a specific chart type (for example, 3D Column) and click Next.

  7. For Display Attributes

    1. Specify the following:

      • Page Number

      • Page Name

      • Region Template

      • Region Name

      • Breadcrumb

      To learn more, 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".

    2. Click Next.

  8. Specify whether to include tabs and click Next.

  9. For Display Attributes, select the appropriate attributes and click Next.

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

  10. For Query:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify the remaining attributes. To learn more, click the item label.

    3. Click Next.

  11. Click Finish.

Upgrading all Flash 3 Charts to Flash 5

To upgrade all Flash 3 charts in an application to Flash 5:

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

    The Application Builder home page appears.

  2. Select an application.

    The Application home page appears.

  3. Click Utilities and then click Upgrade Application.

    The Upgrade Application Summary report appears.

  4. For Upgrade Flash Charts to Flash Chart 5, click the number of candidate objects.

  5. Select the objects to upgrade and click Upgrade.

Upgrading a Flash 3 Chart to Flash 5

To upgrade a Flash 3 charts Flash 5:

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

  2. Access the Chart Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. From the Tasks list, click Upgrade to New Flash Chart.

  4. Click Upgrade.

About SVG Charts

Previous releases of Oracle Application Express supported the creation of SVG charts. Although existing SVG charts are still supported, Oracle Application Express no longer supports the creation of new SVG charts.

Topics:

About Upgrading SVG Charts to Flash

Topics:

You can automatically upgrade single or multiple SVG charts to Flash.

About SVG Chart Upgrade Restrictions

Note that SVG charts can be upgraded with the following restrictions:

  • Only number formats defined in axis format strings will be migrated. Date and time formats will be ignored.

  • Number format elements containing the following will be migrated:

    0,9,D,G,, (comma),. (period),$,C,L,FM
    
  • The label for each series in the Flash chart will be derived from each series' column alias. This differs from SVG charts, where the label for each series was derived from the Series Name attribute.

  • Flash Dial charts display actual values instead of percentages.

  • In SVG charts, only the labels for the first series are used for the x-axis. In Flash charts, this has been enhanced so that all data appears, even if the data's label does not occur in the first series.

Upgrading a SVG Chart to Flash

To upgrade a SVG chart to Flash:

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

  2. Access the Chart Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. From the Tasks list, click Upgrade to New Flash Chart.

  4. Click Upgrade.

Upgrading all SVG Charts in a Application to Flash

To upgrade all SVG Charts to Flash charts:

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

    The Application Builder home page appears.

  2. Select an application.

    The Application home page appears.

  3. Click Utilities and then click Upgrade Application.

    The Upgrade Application Summary report appears.

  4. For Upgrade SVG Charts to Flash Chart 5, click the number of candidate objects.

  5. Select the objects to upgrade and click Upgrade.

Understanding SVG Chart Cascading Style Sheet Classes

When you create a SVG 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 8-10 describes all supported CSS classes. Note that certain classes only apply to specific chart types.

Table 8-10 Available SVG 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 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 SVG Chart 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. Navigate to the Page Definition containing the chart. See "Accessing the Page Definition".

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

    The Chart Attributes page appears.

  4. Scroll down to the CSS section.

  5. From Use Custom CSS, select Yes.

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

      /**/
      
  7. 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 for SVG Charts

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 "About SVG Charts".

  2. Navigate to the Page Definition. See "Accessing the Page Definition".

  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.

Displaying SVG Charts in Other Languages

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

To display an SVG 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;}
    

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. See "Accessing the Page Definition".

  2. Access the Chart Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. Click Chart Attributes.

  4. Edit the appropriate attributes.

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

  6. Click Apply Changes.

Tip:

Removing the chart title of an SVG chart (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 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.

Using Custom XML with Flash Charts

There are additional chart settings that cannot be controlled using the standard attributes on the Chart attributes page. To further control the look and feel of a chart, you can use custom XML.

To use custom XML:

  1. Navigate to the Chart Attributes page:

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

    2. Under Regions, click the chart type, Flash Chart.

      The Chart Attributes page appears.

  2. Scroll down to Chart XML.

  3. From Use Custom XML, select Yes.

    Note that when you select to use custom XML, regions with attributes that no longer apply are hidden. To display these regions again, select No for Use Custom XML.

  4. Edit the XML.

  5. Click Apply Changes.

Tip:

For more information on supported XML format for charts, see the Online XML Reference at:
http://www.anychart.com

Enabling Asynchronous Updates

SVG and Flash charts can 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. Edit an existing SVG or Flash chart. See "About SVG Charts".

  2. Navigate to the Page Definition. See "Accessing the Page Definition".

  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 for an SVG chart, 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;}
    

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:

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. How you create a button depends upon whether the Page Definition is in tree or component view. To learn more, see "About Tree and Component View".

Topics:

Creating a Button in Component View

To create a button in Component view:

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

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

  3. Under Buttons, click the Create icon.

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

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

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

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

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

    1. For Button Attributes:

      • Button Name - Enter a name for this button. Note that the button name becomes the page request value.

      • Label - Enter a label to be displayed for the button.

      • Button Style - Select HTML Button, Image, or Template Based Button

        If the button is an image, you will need to supply an image name. If the button is template controlled, you will need select a template name.

      • Button Template - Define which template controls the display of the button. Button templates are defined on the templates page. You must have at least one button template defined to use button templates. If you wish to no longer use a button template, change the button template to the NULL.

      • Button Attributes - Text entered is added to the HTML element definition. The following example sets the class of a text button:

        class="myclass"
        

        For buttons based on templates your template will need to include the #BUTTON_ATTRIBUTES# substitution string. You can use this to control tab stops. For example:

        tabindex="3"
        
      • Click Next.

    2. For Display Properties, select a position, sequence, and alignment and click Next.

    3. For Action When Clicked:

      • Action - Select the action to be performed when button is clicked.

      • Execute Validations - Select Yes or No. See item Help for more details.

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

    1. Button Name - Enter a button name.

    2. Sequence - Enter the sequence for this button. The sequence determines the order of evaluation amongst the other items in this region.

    3. Show - Select Beginning on New Line or Beginning on New Field. See item Help for more details.

    4. Label - Enter a label for this button. This will become the HTML alt tag if the button is an image.

    5. Request - Enter a Request value to be associated with this button. When the button is pressed the page will be submitted and the page request will be set to this value.

    6. Execute Validations - Select Yes or No. See item Help for more details.

    7. Button Style - Specify the button style.

    8. Image Name - Enter a optional button image name. The button image should be just the image file name, no prefix. The image prefix will be prepended to the image name. This application currently has an image prefix of "/i/".

    9. Image Attributes - Enter button image attributes if you have entered a button image name. The button image attributes are not required however identifying the height and width will allow browsers to paint the page faster. For example:

      width=16 height=14
      
  8. Click Create Button.

Creating a Button in Tree View

To create a button in Tree view, you select a region and then one of two options:

  • Create Region Button displays a button in a region position (for example, to add a button directly to the right of a form field).

  • Create Button displays a button within or between button items (for example, to place a button after a page item.

Creating a Region Button

To create a region button in Tree view:

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

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

  3. Right-click the region to contain the button and select Create Region Button.

  4. For Button Attributes, specify the following:

    1. Button Name - Enter a name for this button. Note that the button name becomes the page request value.

    2. Label - Enter a label to be displayed for the button.

    3. Button Style - Select HTML Button, Image, or Template Based Button

      If the button is an image, you will need to supply an image name. If the button is template controlled, you will need select a template name.

    4. Button Attributes - Text entered is added to the HTML element definition. The following example sets the class of a text button:

      class="myclass"
      

      For buttons based on templates your template will need to include the #BUTTON_ATTRIBUTES# substitution string. You can use this to control tab stops. For example:

      tabindex="3"
      
    5. Click Next.

  5. For Display Properties, specify the following:

    1. Position - Identifies the button position.

    2. Sequence - Specify the sequence for this component. The sequence determines the order of evaluation.

    3. Button Style - Select HTML Button, Image, or Template Based Button

      If the button is an image, you will need to supply an image name. If the button is template controlled, you will need select a template name.

    4. Alignment - Choose the button alignment.

    5. Click Next.

  6. For Action When Clicked, specify the following:

    1. Action - Select action to be performed when button is clicked.

    2. Execute Validations - Select Yes or No. See item Help for more details.

    3. Click Next.

  7. For Conditional Display, select a condition type from the list that must be met in order for this component to be rendered processed.

  8. Click Create Button.

Creating a Button Among Region Items

To create a button that displays within or between button items:

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

  2. If necessary, create a region. See "Understanding Regions".

  3. Click the region to contain the button, right-click and select Create Button.

  4. For the button attributes, specify the following:

    1. Button Name - Enter a button name.

    2. Sequence - Enter the sequence for this button. The sequence determines the order of evaluation amongst the other items in this region.

    3. Show - Select Beginning on New Line or Beginning on New Field. See item Help for more details.

    4. Label - Enter a label for this button. This will become the HTML alt tag if the button is an image.

    5. Request - Enter a Request value to be associated with this button. When the button is pressed the page will be submitted and the page request will be set to this value.

    6. Execute Validations - Select Yes or No. See item Help for more details.

    7. Button Style - Specify the button style.

    8. Image Name - Enter a optional button image name. The button image should be just the image file name, no prefix. The image prefix will be prepended to the image name. This application currently has an image prefix of "/i/".

    9. Image Attributes - Enter button image attributes if you have entered a button image name. The button image attributes are not required however identifying the height and width will allow browsers to paint the page faster. For example:

      width=16 height=14
      
  5. Click Create Button.

Creating Multiple Buttons in Component View

In Component view, 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 in Component view. See "Switching Between Tree and Component View".

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

  3. In Component view, locate the Buttons section and click the Create icon.

    The Create Button wizard appears.Create icon.

  4. From the Tasks list, click Create Multiple Buttons.

    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

When you want to edit a button, you start from the Buttons section on the Page Definition. You can edit the attributes of a button, edit multiple buttons at once, or change a button position within a region.

Topics:

Editing Button Attributes

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

To edit attributes for an existing button:

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

  2. Access the Edit Page Buttons page:

    • Tree view- Under Page Rendering, double-click the button name.

    • Component view - Under Button, select the button name.

      The Edit Page Buttons 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 Execute Validations

The Execute Validations attribute controls the execution of validations when a page is submitted. Set this attribute to No to prevent the execution of validations and associated error messages (for example, in the case of a Cancel button or Delete button). Set this attribute to Yes to execute all page and built-in validations and associated error messages when the page is submitted.

Use this attribute in conjunction with the validation attribute, Always Execute. To learn more, see "Creating a Validation".

Editing Multiple Buttons a Once

Use the Buttons page to delete multiple buttons or view a history of recent changes.

To edit multiple buttons at once:

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

  2. Access the Edit Page Buttons page:

    • Tree view- Under Page Rendering, right-click the button name and select Edit All.

    • Component view - Click the Edit All icon. The Edit All icon resembles a small grid with a pencil on top of it.

      The Buttons page appears. The Buttons page contains a table listing the buttons in the region.

Deleting Multiple Buttons

To delete multiple buttons at once:

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

  2. Access the Edit Page Buttons page:

    • Tree view- Under Page Rendering, right-click the button name and select Edit All.

    • Component view - Click the Edit All icon. The Edit All icon resembles a small grid with a pencil on top of it.

      The Buttons page appears. T

  3. Click Delete Multiple Buttons.

    The Delete Multiple Buttons page appears.

  4. Select the buttons to delete and click Remove Buttons.

History

Use the History page to view a summary of recent edits to buttons.

Using the Reorder Buttons Icon in Component View

In Component view, you can quickly edit a button label or change a button position within a region on the Reorder Buttons page. The Reorder Region Buttons icon resembles a light gray downward arrow and upward arrow and displays next to the region name.

To edit buttons using the Reorder Region Buttons icon:

  1. Navigate to the appropriate Page Definition in Component view. See "Switching Between Tree and Component View".

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

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

  3. To edit the button label, enter a 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. See "Accessing the Page Definition".

  3. Access the Edit Page Buttons page:

    • Tree view- Under Page Rendering, locate the button and double-click the button name.

    • Component view - Under Buttons, click the button name.

  4. Scroll down to Conditions.

  5. Make a selection from the Condition Type list.

  6. Enter an expression in the fields provided.

  7. Click Apply Changes.

Creating Lists of Values at the Application Level

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.

Creating a LOV as a shared component has a number of advantages:

Topics:

Creating a Named LOV 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.

    The Lists of Values page appears.

  5. To create a LOV, click Create.

  6. Follow the on-screen instructions.

    New named LOVs are added to the List of Values repository. Note to add the LOV to a page you must edit the appropriate item or column and select the named LOV.

About the List of Values Page

Once you create an LOV, it appears on the List of Values page.

Description of lov_page.gif follows
Description of the illustration lov_page.gif

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

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

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

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

    • View Icons (default) displays each lists of value as a large icon.

    • View Report displays each lists of value as a line in a report.

  • Go button - Executes a search.

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

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, 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. Click the Grid Edit tab.

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

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:

    • Edit Null Text

    • Locally Defined

    • Grid Edit

    • Conditional Entries

    • Subscription

    • Utilization

    • History

  6. Follow the on-screen instructions.

Edit Null Text

Click Edit Null Text to edit null text for all LOVs in your application.

Locally Defined

Click Locally Defined to view all LOVs that are defined locally with the definition of an item. LOVs that are usable by multiple pages can be converted into shared lists of values. Converting locally defined lists of values into a shared list of values promotes reuse.

Grid Edit

Click Grid Edit to edit the display values of all static LOVs.

Conditional Entries

Click Conditional Entries to view LOVs that display conditionally.

Subscription

Click Subscription to display the List of Values Subscription page. This page displays all subscribed LOVs in your application.

Utilization

Click Utilization to view a report of where LOVs are utilized in your application.

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.

Topics:

About Shortcut Types

When you create a 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 shortcuts using the following syntax:

"MY_SHORTCUT"

Note that the shortcut name must be capitalized and enclosed in quotation marks.

To define a 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.

Creating Trees

Application Builder includes a built-in wizard for generating a tree. Trees utilize jsTree, a JavaScript-based, cross browser tree component that features theme support, optional keyboard navigation, and optional state saving. You can create a Tree from a query that specifies a hierarchical relationship by identifying an ID and parent ID column in a table or view.The tree query utilizes a START WITH .. CONNECT BY clause to generate the hierarchical query.

Topics:

Creating a Tree

To create a tree using a wizard:

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

  2. Select an application.

  3. Click Create Page and click Next.

  4. For Page Type, select Tree.

  5. For Page Attributes, specify the following:

    1. Page Number - Specify the page on which the tree should be created.

    2. Page Name - If the tree will be created on a new page, enter the page name.

    3. Region Template - Select a region template for the tree region.

    4. Region Name - Enter a name for the region to contain the tree.

    5. Breadcrumb - Select whether you want to use a breadcrumb navigation control on your page, and which breadcrumb navigation control you want to use. If you select Breadcrumb, enter the following:

      • Entry Name - Enter a name for the breadcrumb entry.

      • Select Parent Entry - Select a parent entry.

    6. Click Next.

  6. For Tab, specify whether the page should have tabs and click Next.

  7. For Tree Templates, select a tree template and click Next.

  8. For Table/View Owner, select the table or view owner on which to base the tree and click Next.

  9. For Table/View Name, select the table or view and click Next.

  10. For Query, select the columns to include in the tree:

    1. ID - Select the column to use as the ID.

    2. Parent ID - Select the column to use as the parent ID.

    3. Node Text - Select the text to appear on the tree nodes.

    4. Start With - Select the column to be used to specify the root of the hierarchical tree query.

    5. Start Tree - Choose how to start your query. Options include:

      • Based on Existing Item - Select an existing application or page item.

      • Based on a SQL Query - Enter a SQL query that returns a single row or single column.

      • Based on a Static Value - Enter a static value.

      • Value is NULL.

    6. Click Next.

  11. For Where and Order by, specify the following:

    1. Where Clause - Enter an optional WHERE clause.

    2. Order Siblings By - Select the columns by which siblings are ordered.

    3. Click Next.

  12. For Tree Attributes, specify the following:

    1. Include following buttons - Select the buttons to include.

    2. Link Option:

      • Existing Application Item - Makes the leaf node text a link. If you select this option, you must specify a page to link to in the next step.

      • Nothing

    3. Existing Application Item - Can be used to hold the selected node value. If you select an application item, this item can be used to save the tree state, by holding the last selected node of the tree.

    4. Tooltip - Displays a tooltip when the mouse hovers over a leaf node. Options include:

      • Static Assignment - Specifies a static value to be used as the tooltip text. Substitution strings can be used in the static text, to incorporate information from the SQL query in the tooltip text. The following substitution strings can be used:

        #VALUE# - refers to the value of ID column.

        #TITLE# - refers to the value of Node Text column

        #LINK# - refers to the value of Link option

      • Database Column - Select the column to use as the tooltip text.

    5. Click Next.

  13. For Link, select a page the leaf node links to.

    Note that this option only appears if you want make the leaf node text a link.

  14. Confirm your selections and click Finish.

Editing a Tree as a Developer

By default, a tree does not have focus and node links are activated with a single click action. This section describes how developers can customize a tree by editing tree attributes.

Accessing the Tree Attributes Page

Developers can customize a tree by editing attributes on the Tree Attributes page.

To access the Tree Attributes page:

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

  2. Access the Tree Attributes page:

    • Tree view - Right-click on the Region name and select Edit Tree.

    • Component View - Under Regions, click the Tree link next to the name of the tree region you want to edit.

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

    • Display Settings enable you control a tree's appearance and functionality. Use these settings to define a tree template, tooltip, a page item to hold the last selected node value for saving tree state, determine whether the tree has focus on page load, and what action activates node links on a tree.

    • Query enables you to edit the SQL query underlying the tree. The query must be in the following format:

      SELECT status, level, name, icon, id, tooltip, link 
      FROM ... 
      WHERE ... 
      START WITH... 
      CONNECT BY PRIOR id = pid 
      
      ORDER SIBLINGS BY ...
      
  3. 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".

  4. Click Apply Changes.

Using the Find Icon

You can search for items, pages, queries, tables, PL/SQL code, images, or cascading style sheets by clicking the Find icon on numerous pages within Application Builder.

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

About the Item Finder

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.

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:

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

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

    • Go button - Executes a search.

    • Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

    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. To expand the view to all pages, delete the page number in the Page field and click Go.

  3. To edit a specific item, navigate to the appropriate item. See "Understanding Page-Level Items" and "Editing Multiple Items Simultaneously".

Using the Page Finder

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. Click the Pages tab.

    The Page Finder report appears, displaying the page number, page name, page alias, title, tab set, and counts of the number of items and regions on the page.

    A search bar displays at the top of the page and contains the following controls:

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

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

    • Go button - Executes a search.

    • Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

    Description of pages_finder.gif follows
    Description of the illustration pages_finder.gif

  3. To link to the Items page, click the page number.

Using the Queries Finder

You can use the Queries Finder to locate a query within your application or within the schema associated with the workspace.

To search for a query using the Queries Finder:

  1. Click the Find icon.

  2. Click the Queries tab.

    The Query Finder report appears, displaying the page number, page name, the region containing the query, and the query source.

    A search bar displays at the top of the page and contains the following controls:

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

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

    • Go button - Executes a search.

    • Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

    Description of query_finder.gif follows
    Description of the illustration query_finder.gif

  3. To link to the Items page, click the page number.

Using the Table Finder

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. Click the Tables tab.

    The Table Finder appears.

    Description of table_finder.gif follows
    Description of the illustration table_finder.gif

    A search bar displaying the selected tab appears at the top of the page and contains the following controls:

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

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

    • Go button - Executes a search.

    • Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

  3. Select a table name.

    A table definition appears.

    This report displays the column names, data type, length, precision, and scale and the SQL necessary to re-create the table appears at the bottom of the page.

See Also:

"Managing Tables" in Oracle Application Express SQL Workshop Guide

Using the PL/SQL Finder

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. Click the PL/SQL tab.

    The PL/SQL Finder appears.

    A search bar displays at the top of the page and contains the following controls:

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

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

    • Go button - Executes a search.

    • Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

  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 and additional information including the owner, source name, source type, return type, argument names, data types, and IN/OUT parameters.

See Also:

"Managing Database Objects with Object Browser" in Oracle Application Express SQL Workshop Guide

Using the Images Finder

You can use the Images Finder to identify images available to the current application.

To view available images:

  1. Click the Find icon.

  2. Click the Images tab.

    The Images Finder appears.

    Description of image_finder.gif follows
    Description of the illustration image_finder.gif

    A search bar displays at the top of the page and contains the following controls:

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

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

    • Go button - Executes a search.

    • Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

  3. From Show, select the type of images to view. Options include:

    • Standard Images

    • Workspace Images

    • Application Images

See Also:

"Managing Database Objects with Object Browser" in Oracle Application Express SQL Workshop Guide

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. To create an access control page, the application schema needs to have CREATE TABLE, CREATE TRIGGER, CREATE SEQUENCE privileges.

Topics:

See Also:

"Building Queries with Query Builder" in Oracle Application Express SQL Workshop Guide

How the Access Control List Works

You create an access control list by running the Access Control Wizard. The Access Control Wizard creates a 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

  • the authorization schemes that correspond to the application mode list options

  • 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 page. You can create a page on the Application home page, while viewing a Page Definition, or by clicking Create on the Developer toolbar.

Topics:

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. See "Accessing the Page Definition".

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

  3. Click New page and Select New 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 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.

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

  5. Repeat steps 1 to 5 for all users.

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

  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.

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.

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.

You can get and set item attributes and values using the JavaScript function $v('P1_FIRST_NAME'). Consider the following example:

function firstName(){    
  alert('First Name is ' +$v('P1_FIRST_NAME'));
  }
 // or a more generic version would be
function displayValue(pId){   
  alert('The Value is ' + $v(pId));
  }
 
  // Then add the following to the "Form Element Attributes" Attribute of the item:
  onchange="displayValue('P1_FIRST_NAME');"

Incorporating JavaScript Functions

You can include JavaScript functions by incorporating JavaScript

  • Editing the JavaScript attribute of the page

  • Calling the .js file from the HTML Header attribute of the page

  • Calling the .js file from the page template

See Also:

"Text with JavaScript Escaped Single Quotes" for information about referencing a shortcut inside of a JavaScript literal string

Topics:

Incorporating JavaScript Utilizing the JavaScript Attribute

You can include JavaScript into your application by adding it to the JavaScript attribute of the page. This is a good approach for functions that are very specific to a page and a convenient way to test a function before you include it in the .js file.

In the following example, adding the code would make the test function accessible from anywhere on the current page.

To add JavaScript code to the JavaScript attribute:

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

  2. To access the Edit Page:

    • Tree view - Under Page Rendering, double-click the page title at the top of the tree.

    • Component view- Under Page, click the Edit icon.

  3. Scroll down to JavaScript.

  4. Enter code into the following:

    • Function and Global Variable Declaration - Enter JavaScript code (for example, functions or global variable declarations) for code to be used on this page. If the code is used on multiple pages, consider putting it into an external JavaScript file to avoid duplication.

      Code you enter here replaces the #JAVASCRIPT_CODE# substitution string in the page template.

    • Execute When Page Loads - Enter JavaScript code to execute when the page loads. The code is executed after the JavaScript code generated by Oracle Application Expresss.

    For example, adding the following would test a function accessible from anywhere on the current page.

    function test(){
        window.alert('This is a test.');
      }
    
  5. Apply Changes.

Calling the JavaScript File from the HTML Header Attribute

To call a .js file from the HTML Header attribute:

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

  2. To access the Edit Page:

    • Tree view - Under Page Rendering, double-click the page title at the top of the tree.

    • Component view- Under Page, click the Edit icon.

  3. Scroll down to HTML Header and Body Attribute.

  4. In HTML Header, call the JavaScript file using the following syntax:

    <script src="/my_images/custom.js" type="text/javascript"></script>
    
  5. Apply Changes.

Calling the JavaScript File from 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="/my_images/custom.js" type="text/javascript"></script>
</head>
<body #ONLOAD#>#FORM_OPEN#

See Also:

"Page Templates"

Calling JavaScript from a Button

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

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

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 deletion using a process that conditionally executes based on the value of the 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:

javascript:confirmDelete('Would you like to perform this delete action?');

Calling JavaScript Using a Dynamic Action

You can also execute JavaScript code by creating a dynamic action of type Execute JavaScript Code. To learn more, see "Implementing Dynamic Actions".

Optimizing a Page for Printing

You can optimize a page for printing by creating a specific Printer Friendly template and selecting that template on the Create/Edit Theme page. See "Changing the Default Templates in a Theme".

Generally, a Printer Friendly template optimizes a page for printing. For example, a Printer Friendly template might:

If the theme you select does not include a printer friendly template, you can create a Printer Friendly template by creating a new page template.

Topics:

Selecting a Printer Friendly Template for an Application

To select a Printer Friendly template:

  1. Navigate to the Themes page:

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

    2. Select an application.

    3. Click Shared Components.

    4. Under User Interface, select Themes.

      The Themes page appears.

  2. Change to a report view by clicking the View Report icon.

  3. In the Themes list, click the theme name.

    The Create/Edit Theme page appears.

  4. Scroll down to Component Defaults and locate the Printer Friendly Page list.

  5. Make a selection from the Printer Friendly Page list.

  6. Click Apply Changes.

Using f?p Syntax to Toggle to Printer Friendly Mode

Once you create a Printer Friendly template and select it, you can use f?p syntax to toggle to Printer Friendly mode. Including the ninth f?p syntax argument (PrinterFriendly) renders the page in printer friendly mode (optimizing printed output). For example, you could include this argument when coding a link or creating a navigation bar icon.

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:

  1. Create a dedicated Help page and Help region.

  2. Define page Help text.

  3. Define item Help text.

  4. Create a navigation bar icon to link to your Help page.

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:

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 Help Text region:

  1. Create a page for your Help. See "Managing Pages in a Database Application".

  2. Navigate to the Page Definition of your Help page. See "Accessing the 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.

Topics:

Defining Help for a Page

To define page Help text:

  1. Navigate to the Page Definition for the page for which you want to add page Help. See "Accessing the Page Definition".

  2. Under Page, click the Edit page attributes icon to view the existing page attributes.

  3. Scroll down to Help.

  4. Enter your Help text in the field provided.

  5. Click Apply Changes.

Repeat the previous procedure for each page requiring page Help text.

Defining Help Text for an Item

To define item Help text for an item:

  1. Navigate to the Page Definition for the page for which you want to add item Help. See "Accessing the Page Definition".

  2. Under Items, click the 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 Simultaneously

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

  4. Click the Utilities icon.

  5. From Page Specific Utilities, select Item Utilities.

  6. Click Grid Edit of All Item Help Text.

  7. 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 your application does not yet contain item Help, you can perform a mass update of default Help text.

To seed item Help topics:

  1. Access the Bulk Edit Item Help report as described in "Accessing 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. Access the Bulk Edit Item Help report as described in "Accessing the Bulk Edit Item Help Report".

  2. In Help Contains, enter keywords.

  3. Click Go.

Searching for an Item Label

To search for an item label:

  1. Access the Bulk Edit Item Help report as described in "Accessing the Bulk Edit Item Help Report".

  2. In Help Contains, enter keywords.

  3. 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. See "Accessing the Page Definition".

  2. Under Navigation Bar, click the Create icon.

  3. For Attributes:

    1. Sequence - Specify the sequence for this component. The sequence determines the order of evaluation.

    2. Alt Tag Text - Enter ALT text for navigation icons that are images. If you do not specify an image name, then this text displays.

    3. Icon Image Name - Enter the name of the image that displays.

    4. Image Height - Enter the height of the image.

    5. Image Width - Enter the width of the image.

    6. Text - Enter additional text to display with the image. You can include text or use icons with no text. This attribute is optional and can be translated.

    7. Click Next.

    Next, specify the target location.

  4. For Target:

    1. Target is a - Select Page in this application.

    2. Page - Specify the page number of the help page you created in "Creating a Help Page and Region".

    3. Request - Enter the following:

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

    4. Click Next.

  5. Click Create.