Contents

Title and Copyright Information

Preface

• Documentation Topics
• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Part I Getting Started with Oracle Application Express

1 Quick Start

• What is Oracle Application Express?
• About the Database Home Page
• Navigation Alternatives
• Using Online Help

2 Running a Demonstration Application

• Installing a Demonstration Application
• Running a Demonstration Application
  • Running an Application from Demonstration Applications
  • Running an Application from the Application Home Page
• Understanding Sample Application
  • About the Home Page
  • About the Customers Page
  • About the Products Page
  • About the Orders Page
  • About the Charts Page
  • About the Admin Page
  • Viewing Pages in Printer Friendly Mode
• Modifying a Demonstration Application
  • About the Developer Toolbar
  • Editing a Demonstration Application
• Viewing Underlying Database Objects

Part II Application Development

3 Application Builder Concepts

• What Is a Page?
• Understanding Page Processing and Page Rendering
  • How the Application Express Engine Renders and Processes Pages
  • Understanding Conditional Rendering and Processing
  • Verifying User Identity
  • Controlling Access to Controls and Components
• Understanding Session State Management
  • What Is a Session?
  • Understanding Session IDs
  • Viewing Session State
• Managing Session State Values
  • Referencing Session State
  • Setting Session State
  • Clearing Session State
  • About Bind Variables
• Understanding URL Syntax
  • Understanding the URL that Displays for a Page
  • Using f?p Syntax to Link Pages
  • Calling a Page Using an Application and Page Alias
  • Calling a Page from a Button URL
• Using Substitution Strings
  • Built-in Substitution Strings

4 Using Application Builder

• Accessing Application Builder
• About the Application Builder Home Page
• About the Application Home Page
• About Application Attributes
  • Configuring Standard Application Attributes
  • Configuring Security Attributes
  • Configuring Globalization Attributes
• About the Page Definition
  • Accessing a Page Definition
  • Understanding the Page Definition
• Using the Developer Toolbar
• Editing a Page Definition
  • Accessing a Summary View of Controls, Components, and Application Logic
  • Copying or Creating a Control or Component
  • Editing Page Attributes
  • About Page Rendering Controls
  • About Page Processing Controls
  • About Shared Components
  • Understanding Page Computations
  • Understanding Validations
  • Understanding Page Processes
  • Understanding Branches
• Linking to Other Pages from the Page Definition
  • Using the View List on the Page Definition
  • About Events
  • About Objects
  • About History
  • About Export
  • About Groups
  • About Referenced
• About the Shared Components Page
  • Accessing the Shared Components Page
  • Understanding the Shared Components Page
• Understanding Application Processes
  • About On Demand Application Processes
  • Application Process Example
  • Creating an Application Process
  • Accessing Application Processes Reports
• Understanding Application Computations
  • About Application Computations
  • Creating an Application Computation
  • Accessing the Application Computation History Report
• Viewing Application Reports
  • About the Database Object Dependencies Report
  • About the Region Search Source Report

5 Building an Application

• Creating an Application
  • About Creating an Application Using a Wizard
  • About the Create Application Wizard
  • About the Create Application from Spreadsheet Wizard
  • About Demonstration Applications
  • Deleting an Application
• Adding Pages to an Application
  • Creating a Page from Application Home Page
  • Creating a Page from the Page Definition
  • Creating a Page from the Developer Toolbar
  • Running a Page or Application
  • Grouping Pages
  • Locking and Unlocking a Page
  • Deleting a Page
• Creating Reports
  • Creating a Report Using a Wizard
  • Editing Report Attributes
  • Controlling Report Pagination
  • Enabling Column Sorting
  • Adding a CSV Link to a Report
  • Exporting a Report as an XML File or a CSV File
  • Creating a Column Link
  • Defining an Updatable Column
  • Defining a Column as a List of Values
  • Controlling When Columns Display
  • Controlling Column Breaks
• Creating Forms
  • Creating a Form Using a Wizard
  • Creating a Tabular Form
  • Building a Master Detail Form
  • Creating a Form Manually
  • Validating User Input in Forms
