Transcript Oracle: HTMLDB - Northern California Oracle Users Group

Introduction to Oracle Application
Express
What is Application Express?

Formerly called HTML-DB, Project Marvel, Einstein
–

It was a really bad name for a very useful product
A tool for developing and deploying database-centric,
web-based applications
–
–
–
works with Internet Explorer, Netscape, FireFox, Opera, etc.
bundled with Oracle 10G+ (works with Oracle 9.0.2.3+)
provides a wizard driven development environment

requires minimal knowledge of database & web development
–
has many pre-configured application templates
– Reports, charts, data entry forms
– Drill down, hypertext linking, export to spreadsheet

2
uses customizable style sheets for uniform UI look & feel
7/20/2015
Architecture: Data Driven

3
Applications are data driven
– All application pages are
rendered in real time from
metadata stored in the
database
– When you create or extend
your application, no code is
generated; instead metadata
stored in the database is
created or modified
– The page-rendering engine
reads the metadata, generates
the HTML for the page, and
send it to the browser.
Oracle HTTP
Server
HTML DB
Engine
Application Metadata
Browser
Oracle
Database
Oracle Database
7/20/2015
Application Security

Apps use authentication and authorization schemes
–
authentication: establishes the user’s identity before allowing access
to the application


–
authorization: controls access to an application, a page or specific
page components (region, item, button, etc.)



authorization either succeeds or fails
component authorization failures do not generate error messages
Application Express’ built-in security prevents
–
–
–
4
Username/password, secure key, digital certificates
Several preconfigured authentication schemes are available (single-sign
on, LDAP, DAD credentials, ApEx accounts)
intentional attempts by a user to access session state belonging to
someone else
inadvertent access to stale session state (belonging to the same user
from an earlier time/session; mainly due to book-marking a page)
linking to an ApEx page out of context (for example, from a hyperlink
in an e-mail - “deep linking”)
7/20/2015
The Application Express URL

The application URL identifies the location of the
Oracle Application Express engine, the unique
application ID, the page number, and the session ID.
–
–
http:server:port/DAD/f?p=app_id:page_num:session_id
Applications and app pages may have alphanumeric aliases.

Application aliases are unique within a workspace and page
aliases are unique within each application.
When you run an application, the Application Express engine
generates a session ID that serves as a key to your session.
Example:
–

–
5
http://apex.oracle.com/pls/apex/f?p=4350:1:220883407765693447
7/20/2015
Developing in Application Express

Start up browser and enter URL
–

Workspace: an area with the
Application Express environment
where applications are created
–
–
–
–
6
You’ll need a developer account
which includes a workspace
Are associated with one or more
schemas
Can have one or more developers
Can hold one or more applications
Has an administrative account
7/20/2015
Application Express Components

7
Application Express’
development environment has
three main components
– Application Builder: used
to build database-centric
interactive Web applications
– SQL Workshop: used to run
SQL statements and SQL
scripts, browse the database
– Utilities: used to administer
the application environment,
export/import data, view
system metrics/logs
7/20/2015
Application Builder
8

Used to build an HTML
interface (or application) on
top of database objects such
as tables and procedures

Application: a collection of
database-driven web pages
linked together using tabs,
buttons, or hypertext links.
7/20/2015
SQL Workshop

Used to view and manage database
objects from a web browser
–
–
–
–
–

Used to import data into and export
data from the database
–
–
9
Execute SQL commands
Create or modify database objects
Up/Download and run SQL scripts
“Query by example” Interface
Browse the data dictionary
Supported file formats include text
(comma or tab delimited), XML
documents and spreadsheets (import
only)
Users can create and load a table
directly from a spreadsheet without any
SQL knowledge
7/20/2015
Utilities

Used to administer the
application environment
–
–
–
–
–
10
Data loading/Unloading
Generate DDL
Monitor application activity
Monitor database
performance
Perform schema
comparisons
7/20/2015
Application Builder Concepts




