Transcript Introduction to Oracle Application Express Addendum: Reports, Forms, and other Components Report Regions  A report is the formatted result of a SQL query.

Introduction to Oracle Application
Express
Addendum: Reports, Forms,
and other Components
Report Regions

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.
–

2
Reports can be created as part of the application creation process (using
a wizard) or can be added to the application later on in the development
process
Report types:
–
SQL Report - a report based on a custom SQL SELECT statement or a
PL/SQL function returning a SQL SELECT statement that you provide.
–
Interactive Report - an interactive report based on a custom SQL
SELECT statement you provide. End users can customize the layout of
their data by selecting options on the Actions menu.
–
Report Web Service Result - a report based on a web service result.
–
Wizard Report - Creates a report without requiring any SQL knowledge.
Report created based on your specification of schema, table, columns.
11/6/2015
Creating A Report Workflow




3
Invoke the Create
Region feature
Choose REPORT for
the region type
Select the report
type
Follow the report
wizard steps
11/6/2015
Interactive Reports

Pre-defined report
format with many built-in
features

Available with ApEx 3.1
and up

Built-in capability for
–
–
–
–
–
–
–
4
Searching/filtering
column selection
chart creation
aggregation
customizing report
viewing past versions of
data
Conditional formatting
11/6/2015
Editing Reports

Reports and their attributes are
edited from the Page Definition
page

Two type of reports may be listed
in this page, REPORT and RPT,
INTERACTIVE
–
–
–
5
REPORT – a regular report
RPT – a report created using the
Report Wizard
INTERACTIVE – a report built
with standard interactive features
11/6/2015
Region/Report Definition Page

Defines the region in which
the report resides
–
–

For a regular/interactive
reports, contains the SQL
statement defining the report
–
6
Set report title, display
conditions, position on screen
Can add custom
HTML/Javascript to the region’s
HTML header section
For wizard generated reports, a
separate page will contain the
query definition
11/6/2015
Report Attributes Page

Alter column header, display
sequence, column sorting attributes,
aggregation, visibility

Provide access to the edit page for
column attributes

Define report layout and pagination
–

Display position, number of rows
Specify report export/download
settings
–
–
7
Edit Column
Attributes
file type (CSV or XML)
file name (can be dynamic)
11/6/2015
Column Attributes Page

Specifies the attributes for
specific columns in the report
–
–
–

8
column header
display attributes
aggregation/summary settings
Define column link, display
conditions, authorization, help
text
11/6/2015
Forms

Pages that accept and
process user input

You can include a variety of
different types of forms in your
applications.
–
–
9
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.
11/6/2015
Form Page Types
Form Page Type
Form on a Procedure
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.
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.
Master Detail Form
10
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.
11/6/2015
Form Page Types (cont’d)
Form Page Type
Tabular Form
Description
Creates a form in which users can update multiple rows
in a database.
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.
Form and Report on Web Service
11
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.
11/6/2015
Form on a Table With Report
12
11/6/2015
Tabular Forms
13

Enables you 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.

NOTE: Any modification of the
select list of a SQL statement of
a tabular form after it has been
generated is not recommended.
If you do modify the query,
make sure the values of the
updateable columns are not
altered after being queried from
the database by the Application
Express engine.
11/6/2015
Master Detail Forms

reflects a one-to-many
relationship between two
tables in a database.
–

14
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.
11/6/2015
Form Processing

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:
–
create an Automatic Row Processing (DML) process.



–
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.
created a package to handle DML operations

15
you are not required to provide any SQL coding.
Application Express performs DML processing for you.
this process automatically performs lost update detection. Lost update detection
ensures data integrity in applications where data can be accessed concurrently.
you can call procedures and functions within this package from an After Submit
PL/SQL process to process insert, updates, and delete requests.
11/6/2015
Populating Forms

Oracle Application Express populates a
form either “on load” or when the
Application Express engine renders the
page.

To populate a form:
–
–
Create a Data Manipulation process and
define the type as Automated Row Fetch
Create a PL/SQL process to populate the
form manually by referencing a hidden
session state item.


16
PLSQL code can set page items values
based on results of some operational logic
(query, calculation, etc)
The process point for this process would be
“Onload – Before Regions”
11/6/2015
Validations in Forms

Used to check data before sending it to
be processed.

Once you create a validation and the
associated error message, you can
associate it with a specific item.
–

There are several methods by which
validation can be implemented.
–
–
17
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.
Using PL/SQL, SQL – involves coding
Using pre-defined test conditions
 comparing the current value of a
page item to another item or to a
constant
 checking for the presence of a
value (or not).
11/6/2015
Charts

Application Builder includes built-in wizards for generating HTML,
Scalable Vector Graphics (SVG), and Flash charts.
–

