| Oracle® Database Express Edition Application Express User's Guide Release 2.1 Part Number B25309-01 |
|
|
View PDF |
| Oracle® Database Express Edition Application Express User's Guide Release 2.1 Part Number B25309-01 |
|
|
View PDF |
This section describes how to use Application Builder to build an application and application components. It includes instructions for creating an application and adding pages as well as adding components (reports, charts, or forms), page controls (buttons, items, list of values), and shared components (menus, lists, or tabs).
This section contains the following topics:
See Also:
An application is a collection of pages which share a common session state and authentication. You create a new application in Application Builder using a wizard. You delete an application from the Application Builder home page.
Topics in this section include:
When you click Create on the Application Builder home page, you must choose one of the following options:
Create Application. Creates an application based on SQL queries or database tables. You can define blank pages or pages that contain reports, forms, tabular forms, or a report with a linked form. See "About the Create Application Wizard".
Create Application from Spreadsheet. Creates an application based on spreadsheet data. You can upload or paste spreadsheet data to create a table and then add a user interface. In the resulting application, users can create queries, add, insert, or update records, or analyze the data. See "About the Create Application from Spreadsheet Wizard".
Demonstration Application. Installs or uninstalls demonstration applications. Use demonstration applications to learn how to build applications. See "About Demonstration Applications".
See Also:
"Adding Pages to an Application" for information about adding reports and forms by creating a new pageThe Create Application wizard enables you to create a fully functional application based on any number of tables. You can use the Create Application Wizard to create blank pages, or pages based on SQL queries or database tables. You can create SQL queries by manually typing SQL or by using the graphical user interface of Query Builder. Applications based on tables can consist of a simple report, a form and report, or a tabular form. When creating pages on tables, you have the option to generate analysis pages. Analysis pages extend a simple report or a report on a form to include multiple drill-down reports and charts.
Topics in this section include:
You can create an application based on a table, query, or drill-down query by selecting Create Application in the Create Application Wizard.
To create an application based on a table, query, or drill-down query:
On the Database Home Page, click the Application Builder icon.
Click the Create button.
Select Create Application and click Next.
Enter the basic application details and click Next:
Name - Enter a name to identify the application.
Application - Enter an unique integer value to identify the application.
Create Application - Select a creation method:
From scratch enables you to add pages manually
Based on existing application design model enables you 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 using by choosing to copy shared components from another application (See step 7 and "About Application Models and User Interface Defaults").
Schema - Your application will obtain its privileges by parsing all SQL as a specific database schema. Identify the database schema owner.
Next, add pages to your application.
Under Add Pages:
Select the type of page you want to add. Options include:
Blank creates a page with no built-in functionality.
Report creates a page the contains the formatted result of a SQL query. You can choose to build a report based on a table you select, or based on a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement that you provide.
Form creates a form to update a single row in a database table.
Tabular Form creates a form to perform update, insert, and delete operations on multiple rows in a database table.
Report and Form builds a two page report and form combination. On the first page, users select a row to update. On the second page users can add a new record or update or delete an existing record.
Action displays the currently selected page type. For each selection, the wizard prompts you for a variety of different types of information.
Report pages include the Include Analysis Pages check box. Select this option and follow the wizard prompts to extend a simple report or a report on a form to include multiple drill-down reports and charts.
Click Add Page.
The page (or pages) appear at the top of the page. To delete a page, click Delete icon.
Repeat the previous steps until all pages have been added.
Click Next.
Determine whether to include tabs in your application and click Next.
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:
From Copy Shared Components from Another Application, select Yes.
From Copy from Application, select the application from which you want to import shared components.
From Select Components to Import, select the components to import.
Click Next.
Select the following authentication and globalization preferences:
Default Authentication Scheme - Identify an authentication scheme you would like to use by default.
Authentication is the process of establishing users' identities before they can access an application. See "Establishing User Identity Through Authentication".
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.
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 Edit Globablization attributes page. See "Configuring Globalization Attributes".
Click Next.
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".
Confirm your selections. To return to a previous wizard page, click Previous. To accept your selections, click Finish.
The Create Application Wizard is designed with the assumption that the developer may run it multiple times. To facilitate this iterative approach to application development, every time you run the wizard it saves the page definitions to an application model.
Consider the following example. You create a new application by running the Create Application Wizard. After viewing the application, you realize it is not quite what you wanted. Instead of altering it, you can run the wizard again and select an application model. By selecting an existing application model when you rerun the wizard, you can quickly improve your application with minimal time and effort.
See Also:
"Managing Application Models"Another way to increase your productivity when creating an application is to specify user interface defaults. User interface defaults are metadata that enable you to assign default user interface properties to a table, column, or view within a specified schema.
See Also:
"Managing User Interface Defaults"You can increase your productivity when creating applications by leveraging application models and user interface defaults. Consider the following scenario:
Create an application based on tables or views by running the Create Application Wizard.
Run the generated application. Note any functional deficiencies.
Evaluate whether to create or edit user interface defaults.
For example, you can use user interface defaults to control how form field or report labels display. You can also utilize user interface defaults to display specific columns or have columns display in an alternate order.
Navigate to the Application home page and create a new application by clicking Create.
Select Create Application.
When prompted to enter application details, specify the following:
Name - Enter a name to identify the application.
Application - Enter an unique integer value to identify the application, or accept the default.
Create Application - Select Based on existing application design model.
Select an application model.
Note the pages you previously created already appear.
Add pages, edit pages, or remove pages.
Complete the wizard.
Repeat steps 2 through 9 until the application meets your functional requirements.
You can create an application based on spreadsheet data by selecting Create Application from Spreadsheet in the Create Application Wizard.
To create an application from spreadsheet data:
On the Database Home Page, click the Application Builder icon.
Click the Create button.
Select Create Application from Spreadsheet.
Specify how spreadsheet data will be uploaded. Select one of the following and click Next:
Upload file (comma-delimited or tab-delimited). Specify the following and click Next:
File - Click Browse to locate the file to be uploaded.
Separator - Specify the column separator character. Use \t for tab separators.
Optionally Enclosed By - Enter a delimiter character. You can use this character to delaminate the starting and ending boundary of a data value. If you specify a delimiter character, the wizard ignores white space occurring before the starting and ending boundary of a data value. You can also use this option to enclose a data value with the specified delimiter character.
File Character Set - Choose the character set in which the text file is encoded.
Copy and paste (up to 30KB). Copy and paste the spreadsheet data you wish to import and click Next.
Review the preview of how your table will display and click Next. Specify the table name and column names, modify the data types, or specify which columns to include.
Review the displayed Singular Name and enter a Plural Name.
Column User Interface Defaults display default label names.
(Optional) Under Column User Interface Defaults, edit the displayed Label names and click Next.
For Summary By Column, select the columns for which data will be summarized in reports and charts and click Next.
This wizard creates several summary reports. Select columns for which values will be aggregated in summary reports.
Aggregate by Column - Choose one or more columns for which you want data summarized or averaged.
Aggregate Function to Use - Select the aggregate function to use in the report (Sum or Average).
Click Next.
Select Application Options:
Application Name - Enter an alphanumeric name for this application.
Specify a Create Mode:
Read and Write includes insert and update pages.
Read Only does not include insert and update pages.
Select a chart type.
Click Next.
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".
Confirm your selections. To return to a previous wizard page, click Previous. To accept your selections, click Create.
Oracle Application Express installs with a number of demonstration applications. Use these applications to learn more about the different types of functionality you can include in your applications.
See Also:
"Running a Demonstration Application"To access demonstration applications:
On the Database Home Page, click the Application Builder icon.
Click the Create button.
Select Demonstration Application.
The Demonstration Applications page appears, displaying links to the following applications:
To install a demonstration application, scroll down to the application you want to install, click Install.
Installed appears as the Status.
To edit an installed demonstration application, click Edit.
To run an installed demonstration application, click Run.
To reinstall a demonstration application, click Re-Install.
You can delete an application from within Application Builder, or while editing application attributes. If you delete an application you also delete all defined components (reports, charts, or forms), page controls (buttons, items, list of values), and shared components (breadcrumbs, lists, and tabs, but not user interface defaults).
Topics in this section include:
To delete an application from Application Builder:
On the Database Home Page, click the Application Builder icon.
Select an application.
When Application Builder appears, verify the application ID and name at the top of the page.
On the Tasks list, click Delete this Application.
Follow the on-screen instructions.
To delete an application from Edit Application Attributes:
On the Database Home Page, click the Application Builder icon.
Select an application.
Click Edit Attributes.
Click Edit Standard Attributes.
Verify the application ID and name.
Click Delete at the top of the page.
You can add a new page or add a component to an existing page by running the Create Page Wizard. You can access this wizard by:
Clicking Create Page on the Applications home page
Clicking Create on the Page Definition
Selecting the Create link on the Developer toolbar
Note:
You can also add a component (that is, a report, chart, form, wizard, a calendar, or tree) to an existing page using the Create Page Wizard. When prompted, specify an existing page ID.Topics in this section include:
See Also:
"Creating Reports", "Creating Charts", "Creating Forms", "Creating Calendars", and "Creating Trees"To create a new page from the Application home page:
On the Database Home Page, click the Application Builder icon.
Select an application.
The Application home page appears.
Click Create Page.
Select the type of page you want to create:
Blank Page
Multiple Blank Pages
Report
Chart
Form
Wizard
Calendar
Tree
Login Page
Follow the on-screen instructions.
To create a new page while viewing a Page Definition:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
From the navigation bar at the top of the page, click the Create button.
Select the type of page you want to create:
New Page
Region on this page
Page control on this page
Shared Control
Table 5-1 describes the various selections available based on the type of page you select.
Table 5-1 Create Page Options
| Create Page Options | Available Selections |
|---|---|
|
New Page |
Available page types:
|
|
Region on this page |
Regions function as containers for content. Available region types:
|
|
Page control on this page |
Page controls:
|
|
Shared control |
Shared component options:
|
Follow the on-screen instructions.
See Also:
Editing a Page DefinitionUsers who log in to the Database Home Page user interface using a database user account have access to the Developer toolbar. The Developer toolbar displays at the bottom every page and offers a quick way create a new page.
To create a new page from the Developer toolbar:
On the Developer toolbar, select Create.
Select the type of component you want to create and click Next. Available options include:
New Page
Region on this page
Page control on this page
Shared control
Table 5-1 describes the various selections available based on the type of page you select.
Follow the on-screen instructions.
See Also:
"Using the Developer Toolbar"The Application Express engine dynamically renders and processes pages based on data stored in database tables. To view a rendered version of your application, you run or submit it to the Application Express engine. As you create new pages, you can run them individually, or run an entire application. You can run an application by clicking the Run Application icon.
Topics in this section include:
Running an Application from the Application Builder Home Page
Running a Page from the Pages List on the Application Home Page
The Run Application icon resembles a traffic light. A large colored Run Application icon appears on the Application home page.
Many pages within Application Builder also feature a smaller, light green version of this icon. Clicking this smaller Run icon runs the current application or individual pages depending upon the context. For example, clicking the Run icon on the Application home page runs the entire application. Clicking the icon on the Page Definition runs the current page.
To run an entire application from the Application Builder home page:
On the Database Home Page, click the Application Builder icon.
From the View list, select Details and click Go.
Locate the application in the Applications list.
Click the Run icon in the far right column.
To run an entire application from the Application home page:
On the Database Home Page, click the Application Builder icon.
The Application Builder home page appears.
Select on application.
Click the Run icon.
To run a page from the Pages list:
On the Database Home Page, click the Application Builder icon.
Select an application.
From the View list, select Details and click Go.
From the Pages list, locate the page you want to run and click the Run icon in the far right column.
To run a specific page from the Page Definition:
On the Database Home Page, click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Click the Run Page icon in the upper right corner of the page.
Use page groups to organize and manage the pages within an application. To use page groups, you create a group and then assign pages to the group.
Page groups do not have any function other than to help a developer organize their application pages.
You can make the pages within your application easier to access by organizing them into page groups.
Topics in this section include:
The Page Group report offers a comprehensive list of which pages in an application are assigned to a group and which pages are unassigned.
To view the Page Group report from the Application home page:
On the Database Home Page, click the Application Builder icon.
Select the application.
On the Tasks List on the right side of the page, click Manage Page Groups.
On the Tasks list, click Report Page Groups.
To create a page group:
On the Database Home Page, click the Application Builder icon
Select an application.
On the Tasks List on the right side of the page, click Manage Page Groups.
On the Page Groups page, click Create.
Enter a name, a description (optional), and click Create.
To assign pages to page group:
On the Database Home Page, click the Application Builder icon.
Select an application.
On the Tasks List on the right side of the page, click Manage Page Groups.
On the Tasks list, click Report Unassigned Pages.
The Unassigned Pages page appears.
From Page Group, select a group to which you want to assign pages.
Select the pages to be assigned.
Click Assigned Checked.
Selecting the page ID takes you to the Page Attributes page. Selecting the Page Name links to the Page Definition.
You can prevent conflicts during application development by locking pages in your application. By locking a page, you prevent other developers from editing it.
Topics in this section include:
A lock icon indicates whether a page is currently locked. If a page is unlocked, the icon appears as an open padlock. If the page is locked, the icon appears as a locked padlock. A lock icon appears on the following pages:
Application home page. Select Details from the Display list. A list of pages appears. The lock icon appears under the Lock column.
Page Definition. The lock icon appears on the far right side of the page across from the breadcrumb menu.
You can lock pages from the Page Locks page, the Pages list, and from a Page Definition.
To lock a page of your application:
On the Database Home Page, click the Application Builder icon.
Select an application.
On the Tasks List on the right side of the page, click Manage Page Locks.
Select the appropriate pages and click Lock Checked.
Enter a comment in the Comment field.
Click Lock Page(s).
To lock a page from the Pages list:
On the Database Home Page, click the Application Builder icon.
Select an application.
From the View list, select Details and click Go.
In the Pages list, locate the page you want to lock and click the Lock icon.
Enter a comment in the Comment field.
Click Lock Page(s).
To lock a page from the Page Definition:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Click the Lock icon in the upper right corner above Shared Components.
Select the appropriate pages and click Lock Checked.
Enter a comment in the Comment field.
Click Lock Page(s).
Only the developer who locked a page can unlock it. However, a developer with administrative privileges can unlock pages locked by other developers.
To unlock a page from the Page Locks page:
On the Database Home Page, click the Application Builder icon.
Select the application.
On the Tasks List on the right side of the page, click Manage Page Locks.
Select the appropriate pages.
Click UnLock Checked.
To unlock a page from the Pages list:
On the Database Home Page, click the Application Builder icon.
Select an application.
From the View list, select Details and click Go.
In the Pages list, locate the page you want to unlock and click the Lock icon.
The Edit Lock Comment page appears.
Click UnLock.
To unlock pages from the Page Definition:
On the Database Home Page, click the Application Builder icon.
Select the application.
Select the Page you want to unlock in the Pages list.
Click the Lock icon in the upper right corner above Shared Components.
The Page Locks page appears.
Select the Page you want to unlock and click Unlock Checked.
You can access a number of different views of Locked Pages on the Locked Pages page.
To access different views of locked pages:
On the Database Home Page, click the Application Builder icon.
Select the application.
On the Tasks List on the right side of the page, click Manage Page Locks.
On the Tasks list, click one of the following:
Show Locked Pages displays only locked pages within the current application.
Show All Pages displays all pages within the current application.
Show Unlocked Pages display only unlocked pages within the current application.
Administer Locks enables an administrators to unlock any pages locked by a developer.
You can delete a page from the Page Definition or while editing page attributes.
Topics in this section include:
To delete a page from the Page Definition:
On the Database Home Page, click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Verify the page name.
From the navigation bar at the top of the page, click Delete.
Follow the on-screen instructions.
See Also:
"Editing a Page Definition" for information about editing page attributesTo delete a page while editing page attributes:
On the Database Home Page, click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Click the Edit Attributes icon.
Click Edit Standard Attributes.
Verify the application ID and page name.
The Edit Application Attributes page appears.
Click Delete.
Follow the on-screen instructions.
In Oracle Application Express, a report is the formatted result of a SQL query. You can generate reports by selecting and running a built-in query, or by defining a report region based on a SQL query.
Topics in this section include:
Application Builder includes a number of built-in wizards for generating reports.
To create a report using a wizard:
On the Database Home Page, click the Application Builder icon.
Select the application.
Click Create Page.
Select Report.
Select one of the following report types:
Wizard Report - Does not require any SQL knowledge. Select the appropriate schema, table, columns, and result set display.
SQL Report - Creates a report based on a custom SQL SELECT statement or a PL/SQL function returning a SQL SELECT statement that you provide.
Follow the on-screen instructions.
You can use the Report Attributes and Column Attributes pages to precisely control the definition of report pages. 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.
On the Page Definition, you can access the Report Attributes page by clicking either Report or RPT, adjacent to the report region you want to edit. Report indicates the report is a regular report, and RPT indicates the report is an wizard report. You can also navigate to the Report Attributes page by clicking the region name and then selecting the Report Attributes tab.
To access the Report Attributes page:
On the Database Home Page, click the Application Builder icon.
Select the application.
Select a page.
The Page Definition appears.
Under Regions, click Report next to the name of the report region you want to edit.
The Report Attributes page appears.
Heading Type identifies how the heading was generated for the report. Use the Column Attributes section to control report column appearance and functionality. The Link column indicates if a column link is currently defined. The Edit column indicates whether or not a column is currently updatable.
Table 5-2 describes common report column edits.
Table 5-2 Common Report Column Edits
| Description | Developer Action |
|---|---|
|
Alter column display sequence. |
Click the up and down arrows. |
|
Alter heading alignment. |
Under Column Alignment, select a new column alignment. |
|
Change column heading text. |
Under Heading, enter different heading text. |
|
Control which columns display. |
Click Show to indicate a column should display. |
|
Enable an 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.
To access the Column Attributes page, click the Edit icon adjacent to the appropriate column Alias.
See online Help for more information about a specific attribute.
You control report pagination by:
Including a pagination substitution string in the report template
Making selections from Layout and Pagination on the Report Attributes page
You control how pagination displays by making selections from the Layout and Pagination attributes on the Report Attributes page.
To access the Layout and Pagination section of the Report Attributes page:
Create a report. See "Creating a Report Using a Wizard".
Under Regions, click the appropriate report attributes link (Report or RPT).
The Report Attributes page appears.
Scroll down to Layout and Pagination.
You use the Layout and Pagination attributes to select a pagination style, determine where pagination displays, and specify the number of rows that display on each page. Table 5-3 describes the most commonly used Layout and Pagination attributes.
Table 5-3 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. |
|
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 for this item. |
|
Display Position |
Defines where pagination displays. 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. |
|
Strip HTML |
Specifies whether or not to remove HTML tags from the original column values for HTML expressions and column links. If you select values from the database that already contain HTML tags, then those tags can cause conflicts with the HTML generated for your columns links or HTML expressions. When this option is enabled, only the actual data portion of your column value is used. |
To include pagination after the rows in a report:
Create a report. See "Creating a Report Using a Wizard".
Next, select the appropriate Layout and Pagination attributes.
Navigate to the Report Attributes page:
Navigate to the Page Definition.
Under Regions, click the appropriate report attributes link (Report or RPT).
The Report Attributes page appears.
Under Layout and Pagination, select the following:
Report Template - Select a report template (optional).
Pagination Scheme - Select a pagination scheme.
Display Position - Select a display position.
Number of Rows - Specify how many rows display on each page.
Click Apply Changes.
Edit the report template:
Navigate to the Page Definition.
Under Templates, select the report template name.
Include the #PAGINATION# substitution string in the After Rows attribute.
Click Apply Changes.
Run the page.
To include pagination before the rows in a report:
Create a report. See "Creating a Report Using a Wizard".
Next, select the appropriate Layout and Pagination attributes.
Navigate to the Report Attributes page:
Navigate to the Page Definition.
Under Regions, click the appropriate report attributes link (Report or RPT).
The Report Attributes page appears.
Under Layout and Pagination:
Report Template - Select a report template (optional).
Pagination Scheme - Select a pagination scheme.
Display Position - Select a position that contains the word top.
Number of Rows - Specify how many rows display on each page.
Click Apply Changes.
Edit the report template.
Navigate to the Page Definition.
Under Templates, select the report template name.
Include the #TOP_PAGINATION# substitution string in the Before Rows attribute.
Click Apply Changes.
Run the page.
You enable column sorting on the Report Attributes page.
To enable column sorting:
Navigate to the Report Attributes page. See "Editing Report Attributes".
Under Report Column Attributes, select the Sort check box adjacent to the columns to be sorted.
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.
Scroll down to Sorting.
Specify ascending and descending image attributes or click set defaults.
You can create a link within a report that enables users to export the report as a comma-delimited file (.csv) file. To add a CSV link to a report you need to enable the CSV output option. When using the CSV output option, the report template is not important. You can include a CSV link with any report template that has the CSV export substitution string defined.
See Also:
"Automatic CSV Encoding"To enable the Enable CSV output option:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Scroll down to Report Export.
From Enable CSV output, select Yes.
(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.
In the Link Label field, enter link text. This text will display in your report and enable users to invoke a download.
(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).
Click Apply Changes.
You can export a report as an XML files by selecting a report template.
To export a report as a file:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Scroll down to Layout and Pagination.
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.
Click Apply Changes.
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:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Under Report Column Attributes, locate the column to contain the link.
Click the Edit icon adjacent to the column name.
The Column Attributes page appears.
Scroll down to Column Link.
To create a column link to another page:
From Target, select Page in this Application.
(Optional) In Link Attributes, specify additional column link attributes that will be included in the <a href= > tag (for example, a link target, classes, or styles).
In Link Text, enter the text to be displayed as a link, specify an image tag, or pick from the list of default images.
In Page, specify the target page ID. To reset the pagination for this page, select Reset Pagination.
In Request, specify the request to be used.
In Clear Cache, specify the pages (that is, the page IDs) on which to clear cache. You can specify multiple pages by listing the page IDs in a comma-delimited list.
Use the Name and Value fields to specify session state for a specific item.
Click Apply Changes.
To create a column link to a URL:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Access the Column Attributes page by clicking the Edit icon adjacent to the appropriate column.
The Column Attributes page appears.
Scroll down to Column Link.
Under Column Link, make the following selection:
From Target Type, select URL.
In Link Text, enter the text to be displayed as a link and select a substitution string.
(Optional) In Link Attributes, specify additional column link attributes that will be included in the <a href= > tag (for example, a link target, classes, or styles).
In URL, enter the appropriate address.
Click Apply Changes.
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:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Access the Column Attributes page by clicking the Edit icon adjacent to the appropriate column.
The Column Attributes page appears.
Scroll down to Tabular Form Element.
Under Tabular Form Element, make the following selections:
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 PL/SQL process is implemented to process updated data.
Date Picker Format Mask - Make a selection if you selected the Display As type of Date Picker.
Element Width - Specify the width of the form item.
Number of Rows - Specify the height of a form item (applicable to text areas).
Element Attributes - Define a style or standard form element attribute.
Element Option Attributes - Specify form element attributes for items in a radio group or check box.
Primary Key Source Type - Identify the default type.
Primary Key Source - Identify the default source.
If the current column is part of the primary key defined in an MRU process, only the primary key source type and source appear.
Otherwise, Default and Default Type appear. Use Default and Default Type to establish a relationship between two master records in a master detail form, or to set the default values for new rows.
Reference Table Owner - Identify the owner of the referenced table. Use this attribute to build User Interface Defaults for reports.
Reference Table Name - Identify the table or view that contains the current report column.
Reference Column Name - Identify the column name that this report column references.
Click Apply Changes.
Report columns can be rendered as lists of values. For example, a column can be rendered using a select list or a popup list of values. Or, a column can also 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:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Access the Column Attributes page by clicking the Edit icon adjacent to the appropriate column.
The Column Attributes page appears.
Scroll down to List of Values.
From Named LOV, make a selection from the List of Values repository.
To include a null value in a list of values:
In Display Null, select Yes.
In Null Text, specify the value that displays.
A column can also have a value that does not display in its list of values.
To define a value that does not display in the list of values:
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.
In Null Value, specify the value that displays.
If you have not selected a Named LOV, enter the query used to display a select list in the LOV Query field.
If you have not selected a Named LOV, enter the query used to display a select list in LOV Query.
Click Apply Changes.
See Also:
"Creating Lists of Values"You can use the Authorization and Condition column attributes to control when a column displays.
Authorization enables you to control access to resources (such as a report column) based on predefined user privileges. For example, you could create an authorization scheme in which only managers can view a specific report column. Before you can select an authorization scheme, you must first create it.
A condition is a small unit of logic that enables you to control the display of a column based on a predefined condition type. The condition evaluates to true or false based on the values you enter in the Expressions fields.
To specify Authorization and Condition attributes:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Access the Column Attributes page by clicking the Edit icon adjacent to the appropriate column.
The Column Attributes page appears.
Under Authorization, make a selection from the Authorization Scheme list.
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.
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:
Navigate to the appropriate Report Attributes page. See "Editing Report Attributes".
Scroll down to Break Formatting.
Make a selection from the Breaks list.
You can include a variety of different types of forms in your applications. You can include forms that enable users to update just a single row in a table or multiple rows at once. Application Builder includes a number of wizards you can use to create forms automatically, or you can create forms manually.
Topics in this section include:
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:
On the Database Home Page, click the Application Builder icon.
Select an application.
Click Create Page.
Select Form.
Under Forms, select a type of form page as described in Table 5-4.
Table 5-4 Forms Page Types
| Form Page Type | Description |
|---|---|
|
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. |
|
|
Creates a form based on the columns returned by a SQL query such as an EQUIJOIN. |
|
|
Creates a form that enables users to update a single row in a database table. |
|
|
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. |
|
|
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" |
|
|
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" |
|
|
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: "Building a Master Detail Form" |
|
|
Creates a read-only version of a form. Typically used to provide a confirmation page at the end of a wizard. |
|
|
Creates a form in which users can update multiple rows in a database. See Also: "Creating a Tabular Form" |
Follow the on-screen instructions.
A tabular form enables users to update multiple rows in a table. The Tabular Form Wizard creates a form to perform update, insert, and delete operations on multiple rows in a database table.
To create a tabular form:
On the Database Home Page, click the Application Builder icon.
Select an application.
Click Create Page.
Select Form.
Select Tabular Form.
The Tabular Form Wizard appears.
On Identify Table/View Owner:
Specify the table or view owner on which you want to base your tabular form.
Select the operations to be performed on the table (for example, Update, Insert and Delete).
On Identify Table/View Name, select a table.
On Identify Columns to Display:
Specify whether or not to use user interface defaults. Select Yes or No.
User interface defaults enable you to assign default user interface properties to a table, column, or view within a specified schema.
Select the columns (updatable and nonupdatable) to include in the form.
You can modify the column order or your SQL query after you create the page.
On Identify Primary Key, select the Primary Key column and a secondary Primary Key column (if applicable).
On Defaults for Primary and Foreign Keys, select a source type for the primary key column. Valid options include:
Existing trigger - Select this option if a trigger is already defined for the table. You can also select this option if you plan on specifying the primary key column source later after completing the form.
Custom PL/SQL function - Select this option if you wish to provide a PL/SQL function to generate returning key value.
Existing sequence - Select this option is you wish to pick the sequence from a list of sequences available in the selected schema.
On Updatable Columns, select which columns should be updatable.
On Identify Page and Region Attributes.
Specify page and region information.
Select a region template.
Select a report template.
On Identify Tab, specify a tab implementation for this page.
On Button Labels, enter the display text to appear for each button.
On Identify Branching, specify the pages to branch to after the user clicks the Submit and Cancel buttons.
Click Finish.
Note:
Do not modify the select list of a SQL statement of a tabular form after it has been generated. Doing so can result in a checksum error when you alter data in the form.See Also:
"Managing User Interface Defaults"A master detail form reflects a one-to-many relationship between two tables in a database. Typically, a master detail form displays a master row and multiple detail rows within a single HTML form. With this form, users can insert, update, and delete values from two tables or views.
To create a master detail form:
On the Database Home Page, click the Application Builder icon.
Select an application.
Click Create Page.
Select Form.
Select Master Detail Form.
The Master Detail Wizard appears.
On Define Master Table:
Select a table or view owner.
Select a table or view name.
Select the columns to display.
On Define Detail Table:
Specify to show only related tables.
Select the table or view owner.
Select the table or view name.
Select the columns to display.
On Define Primary Key, select the primary key column for the master table, and then select the primary key column for the detail table.
On Define Master and Detail, define the relationships between master and detail tables.
Specify the source for the master table and detail table primary key columns.
On Define Master Options, specify whether or not to include master row navigation.
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.
On Choose Layout, specify the layout of the master detail pages.
You can include the master detail as a tabular form on the same page, or add the master detail on a separate page.
On Page Attributes, review and edit the master page and detail page information.
On Identify Tabs, specify whether or not to include an optional tab set.
Click Create.
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:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Create an HTML region:
Under Regions, click the Create icon.
Select the region type HTML.
Follow the on-screen instructions.
Start adding items to the page:
Under Items, click the Create icon.
Follow the on-screen instructions.
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:
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:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Processes, click the Create icon.
Select the process Data Manipulation.
Select the process category Automatic Row Processing (DML).
Specify the following process attributes:
In the Name field, enter a name to identify the process.
In the Sequence field, specify a sequence number.
From the Point list, select the appropriate processing point. In most instances, select Onload - After Header.
From the Type list, select Automated Row Processing (DML).
Follow the on-screen instructions.
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;
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.
Oracle Application Express populates a form 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:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Processes, click Create.
Select the process type Data Manipulation.
Select the process category Automatic Row Fetch.
Specify the following process attributes:
In the Name field, enter a name to identify the process.
In the Sequence field, specify a sequence number.
From the Point list, select the appropriate processing point.
From the Type list, select Automated Row Fetch.
Follow the on-screen instructions.
You can also populate a form manually by referencing a hidden session state item. For example, the following code in an Oracle Application Express process of type PL/SQL would set the values of ename and sal. The example also demonstrates how to manually populate a form by referencing a hidden session state item named P2_ID.
FOR C1 in (SELECT ename, sal
FROM emp WHERE ID=:P2_ID)
LOOP
:P2_ENAME := C1.ename;
:P2_SAL := C1.sal;
END LOOP;
In this example:
C1 is an implicit cursor.
The value of P2_ID has already been set.
The process point for this process would be set to execute (or fire) on or before Onload - Before Regions.
You can use validations to check data a user enters before processing. Once you create a validation and the associated error message, you can associate it with a specific item. You can choose to have validation error messages display inline (that is, on the page where the validation is performed) or on a separate error page.
Creating an inline error message involves these steps:
Create a new validation and specify error message text.
Associate the validation with a specific item.
To create a new validation:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Validations, click the Create icon.
When the Create Validations Wizard appears, follow the on-screen instructions.
Validation Types are divided into two categories:
Item. These validations start with the term Item and provide common checks you may want to perform on the item with which the validation is associated.
Code. These validations require you provide either a piece of PL/SQL code or SQL query that defines the validation logic. Use this type of validation to perform custom validations that require verifying values of more than one item or accessing additional database tables.
Follow the on-screen instructions.
Note:
Validations cannot contain more than 3,950 characters.To associate an item with a validation and specify error message text:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Validations, select the validation item you want to associate.
The attributes page for the validation appears.
Scroll down to Error Message:
In Error message display location, verify the display location.
In Associated Item, select the item you want to associate with this validation.
Click Apply Changes.
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.
Application Builder includes a built-in wizard for generating a calendar. Once you specify the table on which the calendar is based, you can create drill-down links to information stored in specific columns.
Topics in this section include:
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.
See Also:
"Calendar Display"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:
Navigate to the appropriate calendar template.
Expand the Substitution Stings list on the right side of the page.
See Also:
"Viewing Templates"How you create a calendar depends upon if you are adding a calendar to an existing page or adding a calendar on a new page. When creating calendars remember:
You can only create one calendar for each page.
The date column determines which days on the calendar will contain entries.
The display column defines a specific row that will display a calendar date.
See Also:
"Editing Calendar Attributes"To add a calendar to an existing page:
Navigate to the Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Regions, click the Create icon.
Select Calendar.
Select the type of calendar you want to create:
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.
Follow the on-screen instructions.
To create a calendar on a new page:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Click Create Page.
Select Calendar.
Select the type of calendar you want to create:
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.
Follow the on-screen instructions.
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:
Navigate to the Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Regions, click CAL next to the region name.
The Calendar Attributes page appears.
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 having accessing the Region Source, you can edit the query to meet your needs.
The title that appears at the top of calendar corresponds to the region title.
To alter the region title:
Navigate to the Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Regions, select the region name.
The Region Definition appears.
Under Region, enter a new title.
Click Apply Changes.
Once you have created a calendar, you can alter how it appears by editing its attributes.
Topics in this section include:
To access the Calendar Attributes page:
Navigate to the appropriate Page Definition:
Navigate to the Database Home Page.
Click the Application Builder icon.
Select an application.
Select a page.
The Page Definition appears.
Under Regions, click CAL next to the region name.
The Calendar Attributes page appears. The topics that follow describe the specific sections of the Calendar Attributes page.
Use Calendar Display to specify a calendar template, date columns, and general calendar formatting.
Calendar Template determines what template is used when the Application Express engine renders a calendar. Date Column defines the column from the table or query containing the dates to be placed on the calendar. Display Column defines a specific row that displays on a calendar date.
To select another Display Column:
Navigate to the appropriate Calendar Attributes page.
Locate the Calendar Display section.
To specify another display column, make a selection from the Display Column list.
Click Apply Changes.
To specify a custom Display Column:
Navigate to the appropriate Calendar Attributes page.
Locate the Calendar Display section.
From Display Type, select Custom.
In Column Format, enter a custom column format. You can use an HTML expression and supported substitution strings.
Click Apply Changes.
Use Calendar Interval to define the dates that are included in the calendar.
Begin At Start Of Interval determines when the calendar should start. Selecting this option creates a calendar that spans an entire interval (such as a month). For example:
If Begin at start of interval is selected, the date is June 15th, and the display is monthly, the resulting calendar spans from June 1st to June 30th.
If Begin at start of interval is not selected, the date is June 15th, and the display is monthly, the resulting calendar spans from June 15th to June 30th.
Date Item holds the date on which the calendar is based.
The next two attributes define which items hold the calendar start date and end date. You can use these attributes to create calendars that span multiple months at a time. Item Containing Start Date points to an item that holds the start date of the calendar. Item Containing End Date points to an item that holds the end date of calendar. Note that format of the date of either item must be YYYYMMDD.
Start of Week determines the day of the week on which the calendar starts.
Use Column link to create a link on the column in the calendar.
To create a column link to another page:
Navigate to the appropriate Calendar Attributes page.
Scroll down to Column Link.
From Target is a, select Page in this Application.
In Page, specify the target page ID. To reset the pagination for this page, select reset pagination for this page.
In Request, specify the request to be used.
In Clear Cache, specify the pages (that is, the page IDs) on which to clear cache. Specify multiple pages by listing the page IDs 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.
To set session state:
Set these items - Enter a comma-delimited list of item names for which you would like to set session state.
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\).
Click Apply Changes.
To create a column link to a URL:
Navigate to the appropriate Calendar Attributes page.
Scroll down to Column Link.
From Target is a, select URL.
In URL, enter the appropriate address.
Click Apply Changes.
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:
Navigate to the appropriate Calendar Attributes page.
Scroll down to Day Link.
From Target is a, select Page in this Application.
In Page, specify the target page ID.
To reset the pagination for this page, select reset pagination for this page.
In Request, specify the request to be used.
In Clear Cache, specify the pages (that is, the page IDs) on which to clear cache. Specify multiple pages by listing the page IDs 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.
To set session state:
Set these items - Enter a comma-delimited list of item names for which you would like to set session state.
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\).
Click Apply Changes.
To create a day link to a URL:
Navigate to the appropriate Calendar Attributes page.
Scroll down to Day Link.
From Target is a, select URL.
In URL, enter the appropriate address.
Click Apply Changes.
Application Builder includes built-in wizards for generating HTML and Scalable Vector Graphics (SVG) charts. Oracle Application Express supports two types of graphical charts:
HTML
SVG
SVG is an XML-based language for Web graphics from the World Wide Web Consortium (W3C). SVG charts are defined using an embed tag. When evaluating whether or not a SVG chart is the appropriate chart type for your application remember that:
Some Web browsers do not support SVG charts.
Most Web browsers that support SVG charts require users download an SVG plug-in.
Topics in this section include:
The Adobe SVG plug-in can handle data encoded in UTF-8, UTF-16, ISO-8859-1, and US-ASCII. Encoding of an SVG chart is determined by the database access descriptor (DAD) database character set. If the DAD character set is not UTF8, AL32UTF8, AL16UTF16, WE8ISO8859P1, or US7ASCII, SVG charts may not render properly in the Adobe SVG plug-in.
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, ename, sal FROM scott.emp WHERE deptno = :P101_DEPTNO
To create a dial chart, select a dial chart type and provide a SQL query using the following syntax:
SELECT value , maximum