Introduction to Oracle Application Express - PowerPoint PPT Presentation

About This Presentation
Title:

Introduction to Oracle Application Express

Description:

It was a really bad name for a very useful product ... works with Internet Explorer, Netscape, ... Applications and app pages may have alphanumeric aliases. ... – PowerPoint PPT presentation

Number of Views:324
Avg rating:3.0/5.0
Slides: 56
Provided by: wilfred9
Learn more at: http://www.nocoug.org
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com