Oracle Application Express supports only those three types of graphical
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.
–
Example:
SELECT null, last_name, salary
FROM employees
WHERE DEPARTMENT_ID = :P101_DEPARTMENT_ID

18
Note: Do not change the type of an existing chart. Instead, delete the
chart and then re-create it.
11/6/2015
About Chart Types

About Flash Charts
–
–
–

support based on the Anychart Flash Chart Component
are rendered by a browser and require Flash player 8 or later.
More info available at: http://www.anychart.com
About SVG Plug-in Support
–
–
an XML-based language for Web graphics from the World Wide Web
Consortium (W3C).
When evaluating whether or not an SVG chart is the appropriate chart
type for your application, remember that:


19
some Web browsers do not support SVG charts.
most Web browsers that support SVG charts require that users download an
SVG plug-in.
11/6/2015
Page Items

An item is part of an HTML form (not a data entry form).
–
–
–

20
An item can be a text field, text area, password, select list, check box, etc.
Item attributes affect the display of items on a page.
You can use HTML/JavaScript techniques to customize item
appearance/behavior
There are two types of items: page items and application items.
–
Page items are placed on a page and have associated user interface
properties, such as Display As, Label and Label Template.
–
Application items are not associated with a page and therefore have no user
interface properties. They can be treated as global variables.

You can associate validations and/or do computations with items

Items are created/edited in the Items section of the Page Definition page

Tip: prefix page item names with”Pnn-” where nn is the page number
example: P12_DOC_NAME
11/6/2015
Create/Edit Item Workflow
Open application
Open page to be edited
Click on Create Icon in
Items Section
Create Item
Select item type
Enter item-type specific
information/attributes
21
11/6/2015
Item Types
See online documentation for detail description of all items
22
11/6/2015
Item Edit Page

Allows you to:
–
–
–
–
–
–
–
–
23
Name the item,
Select the item type
Specify the region in which the item
resides
Set item attributes
Specify item source/default value/LOV
Specify the item’s label
Set the item’s display condition
Enter item help message
11/6/2015
List of Values


A list of values (LOV) is a static or dynamic set of values used to display
a specific type of page item, such as popup lists of values, a select list, a
check box, a radio group, or multiple select lists.
Creating a LOV as a shared component has a number of advantages:
–
–


It can be added to any page within an application.
All LOV definitions are stored in one location, making them easy to locate and
update.
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.
SELECT ename display, empno
returned
FROM emp
WHERE deptno = :P1_DEPTNO
ORDER BY ename
24
11/6/2015
Create LOV Workflow




Invoke the Create
LOV feature
Choose LOV
creation method –
scratch/copy
Choose LOV type –
static or dynamic
Define LOV source
–
–
25
Static: enter entries
and values
Dynamic: enter
SQL
11/6/2015
Themes and Templates


26
Application Express creates the user interface of an
application based on a named collection of templates
called a theme.
Collections of templates that can be used to define the
layout and style of an entire application.
– templates are organized first by type (breadcrumb,
button, calendar, label, list, page, popup list of
values, region, and report) and then by template
classes, identifying the purpose of the each
template within that type.
11/6/2015
Region/Report Template Edit Pages
Region Template Edit Page
27
Report Template Edit Page
11/6/2015
Lists

Lists are a shared collection of links.

Add a list to a page by creating a region and specifying the region
type as List.
–
–
–
control the appearance of a list through list templates.
each list element has a display condition, which enables you to
control when it displays.
you can define a list element to be either current or non-current for a
specific page.


Once created, you need to add entries to a list. You can add list
entries from scratch, copy one list entry within a list, or copy
existing entries from one list to another.
–
28
further specify what current looks like using template attributes.
You can also create hierarchical lists that contain sublists.
11/6/2015
Breadcrumbs


Breadcrumbs provide users with hierarchical
navigation.
A breadcrumb is a hierarchical list of links that
display using templates.
–

A breadcrumb trail indicates where the user is
within the application from a hierarchical
perspective.
–
–

29
You can display a breadcrumb as a list of links
or as a breadcrumb path.
users can click a specific breadcrumb link to
instantly view the page.
breadcrumbs act as a second level of
navigation at the top of each page,
complementing other user interface elements
such as tabs and lists.
You can create breadcrumbs while creating a
page, or manually by running the Create
Breadcrumb Wizard.
11/6/2015
Trees


Tree are used to effectively communicate hierarchical
or multiple level data.
Trees are created using the Create Tree wizard (from
the Shared Components page).
–

A tree is based on a query and returns data that can be
represented in a hierarchy.
–
30
Unlike most components, trees reside on their own page and
are referenced from other pages
The hierarchy is determined by the relationship between the
values of a “child”column and a ”parent” column.
11/6/2015
Create Tree Workflow
Access Shared
Components page
Click on TREES link
Enter name for tree
Select tree and page
template/attributes
Enter query
31
11/6/2015