11
Application Builder is used to
assemble an HTML interface (or
application) on top of database
objects such as tables and
procedures.
Each application is a collection of
pages linked together using tabs,
buttons, or hypertext links.
A page is the basic building block of
an application. Each page contains
user interface elements, such as
tabs, lists, buttons, items within
containers called regions.
There are many different types of
regions including report, form,
chart, tree, HTML, etc.
7/20/2015
Generic Application Creation Workflow
Create Application
Create/Add Page
Create/Add Region
Create/Add Items
Create
Branches
Create
Processes
Create
Validations
Create
Computations
Create
Page/Region/Item/Branch/Process/Validation/Computation
12
7/20/2015
Create Application Workflow
Select Create Application
from Application Builder
Select ‘Create Application’
Give application a name
Add at least one page
Choose a ‘look and feel’
(theme)
13
Click CREATE button on
Confirm screen
7/20/2015
Pages

14
A page is the basic building block of an
application.
– Each page can have buttons and fields
(called items), which are grouped into
containers called regions.
–
Pages can also have application logic
(or processes).
–
You can branch from one page to the
next using conditional navigation;
perform calculations (called
computations); perform validations
(such as edit checks); and display
reports, calendars, and charts.
–
You view, create, and edit the controls
that define a page by accessing the
Page Definition page
Items (some call processes)
Regions
7/20/2015
Create/Add Page(s) Workflow
Open application for editing
Click Create Page button
Select page/region type
Name the page
Set page tab attributes
Enter page-type specific
information/attributes
Confirm page creation
15
7/20/2015
Regions



Pages consist of regions
Regions are containers which hold application
components (reports, buttons, charts, etc.)
There are many different types of regions
–

Regions can be created during the app creation
process or added later as needed
–

16
Region attributes vary by region type
Regions can be added by editing an existing page
The display of a region and/or its components can be
conditional
7/20/2015
Create/Add Region(s) Workflow
Open application
Open page to be edited
Click on Create Icon in
Regions Section
Create Icon
Select region type
Enter region-type
specific
information/attributes
Specify condition(s) for display
17
7/20/2015
Application Items/Components

Items are HTML components that appear in a region on a page.

There are two types of 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.

18
Examples include a check box, date picker, display as text, file browse
field, popup list of values, select list, or a text area.
Page items are associated with a region on the page
You can use application items as global variable.

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_NAME)
7/20/2015
Create/Add Item(s) 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
Specify condition(s) for display
19
7/20/2015
Application Express Information

http:www.oracle.com/technology/products/database/ap
plication_express/index.html
–

Pro Oracle Application Express
–
–
20
Tutorials, viewlets, forum, documentation, downloads
By Scott John, Spendolini Scott, Scott J.
Available in Sept, 2008
7/20/2015
Build Demo Application
21
7/20/2015
Introduction To Application
Express
Application Builder
Concepts
Application Builder Concepts



23
Application Builder is used to
assemble an HTML interface (or
application) on top of database
objects such as tables and
procedures. Each application is a
collection of pages linked together
using tabs, buttons, or hypertext
links.
A page is the basic building block of
an application. When you build an
application in Application Builder,
you create pages that contain user
interface elements, such as tabs,
lists, buttons, items, and regions.
There are many different types of
pages/regions including report,
form, chart, tree, HTML, etc.
7/20/2015
Application Builder Concepts

When you create an application
in Application Builder, you link
pages together using tabs,
buttons, or hypertext links.
–
–
–
24
Hyperlink
Button
Tab
Each page can have buttons
and items and can include
application logic.
You can branch from one page
to the next using conditional
navigation, perform calculations
and validations, and display
reports, calendars, and charts.
You can generate reports,
charts, and forms using built-in
wizards, static HTML, or deliver
more custom rendering with
PL/SQL programming.
7/20/2015
Rendering and Processing Pages



25
The Application Express engine dynamically renders and
processes pages based on data stored in Oracle database tables.
To view a rendered version of your application, you request it from
the Application Express engine.
When you run an application, the Application Express engine
relies on two processes:
– Show Page is the page rendering process. It assembles all
the page attributes (including regions, items, and buttons) into
a viewable HTML page.
– Accept Page performs page processing. It performs any
computations, validations, processes, and branching.
7/20/2015
Page Definition

