Skip Headers
Oracle® Application Express Application Builder User's Guide
Release 3.2

Part Number E11947-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

1 Quick Start

This section offers a quick introduction to using Oracle Application Express. It is assumed you have completed the installation process.

This section contains the following topics:

See Also:

Oracle Database 2 Day + Oracle Application Express Developer's Guide

What is Oracle Application Express?

Oracle Application Express is a hosted declarative development environment for developing and deploying database-centric Web applications. Thanks to built-in features such as user interface themes, navigational controls, form handlers, and flexible reports, Oracle Application Express accelerates the application development process.

The Application Express engine renders applications in real time from data stored in database tables. When you create or extend an application, Oracle Application Express creates or modifies metadata stored in database tables. When the application is run, the Application Express engine then reads the metadata and displays the application.

To provide stateful behavior within an application, Oracle Application Express transparently manages session state in the database. Application developers can get and set session state using simple substitutions and standard SQL bind variable syntax.

The sections that follow describe key features of Oracle Application Express.

Reporting

With Oracle Application Express, you can quickly generate HTML reports that display the results of SQL queries. You can also download and print reports in HTML, an Adobe Portable Document Format (PDF), Microsoft Word Rich Text Format (RTF), or Microsoft Excel format (XLS), or XML.

You can declaratively link reports together to provide drill-down reporting and use bind variables to pass information from session state to a report. Reports support declarative column heading sorting, control breaks, sums, and pagination. Report sorting and pagination can use Partial Page Refresh (PPR) technology to avoid refreshing the entire page. You can also add declarative links to a report to download the report data to CSV or XML formats. Plus, you can customize the report appearance using templates. See "About Bind Variable Syntax" and "Creating Reports".

Interactive Reporting Regions enable end users to customize reports. Users can alter the layout of report data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, different charts, and their own computations. Users can create multiple variations of the report and save them as named reports, output to comma-delimited files, and print them to PDF documents. See "Editing Interactive Reports".

Forms

Using wizards, you can easily create forms on tables or on a stored procedure. For example, when creating a form on a table, these wizards provide automatic management of insert, update, and delete as well as lost update detection. Once you create a form, you can rearrange form fields (called form items) using a visual representation, enabling you to quickly achieve the layout you want. Form items offer a variety of display options including text fields, text areas, radio groups, select lists, check boxes, date pickers, and popup list of values. See "Creating Forms".

Charting

You can also use wizards to create HTML, SVG, or Flash charts. You can create charts that enable users to drill down from one chart to another chart or report. Charts can also be refreshed using Partial Page Refresh (PPR) technology, avoiding the need to refresh an entire page. You can also configure a chart to refresh at defined intervals. Additionally, you can take advantage of report column templates to add simple HTML bar charts to any report. See "Creating Charts".

Spreadsheet Upload

Use the Create Application from Spreadsheet Wizard to quickly upload spreadsheet data directly into the database. You can choose to store the data in a new database table or add it to an existing database table. Once the data is uploaded, you can quickly create an application. This handy wizard enables you to go from spreadsheet to a shared application in just a few clicks. See "About the Create Application from Spreadsheet Wizard".

Session State Management

Oracle Application Express transparently manages session state (or application context) in the database. Forms automatically save session state, remembering your application context over your session. Referencing session state within SQL and PL/SQL is as simple as using bind variables. For example, consider the following SELECT statement:

SELECT * FROM EMP WHERE EMPNO = :P1_ID

In this example, the value in the item P1_ID is automatically bound when the query is run. You can also reference session state within a static context by prefixing the item name with an ampersand (&) and suffixing it with a period(.), for example:

&P1_NAME.

For management of two dimensional data sets, Oracle Application Express provides a robust collections infrastructure. Best of all, session management is stateless and does not consume any memory. See "Managing Session State Values" and "About Bind Variable Syntax".

User Interface Themes

Oracle Application Express separates presentation (or user interface themes) from the application logic. You can design your application in one theme, change to another supplied theme, or create and use your own custom theme. By separating the application logic (such as queries, processes, and branches) from the HTML rendering, your application can take advantage of new designs and other technological advances without an application rewrite. See "Managing Themes".

Flow Control and Navigation

