You are previewing Pro Oracle Application Express 4, Second Edition.
O'Reilly logo
Pro Oracle Application Express 4, Second Edition

Book Description

Pro Oracle Application Express 4 is your key to mastering one of the most innovative products to come out Oracle in years. Application Express, termed APEX for short, is fast becoming one of the easiest and most widely-used of tools for creating enterprise-level applications that run against an Oracle database. APEX is easy enough for power users to create ad-hoc applications atop something more reliable than a spreadsheet. Yet APEX is powerful and extensible enough to enable fully-scalable, enterprise-level applications that are accessed by thousands of users.

Authors Tim Fox, John Scott, and Scott Spendolini take you to the professional level in developing for Application Express. They show how to handle user authentication in enterprise environments and how to extend APEX by writing components based upon Oracle's new plug-in architecture. You'll learn to deal with localization issues such as time zones and translations, and to customize the look and feel of an APEX website to blend in with your corporate branding strategy. The authors also cover web service development, performance and scalability, and the production issues that you encounter in enterprise-level deployments. Many years of experience in solving the "hard problems" are coalesced in this book to help you, the reader, take advantage of all that APEX has to offer.

  • Focuses on high-end, enterprise-level development

  • Covers new features such as plug-ins and Websheets

  • Introduces the new interface released with APEX 4.0

What you'll learn

  • Design APEX solutions that conform to your corporate look-and-feel

  • Create compelling reports, charts, navigation, and layout

  • Create your own components based upon APEX's new plug-in architecture

  • Authenticate users and manage their access to your applications

  • Develop and deploy web services using APEX

  • Scale APEX applications to thousands of users

  • Secure your applications and data from intrusion

Who this book is for

Pro Oracle Application Express 4 is aimed at application developers looking for a rapid application development environment harnessing the full power of the Oracle Database, including SQL and PL/SQL. The book meets the needs of developers working at both the departmental and the enterprise level, with an emphasis on high-end, enterprise deployment.