• Creating Calendars
  • About Creating Calendars
  • Creating a New Calendar
  • Converting an Easy Calendar to a SQL Calendar
  • Editing a Calendar Title
  • Editing Calendar Attributes
• Creating Charts
  • About SVG Plug-in Support
  • About Creating Charts
  • Creating a New Chart
  • Editing Chart Attributes
  • Understanding Chart Cascading Style Sheet Classes
  • Referencing a Custom Cascading Style Sheet
  • Specifying Custom CSS Styles Inline
  • Enabling Asynchronous Updates
  • Displaying Charts in Other Languages
• Creating Buttons
  • Creating a Button Using a Wizard
  • Creating Multiple Buttons
  • Understanding the Relationship Between Button Names and REQUEST
  • About Branching with Buttons
  • Displaying Buttons Conditionally
• Creating Items
  • Creating a Page-Level Item
  • Referencing Item Values
  • Editing Page Item Attributes
  • Displaying Conditional or Read-Only Page Items
  • Working with a Multiple Select List Item
  • Creating an Application-Level Item
  • Populating an Alternative Date Picker Format
• Creating Lists of Values
  • Creating Named LOVs at the Application Level
  • About Static LOVs
  • Editing an Existing LOV
  • Referencing Session State Within a LOV
  • Referencing a Null Value in an Item Based on a LOV
  • Accessing LOV Reports
• Using Shortcuts
  • About Shortcut Types
  • Defining Shortcuts
  • Accessing Shortcut Reports
• Incorporating JavaScript into an Application
  • Referencing Items Using JavaScript
  • Incorporating JavaScript Functions
  • Calling JavaScript from a Button
• Creating Dependent Select Lists
• Creating a Help Page
  • Creating a Help Page and Region
  • Defining Help Text
  • Creating a Help Navigation Bar Entry

6 Adding Navigation

• Creating a Navigation Bar Entry
  • About Navigation Bars
  • How to Create a Navigation Bar Entry
  • Editing a Navigation Bar Entry
  • Editing Multiple Navigation Bar Entries Simultaneously
  • Accessing Navigation Bar Entry Reports
• Creating Tabs
  • About Template Support
  • About the Tabs Page
  • Editing Multiple Tabs at Simultaneously
  • Accessing Tab Reports
• Controlling Navigation Using Branches
• Creating Breadcrumbs
  • About Breadcrumbs
  • How to Create Breadcrumbs
  • Editing Breadcrumbs
  • Accessing Breadcrumb Reports
• Creating Lists
  • Creating a List
  • Adding a List to a Page
  • Editing Lists
  • Editing Multiple List Entries Simultaneously
  • Accessing List Reports
• Creating Trees
  • Creating a Tree
  • Editing a Tree
  • Accessing Tree Reports

7 Controlling Page Layout and User Interface

• Understanding Page Layout
• Displaying Components on Every Page of an Application
• Customizing Regions
  • Creating a Region
  • How Region Attributes Affect Page Layout
• How Item Attributes Affect Page Layout
• Incorporating Content from Other Web Sites
• Managing Themes
  • Accessing the Themes Page
  • Changing Default Templates in a Theme
  • Creating a New Theme
  • Switching an Active Theme
  • Copying a Theme
  • Deleting a Theme
  • About Exporting and Importing Themes
  • Changing a Theme Identification Number
  • Viewing Theme Reports
• Customizing Templates
  • About Cascading Style Sheets
  • Selecting a Default Page Template
  • Viewing Templates
  • Creating a New Template
  • Viewing Template Reports
  • Editing Templates
  • Breadcrumb Templates
  • Button Templates
  • Calendar Templates
  • Label Templates
  • List Templates
  • Page Templates
  • Popup LOV Templates
  • Region Templates
  • Report Templates
• Optimizing a Page for Printing
  • Setting a Print Mode Template for an Application
  • Using f?p Syntax to Toggle to Print Mode
• Using Custom Cascading Style Sheets
  • Uploading Cascading Style Sheets
  • Referencing an Uploaded Cascading Style Sheet in the Page Template
• Managing Images
  • Uploading Images
  • Referencing Images
  • Editing Image Attributes
  • Deleting an Image
• Managing Static Files
  • Uploading Static Files
  • Editing an Uploaded File
  • Downloading an Uploaded File
  • Deleting an Uploaded File