Every Web application needs navigation and dynamic applications need flow control. Oracle Application Express provides built-in components to simplify the development and maintenance of navigational controls. Navigation is controlled using declarative tabs (one or two levels), breadcrumbs, tree controls, and lists of links. Flow control is performed using declarative branches which can take effect at specific events and under certain conditions. The appearance of navigation controls are managed through templates, making it easy to change from one look to another. See "Adding Navigation" and "Controlling Navigation Using Branches".

Conditionality on All Components

When creating dynamic Web applications, many application components and processing are conditional. In other words, you only show or process certain pieces of information based on the application context, the data, an event, or a privilege. Oracle Application Express enables you to declaratively specify conditionality of all components. This gives you exact control over what users see or do not see on a tab, button, item, list entry, and so. See "Understanding Conditional Rendering and Processing".

External Interfaces and Extensibility

Even though Oracle Application Express provides a robust declarative environment for building applications, you also have the option of developing custom interfaces or controls. For example, if a component does not meet the needs of your environment, you can generate your own custom HTML using PL/SQL. See "Rendering HTML Using Custom PL/SQL". You can also call external services using Web services. See "Implementing Web Services". Oracle Application Express also includes APIs to easily integrate email alerts into an application. See "Sending Email from an Application". Plus, because Oracle Application Express resides in the Oracle database, you can take advantage of inherent database capabilities, including external tables, PL/SQL, database links, gateways, and database Java to extend the functionality of your application.

Security

With Oracle Application Express, you can create public applications that do not require a user log in, or you can create secure applications that require authentication. Oracle Application Express provides a number of built-in authentication schemes including Single Sign On, Database Account Credentials, and an easy-to-use user management system. You can also use custom schemes that interface with just about any authentication service including Microsoft Active Directory and Oracle Applications.

Additionally, you can customize authorization to meet the needs of your environment and apply authorization selectively to an entire application, a page, or a page component. Finally, you can also take advantage of an innovative session state protection feature to prevent URL tampering and built-in features to protect an application form SQL Injection and cross-site scripting (XSS) attacks. See "Managing Application Security".

SQL Workshop Tools

SQL Workshop provides tools to enable you to view and manage database objects from a Web browser. Use SQL Commands to run SQL and PL/SQL statements. See "Using SQL Commands" in Oracle Application Express SQL Workshop and Utilities Guide.

Query Builder enables you to define queries by dragging and dropping tables and easily create relationships between objects. See "Building Queries with Query Builder" in Oracle Application Express SQL Workshop and Utilities Guide.

Object Browser provides an easy-to-use graphical user interface for viewing, creating, modifying, browsing, and dropping database objects. See "Managing Database Objects with Object Browser" in Oracle Application Express SQL Workshop and Utilities Guide.

Finally, you can use SQL Scripts to create, edit, view, run, and delete script files. See "Using SQL Scripts" in Oracle Application Express SQL Workshop and Utilities Guide.

Supporting Objects Utility

You can simplify the steps needed to export, install, upgrade, and deinstall an application in another Oracle Application Express instance by creating a packaged application. Using the Supporting Objects utility, you can bundle the application definition with scripts for creating the database objects, seed data, images, cascading style sheets, and JavaScript.

Creating a packaged application provides application users with an installer-like experience and automates the process of importing and installing an application in another development, test, or even production instance. See "How to Create a Packaged Application".

Performance

Oracle Application Express provides application developers and application users with an extremely high level of performance. Because Oracle Application Express resides in the Oracle database, it has minimal impact on network traffic. Plus, Application Builder includes a large number of monitoring reports to enable you to identify and tune application performance. See "Debugging an Application".

Hosted Development

Oracle Application Express enables a single database to host large numbers of users. Users work in a dedicated work area called a workspace. A workspace is a virtual private database that enables multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private. This flexible architecture enables a single database instance to manage thousands of applications.

You determine how the process of provisioning (or creating) a workspace works. For example, in email verification provision mode, users request a workspace using a link on the login page. After the workspace request has been granted, users receive an email containing a link that they must click to verify the validity of their email address. Then they receive an email with their login credentials. To see an example of email verification provision mode, go to:

http://apex.oracle.com

See "Configuring Your Oracle Application Express Environment" and "Oracle Application Express Hosted Instance Administration" in Oracle Application Express Administration Guide.

