You are previewing Beginning Oracle Application Express 4.
O'Reilly logo
Beginning Oracle Application Express 4

Book Description

Beginning Oracle Application Express 4 introduces one of the most talked about development platforms to come out of Oracle Corporation in years. Oracle Application Express, called APEX for short, enables rapid and easy development of web-based applications that make full use of Oracle Database. The release of APEX 4 brings a huge leap forward in terms of functionality and usability for both the developer and the end user. Power users and programmers alike can quickly put together robust and scalable applications for use by one person, by a department, by an entire company.

Whether you're new to Oracle or an old hand who's yet to test the waters of APEX, Beginning Oracle Application Express 4 introduces the processes and best practices you'll need to become proficient with APEX. The book shows off the programming environment, the utilities and tools available, and then continues by walking through the process of building a working system from the ground up. While the book assumes a foundational knowledge of SQL and PL/SQL, all code used is documented and explained so that those new to the languages will not be lost.

  • Covers brand-new functionality in APEX 4

  • Provides fully documented and explained example code

  • Guides you through creating a working and fully deployable application

What you'll learn

  • Provides fully documented and explained example code

  • Guides you through creating a working and fully deployable application

  • Design, code, and troubleshoot APEX applications

  • Create forms and generate reports

  • Apply best-practices to database and application design

  • Create intuitive navigation that inspires confidence amongst users

  • Secure your applications from outside attack

  • Deploy applications for one, a dozen, or hundreds of users

Who this book is for

Beginning Oracle Application Express 4 is for programmers and power users wanting to create rich, data-centric applications that are web-based, and that rest on Oracle's industry-leading database technology.