26
A Page Definition is the basic building block
of a page.
– Each page can have buttons and fields
(called items), which are grouped into
containers called regions.
–
Pages can also have application logic
(or processes).
–
You can branch from one page to the
next using conditional navigation;
perform calculations (called
computations); perform validations
(such as edit checks); and display
reports, calendars, and charts.
–
You view, create, and edit the controls
that define a page by accessing the
Page Definition in the Page Definition
page
Items (some call processes)
Regions
7/20/2015
Page Definition Page

27
The Page Definition page is divided into
three sections:
– Page Rendering: the process of
generating a page from the database.
The Page Rendering section lists user
interface controls and logic that execute
when a page is rendered.
–
Page Processing: occurs once a page
is submitted. Typically a page is
submitted when a user clicks a button.
The Page Processing section lists logic
controls (such as computations and
processes) that are evaluated and
executed when the page is processed.
–
Shared Components: lists common
components that can be used by one or
more pages within an application.
7/20/2015
Generic Application Creation Workflow
Create Application
Create/Add Page
Create/Add Region
Create/Add Items
Create
Branches
Create
Processes
Create
Validations
Create
Computations
Create
Page/Region/Item/Branch/Process/Validation/Computation
28
7/20/2015
Application Creation Options

Create Application
–
–
–

Based on Spreadsheet:
–

29
From Scratch: Specify tabs, select
a user interface theme, and many
other options.
Based on an Existing Application:
Create a copy of another application
using the same user interface
theme, but minus the pages.
Based on an Existing Table:
Create an application based on
existing tables. Includes a report
page for each table, a form page to
insert and update data, and a page
containing a graphical chart.
Create an easily deployable
application from a spreadsheet (or
text file).
Build demo application
7/20/2015
Create Application Workflow
Select Create Application
from Application Builder
Select ‘Create Application’
Give application a name
Add at least one page
Choose a ‘look and feel’
(theme)
30
Click CREATE button on
Confirm screen
7/20/2015
Themes and Templates

Templates:
–
–
–

Themes:
–
–

31
HTML and CSS code that defines the layout and
format of application components (reports, charts,
buttons, links, tabs, menus, etc.)
control the look and feel of the components in your
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.
collections of templates that can be used to define the
layout and style of an entire application.
the idea behind a theme is to provide a complete set
of templates that accommodate every UI pattern that
may be needed in an application.
Knowledgeable developers can define/customize
their own themes and templates. This process
requires knowledge of HTML, Cascading Style
Sheets, and web design.
7/20/2015
Creating a Page

A page is the basic building block of
an application. When you build an
application in Application Builder, you
Create Icon
create pages that contain user
interface elements, such as tabs, lists,
buttons, items, and regions.

You add a new page or add a
component to an existing page by
running the Create Page Wizard. You
can access this wizard by:
–
–
–
32
Clicking Create Page on the
Application home page.
Clicking Create on the Page Definition.
Click the Create link on the Developer
toolbar and then select New Page.
7/20/2015
Create Initial Page (with app) Workflow
• Applications are made up of pages
• Pages can be created during app creation or added later
33
7/20/2015
Adding Page(s) Workflow
Open application for editing
Click Create Page button
Select page/region type
Name the page
Set page tab attributes
Enter page-type specific
information/attributes
Confirm page creation
34
7/20/2015
Page/Region Types

Wizard-driven Page Creation
–
Blank Page(s): Creates one or more blank pages.
–
Report: Creates a report based on a custom SQL
SELECT statement or a PL/SQL function returning a
SQL SELECT statement
Chart: create three types of graphical charts: HTML,
Scalable Vector Graphics (SVG), and Flash
Form: Creates a form interface with which users can
update a single row or multiple rows within a table
–
–
–
–
–
–
–
–
35
Wizard: Create a wizard.
Calendar: Generates a calendar with monthly,
weekly, and daily views.
Tree: Creates a a tree to graphically communicate
hierarchical or multiple level data.
Login Page: Creates a login page
Access Control: Creates a page containing a
access control list, enabling developers to control
access to an application, individual pages, or page
components
Page Zero: A page zero that functions as a master
page. The Application Express engine renders all
components you add to page zero on every page
within your application.
7/20/2015
Regions