About Oracle Application Express Architecture

Oracle Application Express installs with your Oracle database and is comprised of data in tables and PL/SQL code.

Whether you are running the Oracle Application Express development environment or an application you built using Oracle Application Express, the process is the same. Your browser sends a URL request that is translated into the appropriate Oracle Application Express PL/SQL call. After the database processes the PL/SQL, the results are relayed back to your browser as HTML. This cycle happens each time you either request or submit a page.

The application session state is managed in the database tables within Application Express. It does not use a dedicated database connection. Instead, each request is made through a new database session, consuming minimal CPU resources.

About Oracle HTTP Server (Apache) and the Embedded PL/SQL Gateway

The version of Oracle Database you use determines how the URL is translated:

The embedded PL/SQL gateway provides the Oracle database with a Web server and also the necessary infrastructure to create dynamic applications. The embedded PL/SQL gateway runs in the XML DB HTTP server in the Oracle database and includes the core features of mod_plsql, but does not require the Oracle HTTP Server powered by Apache. Inclusion of the embedded PL/SQL gateway simplifies the architecture and eliminates the middle tier entirely.

About the Application Express Engine

The Application Express engine renders and processes pages. It also performs these tasks:

Different Oracle Application Express Environments

When you install Oracle Application Express, you can install two different environments:

Understanding Application Express User Roles

To access the Oracle Application Express development environment, users log in to a shared work area called a workspace. Users are divided into four primary roles:

Logging In to Oracle Application Express

When you log in to Oracle Application Express you log in to a workspace. A workspace is an area within the Oracle Application Express development environment where developers can create applications.

How you log in to Oracle Application Express depends upon whether you have configured your development environment:

Topics in this section include:

About Browser Requirements

You open the Oracle Application Express home page in a Web browser. To view or develop Oracle Application Express applications, the Web browser must support Java Script and the HTML 4.0 and CSS 1.0 standards. Ensure also that cookies are enabled. The following browsers meet this requirement:

  • Microsoft Internet Explorer 6.0 or later

  • Mozilla Firefox 1.0 or later

Configuring Your Oracle Application Express Environment

How you set up Oracle Application Express depends upon your user role. If you are a developer accessing a hosted development environment, an administrator must grant you access to a workspace. If you are an Oracle Application Express administrator, you must perform the following steps:

  1. Log in to Oracle Application Express Administration Services. Oracle Application Express Administration Services is a separate application for managing an entire Oracle Application Express instance. You log in using the ADMIN account and password created or reset during the installation process.

    1. In a Web browser, navigate to the Oracle Application Express Administration Services application. By default, Oracle Application Express Administration Services installs to the following location:

      • If your setup uses the embedded PL/SQL gateway, go to:

        http://hostname:port/apex/apex_admin
        

        Where:

        hostname is the name of the system where Oracle XML DB HTTP Server is installed.

        port is the port number assigned to Oracle XML DB HTTP Server. In a default installation, this number is 8080. See "Verifying the Oracle XML DB HTTP Server Port" in Oracle Application Express Application Builder User's Guide.

        apex is the database access descriptor (DAD) defined in the configuration file.

      • If your setup uses Apache and mod_plsql, go to:

        http://hostname:port/pls/apex/apex_admin
        

        Where:

        hostname is the name of the system where Oracle HTTP Server is installed.

        port is the port number assigned to Oracle HTTP Server. In a default installation, this number is 7777.

        pls is the indicator to use the mod_plsql cartridge.

        apex is the database access descriptor (DAD) defined in the mod_plsql configuration file.

    2. On the Login page:

      • In Username, enter admin.

      • In Password, enter the Oracle Application Express administrator account password you specified when you installed Oracle Application Express.

      • Click Login.

    See Also:

    See "Logging in to Oracle Application Express Administration Services" in Oracle Application Express Administration Guide.
  2. Specify a provisioning mode. In Oracle Application Express Administration Services you must determine how the process of creating (or provisioning) a workspace will work. See "About Workspace Provisioning" in Oracle Application Express Administration Guide.

  3. Create a Workspace. A workspace is a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private. Each workspace has a unique ID and name. An Oracle Application Express administrator can create a workspace manually or have users submit requests. See "Creating Workspaces" and "Managing Workspace Requests" in Oracle Application Express Administration Guide.

  4. Log in to a Workspace. Once you create a workspace in Oracle Application Express Administration Services, return to the Oracle Application Express Login page and log in to that workspace. See "Logging In to Oracle Application Express as a Developer".