Table of Contents

  1. Copyright
  2. About the Authors
  3. Acknowledgments
  4. 1. An Introduction to APEX 4
    1. 1.1. What is APEX?
    2. 1.2. A Brief History of APEX
      1. 1.2.1. Ancient History
      2. 1.2.2. More Recent History
      3. 1.2.3. APEX 4 and the Future
    3. 1.3. What You Need to Get Started
      1. 1.3.1. Access to an APEX Instance
      2. 1.3.2. Web Browser
      3. 1.3.3. SQL Developer
    4. 1.4. Summary
  5. 2. A Developer's Overview
    1. 2.1. The Anatomy of a Workspace
      1. 2.1.1. APEX Users
      2. 2.1.2. Applications, Pages, Regions, and Items
      3. 2.1.3. Workspaces, Applications, and Schemas
      4. 2.1.4. A Final Word on Workspaces
    2. 2.2. A Tour of the APEX Modules
      1. 2.2.1. The Home Page
      2. 2.2.2. Application Builder
        1. 2.2.2.1. The Application Builder Home Page
        2. 2.2.2.2. The Application Home Page
      3. 2.2.3. SQL Workshop
        1. 2.2.3.1. The Object Browser
        2. 2.2.3.2. The SQL Commands Interface
        3. 2.2.3.3. SQL Scripts Interface
        4. 2.2.3.4. The Query Builder
        5. 2.2.3.5. Utilities
      4. 2.2.4. Administration and Team Development
    3. 2.3. Summary
  6. 3. Identifying the Problem & Designing the Solution
    1. 3.1. Identifying System Requirements
      1. 3.1.1. Never a Clean Slate
      2. 3.1.2. A Broken System
      3. 3.1.3. How Do You Fix Things?
        1. 3.1.3.1. Defining the Requirements
        2. 3.1.3.2. Extrapolating to a Database Design
    2. 3.2. System Design with APEX in Mind
      1. 3.2.1. Table Definition and User Interface Defaults
      2. 3.2.2. APEX and Primary Keys
      3. 3.2.3. Business Logic vs. User Interface Logic
      4. 3.2.4. Placement of Database Objects
    3. 3.3. Translating Theory to Practice
    4. 3.4. Summary
  7. 4. SQL Workshop
    1. 4.1. Creating Objects with the Object Browser
    2. 4.2. Loading Data with the Data Workshop Utility
    3. 4.3. Creating a Lookup Table
    4. 4.4. Loading and Running SQL Scripts
    5. 4.5. User Interface Defaults
      1. 4.5.1. Defining UI Defaults for Your Tables
    6. 4.6. Summary
  8. 5. Application and Navigation
    1. 5.1. The Create Application Wizard
      1. 5.1.1. Sample Applications
      2. 5.1.2. Websheet Applications
      3. 5.1.3. Database Applications from Spreadsheets
      4. 5.1.4. Database Applications from Scratch
        1. 5.1.4.1. Naming the Application
        2. 5.1.4.2. Laying Out Pages
        3. 5.1.4.3. Choosing an Approach to Tabs
        4. 5.1.4.4. Copying Shared Components
        5. 5.1.4.5. Selecting an Authentication Method
        6. 5.1.4.6. Selecting a Theme
        7. 5.1.4.7. Completing the Application Creation Wizard
    2. 5.2. Static HTML Regions
    3. 5.3. Public Pages
    4. 5.4. Navigation Bar Entries
    5. 5.5. Page Zero
    6. 5.6. Breadcrumb Region
    7. 5.7. Breadcrumb Entries
    8. 5.8. Lists
    9. 5.9. List Regions
    10. 5.10. Template Positions
    11. 5.11. Lists of Values
      1. 5.11.1. Static List of Values
      2. 5.11.2. Dynamic List of Values
    12. 5.12. Summary
  9. 6. Forms and Reports – The Basics
    1. 6.1. APEX Forms
    2. 6.2. Form on a Table
      1. 6.2.1. Creating a Form on a Table
      2. 6.2.2. Modifying a Form on a Table
      3. 6.2.3. Looking Behind the Scenes
    3. 6.3. Form on a Procedure
      1. 6.3.1. Creating a Form on a Procedure
      2. 6.3.2. Modifying a Form on a Procedure
      3. 6.3.3. Looking Behind the Scenes
    4. 6.4. Master-Detail Report and Form
      1. 6.4.1. Creating a Master-Detail Report and Form
      2. 6.4.2. Modify a Master-Detail Report
    5. 6.5. Session State
      1. 6.5.1. Understanding Session State
      2. 6.5.2. Sharing Database Connections
      3. 6.5.3. Setting and Retrieving Session State
      4. 6.5.4. Viewing Session State
    6. 6.6. APEX Items
      1. 6.6.1. Page versus Application items
      2. 6.6.2. The Importance of Bind Variables
      3. 6.6.3. Built In Items
    7. 6.7. APEX URL Syntax
    8. 6.8. Searchable APEX Reports
      1. 6.8.1. Creating a Searchable APEX Report
      2. 6.8.2. Looking Behind the Scenes – APEX Report
      3. 6.8.3. Looking Behind the Scenes – APEX Master-Detail Forms
    9. 6.9. More on APEX Forms
      1. 6.9.1. Item Layout
      2. 6.9.2. Master-Detail Cleanup
    10. 6.10. APEX Help
      1. 6.10.1. Adding a Help Text Region
      2. 6.10.2. Seeding Help Text
    11. 6.11. Declarative Blobs
    12. 6.12. Summary
  10. 7. Forms and Reports — Advanced
    1. 7.1. Tabular Forms
      1. 7.1.1. Creating a Tabular Form
      2. 7.1.2. Modifying a Tabular Form
      3. 7.1.3. Looking Behind the Scenes
    2. 7.2. Interactive Reports
      1. 7.2.1. Creating an Interactive Report
      2. 7.2.2. Running an Interactive Report
      3. 7.2.3. Restricting Functionality by Report
      4. 7.2.4. Restricting Functionality by Column
      5. 7.2.5. Using the Column Heading Menu
      6. 7.2.6. Searching by Column
      7. 7.2.7. Selecting Columns
      8. 7.2.8. Filtering
      9. 7.2.9. Sorting
      10. 7.2.10. Adding Breaks
      11. 7.2.11. Highlighting
      12. 7.2.12. Computing Columns
      13. 7.2.13. Adding Aggregates
      14. 7.2.14. Adding Charts
      15. 7.2.15. Grouping
      16. 7.2.16. Using Flashback
      17. 7.2.17. Saving an Interactive Report
      18. 7.2.18. Resetting an Interactive Report
      19. 7.2.19. Getting Help
      20. 7.2.20. Adding a Subscription
      21. 7.2.21. Downloading
      22. 7.2.22. Modifying an Interactive Report
      23. 7.2.23. Looking Behind the Scenes
    3. 7.3. Calendars
      1. 7.3.1. Understanding Calendar Types
      2. 7.3.2. Creating a Calendar
      3. 7.3.3. Looking Behind the Scenes
    4. 7.4. Charts
      1. 7.4.1. Writing Queries for Charts
      2. 7.4.2. Creating a Chart
      3. 7.4.3. Looking Behind the Scenes
    5. 7.5. Summary
  11. 8. Programmatic Elements
    1. 8.1. Conditions
    2. 8.2. Validations
      1. 8.2.1. Required Values
      2. 8.2.2. Item Level Validation
      3. 8.2.3. Page Level Validation
      4. 8.2.4. Tabular Form Validation
    3. 8.3. Computations
      1. 8.3.1. Execution
      2. 8.3.2. Types
      3. 8.3.3. Creating a Computation
    4. 8.4. Processes
      1. 8.4.1. Execution Points
      2. 8.4.2. Process Types
      3. 8.4.3. Process in the Help Desk Application
    5. 8.5. PL/SQL Regions
    6. 8.6. Dynamic SQL
    7. 8.7. Dynamic Actions
      1. 8.7.1. Major Parts
      2. 8.7.2. Dynamic Actions in the Help Desk Application
    8. 8.8. Summary
  12. 9. Security
    1. 9.1. User Maintenance Navigation
    2. 9.2. User Maintenance Data Entry
    3. 9.3. Authentication
    4. 9.4. Custom Authentication Schemes
    5. 9.5. Conditional Security
    6. 9.6. Access Control and Authorization
    7. 9.7. Read-Only Items
    8. 9.8. Data Security
    9. 9.9. Session State Protection
    10. 9.10. Summary
  13. 10. Application Deployment
    1. 10.1. Exporting
    2. 10.2. Importing
    3. 10.3. Supporting Objects
    4. 10.4. Summary
  14. 11. Understanding Websheets
    1. 11.1. Websheet Structure
    2. 11.2. Navigation
      1. 11.2.1. Content Navigation
      2. 11.2.2. Structural Navigation
    3. 11.3. Help
    4. 11.4. Markup Syntax
    5. 11.5. User Authentication
    6. 11.6. User Authorization
    7. 11.7. Sections
      1. 11.7.1. Text Sections
      2. 11.7.2. Navigation Sections
      3. 11.7.3. Data Sections
        1. 11.7.3.1. Reports - Setup
        2. 11.7.3.2. Reports - Creation
        3. 11.7.3.3. Reports - Accessing the Data
        4. 11.7.3.4. Data Grids
      4. 11.7.4. Chart Sections
    8. 11.8. Annotations
    9. 11.9. Administration
    10. 11.10. Summary
  15. 12. A Websheet Example
    1. 12.1. Setup
    2. 12.2. Creating and Configuring a Websheet Application
    3. 12.3. Adding Content to a Websheet
      1. 12.3.1. Creating Data Grids
        1. 12.3.1.1. Applying Constraints
      2. 12.3.2. Adding Players
      3. 12.3.3. Creating Alternate Default Reports
      4. 12.3.4. Creating Page Sections
      5. 12.3.5. SQL Tags
    4. 12.4. Access Controls
    5. 12.5. Summary
  16. 13. Extended Developer Tools
    1. 13.1. Page Locks
      1. 13.1.1. APEX Conflicts
      2. 13.1.2. Locking an APEX Page
      3. 13.1.3. Unlocking a Page
      4. 13.1.4. Administering Page Locks
    2. 13.2. Application and Page Groups
      1. 13.2.1. Application Groups
      2. 13.2.2. Page Groups
    3. 13.3. APEX Views and the APEX Dictionary
      1. 13.3.1. The APEX Schema
      2. 13.3.2. APEX Views
      3. 13.3.3. APEX Dictionary
    4. 13.4. Searching within APEX
      1. 13.4.1. APEX Finder
      2. 13.4.2. Search Application
    5. 13.5. Monitoring your APEX Application
      1. 13.5.1. Enabling Logging
      2. 13.5.2. Using the Activity Logs
      3. 13.5.3. Login Attempts
    6. 13.6. APEX Advisor
    7. 13.7. Build Options
      1. 13.7.1. Understanding the Need
      2. 13.7.2. Creating a Build Option
      3. 13.7.3. Configuring Build Options
      4. 13.7.4. Prompting for Build Option Status
      5. 13.7.5. Applying Build Options
      6. 13.7.6. Reporting on Build Option Utilization
    8. 13.8. Page Specific Utilities
    9. 13.9. APEX and Oracle SQL Developer
      1. 13.9.1. Integration
      2. 13.9.2. Refactoring Support
    10. 13.10. Summary
  17. 14. Managing Workspaces
    1. 14.1. Learning About Your Environment
      1. 14.1.1. Viewing Instance Information
      2. 14.1.2. Checking the APEX Version
    2. 14.2. Managing the Service
      1. 14.2.1. Workspace Preferences
      2. 14.2.2. Announcements
    3. 14.3. Managing Meta Data
      1. 14.3.1. Developer Activity and Click Count Logs
        1. 14.3.1.1. Developer Activity Logs
        2. 14.3.1.2. Click Count Logs
      2. 14.3.2. Session State
        1. 14.3.2.1. Manage Session State
        2. 14.3.2.2. Manage Preferences
      3. 14.3.3. Application Cache
      4. 14.3.4. Websheet Database Objects
      5. 14.3.5. Application Build Status
      6. 14.3.6. Application Models
      7. 14.3.7. File Utilization
      8. 14.3.8. Interactive Report Settings
        1. 14.3.8.1. Saved Reports
        2. 14.3.8.2. Subscriptions
    4. 14.4. Managing Users and Groups
      1. 14.4.1. Creating One User
      2. 14.4.2. Creating Multiple Users
      3. 14.4.3. Organizing Users Into Groups
        1. 14.4.3.1. Creating a Group
        2. 14.4.3.2. Assigning Users to a Group
    5. 14.5. Viewing Usage Reports and Dashboards
    6. 14.6. Summary
  18. 15. Team Development
    1. 15.1. Team Development Overview
    2. 15.2. Team Development Interface
      1. 15.2.1. APEX Home Page
      2. 15.2.2. Team Development Home Page
      3. 15.2.3. Common Design Elements
      4. 15.2.4. Drilldown Functionality
      5. 15.2.5. Tagging
    3. 15.3. Features
      1. 15.3.1. Features Tab
      2. 15.3.2. History Tab
      3. 15.3.3. Progress Log Tab
    4. 15.4. Milestones
      1. 15.4.1. Milestones Tab
      2. 15.4.2. Milestone Details Tab
      3. 15.4.3. Features by Milestone Tab
    5. 15.5. To Do Items
    6. 15.6. Bugs
    7. 15.7. Feedback
      1. 15.7.1. Configuring Feedback
      2. 15.7.2. Polishing the Feedback Page
      3. 15.7.3. Viewing Feedback
      4. 15.7.4. Responses to Feedback
      5. 15.7.5. Communication Between Workspaces
    8. 15.8. Team Actions
      1. 15.8.1. Manage Links
      2. 15.8.2. Manage News
      3. 15.8.3. Team Development Settings
      4. 15.8.4. Release Summary
      5. 15.8.5. Utilities
    9. 15.9. User Roles for Team Development
    10. 15.10. Summary