Pages consist of regions
Regions are containers which hold application
components (reports, buttons, charts, etc.)
There are many different types of regions
–

Regions can be created during the app creation
process or added later as needed
–

36
Region attributes vary by region type
Regions can be added by editing an existing page
The display of a region and/or its components can be
conditional
7/20/2015
Adding Region(s) Workflow
Open application
Open page to be edited
Click on Create Icon in
Regions Section
Create Icon
Select region type
Enter region-type
specific
information/attributes
Specify condition(s) for display
37
7/20/2015
Application Items/Components

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.

38
Examples of page-level items include a check box, date picker, display as
text, file browse field, popup list of values, select list, or a text area.
Page items are associated with a region on the page
You can use an application item as a global variable.

Tip: prefix page item names with”Pnn-” where nn is the page
number (example: P12_NAME)

Items are created/edited in the Items section of the Page
Definition page
7/20/2015
Adding Item(s) 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
Specify condition(s) for display
39
7/20/2015
Branches


A branch is an instruction to go to a specific page,
procedure, or URL. For example, you can branch from
page 1 to page 2 after page 1 is submitted.
You create a new branch by running the Create Page
Branch Wizard and specifying Branch Point and
Branch Type. The Branch Type defines the type of
branch you are creating.
–
–
–
40
The page number of the page to which you want to branch
PL/SQL procedure which ultimately renders a branch target
page
A URL address
7/20/2015
Branch Points

You can control when a branch executes by making a
selection from the Branch Point list.

Branch Point Options:
–
–
41
On Submit: Before Computation - Branching occurs before
computations, validations, or processing. Use this option for buttons
that do not need to invoke any processing (for example, a Cancel
button).
On Submit: Before Validation - Branching occurs after
computations, but before validations or processing. If a validation
fails, page processing stops, a rollback is issued, and the page
displays the error. Because of this default behavior, you do not need
to create branches to accommodate validations. However, you may
want to branch based on the result of a computation (for example, to
a previous branch point).
7/20/2015
Branch Points (cont’d)
42
–
On Submit: Before Processing - Branching occurs after
computations and validations, but before processing. Use this
option to branch based on a validated session state, but before
performing any page processing.
–
On Submit: After Processing - Branching occurs after
computations, validations, and processing. This option
branches to a URL or page after performing computations,
validations, and processing. When using this option, remember
to sequence your branches if you have multiple branches for a
given branch point.
–
On Load: Before Header - Branching occurs before a page is
rendered. This option displays another page instead of the
current page or redirects the user to another URL or
procedure.
7/20/2015
Adding Branch(es) Workflow
Open application
Open page to be edited
Click on Create Icon in
Branches Section
Create Branch
Select branch point
Select branch type
Enter branch-type
specific
information/attributes
43
Specify condition(s) for display
7/20/2015
Processes
44

A page process is a unit of logic that performs an action at a
specified point (i.e., when a specific event occurs) during the
rendering or submission of the page.

From a functional perspective, there is no difference between
page-level and application-level processes. The difference
between these two process types is where the process is defined,
that is at the page level or at the application level.

You create a process by running the Create Process Wizard.
During the wizard, you define a process name, specify a
sequence and the point at which the process will execute, and
select a process category.
7/20/2015
Process Types

Data Manipulation
–
Data Manipulation process types are frequently used by wizards to
support data manipulation language (DML) actions. Application
Builder supports the following declarative data manipulation
processes:




Close Popup Window
–
45
Select Automatic Row Fetch and Automatic Row Processing (DML)
to create an automatic data manipulation language (DML) process.
Use Multi Row Update and Multi Row Delete in conjunction with
tabular forms.
Use Add Rows to Tabular Form in conjunction with a tabular form.
Applies to processes running within a popup window. Upon
execution, this process type closes the current popup window.
7/20/2015
Process Types (cont’d)

Form Pagination
–

On Demand
–

Creates an application-level process that can only be executed when
called from a specific page. When you create this process type at the
page-level, you are creating a reference to an existing applicationlevel process.
PL/SQL
–
46
Implements pagination through the detail records associated with a
master detail form. Most often used in master detail forms (such as in
the Master Detail Wizard), this process checks the master table to
determine which set of detail records you are in and determines what
the next detail record should be
Runs the PL/SQL you provide. Use this process type to execute a
block of PL/SQL entered directly into the process or to simply call an
existing API or a stored procedure.
7/20/2015
Process Types (cont’d)