Table of Contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewer
  4. Acknowledgments
  5. Foreword
  6. Introduction
  7. 1. Development Best Practices
    1. 1.1. APEX Installation Decisions
    2. 1.2. Application Development Considerations
      1. 1.2.1. Users and Administrators
      2. 1.2.2. Workspaces and Schemas
        1. 1.2.2.1. Choosing a Parsing Schema
        2. 1.2.2.2. Controlling Access to New Schemas
        3. 1.2.2.3. Creating Workspaces
      3. 1.2.3. Application Deployment
        1. 1.2.3.1. Deploying Workspaces
        2. 1.2.3.2. Deploying Applications
        3. 1.2.3.3. Deploying Static Files
          1. 1.2.3.3.1. Uploading Static Files to the Database
          2. 1.2.3.3.2. Storing Static Files on the Server File System
      4. 1.2.4. Application Portability and Code Reuse
        1. 1.2.4.1. Using Substitution Strings to Avoid Hard-Coding References
        2. 1.2.4.2. Using the Publish/Subscribe Feature
        3. 1.2.4.3. Separating Data and Application Logic from Style and Presentation
        4. 1.2.4.4. Using Page Zero
    3. 1.3. Performance Considerations
      1. 1.3.1. Bind Variables
      2. 1.3.2. Report Pagination Style
      3. 1.3.3. Error and Exception Handling
      4. 1.3.4. Packaged Code
    4. 1.4. Team Development
      1. 1.4.1. Using Team Development
        1. 1.4.1.1. Preparing for Team Development
        2. 1.4.1.2. Creating Features
        3. 1.4.1.3. Recording Bugs and To Dos
        4. 1.4.1.4. Recording a Milestone
        5. 1.4.1.5. Gathering User Feedback
        6. 1.4.1.6. Tracking Development Progress
          1. 1.4.1.6.1. Feature Dashboard
          2. 1.4.1.6.2. Bug Dashboard
          3. 1.4.1.6.3. To Dos Dashboard
    5. 1.5. New Development Features in APEX 4.0
      1. 1.5.1.
        1. 1.5.1.1. APEX Development Advisor
        2. 1.5.1.2. APEX Code Debugger
    6. 1.6. Summary
  8. 2. Authentication and User Management
    1. 2.1. Preconfigured Authentication Schemes
    2. 2.2. Open Door Credentials
    3. 2.3. No Authentication
    4. 2.4. Application Express Account Credentials
      1. 2.4.1. Creating New Application Users
      2. 2.4.2. Creating Groups
      3. 2.4.3. Controlling Authentication with Groups
      4. 2.4.4. Maintaining Cookie Users Within Your Application
    5. 2.5. Database Account Authentication
    6. 2.6. Custom Authentication
      1. 2.6.1. Creating an Application from a Spreadsheet
      2. 2.6.2. Running the Application
      3. 2.6.3. Creating the User Repository
      4. 2.6.4. Creating a New Authentication Scheme
      5. 2.6.5. Regarding Index Usage
      6. 2.6.6. Hash Rather Than Crypt
    7. 2.7. Implementing Locked User Accounts
      1. 2.7.1. Understanding the Login Process
      2. 2.7.2. Modifying the Notification Message
      3. 2.7.3. A Note About Session Management
    8. 2.8. Automating User Registration
      1. 2.8.1. Modifying the User Repository
      2. 2.8.2. Modifying the Authentication Method
      3. 2.8.3. Creating a Registration Form
      4. 2.8.4. Adding a Verification Link Table
      5. 2.8.5. Adding a User Registration Procedure
      6. 2.8.6. Adding the Procedure to Send the Verification
      7. 2.8.7. Handling the Verification Link
    9. 2.9. Managing Session Timeouts
    10. 2.10. Summary
  9. 3. Conditions and Authorization Schemes
    1. 3.1. Specifying Condition Types
      1. 3.1.1. No Condition
      2. 3.1.2. Exists (SQL Query Returns at Least One Row)
      3. 3.1.3. NOT Exists (SQL Query Returns No Rows)
      4. 3.1.4. SQL Expression
      5. 3.1.5. PL/SQL Expression
      6. 3.1.6. PL/SQL Function Body Returning a Boolean
      7. 3.1.7. Request = Expression 1
      8. 3.1.8. Request != Expression 1
      9. 3.1.9. Request Is Contained Within Expression 1
      10. 3.1.10. Value of Item in Expression 1 = Expression 2
      11. 3.1.11. Value of Item in Expression 1 Is NULL
      12. 3.1.12. Current Page = Expression 1
      13. 3.1.13. Current Page Is Contained Within Expression 1 (Comma Delimited List of Pages)
      14. 3.1.14. User Is Authenticated (Not Public)
      15. 3.1.15. User Is the Public User (User Has Not Authenticated)
      16. 3.1.16. Current Language Is Contained Within Expression 1
      17. 3.1.17. Never
    2. 3.2. Using Conditions Appropriately
    3. 3.3. Authorization Schemes
      1. 3.3.1. Creating an Authorization Scheme
      2. 3.3.2. Protecting Your Resources
      3. 3.3.3. To Cache or Not to Cache
      4. 3.3.4. Resetting the Caching
    4. 3.4. Summary
  10. 4. Data Security
    1. 4.1. URLs and Security
      1. 4.1.1. Understanding the URL Syntax
      2. 4.1.2. Manipulating the URL
    2. 4.2. Session State Protection
      1. 4.2.1. Enabling Session State Protection
      2. 4.2.2. Configuring Session State Protection
        1. 4.2.2.1. Page Access Protection
        2. 4.2.2.2. Application Item Protection
        3. 4.2.2.3. Page Data Entry Item Protection
        4. 4.2.2.4. Page Display-Only Item Protection
        5. 4.2.2.5. A Note About Bookmarks
    3. 4.3. Virtual Private Database
      1. 4.3.1. Implementing VPD
        1. 4.3.1.1. Defining the Policy Function
        2. 4.3.1.2. Closing the Loopholes
      2. 4.3.2. Using Contexts with VPD
        1. 4.3.2.1. Creating and Setting Application Contexts
        2. 4.3.2.2. Using Application Contexts in an APEX Environment
      3. 4.3.3. Using Advanced VPD Features
        1. 4.3.3.1. Column-Level VPD Policy
        2. 4.3.3.2. Column Masking
        3. 4.3.3.3. A Note About Policy Function Types
      4. 4.3.4. VPD Best Practices
    4. 4.4. Auditing
      1. 4.4.1. Enabling Auditing
      2. 4.4.2. Viewing Audit Data
    5. 4.5. Summary
  11. 5. Navigation and Layout
    1. 5.1. Tabs
      1. 5.1.1. Understanding Tab States: Current and Noncurrent
      2. 5.1.2. Using Standard Tabs
      3. 5.1.3. Using Parent Tabs
    2. 5.2. Navigation Bars
      1. 5.2.1. Accessing Navigation Bar Entries
      2. 5.2.2. Creating Navigation Bar Entries
      3. 5.2.3. Performing an Action on the Current Page
    3. 5.3. Breadcrumbs
      1. 5.3.1. Accessing Breadcrumb Entries
      2. 5.3.2. Using Dynamic Breadcrumb Entries
      3. 5.3.3. Displaying Breadcrumbs
    4. 5.4. Lists
      1. 5.4.1. Accessing List Entries
      2. 5.4.2. Creating a Menu Using a List
      3. 5.4.3. Tracking Clicks on List Entries
      4. 5.4.4. Using User-Defined Attributes for List Entries
    5. 5.5. Trees
      1. 5.5.1. Creating a Table for the Tree Entries
      2. 5.5.2. Creating the Tree Component
      3. 5.5.3. Enabling and Disabling Tree Entries
    6. 5.6. Page Zero
      1. 5.6.1. Creating Page Zero
      2. 5.6.2. Adding Regions to Page Zero
    7. 5.7. Layout
      1. 5.7.1. Positioning Regions
      2. 5.7.2. Positioning Page Items
      3. 5.7.3. Drag-and-Drop Positioning
    8. 5.8. Summary
  12. 6. Reports and Charts
    1. 6.1. Reports
      1. 6.1.1. Report Headers
        1. 6.1.1.1. Initial Capping
        2. 6.1.1.2. Custom Headings
        3. 6.1.1.3. Custom Headings via a PL/SQL Function
      2. 6.1.2. Named Columns vs. Generic Columns
        1. 6.1.2.1. Generic Columns
        2. 6.1.2.2. Named Columns
      3. 6.1.3. Report Pagination
      4. 6.1.4. Break Formatting
      5. 6.1.5. Column Formatting
        1. 6.1.5.1. Number and Date Formatting
        2. 6.1.5.2. CSS Classes
        3. 6.1.5.3. Highlighted Words
        4. 6.1.5.4. HTML Expressions
      6. 6.1.6. Columns as Links
    2. 6.2. Charts
      1. 6.2.1. Chart Query Types
        1. 6.2.1.1. Dial Chart Syntax
        2. 6.2.1.2. Multiple Series Syntax (Flash Charts Only)
        3. 6.2.1.3. Range Chart Syntax (Flash Charts Only)
        4. 6.2.1.4. Candlestick Chart Syntax (Flash Charts Only)
        5. 6.2.1.5. Gantt Charts
        6. 6.2.1.6. Flash Maps
      2. 6.2.2. HTML Charts
      3. 6.2.3. Flash Charts
        1. 6.2.3.1. Flash Maps
        2. 6.2.3.2. Chart Localization
        3. 6.2.3.3. Asynchronous Updates
        4. 6.2.3.4. Multiple Series
        5. 6.2.3.5. Flash Chart XML Customization
      4. 6.2.4. Generic Charting
        1. 6.2.4.1. Function to Return the SQL
        2. 6.2.4.2. Pipelined Functions
    3. 6.3. Summary
  13. 7. Dynamic Actions
    1. 7.1. Implementing a Dynamic Search
    2. 7.2. Calling On Demand Processes
      1. 7.2.1. Understanding Your Choices
        1. 7.2.1.1. Creating a Dynamic Action
    3. 7.3. Showing and Hiding Page Elements
      1. 7.3.1. Showing and Hiding Fields Using Advanced Dynamic Actions
      2. 7.3.2. Showing and Hiding Items Using Standard Dynamic Actions
      3. 7.3.3. Ajax within APEX
    4. 7.4. Summary
  14. 8. Websheets
    1. 8.1. Preparing for Websheet Development
    2. 8.2. Building a Websheet Application
    3. 8.3. Running a Websheet Application
      1. 8.3.1. Logging In
      2. 8.3.2. Building a Data Grid
      3. 8.3.3. Using the Buglist Websheet
      4. 8.3.4. Contributing Content to a Websheet
    4. 8.4. Summary
  15. 9. Reporting and Printing
    1. 9.1. Choosing a Print Server
    2. 9.2. Configuring APEX to Use a Print Server
    3. 9.3. Printing Reports
      1. 9.3.1. Enabling Printing for a Report
      2. 9.3.2. Troubleshooting Print Problems
      3. 9.3.3. Configuring Some Simple Print Options
        1. 9.3.3.1. Choosing a Report Output Format
        2. 9.3.3.2. Setting Page Attributes
        3. 9.3.3.3. Selecting Columns
        4. 9.3.3.4. Testing Print Settings
    4. 9.4. Creating Custom Report Layouts with BI Publisher
      1. 9.4.1. Installing the Client-Side Layout Tool
      2. 9.4.2. Creating a New Report Layout
        1. 9.4.2.1. Creating a New Query
        2. 9.4.2.2. Downloading the XML Data or Schema
        3. 9.4.2.3. Designing the Report Layout
        4. 9.4.2.4. Testing the Report
        5. 9.4.2.5. Showing the Search Criteria
        6. 9.4.2.6. Highlighting Priority Items
        7. 9.4.2.7. Saving the New Layout for Later Use
      3. 9.4.3. Adding Graphics and Charts
    5. 9.5. Generating Reports Through Apache FOP
      1. 9.5.1. Installing Apache FOP
      2. 9.5.2. Creating a New Layout Using XSL-FO
        1. 9.5.2.1. Understanding the XSL-FO Code
        2. 9.5.2.2. Formatting Report Headings
        3. 9.5.2.3. Highlighting Priority Items
      3. 9.5.3. Adding Graphics to a Report
    6. 9.6. Summary
  16. 10. Themes and Templates
    1. 10.1. Themes
      1. 10.1.1. Associating a Theme with an Application
        1. 10.1.1.1. Copying a Theme
        2. 10.1.1.2. Choosing a Prebuilt Theme
      2. 10.1.2. Viewing Theme Details and Reports
        1. 10.1.2.1. Application Templates Report
        2. 10.1.2.2. Theme Template Counts Report
        3. 10.1.2.3. File References Report
        4. 10.1.2.4. Class References Report
        5. 10.1.2.5. Template Substitution Strings Report
      3. 10.1.3. Performing Theme Tasks
      4. 10.1.4. Defining Theme Attributes
        1. 10.1.4.1. Name
        2. 10.1.4.2. Component Defaults
        3. 10.1.4.3. Region Defaults
        4. 10.1.4.4. Calendar Icon Details
      5. 10.1.5. Switching Themes
    2. 10.2. Templates
      1. 10.2.1. Removing Unused Templates
      2. 10.2.2. Viewing Template Information
        1. 10.2.2.1. Templates Subtab: Template Lists, Previews, and Tasks
          1. 10.2.2.1.1. Template Previews
          2. 10.2.2.1.2. Template Tasks
        2. 10.2.2.2. Utilization Subtab: Viewing Template References
        3. 10.2.2.3. Subscriptions Subtab: Viewing Template Subscriptions
        4. 10.2.2.4. History Subtab: Viewing Historical Changes
      3. 10.2.3. Understanding Template Types and Classes
      4. 10.2.4. Managing Template Files
        1. 10.2.4.1. Images
        2. 10.2.4.2. Cascading Style Sheets
        3. 10.2.4.3. JavaScript Libraries
        4. 10.2.4.4. Image, CSS, and JavaScript Storage
        5. 10.2.4.5. Template Source
      5. 10.2.5. Choosing a Template Type
        1. 10.2.5.1. Breadcrumb Templates
        2. 10.2.5.2. Button Templates
        3. 10.2.5.3. Region Templates
        4. 10.2.5.4. Label Templates
        5. 10.2.5.5. List Templates
        6. 10.2.5.6. Page Templates
          1. 10.2.5.6.1. Page Header
          2. 10.2.5.6.2. Page Body
          3. 10.2.5.6.3. Page Tabs
          4. 10.2.5.6.4. Error Page
        7. 10.2.5.7. Report Templates
          1. 10.2.5.7.1. Generic Column Templates
          2. 10.2.5.7.2. Named Column Templates
          3. 10.2.5.7.3. Pagination Template
        8. 10.2.5.8. Popup List of Values Templates
        9. 10.2.5.9. Calendar Templates
        10. 10.2.5.10. Tree Templates
    3. 10.3. Template Subscriptions
      1. 10.3.1. Setting Up a Theme Subscription System
      2. 10.3.2. Refreshing Subscriptions
    4. 10.4. Tools for Working with Templates
    5. 10.5. Summary
  17. 11. Localization Issues
    1. 11.1. Localizing Application Builder
      1. 11.1.1. Choosing a Language
      2. 11.1.2. Installing a Language File
    2. 11.2. Localizing Your Applications
      1. 11.2.1. A Simple Currency Example
      2. 11.2.2. User-Dependent Localization
      3. 11.2.3. NLS Parameters
    3. 11.3. Fully Translating Your Applications
      1. 11.3.1. Defining the Primary Application Language and Derived From Language
      2. 11.3.2. Creating Translated Versions of an Application
        1. 11.3.2.1. Mapping a Translation
        2. 11.3.2.2. Seeding and Exporting the Translation Text
        3. 11.3.2.3. Translating Text
        4. 11.3.2.4. Applying Your Translation File and Publishing
        5. 11.3.2.5. Testing the Translation
      3. 11.3.3. Translating On the Fly
      4. 11.3.4. Translating the Standard Messages
    4. 11.4. Summary
  18. 12. Plug-ins
    1. 12.1. Creating Your First Plug-in
      1. 12.1.1. Running the APEX Plug-in Builder
      2. 12.1.2. Using Your New Plug-in
      3. 12.1.3. Using Custom Attributes
    2. 12.2. Exporting Plug-ins
    3. 12.3. Importing Plug-ins
    4. 12.4. Summary
  19. 13. Web Services
    1. 13.1. Creating a Native Database Web Service
    2. 13.2. Creating a WSDL-based Web Service Reference in APEX
    3. 13.3. Creating a RESTful Web Service
    4. 13.4. Invoking an External Service
    5. 13.5. Summary
  20. 14. Performance and Scalability
    1. 14.1. Diagnosing Performance Problems
      1. 14.1.1. Viewing Application Reports
        1. 14.1.1.1. Page Performance Reports
        2. 14.1.1.2. Page Views by View Report
        3. 14.1.1.3. Archived Log Timing Information
        4. 14.1.1.4. Automated Statistic Threshold Notification
      2. 14.1.2. Using Debug Mode
      3. 14.1.3. Using SQL Tracing and TKProf
        1. 14.1.3.1. Enabling SQL Tracing
        2. 14.1.3.2. Finding the Trace Information
        3. 14.1.3.3. Processing and Interpreting Trace Information
      4. 14.1.4. Giving Timing Information to the Users
    2. 14.2. Making Your Applications More Scalable
      1. 14.2.1. Image Caching Revisited
      2. 14.2.2. Page and Region Caching
      3. 14.2.3. HTTP Compression
        1. 14.2.3.1. Examining HTTP Headers
        2. 14.2.3.2. Configuring mod_gzip
        3. 14.2.3.3. Configuring mod_deflate
        4. 14.2.3.4. Testing Compression
        5. 14.2.3.5. Benchmarking Compression
    3. 14.3. Summary
  21. 15. Production Issues
    1. 15.1. Managing URLs
      1. 15.1.1. Using a Location Redirect
      2. 15.1.2. Using Frames
      3. 15.1.3. Using Apache mod_rewrite
        1. 15.1.3.1. Adding Rewrite Rules
        2. 15.1.3.2. Using Domains
      4. 15.1.4. Proxying Requests
    2. 15.2. Backing Up Applications
      1. 15.2.1. Manual Exports
      2. 15.2.2. Easy Backups the Database Way
      3. 15.2.3. Automated Backups
      4. 15.2.4. "As of" Backups
    3. 15.3. Migrating Between Environments
      1. 15.3.1. Upgrading Applications
      2. 15.3.2. Cloning an Application
        1. 15.3.2.1. Exporting the Workspace and Application
        2. 15.3.2.2. Cloning Schema Objects and Data
          1. 15.3.2.2.1. Using Scripts to Clone Schemas
          2. 15.3.2.2.2. Using exp/imp to Clone Schemas
          3. 15.3.2.2.3. Using Data Pump to Clone Schemas
        3. 15.3.2.3. Closing the Loop: Setting Up the Workspace
          1. 15.3.2.3.1. Importing with Application Builder
          2. 15.3.2.3.2. Importing with SQL*Plus
    4. 15.4. Summary
  22. 16. APEX Dictionary
    1. 16.1. Accessing the APEX Dictionary
      1. 16.1.1. Using the Application Builder Interface
      2. 16.1.2. Using the apex_dictionary View
    2. 16.2. Uses for the APEX Dictionary
      1. 16.2.1. Quality Assurance
        1. 16.2.1.1. Checking Label Alignment
        2. 16.2.1.2. Checking Maximum Widths
        3. 16.2.1.3. Checking for Fields Without Help
      2. 16.2.2. Self-Documentation
        1. 16.2.2.1. Retrieving Page Comments
        2. 16.2.2.2. Retrieving Page Branches
        3. 16.2.2.3. Retrieving Modification Dates
      3. 16.2.3. Automated Monitoring
    3. 16.3. Using the API
      1. 16.3.1. Adding Items to Your Pages
      2. 16.3.2. Creating Text Fields Programmatically
      3. 16.3.3. Generating Applications
      4. 16.3.4. A Final Warning!
    4. 16.4. Summary