| Oracle® Database Application Express User's Guide Release 3.0 Part Number B32258-01 |
|
|
View PDF |
| Oracle® Database Application Express User's Guide Release 3.0 Part Number B32258-01 |
|
|
View PDF |
This section offers a quick introduction to using Oracle Application Express. It is assumed you have already completed the installation process.
This section contains the following topics:
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 as well as 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, PDF, RTF (compatible with Microsoft Word), and XLS (compatible with Microsoft Excel) formats.
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".
Forms
Using wizards, you can easily create forms on tables or on a stored procedure. 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". Query Builder enables you to define queries by dragging and dropping tables and easily create relationships between objects. See "Building Queries with Query Builder". 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". Finally, you can use SQL Scripts to create, edit, view, run, and delete script files. See "Using SQL Scripts".
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 "Managing an Oracle Application Express Hosted Service" .
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:
Versions prior to Oracle Database 11.1 require Oracle HTTP Server (Apache) with mod_plsql. The following graphic illustrates the three-tier architecture.
With Oracle Database 11.1 or higher or Oracle Database 10g Express Edition, you can remove Oracle HTTP Server and mod_plsql from the architecture and replace it with the embedded PL/SQL gateway. The following graphic illustrates the two-tier architecture using the embedded PL/SQL gateway.
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:
session state management
authentication services
authorization services
page flow control
validations processing
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:
Developers are users who create and edit applications
Workspace administrators are users who perform administrator tasks specific to a workspace such as managing user accounts, monitoring workspace activity, and viewing log files
End users have no development privileges and are defined to provide access to applications that do not use an external authentication scheme.
Oracle Application Express administrators are superusers that manage an entire hosted instance using the Application Express Administration Services application
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:
If you have recently installed Oracle Application Express, you need to configure your development environment
If you are a developer logging into a previously configured development environment, an administrator must grant you access to a workspace
Topics in this section include:
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 higher
Mozilla Firefox 1.0 or higher
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:
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. See "Logging in to Oracle Application Express Administration Services" .
Specify a provisioning mode. In Oracle Application Express Administration Services you need to determine how the process of creating (or provisioning) a workspace will work. See "About Workspace Provisioning" and "Specifying a Provisioning Mode".
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".
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".
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"Topics in this section include:
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:
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.
Under Tasks, click Request a Workspace.
The Request Service Wizard appears.
Click Next and follow the on-screen instructions.
See Also:
"Creating Workspaces"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:
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 descriptorsThe Login page appears.
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.
Click Login.
Note that, depending on your setup, you might be required to change your password when you log in for the first time.
To verify the port number where the Oracle XML DB HTTP Server is running:
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.
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 ServerIf 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:
On the Login page, click Find My Workspace on the Tasks list.
Enter your email address and click Find Workspace.
An email with the list workspace names. is sent to you.
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:
Log in to Oracle Application Express. See "Logging In to Oracle Application Express".
From the Administration list, click Change Password.
The Change Password page appears.
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.
Click Apply Changes.
See Also:
"Changing an End User Password"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:
Application Builder. Use Application Builder to assemble an HTML interface (or application) on top of database objects such as tables and procedures. See "Application Builder Concepts" and "Using Application Builder".
SQL Workshop. Use the SQL Workshop to access tools for viewing and managing database objects. Click SQL Workshop to access the following database tools:
Object Browser. View, create, modify, browse, and drop database objects. Use the PL/SQL editor to edit and compile packages, procedures, functions, and triggers while taking advantage of error reporting. See "Managing Database Objects with Object Browser".
SQL Commands. Run SQL commands and anonymous PL/SQL, scripts, and saved queries. See "Using SQL Commands".
SQL Scripts. Use SQL Scripts to create, edit, view, run, and delete script files. You can also upload and download scripts from your local file system. See "Using SQL Scripts".
Query Builder. Use Query Builder's graphical user interface to search and filter database objects, select objects and columns, create relationships between objects, view formatted query results, and save queries with little or no SQL knowledge. See "Building Queries with Query Builder".
Utilities. Use Utilities to load and unload data from the database, generate DDL, view object reports, restore dropped database objects, and monitor the database. See "Using Application Express Utilities".
An Administration list appears on the right side of the Workspace home page. Use the following links to administer your application development environment:
Administration links to the Application Administration page. Use this page to perform administrative tasks. See "About the Application Administration Page".
Manage Services links to the Manage Services page. Use this page to manage session state, log files, preferences, and application models. See "About the Manage Services Page".
Manage Application Express Users links to the Existing Users page. Use this page to manage existing Application Express users as well as user groups. See "Managing Application Express Users" and "Using Groups to Manage Application Express Users".
Monitor Activity links to the Monitor Activity page. Use this page to monitor page views and view application changes. See "Monitoring Activity within a Workspace".
Change Password links to the Change Password page. Use this page to change your workspace password. See "Resetting Your Password".
About Application Express links to the About Application Express page. This page displays version and configuration information for both Application Express and the Oracle database. See "Viewing the Application Express Product Information Page".
Use the Migrations link to migrate a Microsoft Access application and generate an Oracle Application Express application. See "Migrating Applications".
The Workspace Schemas list displays beneath the Administration list. It displays the database schemas that are associated with, and therefore accessible to, this workspace.
The Links list displays on the lower side of the Workspace home page. Use this list to access the following Application Express resources:
Oracle Technology Network links to the Oracle Application Express section of Oracle Technology Network. Use this page to access additional information and resources about using Oracle Application Express.
Discussion Forum links to Oracle Application Express Discussion Forum. Use this page as a resource for research as well as for posting your own questions or answering those of other users.
User's Guide links to an HTML-based online Help system. You can also access online Help by clicking the Help link in the upper right corner of any page in Oracle Application Express. See "About Procedural Online Help".
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:
Primary Navigation (drill-down). Click a large icon to drill-down to the appropriate page.
Secondary Navigation (drop down menus). Click the down arrow on the right side of the icon to view a drop down menu. Select an option from the menu.
Note:
For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.
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.
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.
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.
You can browse through help topics by:
Expanding and collapsing the table of contents. To view a topic, simply select it.
Clicking the breadcrumb links at the top of each help topic.
Clicking the Previous and Next buttons within a topic. Click these buttons to access the previous and next help topic within the structure of the help set.
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.
Click the item label to display a description in a separate window.
