Introduction to Oracle Application Express

1
Introduction to Oracle Application Express
2
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
• uses customizable style sheets for uniform UI
  look feel

3
Architecture Data Driven

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

4
Application Security

• Apps use authentication and authorization schemes
• authentication establishes the users identity
  before allowing access to the application
• Username/password, secure key, digital
  certificates
• Several preconfigured authentication schemes are
  available (single-sign on, LDAP, DAD credentials,
  ApEx accounts)
• 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
• 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)

5
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.
• httpserverport/DAD/f?papp_idpage_numsession_i
  d
• 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
• http//apex.oracle.com/pls/apex/f?p435012208834
  07765693447

6
Developing in Application Express

• Start up browser and enter URL
• Youll need a developer account which includes a
  workspace
• Workspace an area with the Application Express
  environment where applications are created
• Are associated with one or more schemas
• Can have one or more developers
• Can hold one or more applications
• Has an administrative account

7
Application Express Components

• 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

8
Application Builder

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

9
SQL Workshop

• Used to view and manage database objects from a
  web browser
• Execute SQL commands
• Create or modify database objects
• Up/Download and run SQL scripts
• Query by example Interface
• Browse the data dictionary
• Used to import data into and export data from the
  database
• 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

10
Utilities

• Used to administer the application environment
• Data loading/Unloading
• Generate DDL
• Monitor application activity
• Monitor database performance
• Perform schema comparisons

11
Application Builder Concepts

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

12
Generic Application Creation Workflow
Create Application
Create/Add Page
Create/Add Region
Create/Add Items
Create Processes
Create Computations
Create Validations
Create Branches
Create Page/Region/Item/Branch/Process/Validation/
Computation
13
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)
Click CREATE button on Confirm screen
14
Pages

• 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

Regions
Items (some call processes)
15
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
16
Regions

• Pages consist of regions
• Regions are containers which hold application
  components (reports, buttons, charts, etc.)
• There are many different types of regions
• Region attributes vary by region type
• Regions can be created during the app creation
  process or added later as needed
• Regions can be added by editing an existing page
• The display of a region and/or its components can
  be conditional

17
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
18
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.
• 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
• Application items are not associated with a page
  and therefore have no user interface properties.
• 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 withPnn- where nn
  is the page number (example P12_NAME)

19
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
20
Application Express Information

• httpwww.oracle.com/technology/products/database/a
  pplication_express/index.html
• Tutorials, viewlets, forum, documentation,
  downloads
• Pro Oracle Application Express
• By Scott John, Spendolini Scott, Scott J.
• Available in Sept, 2008

21
Build Demo Application
22
Introduction To Application Express

• Application Builder Concepts

23
Application Builder Concepts

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

24
Application Builder Concepts

• When you create an application in Application
  Builder, you link pages together using tabs,
  buttons, or hypertext links.
• 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.

Hyperlink Button Tab
25
Rendering and Processing Pages

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

26
Page Definition

• 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

Regions
Items (some call processes)
27
Page Definition Page

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

28
Generic Application Creation Workflow
Create Application
Create/Add Page
Create/Add Region
Create/Add Items
Create Processes
Create Computations
Create Validations
Create Branches
Create Page/Region/Item/Branch/Process/Validation/
Computation
29
Application Creation Options

• Create Application
• 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.
• Based on Spreadsheet
• Create an easily deployable application from a
  spreadsheet (or text file).
• Build demo application

30
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)
Click CREATE button on Confirm screen
31
Themes and Templates

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

32
Creating a Page

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

Create Icon
33
Create Initial Page (with app) Workflow

• Applications are made up of pages
• Pages can be created during app creation or
  added later

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

36
Regions

• Pages consist of regions
• Regions are containers which hold application
  components (reports, buttons, charts, etc.)
• There are many different types of regions
• Region attributes vary by region type
• Regions can be created during the app creation
  process or added later as needed
• Regions can be added by editing an existing page
• The display of a region and/or its components can
  be conditional

37
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
38
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.
• 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
• Application items are not associated with a page
  and therefore have no user interface properties.
• You can use an application item as a global
  variable.
• Tip prefix page item names withPnn- where nn
  is the page number (example P12_NAME)
• Items are created/edited in the Items section of
  the Page Definition page

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

41
Branch Points

• You can control when a branch executes by making
  a selection from the Branch Point list.
• Branch Point Options
• 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).

42
Branch Points (contd)

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

43
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
Specify condition(s) for display
44
Processes

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

45
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
• 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.
• Close Popup Window
• Applies to processes running within a popup
  window. Upon execution, this process type closes
  the current popup window.

46
Process Types (contd)

• Form Pagination
• 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
• 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 application-level process.
• PL/SQL
• 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.

47
Process Types (contd)

• Reset Pagination
• 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.
• 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
• Implements a Web Service as a process on a page.
  Running the process submits a request to the
  service provider.

48
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
• Once for each page visit
• Once for each session or when reset

49
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
50
Validations

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

51
Validation Methods

• SQL
• 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)

52
Validations (contd)

• Item Level Null
• 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.
• 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
• 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
• (digit6,)

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

55
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