Logging In to Oracle Application Express as a Developer

When you log in to Oracle Application Express, you log in to a workspace. If you are a developer, an administrator must grant you access to a workspace.

Note:

Before users can request a workspace or change their passwords, an Oracle Application Express administrator must configure Oracle Application Express environment preferences.

See Also:

"Managing Environment Settings"in Oracle Application Express Administration Guide

Topics in this section include:

Requesting a Workspace

Note:

This section applies only if your Oracle Application Express administrator has configured Oracle Application Express to support workspace requests.

Before you can log in to Oracle Application Express, an administrator must grant you access to a workspace. Each workspace has a unique ID and name.

To request a workspace:

  1. In a Web browser, navigate to the Oracle Application Express Login page.

    If you do not know the URL for logging in to Application Express, see "Logging in to a Workspace".

    The Login page appears.

  2. Under Tasks, click Request a Workspace.

    The Request Service Wizard appears.

  3. Click Next and follow the on-screen instructions.

    See Also:

    "Creating Workspaces" in Oracle Application Express Administration Guide

Logging in to a Workspace

After an Oracle Application Express administrator approves a workspace request, an e-mail arrives with your login credentials (the workspace name, user name, and password).

Note that if your administrator selected Email Verification as the automated method for handling new workspace requests, you might first receive an email containing a verification link. This step ensures that your email is a valid one before the workspace request is approved.

To log in to Oracle Application Express:

  1. In a Web browser, navigate to the Oracle Application Express Login page. By default, Oracle Application Express installs to the following location:

    • If your setup uses the embedded PL/SQL gateway, go to:

      http://hostname:port/apex
      

      Where:

      • hostname is the name of the system where Oracle XML DB HTTP Server is installed.

      • port is the port number assigned to Oracle XML DB HTTP Server. In a default installation, this number is 8080. See "Verifying the Oracle XML DB HTTP Server Port".

      • apex is the database access descriptor (DAD) defined in the configuration file.

        For users who have upgraded from earlier releases, or who have a custom configuration, this value may be htmldb or something else. Verify your DAD with your Oracle Application Express administrator.

    • If your setup uses Oracle HTTP Server (Apache) and mod_plsql, go to:

      http://hostname:port/pls/apex
      

      Where:

      • hostname is the name of the system where Oracle HTTP Server is installed.

      • port is the port number assigned to Oracle HTTP Server. In a default installation, this number is 7777. You can find information about your Oracle HTTP Server installation's port number from either of the following files:

        ORACLE_BASE\ORACLE_HOME\install\portlist.ini
        ORACLE_BASE\ORACLE_HTTPSERVER_HOME\Apache\Apache\conf\httpd.conf
        

        Be aware that if you change a port number, it is not updated in the portlist.ini file. You can only rely on this file immediately after installation.

      • pls is the indicator to use the mod_plsql cartridge.

      • apex is the database access descriptor (DAD) defined in the mod_plsql configuration file.

        For users who have upgraded from earlier releases, or who have a custom configuration, this value may be htmldb or something else. Verify your DAD with your Oracle Application Express administrator.

        See Also:

        "Managing Oracle Database Port Numbers" in Oracle Database Installation Guide and ORACLE_BASE\ORACLE_HTTPSERVER_HOME\Apache\modplsql\conf\dads.readme for more information on database access descriptors

    The Login page appears.

  2. Under Login, enter the following:

    • In the Workspace field, enter the name of your workspace.

    • In the Username field, enter your user name.

    • In the Password field, enter your case-sensitive password.

  3. Click Login.

    Note that, depending on your setup, you might be required to change your password when you log in for the first time.

Verifying the Oracle XML DB HTTP Server Port

To verify the port number where the Oracle XML DB HTTP Server is running:

  1. Start SQL*Plus and connect the database where Oracle Application Express is installed as SYS:

    • On Windows:

      SYSTEM_DRIVE:\ sqlplus /nolog
      connect sys as sysdba
      
    • On UNIX and Linux:

      $ sqlplus /nolog
      connect sys as sysdba
      

    When prompted, enter the appropriate username and password.

  2. Enter the following statement to verify the port number:

    SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
    

    If the port number returns 0, the Oracle XML DB HTTP Server is disabled.