Reset Pagination
–

Session State
–

Sets the values of existing session state items to null. Select this
process type to clear the cache for applications, sessions, or items as
well as to clear existing user
Web Services
–
47
In Report regions, resets pagination back to the first result set. The
Application Express engine keeps track of where the user is within a
given result set. This process category returns the user to the
beginning result set. In other words, this category resets the counters
associated with the report region to return the first part of the result
set the next time the result set displays.
Implements a Web Service as a process on a page. Running the
process submits a request to the service provider.
7/20/2015
Process Points


You control when a process executes by
specifying a sequence number and a process
point under Process Point.
You can prevent a process from running during
subsequent visits to a page by selecting one of
the following options under Run Process:
–
–
48
Once for each page visit
Once for each session or when reset
7/20/2015
Adding Process(es) Workflow
Open application
Open page to be edited
Click on Create Icon in
Processes Section
Create Process
Select process type
Select process point
Enter process-type
specific
information/attributes
Specify condition(s) for display
49
7/20/2015
Validations




50
Validations are used to verify the correctness
of the data before page processing
You can define a validation declaratively by
selecting a validation method.
If a validation fails, subsequent page
processes or computations will not occur.
The validation you enter must be consistent
with the validation type you selected.
7/20/2015
Validation Methods

SQL
–

51
Compares item values to data in the database. For example, you can use a SQL
validation to verify whether a last name typed into a field exists in the database. In the
following Exists SQL validation, the field is named P1_LAST_NAME and the table is
named customers.
SELECT 1 FROM customers WHERE last_name = :P1_LAST_NAME
PL/SQL
–
Useful if you need complex logic to validate entered data. For example, suppose you need
to create a validation for an address form that requires the user to enter a province if the
address is not in the United States. You could create the validation as a Function
Returning Boolean, using the following PL/SQL:
BEGIN
IF :P1_COUNTRY = 'US' AND :P1_PROVINCE IS NULL THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF; END;
–
You could also create the same validation implemented as a PL/SQL Expression as
follows:
NOT (:P1_COUNTRY='US' AND :P1_PROVINCE IS NULL);
7/20/2015
Validations (cont’d)

Item Level Null
–

Item String Comparison
–
–

Compares the value of an item to a specific string.
There are several string comparison validations that compare the value of an item to a
literal string. For example, you select the validation type Item in Expression 1 is
contained in Expression 2 to validate a user entry in a field against a list of values you
provide. In Expression 1, enter the name of the item you want to validate without a colon,
(e.g., :P1_VALUE). In Expression 2, enter a string of values you want to validate against.
Regular Expression
–
–
52
Checks if an item's value in session state is null. For example, you could validate that the
user enters a value in a field by creating an item validation and then selecting the
validation method Item Not Null.
Regular expressions provide a method to describe text patterns.
Use a Regular Expression validation to perform data validation. For example, you could
use the following regular expression validation to verify that a string of entered data
always consists of groups of six numbers separated by commas and followed by a
comma:
^([[:digit:]]{6},)+$
7/20/2015
Adding Validations Workflow
Open application
Open page to be edited
Click on Create Icon in
Validations Section
Create Validation
Select validation level
Select validation method
Enter validation method
specific
information/attributes
Specify condition(s) for display
53
7/20/2015
Computations


Computations are used to assign a value to an
identified item when a page is submitted or displayed.
Computations can be defined at application level or at
page level.
–
–
–

54
Most application-level computations are performed for every
page in an application
Page-level computations only execute when that page is
rendered or processed
Similar to branches and processes, you can define a point at,
(computation point), which the computation will be performed.
This includes during page rendering or during page processing
If a computation fails, you can optionally define an
error message.
7/20/2015
Adding Computations Workflow
Open application
Open page to be edited
Click on Create Icon in
Computations Section
Create Computation
Select computation level
Select computation item
Enter computation
Specify condition(s) for display
55
7/20/2015