• Creating a Multiple Column Layout
  • Creating Regions in Multiple Columns
  • Creating a Multiple Column Page Template
• Rendering HTML Using Custom PL/SQL

8 Understanding Application Administration

• About the Application Administration Page
  • Accessing Application Administration
  • About Application Express
• About the Manage Services Page
  • Accessing the Manage Services Page
• Managing Session State and User Preferences
  • Managing Session State
  • Managing User Preferences
• Managing Log Files
  • Truncating Page Views
  • Purging the External Clicks Log
  • Purging the Builder Activity Log
  • Deleting SQL History Logs
  • Deleting SQL Script Execution Logs
• Disabling PL/SQL Program Unit Editing
• Managing Application Models
  • Deleting an Application Model
• Managing Application Express End Users
  • Editing Existing End User Accounts
  • Creating New End User Accounts
  • Changing an End User Password
  • Managing Application Express Users Using Groups
• Monitoring Activity
  • Accessing the Monitor Page
  • Viewing Application Changes by Application
  • Viewing Application Changes by Day
  • Viewing Active Sessions
• How to Send Email from an Application
  • Configuring Email Environment Settings
  • Sending Email from an Application
  • Viewing the Email Log
  • Viewing the Mail Queue

9 Managing User Interface Defaults

• Viewing Tables or Views Utilizing User Interface Defaults
  • Navigating to the User Interface Defaults Page
  • Viewing the UI Defaults Report in Object Browser
• Editing Column Attributes
  • About the Column Definition
  • About List of Values
  • About the Database Column Definition Report
• Comparing User Interface Defaults Across Applications
• About Exporting and Importing User Interface Defaults

10 Debugging an Application

• About Tuning Performance
• Reviewing Session State
• Accessing Debug Mode
• Enabling SQL Tracing and Using TKPROF
• Monitoring Application and Page Resource Use
• Viewing Reports
• Debugging Problematic SQL Queries
• Removing Controls and Components to Isolate a Problem

11 Deploying an Application

• About the Oracle Application Express Application Development Life Cycle
  • System Development Life Cycle Methodologies to Consider
• About Deploying an Application to Another Instance
  • Deployment Options to Consider
  • Deciding to Copy the Workspace
  • Deciding to Copy the Database
  • About the Application ID
• How to Deploy an Application to Another Oracle Application Express Instance
  • About Managing Database Objects
• Exporting an Application and Related Files
  • Exporting an Application
  • Exporting Application Express Users
  • Exporting a Page in an Application
  • Exporting Cascading Style Sheets
  • Exporting Images
  • Exporting Static Files
  • Exporting Script Files
  • Exporting Themes
  • Exporting User Interface Defaults
• Importing Export Files
• Installing Export Files
  • Installing Files from the Export Repository
  • Installing Export Files from SQL Command Line
• About Publishing the Application URL
• Using Build Options to Control Configuration
  • Creating Build Options
  • About the Build Options Page
  • Viewing the Build Option Utilization Report

12 Managing Application Security

• About Cross-Site Scripting Protection
  • Protecting HTML Regions and Other Static Areas
  • Protecting Dynamic Output
  • Protecting Report Regions
  • Protecting Form Items
• Understanding Session State Protection
  • How Session State Protection Works
  • Enabling Session State Protection
  • Configuring Session State Protection
• Understanding the Security Risks of File Upload Tables
• Establishing User Identity Through Authentication
  • Understanding How Authentication Works
  • Determining Whether to Include Authentication
  • Creating an Authentication Scheme
  • Using the Authentication Scheme Repository
  • Viewing the Current Authentication Scheme for an Application
  • Changing the Current Authentication Scheme for an Application
  • Viewing Authentication Scheme Utilization
  • About Preconfigured Authentication Schemes
  • About Creating an Authentication Scheme from Scratch
• Providing Security Through Authorization
  • How Authorization Schemes Work
  • Creating an Authorization Scheme
  • Attaching an Authorization Scheme to an Application, Page, or Components
  • Viewing Authorization Reports

13 Advanced Programming Techniques

