Introduction to Oracle Application Express

1
Introduction to Oracle Application Express
Addendum Reports, Forms, and other Components
2
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.
• 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.

3
Creating A Report Workflow

• Invoke the Create Region feature
• Choose REPORT for the region type
• Select the report type
• Follow the report wizard steps

4
Interactive Reports

• Pre-defined report format with many built-in
  features
• Available with ApEx 3.1 and up
• Built-in capability for
• Searching/filtering
• column selection
• chart creation
• aggregation
• customizing report
• viewing past versions of data
• Conditional formatting

5
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
• REPORT a regular report
• RPT a report created using the Report Wizard
• INTERACTIVE a report built with standard
  interactive features

6
Region/Report Definition Page

• Defines the region in which the report resides
• Set report title, display conditions, position on
  screen
• Can add custom HTML/Javascript to the regions
  HTML header section
• For a regular/interactive reports, contains the
  SQL statement defining the report
• For wizard generated reports, a separate page
  will contain the query definition

7
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
• file type (CSV or XML)
• file name (can be dynamic)

Edit Column Attributes
8
Column Attributes Page

• Specifies the attributes for specific columns in
  the report
• column header
• display attributes
• aggregation/summary settings
• Define column link, display conditions,
  authorization, help text

9
Forms

• Pages that accept and process user input
• 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.

10
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. 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.
11
Form Page Types (contd)

• Form Page Type Description
• Tabular Form 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 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.

12
Form on a Table With Report
13
Tabular Forms

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

14
Master Detail Forms

• reflects a one-to-many relationship between two
  tables in a database.
• 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.

15
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.
  
• 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.
• 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
• you can call procedures and functions within this
  package from an After Submit PL/SQL process to
  process insert, updates, and delete requests.

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

17
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.
• 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.
• There are several methods by which validation can
  be implemented.
• 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).

18
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
• Note Do not change the type of an existing
  chart. Instead, delete the
• chart and then re-create it.

19
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
• some Web browsers do not support SVG charts.
• most Web browsers that support SVG charts require
  that users download an SVG plug-in.

20
Page Items

• An item is part of an HTML form (not a data entry
  form).
• 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 withPnn- where nn
  is the page number
• example P12_DOC_NAME

21
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
22
Item Types
See online documentation for detail description
of all items
23
Item Edit Page

• Allows you to
• 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 items label
• Set the items display condition
• Enter item help message

24
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
25
Create LOV Workflow

• Invoke the Create LOV feature
• Choose LOV creation method scratch/copy
• Choose LOV type static or dynamic
• Define LOV source
• Static enter entries and values
• Dynamic enter SQL

26
Themes and Templates

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

27
Region/Report Template Edit Pages
Region Template Edit Page
Report Template Edit Page
28
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.
• further specify what current looks like using
  template attributes.
• 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.
• You can also create hierarchical lists that
  contain sublists.

29
Breadcrumbs

• Breadcrumbs provide users with hierarchical
  navigation.
• A breadcrumb is a hierarchical list of links that
  display using templates.
• You can display a breadcrumb as a list of links
  or as a breadcrumb path.
• A breadcrumb trail indicates where the user is
  within the application from a hierarchical
  perspective.
• 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.

30
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).
• Unlike most components, trees reside on their own
  page and are referenced from other pages
• A tree is based on a query and returns data that
  can be represented in a hierarchy.
• The hierarchy is determined by the relationship
  between the values of a childcolumn and a
  parent column.

31
Create Tree Workflow
Access Shared Components page
Click on TREES link
Enter name for tree
Select tree and page template/attributes
Enter query