See Also:

Oracle Database Installation Guide to learn how to enable the Oracle XML DB HTTP Server

Finding Your Workspace Name

If you cannot remember your workspace name, you can request a list of all workspace names associated with your email address.

To find your workspace name:

  1. On the Login page, click Find My Workspace on the Tasks list.

  2. Enter your email address and click Find Workspace.

    An email with the list workspace names is sent to you.

Resetting Your Password

You can reset your password by clicking the Change Password link on the Workspace home page.

To reset your password from the Workspace home page:

  1. Log in to Oracle Application Express. See "Logging In to Oracle Application Express".

  2. From the Administration list, click Change Password.

    The Change Password page appears.

  3. In Change Password, enter the following:

    • Enter Current Password - Enter your current password.

    • Enter New Password - Enter your new password.

    • Confirm New Password - Enter your new password again.

  4. Click Apply Changes.

Tip:

You can also reset your password on the Login page by clicking the Reset Password link.

See Also:

"Changing an End User Password" in Oracle Application Express Administration Guide

Logging Out of a Workspace

To log out of Oracle Application Express, click the Logout icon in the upper right corner of the window.

About the Workspace Home Page

When you log in to Oracle Application Express, the Workspace home page appears. A workspace is a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private.

Your user name and workspace name display in the lower left corner of the page. The following three large icons display in the center of the page:

About Administration

An Administration list appears on the right side of the Workspace home page. Use the following links to administer your application development environment:

About Migrations

Use the Application Migrations link to migrate a Microsoft Access application and generate an Oracle Application Express application. See Oracle Application Express Migration Guide.

About Workspace Schemas

The Workspace Schemas list displays beneath the Administration list. It displays the database schemas that are associated with, and therefore accessible to, this workspace.

About Links

The Links list displays on the lower side of the Workspace home page. Use this list to access the following Application Express resources:

Navigation Alternatives

This section describes alternative methods for navigating between pages in the Application Express user interface.

Navigating Using Icons or Drop Down Menus

You can move between pages in Oracle Application Express by clicking large graphical icons. When using these icons, you have two navigation options:

Note:

For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.
Description of new_menu.gif follows
Description of the illustration new_menu.gif

Navigating Using Breadcrumbs

Breadcrumbs (also called locator links) appear at the top of every page within the Oracle Application Express user interface. Each breadcrumb entry indicates where the current page is relative to other pages within the user interface. You can use breadcrumbs to instantly link to a previous page. For example, clicking on Home takes you to the Workspace home page.

Description of breadcrum.gif follows
Description of the illustration breadcrum.gif

Using Online Help

The Application Express user interface features three types of online help: Procedural Online Help, Page-level Help, and Field-Level Help.

About Procedural Online Help

You can access an HTML-based online Help system by clicking the Help link in the upper right corner of the window.

Description of hlp_ico.gif follows
Description of the illustration hlp_ico.gif

When you click the Help link, a help topic appears that describes the current page. To view the table of contents of another help set, select it from the list in the upper left side of the window.

Description of help_window.gif follows
Description of the illustration help_window.gif

You can browse through help topics by:

The top of the window features a gray bar. Click Back and Forward to return to a previously viewed page. These controls work similarly to the Back and Forward controls in a Web browser.

Click Find to perform a keyword search of the entire help system. When the search field appears, enter a case insensitive query in the field provided and click Find. To search for an exact phrase, enclose the phrase in double quotation marks.

Tip:

With Oracle Database 11g, you must enable network services in order use the Find link. See "Enabling Network Services in Oracle Database 11g"

About Page-level Help

Many pages with in the Application Express user interface include Page-level Help. Page-level Help displays in a text box on the right side of the page and offers a brief description of the page functionality.

About Field-Level Help

Most select lists, check boxes, items, and fields within the Application Express user interface include Field-level Help. Items within the user interface that have Field-level Help have a light gray underline. When Field-level Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark.

Description of hlp_item.gif follows
Description of the illustration hlp_item.gif

Click the item label to display a description in a separate window.