• Accessing Data with Database Links
• Using Collections
  • About the HTMLDB_COLLECTION API
  • Creating a Collection
  • Truncating a Collection
  • Accessing a Collection
  • Deleting a Collection
  • Adding Members to a Collection
  • Updating Collection Members
  • Deleting Collection Members
  • Determining Collection Status
  • Merging Collections
  • Managing Collections
  • Clearing Collection Session State
• Creating Custom Activity Reports Using HTMLDB_ACTIVITY_LOG
• Running Background PL/SQL
  • Understanding the HTMLDB_PLSQL_JOB Package
  • About System Status Updates
  • Using a Process to Implement Background PL/SQL
• Implementing Web Services
  • Understanding Web Service References
  • Creating a Web Service Reference
  • Using the Web Service Reference Repository
  • Testing a Web Service Reference
  • Creating an Input Form and Report on a Web Service
  • Creating a Form on a Web Service
  • Invoking a Web Service as a Process
  • Editing a Web Service Process
  • Viewing a Web Service Reference History

14 Managing Application Globalization

• About Translating an Application and Globalization Support
  • About Language Identification
  • Rules for Translating Applications Built in Application Builder
  • How Translated Applications Are Rendered
  • About Translatable Components
• Specifying the Primary Language for an Application
  • Using Format Masks for Items
  • Translating Applications for Multibyte Languages
• Understanding the Translation Process
  • Step 1: Navigate to the Translate Application Page
  • Step 2: Map Primary and Target Application IDs
  • Step 3: Seed and Export Text to a Translation File
  • Step 4: Translate the XLIFF File
  • Step 5: Upload and Publish a Translated XLIFF Document
• Translating Messages Used in PL/SQL Procedures
  • Defining Translatable Messages
  • HTMLDB_LANG.MESSAGE API
• Translating Data That Supports List of Values
  • Defining a Dynamic Translation
  • HTMLDB_LANG.LANG API
• About Supported Globalization Codes

15 Oracle Application Express APIs

• HTMLDB_UTIL
  • CHANGE_CURRENT_USER_PW Procedure
  • CLEAR_APP_CACHE Procedure
  • CLEAR_PAGE_CACHE Procedure
  • CLEAR_USER_CACHE Procedure
  • COUNT_CLICK Procedure
  • CREATE_USER Procedure
  • CREATE_USER_GROUP Procedure
  • CURRENT_USER_IN_GROUP Function
  • EDIT_USER Procedure
  • EXPORT_USERS Procedure
  • FETCH_APP_ITEM Function
  • FETCH_USER Procedure
  • FIND_SECURITY_GROUP_ID Function
  • FIND_WORKSPACE Function
  • GET_ATTRIBUTE Function
  • GET_CURRENT_USER_ID Function
  • GET_DEFAULT_SCHEMA Function
  • GET_EMAIL Function
  • GET_FILE Procedure
  • GET_FILE_ID Function
  • GET_FIRST_NAME Function
  • GET_GROUPS_USER_BELONGS_TO Function
  • GET_GROUP_ID Function
  • GET_GROUP_NAME Function
  • GET_LAST_NAME Function
  • GET_USERNAME Function
  • GET_NUMERIC_SESSION_STATE Function
  • GET_PREFERENCE Function
  • GET_SESSION_STATE Function
  • GET_USER_ID Function
  • GET_USER_ROLES Function
  • IS_LOGIN_PASSWORD_VALID Function
  • IS_USERNAME_UNIQUE Function
  • KEYVAL_NUM Function
  • KEYVAL_VC2 Function
  • PREPARE_URL Function
  • PUBLIC_CHECK_AUTHORIZATION Function
  • REMOVE_PREFERENCE Procedure
  • REMOVE_SORT_PREFERENCES Procedure
  • REMOVE_USER Procedure
  • RESET_PW Procedure
  • RESET_AUTHORIZATIONS Procedure
  • SAVEKEY_NUM Function
  • SAVEKEY_VC2 Function
  • SET_ATTRIBUTE Procedure
  • SET_EMAIL Procedure
  • SET_FIRST_NAME Procedure
  • SET_LAST_NAME Procedure
  • SET_USERNAME Procedure
  • SET_PREFERENCE Procedure
  • SET_SESSION_STATE Procedure
  • STRING_TO_TABLE Function
  • TABLE_TO_STRING Function
  • URL_ENCODE Function
• HTMLDB_MAIL
  • SEND Procedure
  • PUSH_QUEUE Procedure
• HTMLDB_ITEM
  • CHECKBOX Function
  • DATE_POPUP Function
  • DISPLAY_AND_SAVE Function
  • HIDDEN Function
  • MD5_CHECKSUM Function
  • MD5_HIDDEN Function
  • MULTI_ROW_UPDATE Procedure
  • POPUP_FROM_LOV Function
  • POPUP_FROM_QUERY Function
  • POPUPKEY_FROM_LOV Function
  • POPUPKEY_FROM_QUERY Function
  • RADIOGROUP Function
  • SELECT_LIST Function
  • SELECT_LIST_FROM_LOV Function
  • SELECT_LIST_FROM_LOV_XL Function
  • SELECT_LIST_FROM_QUERY Function
  • SELECT_LIST_FROM_QUERY_XL Function
  • TEXTAREA Function
  • TEXT Function
  • TEXT_FROM_LOV Function
  • TEXT_FROM_LOV_QUERY Function
• HTMLDB_APPLICATION
  • Referencing Arrays
  • Referencing Values Within an On Submit Process
  • Converting an Array to a Single Value
• HTMLDB_CUSTOM_AUTH
  • APPLICATION_PAGE_ITEM_EXISTS Function
  • CURRENT_PAGE_IS_PUBLIC Function
  • DEFINE_USER_SESSION Procedure
  • GET_COOKIE_PROPS Procedure
  • GET_LDAP_PROPS Procedure
  • GET_NEXT_SESSION_ID Function
  • GET_SESSION_ID_FROM_COOKIE Function
  • GET_USERNAME Function
  • GET_SECURITY_GROUP_ID Function
  • GET_SESSION_ID Function
  • GET_USER Function
  • IS_SESSION_VALID Function
  • LOGIN Procedure
  • LOGOUT Procedure
  • POST_LOGIN Procedure
  • SESSION_ID_EXISTS Function
  • SET_USER Procedure
  • SET_SESSION_ID Procedure
  • SET_SESSION_ID_TO_NEXT_VALUE Procedure
• HTMLDB_LDAP
  • AUTHENTICATE Function
  • IS_MEMBER Function
  • MEMBER_OF Function
  • MEMBER_OF2 Function
  • GET_USER_ATTRIBUTES Procedure
  • GET_ALL_USER_ATTRIBUTES Procedure

Part III Database Tools

16 Managing Database Objects with Object Browser

• About Object Browser
  • Accessing Object Browser
• Searching For and Browsing Database Objects
  • Searching For and Selecting Database Objects
  • Hiding the Object Selection Pane
• About Creating New Database Objects
• Managing Tables
  • Creating a Table
  • Browsing a Table
  • Editing a Table
  • Dropping a Table
• Managing Views
  • Creating a View
  • Browsing a View
  • Editing a View
  • Compiling a View
  • Dropping a View
• Managing Indexes
  • Creating an Index
  • Browsing an Index
  • Dropping an Index
• Managing Sequences
  • Creating a Sequence
  • Browsing a Sequence
  • Dropping a Sequence
• Managing Types
  • Creating a Type
  • Browsing a Type
  • Dropping a Type
• Managing Packages
  • Creating a Package
  • Viewing a Package
  • Editing a Package
  • Compiling a Package
  • Downloading a Package
  • Dropping a Package
• Managing Procedures
  • Creating a Procedure
  • Browsing a Procedure
  • Editing a Procedure
  • Compiling a Procedure
  • Downloading a Procedure
  • Dropping a Procedure
• Managing Functions
  • Creating a Function
  • Browsing a Function
  • Editing a Function
  • Compiling a Function
  • Downloading a Function
  • Dropping a Function
• Managing Triggers
  • Creating Triggers
  • Browsing a Trigger
  • Editing a Trigger
  • Compiling a Trigger
  • Downloading a Trigger
  • Dropping a Trigger
• Managing Database Links
  • Creating a Database Link
  • Browsing a Database Link
  • Dropping a Database Link
• Managing Materialized Views
  • Creating a Materialized View
  • Browsing a Materialized View
  • Dropping a Materialized View
• Managing Synonyms
  • Creating Synonyms
  • Viewing a Synonym
  • Dropping a Synonym

17 Building Queries with Query Builder

• About Query Builder
  • Accessing Query Builder
  • Understanding the Query Building Process
• Using the Object Selection Pane
  • Searching and Filtering Objects
  • Hiding the Object Selection Pane
• Selecting Objects
  • About Supported Column Types
  • Adding an Object to the Design Pane
  • Removing or Hiding Objects in the Design Pane
• Specifying Query Conditions
• Creating Relationships Between Objects
  • About Join Conditions
  • Joining Objects Manually
  • Joining Objects Automatically
• Working with Saved Queries
  • Saving a Query
  • Editing a Saved Query
  • Deleting a Saved Query
• Viewing Generated SQL
• Viewing Query Results

18 Using SQL Scripts

• What is SQL Scripts?
• Accessing SQL Scripts
  • About the SQL Scripts Page
• Creating a SQL Script
  • Creating a SQL Script in the Script Editor
  • Uploading a SQL Script
• Using the Script Editor
• Deleting a SQL Script
  • Deleting Scripts from the SQL Scripts Page
  • Deleting a Script in the Script Editor
• Copying a SQL Script
• Executing a SQL Script
  • Executing a SQL Script in the Script Editor
  • Executing a SQL Script from the SQL Scripts Page
  • About the Run Script Page
• Viewing SQL Script Results
  • Viewing Results from the SQL Scripts Page
  • About the Results Page
• Exporting and Importing SQL Scripts
  • Copying Scripts to an Export Script
  • Importing Scripts from an Export Script
• Viewing Script and Result Quotas

19 Using SQL Commands

• What is SQL Commands?
• Accessing SQL Commands
• About the SQL Commands Home Page
• Executing a SQL Command
  • Running a SQL Command
  • About Transactions in SQL Commands
  • About Unsupported SQL*Plus Commands
  • About Command Termination
  • Using Bind Variables
• Saving a SQL Command
• Copying a Command
• Using Saved Commands
  • Accessing Saved Commands
  • About the Saved SQL Pane
• Using SQL Command History
  • Accessing a Command from Command History
  • About the History Pane
• Viewing Results
  • Accessing the Results Pane
  • About the Results Pane
• Using Explain Plan
  • Viewing an Explain Plan
  • About Explain Plan Pane

20 Using SQL Utilities

• Loading and Unloading Data from From the Database
  • Accessing the Data Load/Unload Page
  • Loading Data
  • Unloading Data
  • Using Text Data Load Repository
• Generating DDL
• Viewing Object Reports
  • Viewing All Objects Reports
  • Accessing the Data Dictionary
  • Viewing PL/SQL Reports
  • Viewing Security Reports
  • Viewing Details about the Tables in Your Database
• Using the Recycle Bin to View and Restore Dropped Objects
  • Managing Objects in the Recycle Bin
  • Emptying the Recycle Bin Without Viewing the Objects

Part IV Administration

21 Administering the Database

• About the Administration Page
• Changing Your Password
  • About Expired Passwords
• Changing the Login Message
• Monitoring Database Storage
  • Monitoring the Space Available
  • Reclaiming Space in the Database
  • Viewing Tablespaces
  • Viewing Datafiles
  • Viewing Database Logging Status
• Monitoring Memory
  • About System Global Area Memory
  • About Program Global Area Memory
  • Viewing Current Memory Utilization
  • Configuring System Global Area Memory
  • Configuring Program Global Area Memory
• Managing Database User Accounts
  • Viewing Database Users
  • Creating a Database User
  • Editing an Existing Database User
  • Dropping a Database User
• Monitoring the Database
  • Sessions
  • About System Statistics
  • About Top SQL
  • About Long Operations
• Viewing Database Details
• Managing HTTP Access to the User Interface
• About the Embedded PL/SQL Gateway
  • Restricting Access to Oracle Database XE

A Available Conditions

Index