http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21679.pdf
========================================================================
Contents
Preface ............................................................................................................................................................... vii
Topic Overview ......................................................................................................................................... vii
Audience.................................................................................................................................................... viii
Documentation Accessibility ................................................................................................................... viii
Related Documents .................................................................................................................................. viii
Conventions ................................................................................................................................................ ix
What’s New ..................................................................................................................................................... xi
What’s New in this Document for Release 4.1 ........................................................................................ xi
1 Introducing Oracle Application Express
About Oracle Application Express........................................................................................................ 1-1
Understanding Oracle Application Express Architecture................................................................ 1-1
About the Oracle Application Express Environment........................................................................ 1-3
About Workspaces ............................................................................................................................. 1-3
About Oracle Application Express User Roles................................................................................... 1-4
Overview of the Application You Build .............................................................................................. 1-5
2 Accessing Your Development Environment
Before You Begin ..................................................................................................................................... 2-1
Accessing the Hosted Demo Environment.......................................................................................... 2-2
Logging In To the Hosted Demo Environment ............................................................................. 2-3
Setting Up Your Own Local Environment .......................................................................................... 2-3
Logging In To Administration Services ......................................................................................... 2-3
Creating a Workspace....................................................................................................................... 2-5
Logging In To Your Local Instance ................................................................................................. 2-7
Creating Additional User Accounts...................................................................................................... 2-8
3 Getting Started with Oracle Application Express
About the Workspace Home Page......................................................................................................... 3-1
What Is Application Builder? ................................................................................................................ 3-2
Installing the Sample Objects................................................................................................................ 3-2
Downloading OEHR Sample Objects ............................................................................................. 3-3
Importing the OEHR Sample Objects ............................................................................................. 3-3
iv
Checking Your Workspace Storage Space............................................................................... 3-4
Deleting the OEHR Sample Objects Application ................................................................... 3-4
Viewing Your Database Objects....................................................................................................... 3-5
4 Building Your Application
About the Application You Build in This Tutorial ............................................................................ 4-1
Creating the Initial Application ............................................................................................................ 4-2
Previewing Your Application........................................................................................................... 4-5
Modifying the Departments Report ..................................................................................................... 4-7
Run the Report to View Added Columns ...................................................................................... 4-9
Using an Interactive Report................................................................................................................. 4-11
Save Report Settings as the Default.............................................................................................. 4-11
Create a Filter.................................................................................................................................. 4-12
Change the Sort Order.................................................................................................................... 4-13
Add a Controlled Break ................................................................................................................. 4-14
Display the Single Row View........................................................................................................ 4-15
Reset Report Settings ...................................................................................................................... 4-16
Adding an Employees Report and Form........................................................................................... 4-16
Previewing the Pages...................................................................................................................... 4-18
Editing the Employees Report ............................................................................................................ 4-19
Create a Function ............................................................................................................................ 4-19
Add a Column ................................................................................................................................ 4-20
Previewing the Page ....................................................................................................................... 4-21
Changing the Column Format ...................................................................................................... 4-22
Adding a Link on the Home Page to the Employees Report ........................................................ 4-23
Adding a Department Column to the Employees Report ............................................................. 4-24
Add a Column ................................................................................................................................ 4-24
Display Column in the Report ...................................................................................................... 4-24
Save Report Settings as the Default.............................................................................................. 4-25
Linking a Column Value to Another Page........................................................................................ 4-26
Change Column Value to a Link................................................................................................... 4-26
Run the Departments Report ........................................................................................................ 4-27
Create a Highlight to Test Clear Interactive Report Command............................................... 4-28
Adding a List of Values to the Employees Report .......................................................................... 4-29
Add a List of Values ....................................................................................................................... 4-29
Add an Item to Display List of Values......................................................................................... 4-30
Change Employees Report Query ............................................................................................... 4-31
Change Column Link on Department Report ........................................................................... 4-31
Turning Off Pagination .................................................................................................................. 4-31
Adding Your Company Name............................................................................................................. 4-32
Switching Themes to Change the User Interface............................................................................ 4-33
5 About Deploying Your Application
Overview of Deploying Your Application ......................................................................................... 5-1
Creating End Users.................................................................................................................................. 5-1
About Publishing the Application URL.............................................................................................. 5-3
v
6 About Globalization
About Language Identification and Rendering ................................................................................. 6-1
Specifying the Method for Global Identification.............................................................................. 6-1
7 Next Steps
Additional Resources .............................................................................................................................. 7-1
vi
vii
Preface
Oracle Database 2 Day + Application Express Developer's Guide introduces you to
application development using Oracle Application Express. This guide shows you
how to set up a development environment, or access a hosted demonstration
environment. It then walks you through building an initial application, modifying it,
and previewing it.
Through a series of exercises, you become familiar with the Oracle Application
Express user interface, basic product functionality, and some underlying concepts that
enable you to develop professional, fast, and secure applications.
Topics:
■ Topic Overview
■ Audience
■ Documentation Accessibility
■ Related Documents
■ Conventions
Topic Overview
This document contains the following sections:
Title Description
Introducing Oracle Application
Express
Describes Oracle Application Express, its architecture
and the environment for using the product. It also
introduces you to some concepts and terms used in this
guide.
Accessing Your Development
Environment
Leads you through the steps to access a development
environment for your use.
Getting Started with Oracle
Application Express
Introduces you to areas of the user interface. It also leads
you through installing the sample objects to use with this
guide.
Building Your Application Leads you through the steps for building an application,
modifying it, and previewing it at different points during
the development process.
About Deploying Your Application Describes some basic information about deploying your
application. It also leads you through the steps to create
end users and to determine the URL of your application.
viii
Audience
Oracle Database 2 Day + Application Express Developer's Guide is intended for:
■ Workspace administrators who want to set up the Oracle Application Express
development environment
■ Application developers who want to learn how to build database-centric Web
applications using Oracle Application Express
To use this guide, you must have a general understanding of relational database
concepts and the operating system environment under which Oracle Application
Express is running.
Documentation Accessibility
For information about Oracle's commitment to accessibility, visit the Oracle
Accessibility Program website at
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For
information, visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are
hearing impaired.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The
conventions for writing code require that closing braces should appear on an
otherwise empty line; however, some screen readers may not always read a line of text
that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or
organizations that Oracle does not own or control. Oracle neither evaluates nor makes
any representations regarding the accessibility of these Web sites.
Related Documents
For more information, see these Oracle resources:
■ Oracle Application Express Installation Guide
■ Oracle Application Express Release Notes
■ Oracle Application Express End User's Guide
About Globalization Describes some basic information about globalization. It
also leads you through the steps to specify how the
application determines global identification.
Next Steps Describes additional resources to expand your
knowledge of Oracle Application Express and boost your
productivity.
Title Description
ix
■ Oracle Application Express Application Builder User's Guide
■ Oracle Application Express SQL Workshop Guide
■ Oracle Application Express API Reference
■ Oracle Application Express Migration Guide
■ Oracle Application Express Administration Guide
■ Oracle Database Concepts
■ Oracle Database Advanced Application Developer's Guide
■ Oracle Database Administrator's Guide
■ Oracle Database SQL Language Reference
■ SQL*Plus User's Guide and Reference
■ Oracle Database PL/SQL Language Reference
For additional documentation available on Oracle Technology Network (OTN), visit
the Oracle Application Express Web site located at:
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html
For information about Oracle error messages, see Oracle Database Error Messages.
Oracle error message documentation is available only in HTML. If you have access to
the Oracle Database Documentation Library, you can browse the error messages by
range. Once you find the specific range, use your browser's "Find in Page" feature to
locate the specific message. When connected to the Internet, you can search for a
specific error message using the error message search feature of the Oracle online
documentation.
Many books in the documentation set use the sample schemas of the seed database,
which is installed by default when you install Oracle. See Oracle Database Sample
Schemas for information about how these schemas were created, and how you can use
them yourself.
For additional application examples, go to the Learning Library. Search for free online
training content, including Oracle by Example (OBE), demos, and tutorials. To access
the Oracle Learning Library, go to:
http://www.oracle.com/technetwork/tutorials/index.html
Printed documentation is available for sale in the Oracle Store at:
http://shop.oracle.com/
If you have a user name and password for OTN, then you can go directly to the
documentation section of the OTN Web site at:
http://www.oracle.com/technetwork/indexes/documentation/index.html
Conventions
The following text conventions are used in this document:
Convention Meaning
boldface Boldface type indicates graphical user interface elements associated
with an action, or terms defined in text or the glossary.
x
italic Italic type indicates book titles, emphasis, or placeholder variables for
which you supply particular values.
monospace Monospace type indicates commands within a paragraph, URLs, code
in examples, text that appears on the screen, or text that you enter.
Convention Meaning
xi
What’s New
This section summarizes updates made to Oracle Application Express Administration
Guide for release 4.1.
What’s New in this Document for Release 4.1
This section summarizes general updates, revised content, and new content added to
Oracle Database 2 Day + Application Express Developer's Guide for release 4.1.
General Updates
■ All content has been updated to reflect new functionality
■ Screen captures and graphics have been added and updated to reflect release 4.1
user interface enhancements
xii
Introducing Oracle Application Express 1-1
1
Introducing Oracle Application Express
This section describes Oracle Application Express, its architecture, and the
environment for using the product. It also introduces you to concepts and terms used
in this guide when discussing Oracle Application Express.
Topics:
■ About Oracle Application Express
■ Understanding Oracle Application Express Architecture
■ About the Oracle Application Express Environment
■ About Oracle Application Express User Roles
■ Overview of the Application You Build
About Oracle Application Express
Oracle Application Express is a rapid Web application development tool for the Oracle
database. Using only a Web browser and limited programming experience, you can
develop professional applications that are both fast and secure. 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.
From the end user’s perspective, the deployed applications require only a browser and
access to an Oracle database running Application Express.
Understanding 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 run 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 request or submit a
page.
The application session state is managed in the database tables within Oracle
Application Express. It does not use a dedicated database connection. Instead, each
Tip: To learn more about Oracle Application Express features, click
the Learn more... button on the Workspace home page.
Understanding Oracle Application Express Architecture
1-2 Oracle Database 2 Day + Application Express Developer's Guide
request is made through a separate database session, consuming minimal CPU
resources.
Understanding Web Server Options
In order to run, Oracle Application Express must have access to Oracle Application
Express Listener, Oracle HTTP Server and mod_plsql, or the embedded PL/SQL
gateway.
About Oracle HTTP Server (Apache) and Oracle Application Express Listener
Oracle HTTP Server uses the mod_plsql plug-in to communicate with the Oracle
Application Express engine within the Oracle database. Oracle Application Express
Listener communicates directly with the Oracle Application Express engine, thus
eliminating the need for the mod_plsql plug-in.
Both Oracle HTTP Server and the Oracle Application Express Listener function as
communication brokers between the Web server and the Oracle Application Express
objects in the Oracle database. More specifically, they map browser requests into
database stored procedure calls over a Oracle Net Services connection. The following
graphic illustrates the Oracle Application Express architecture using Oracle HTTP
Server and mod_plsql.
Note that this three tier architecture consists of the following components:
■ A Web browser
■ Oracle HTTP Server (Apache) with mod_plsql or Oracle Application Express
Listener
■ An Oracle database containing Oracle Application Express
Advantages of either Oracle HTTP Server (Apache) with mod_plsql or Oracle
Application Express Listener include:
■ Separation from mid-tier for the database tier
■ Appropriate for Oracle Real Application Clusters (Oracle RAC) environments
■ Versions prior to Oracle Database 11g Release 1 require Oracle Application Express
Listener or Oracle HTTP Server (Apache) with mod_plsql
About the Embedded PL/SQL Gateway
The embedded PL/SQL gateway installs with in the Oracle database. It 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 Oracle XML DB
HTTP server in the Oracle database and includes the core features of mod_plsql. The
following graphic illustrates the Oracle Application Express architecture using the
embedded PL/SQL gateway.
About the Oracle Application Express Environment
Introducing Oracle Application Express 1-3
As shown in the previous graphic, the embedded PL/SQL gateway is a simple two tier
architecture and consists of these components: a Web browser and an Oracle database
containing the embedded PL/SQL, and Oracle Application Express.
Advantages of the embedded PL/SQL gateway:
■ Ease of configuration
■ Included in the database
■ No separate server installation
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
About the Oracle Application Express Environment
Oracle Application Express enables a single Oracle database to become a shared
workgroup database service. Multiple users can access it using a Web browser without
installing additional software.
About Workspaces
The area where you develop applications is 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.
In a typical development environment, you might create a single workspace for all
your developers to share. However, you can also create dedicated workspaces for
specific developers or projects. Creating a dedicated workspace limits access to the
workspace objects to only those users associated with the workspace.
The following illustration shows the relationship among users and developers,
workspaces, and database schemas.
See Also: "Understanding Administrator Security Best Practice" in
Oracle Application Express Application Builder User's Guide
About Oracle Application Express User Roles
1-4 Oracle Database 2 Day + Application Express Developer's Guide
When you create a workspace, you associate it with a new or existing schema. A
schema is a logical container for database objects such as tables, views, and stored
procedures. A single schema can be associated with one or more workspaces.
About Oracle Application Express User Roles
When setting up Application Express users at a large organization, you assign roles
and privileges to specific users. The roles within Oracle Application Express include
the following:
■ End users have no development privileges. You define end users so that they can
access applications that do not use an external authentication scheme.
■ Developers are users who create and edit applications and modify database
objects. Developers can have their own workspace or share a workspace.
■ Workspace administrators are users who perform administrator tasks specific to a
workspace such as managing user accounts, monitoring workspace activity, and
viewing log files.
■ Instance administrators are superusers that manage an entire hosted instance
using the Application Express Administration Services application.
The following illustration shows multiple users with various roles accessing the Oracle
Application Express development environment, Application Express Administration
Services, and the published applications.
Overview of the Application You Build
Introducing Oracle Application Express 1-5
For this guide, you must have the privileges usually associated with three roles:
Instance Administrator, Workspace Administrator, and Developer. The following
illustration shows an Oracle Application Express instance with a single user.
Overview of the Application You Build
In "Building Your Application" on page 4-1, you create a simple Human Resources
(HR) application for a fictitious company called AnyCo Corp. The application manages
departmental and employee information stored in the department and employees
tables that you create. The tutorial shows you how to build the application, modify it,
and then preview it.
Overview of the Application You Build
1-6 Oracle Database 2 Day + Application Express Developer's Guide
Accessing Your Development Environment 2-1
2
Accessing Your Development Environment
This section guides you through the steps to access an Oracle Application Express
development environment for your use. Then, you and other developers can begin
building applications within Oracle Application Express.
Topics:
■ Before You Begin
■ Accessing the Hosted Demo Environment
■ Setting Up Your Own Local Environment
■ Creating Additional User Accounts
Before You Begin
Before you begin this section, do the following:
■ Install Oracle Application Express as described in Oracle Application Express
Installation Guide.
■ Read "Introducing Oracle Application Express" on page 1-1.
■ Confirm your Web browser supports JavaScript and the HTML 4.0 and Cascading
Style Sheet (CSS) 1.0 standards. The following browsers meet this requirement:
– Microsoft Internet Explorer 7.0 or later version
– Mozilla Firefox 3.6 or later version
– Google Chrome 4.0 or later version
– Apple Safari 4.0 or later version
■ Enable cookies for your Web browser.
■ Determine which environment you want to use:
– Oracle provides a hosted demonstration (demo) environment where you can
try out Oracle Application Express.
Using the demo environment is the fastest way to access a development
environment. You request a workspace from an Oracle Application Express
Web site and provide some basic user information. Shortly after you submit
your request, you receive your login credentials in an email. This option does
not require installing any software.
For instructions on using the demo environment, see "Accessing the Hosted
Demo Environment" on page 2-2.
Accessing the Hosted Demo Environment
2-2 Oracle Database 2 Day + Application Express Developer's Guide
– Set up your own local instance of Oracle Application Express. Istall Oracle
Application Express and then follow the steps in "Setting Up Your Own Local
Environment" on page 2-3.
Accessing the Hosted Demo Environment
To use Oracle Application Express without installing the product locally, go to the
hosted demo environment and sign up for a workspace.
To access the hosted demo environment:
1. In a Web browser, go to:
http://apex.oracle.com
2. Click the Get Started button.
3. On Welcome, review the information and click Next.
4. Identify the administrator for the service. Enter a first name, last name, and valid
email address and click Next.
The system generates an email with your login credentials and sends it to the
email address you enter.
5. For Workspace, enter a workspace name.
Workspace names must be unique. Requesting a duplicate name might delay your
request. In general, enter a name that is significant to you. For example, enter your
own name, your company name, or the name of the project or department to
which your applications pertain.
To use the same name for your workspace and your schema (defined in the next
step) use alphabetic characters when naming your workspace. Schema names are
restricted to alphabetic characters.
6. Specify a schema schema. Options include:
a. Use an existing schema from the database - Enter a the existing schema
name.
b. Request a new schema - Enter a name containing only alphabetic characters.
c. Initial Space Allocation - Select at least 10 MB to accommodate the space
needed for creating the objects required for this guide.
d. Click Next.
A survey appears.
7. Answer the survey questions and click Next.
8. Indicate why you are requesting this service and click Next.
A license agreement apears.
9. Review the agreement, select I accept the terms, and click Next.
10. Click Submit Request.
Note: Do not use the hosted demonstration environment for your
production work.
Setting Up Your Own Local Environment
Accessing Your Development Environment 2-3
An initial email containing a link is sent to you. Click the link within the email to
complete the workspace provisioning process. This step verifies that your email
address is valid before approving a workspace. After that, an email with your login
credentials is sent to you. Use those credentials to log in to the hosted demo
environment and begin using Oracle Application Express.
Logging In To the Hosted Demo Environment
Using the login credentials you received in an email, log in to your dedicated
workspace on the hosted demo environment.
To log in to the hosted demo environment:
1. In a Web browser, go to:
http://apex.oracle.com
2. On the Login page, enter your login credentials.
3. Click Login.
The Workspace home page appears. You may be prompted to change your
password. If not, you can change it manually by clicking the Change Password
link on the right side of the Workspace home page. Then, enter in the information
requested on the page that appears.
To continue with the tutorials, skip the rest of this section and go to "Getting
Started with Oracle Application Express" on page 3-1.
Setting Up Your Own Local Environment
Instead of using the hosted demo environment, you can install Oracle Application
Express locally. If you do a local installation, you must set up your development
environment. The general steps you must perform include:
1. Install Oracle Application Express as described in Oracle Application Express
Installation Guide.
2. Log in to Application Express Administration Services, as described in "Logging
In To Administration Services" on page 2-3.
3. Create a workspace, as described in "Creating a Workspace" on page 2-5.
4. (Optional) Create additional users with developer or administrator privileges, as
described in "Creating Additional User Accounts" on page 2-8.
Logging In To Administration Services
To set up your local development environment, you first must log in to Oracle
Application Express Administration Services. This is a separate application for
managing an entire Oracle Application Express instance.
To log in to the Oracle Application Express Administration Services:
Note: This tutorial explains a simplified method for setting up your
development environment so that you can begin using Oracle
Application Express. However, when you want to set up your
development environment to accommodate your real-life
development needs, review “Oracle Application Express Hosted
Instance Administration" in Oracle Application Express Administration
Guide.
Setting Up Your Own Local Environment
2-4 Oracle Database 2 Day + Application Express Developer's Guide
1. In a Web browser, go to the Oracle Application Express Administration Services
application. Depending on which setup you are using, Oracle Application Express
is installed in one of these locations:
■ If your setup uses the embedded PL/SQL gateway:
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.
– apex is the database access descriptor (DAD) defined in the configuration
file.
For users who have upgraded from earlier versions, 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 the Oracle Application Express Listener, go to:
http://hostname:port/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, for Oracle WebLogic Server this number is 7001, in OC4J this
number is 8888, in Oracle Glassfish Server this number is 8080. These
defaults are correct at the time this document was written. Please review
the related Web server documentation for the latest default port if
necessary.
– apex is the mount point defined in the Web Server configuration file.
■ If your setup uses the Oracle HTTP Server (Apache) with mod_plsql:
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. 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
See Also: "Verifying the Oracle XML DB HTTP Server Port" in Oracle
Application Express Application Builder User's Guide
See Also: Oracle Application Express Listener documentation
Setting Up Your Own Local Environment
Accessing Your Development Environment 2-5
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 versions, or who have a custom
configuration, this value may be htmldb or something else. Verify the
DAD with your Oracle Application Express administrator.
The Login page appears.
2. In Username, enter the username you specified when you installed Oracle
Application Express.
3. In Password, enter the Oracle Application Express administrator account
password you specified when you installed Oracle Application Express.
4. Click Login.
Oracle Application Express Administration Services appears.
Creating a Workspace
From Administration Services, create a workspace. A workspace is a shared work area
within the Oracle Application Express development environment that has a unique ID
and name. All users in the workspace can access all its applications and scripts. For
more information about workspaces, see "About Workspaces" on page 1-3.
To create a workspace:
1. Log in to Oracle Application Express Administration Services.
The Instance Administration Home page appears.
2. Click Manage Workspaces.
3. Under Workspace Actions, click Create Workspace.
See Also: "Managing Oracle Database Port Numbers" in Oracle
Application Express Installation Guide and ORACLE_BASE\ORACLE_
HTTPSERVER_HOME\Apache\modplsql\conf\dads.readme for
more information on database access descriptors
Setting Up Your Own Local Environment
2-6 Oracle Database 2 Day + Application Express Developer's Guide
The Create Workspace Wizard appears.
4. For Identify Workspace, enter the following:
a. Workspace Name - Enter a unique workspace name. This guide uses
2DayPlus for the workspace name.
In general, you want to enter a workspace name that is significant to you. For
example, you might enter your own name, your company name, or the name
of the project or department to which your application pertains.
If you want to use the same name for both your workspace and schema, enter
a workspace name that contains only alphabetic characters. Schema names are
restricted to alphabetic characters.
b. Workspace ID - Leave Workspace ID blank to have the new Workspace ID
automatically generated. A Workspace ID must be a positive integer greater
than 100000.
c. Workspace Description - Enter a workspace description.
d. Click Next.
5. For Identify Schema, enter the following:
a. Re-use existing schema - Select No if you are creating your first workspace.
A schema is a logical container for database objects. In this step, you are
creating the container for database objects you add later in this tutorial.
b. Schema Name - Enter a schema name. This guide uses TWODAYPLUS for the
schema name.
You must use only alphabetic characters in your schema name.
c. Schema Password - Enter a case-sensitive password.
d. Space Quota (MB) - Select at least 10 MB to accommodate the necessary space
for creating the objects required for this guide.
Setting Up Your Own Local Environment
Accessing Your Development Environment 2-7
e. Click Next.
6. For Identify Administrator, enter the following:
a. Administrator Username - Accept the default, ADMIN, or enter another user
name to be used in Oracle Application Express.
You are creating an account for only one user, yourself as an administrator. To
see instructions for creating additional users, see "Creating Additional User
Accounts" on page 2-8.
b. Administrator Password - Enter a case-sensitive password.
c. Enter the remaining information, including your valid email address, and click
Next.
The login credentials for accessing Oracle Application Express are sent to the
email address you enter here, if you have configured the email settings for
your Oracle Application Express environment.
7. Confirm your selections and click Create Workspace.
Logging In To Your Local Instance
To log in to your local instance of Oracle Application Express, you must have this
information:
■ URL where you can access Oracle Application Express
■ Workspace name you use to develop applications
■ Username set up for you for this workspace within Oracle Application Express
■ Password set up for you for this workspace within Oracle Application Express
If you set up your development environment, you have your login credentials.
Otherwise, contact the person who created your user account to supply the
information.
To log in to Oracle Application Express:
Creating Additional User Accounts
2-8 Oracle Database 2 Day + Application Express Developer's Guide
1. In a Web browser, go to the Oracle Application Express Login page. Depending on
your setup, Oracle Application Express installs to one of these locations:
■ If your setup uses the embedded PL/SQL gateway or the Oracle Application
Express Listener:
http://hostname:port/apex
■ If your setup uses the Oracle HTTP Server (Apache) with mod_plsql:
http://hostname:port/pls/apex
For an explanation of the URLs, see "Logging In To Administration Services" on
page 2-3.
The Login page appears.
2. Under Login, enter your login credentials.
3. Click Login.
Creating Additional User Accounts
As an administrator, you inherit all the privileges associated with the developer role. If
you want to start developing the application shown in this guide, skip this section and
go to "Logging In To Your Local Instance" on page 2-7.
However, you might want to create user accounts for other developers who share this
workspace. Associating users with the workspace grants them access to all
applications and scripts within that workspace. You can also create additional user
accounts for workspace administrators.
To create a user account:
1. Log in to the workspace where you want to add users. See "Logging In To the
Hosted Demo Environment" on page 2-3 or "Logging In To Your Local Instance"
on page 2-7.
The Workspace home page appears.
2. Click Administration.
3. Click Manage Users and Groups.
4. Click Create User.
The Create User page appears.
Creating Additional User Accounts
Accessing Your Development Environment 2-9
5. Under User Identification, enter:
a. Username - Enter the username used to log in to the system. Restrictions
include:
– Maximum length of 100 characters
– No spaces
– Only these special characters are permitted: ampersand (@) and period (.)
b. Email Address - Enter the valid email address for this user.
c. First Name - Enter the first or given name to further identify the user
(optional).
d. Last Name - Enter the last or family name to further identify the user
(optional).
e. Description - Enter comments about this user (optional).
6. Under Account Privileges:
a. Default Schema - Specify the default schema used for data browsing,
application creation, and SQL script execution.
When using workspaces that have more than one schema available, this
schema is the default. This setting does not control security, only the user's
preference.
b. Accessible Schemas (null for all) - Leave this blank to enable the end user to
access all applications in the workspace, or enter a colon-delimited list of
schemas for which this user has permissions when using the SQL Workshop.
c. User is a workspace administrator - Specify if this user should have
workspace administrator privileges.
Administrators are given access to all components. Additionally, they can
manage user accounts, groups, and development services. Components may
not be available if they are switched off by Instance Administrators.
d. User is a developer - Specify if this user should have developer privileges.
Creating Additional User Accounts
2-10 Oracle Database 2 Day + Application Express Developer's Guide
Developers must have access to either Application Builder, SQL Workshop, or
both. Components may not be available if they are switched off by Instance
Administrators.
e. Application Builder Access - Determines whether a developer has access to
the Application Builder.
f. SQL Workshop Access - Determines whether a developer has access to the
SQL Workshop.
g. Team Development Access - Determines whether a developer has access to
the Team Development.
h. Set Account Availability - Select Locked to prevent the account from being
used. Select Unlocked to allow the account to be used.
7. Under Password:
■ Password - Enter a case sensitive password.
■ Confirm Password - Enter the password again.
■ Require Change of Password On First Use - Select No to not require this user
to change his or her password at first log in. Select Yes to require the user to
change the password immediately after logging in the first time.
8. Under User Groups, optionally select a user group.
9. Click Create User or Create and Create Another.
See also: "Managing Application Express Users" in Oracle
Application Express Administration Guide
Getting Started with Oracle Application Express 3-1
3
Getting Started with Oracle Application
Express
This section introduces you to areas of the Oracle Application Express user interface. It
also shows you how to install the sample objects to use with this guide.
Before you begin, log in to Oracle Application Express. For instructions, see "Accessing
Your Development Environment" on page 2-1.
Topics:
■ About the Workspace Home Page
■ What Is Application Builder?
■ Installing the Sample Objects
About the Workspace Home Page
When you log in to Oracle Application Express, the Workspace home page appears.
Take a few minutes to explore the product. The following 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.
■ SQL Workshop. Use the SQL Workshop to access tools for viewing and managing
database objects.
■ Team Development. Use Team Development to track new features, non-feature to
do tasks, bugs, and milestones. Users can also provide real-time feedback which
then can be categorized into features, to do tasks, or bugs.
■ Administration links to the Workspace Administration page.
What Is Application Builder?
3-2 Oracle Database 2 Day + Application Express Developer's Guide
About the Learn More Link
Click the Learn More link in the About box to view an informative introduction to
Oracle Application Express. Topics covered include architecture, self service, security,
Websheets, application development, SQL Workshop, Team Development,
administration, and managing development.
What Is Application Builder?
You use the Application Builder component of Oracle Application Express to build
your application. Through wizards or your direct input, you can assemble an HTML
interface (or application) on top of database objects such as tables and procedures.
Each application is a collection of linked pages using tabs, buttons, or hypertext links.
A page is the basic building block of an application. Pages also contain user interface
elements such as tabs, lists, buttons, items, and regions. To see the definition of each
page belonging to your application, you use the Page Definition page.
The Page Definition contains three main areas:
■ Page Rendering lists user interface controls and logic that are executed when a
page is rendered. Page rendering is the process of generating a page from the
database.
■ Page Processing lists logic controls (such as computations and processes) that are
evaluated and executed when the page is processed.
■ Shared Components lists components used by the current page that can also be
referenced by other pages within your application.
Installing the Sample Objects
In Oracle Application Express, users log in to a workspace. You can think of each
workspace as a shared work area that separates your objects, data, and applications
into a virtual private database.
Before performing any tasks in this guide, you must create the appropriate sample
objects within your workspace. These sample objects are copies of the objects that are
typically installed in two schemas:
■ Human Resources (HR)
The HR schema contains information about the employees and the facilities where
they work. Each employee has an identification number, email address, job
identification code, salary, and manager. Employees are assigned to a department,
and each department is associated with one location that has a full address,
including the street name, postal code, city, state or province, and country code.
■ Order Entry (OE)
The OE schema tracks product inventories and sales of a company's products,
including the product identification number, the product name, the associated
associates product category, product descriptions, the weight group (for shipping
purposes), the warranty periods, the suppliers, the availability status, and a
minimum price.
To create the objects locally in your workspace, import the OEHR Sample Objects
application.
Topics:
■ Downloading OEHR Sample Objects
Installing the Sample Objects
Getting Started with Oracle Application Express 3-3
■ Importing the OEHR Sample Objects
■ Viewing Your Database Objects
Downloading OEHR Sample Objects
To import the OEHR Sample Objects application, you must download it from the Oracle
Technology Network (OTN).
To download the OEHR Sample Objects application from OTN:
1. In your Web browser, go to:
http://www.oracle.com/technetwork/testcontent/oehr-sample-objects-1
31098.zip
2. Under Sample Code, click OEHR Sample Objects.
3. Download and save the oehr_sample_objects.zip file to your computer.
4. Unzip and extract the oehr_sample_objects_installer.sql file:
■ Microsoft Windows - Double-click the oehr_sample_objects.zip file
■ UNIX or Linux - Enter the following command:
$ unzip oehr_sample_objects.zip
Importing the OEHR Sample Objects
After you download and unzip the OEHR Sample Objects application, import it into
Oracle Application Express. During the import process, specify installation of both the
application and the supporting objects. Installing the application creates the objects
and sample data needed to complete the exercises in this guide.
To import and install the OEHR Sample Objects application:
1. Log in to Oracle Application Express.
For instructions, see "Logging In To the Hosted Demo Environment" on page 2-3
or "Logging In To Your Local Instance" on page 2-7.
2. On the Workspace home page, click Application Builder.
The Application Builder home page appears.
3. Click the Import button.
4. For Specify File, specify the following:
a. Import file - Click Browse and navigate to the oehr_sample_objects_
installer.sql file.
b. File Type - Select Database Application, Page, or Component Export.
c. File Character Set - Select the correct character set.
d. Click Next.
Tip: In order to successfully import the objects associated with the
OEHR Sample Objects application, your Oracle database must include
Oracle Spatial. If your database instance does not include Oracle
Spatial, you can install it using Database Configuration Assistant. To
learn more, see the Oracle Application Express Installation Guide for your
operating environment.
Installing the Sample Objects
3-4 Oracle Database 2 Day + Application Express Developer's Guide
Next, you install the imported file.
5. To install an imported file, click Next.
The Install Database Application Wizard appears.
6. For Install Application, specify the following:
a. Parsing Schema - Select the schema you created when you set up your
development environment.
b. Build Status - Select Run and Build Application.
c. Install As Application - Select Auto Assign New Application ID.
d. Click Install.
Once you install the application, you must install the supporting objects.
7. For Install Supporting Objects, select Yes and click Next.
8. Confirm your selections by clicking Install.
9. After the installation is completed, click the Home breadcrumb link at the top of
the page.
The Application Builder home page appears.
Checking Your Workspace Storage Space
If you experience problems installing the OEHR Sample Objects application, verify the
available space in your workspace. If you do not have enough space, request
additional storage space.
If you are a workspace administrator, you can:
■ Determine if you have enough storage space. See "About the Workspace
Utilization Report" in Oracle Application Express Administration Guide.
■ Request additional storage space. See "Requesting a New Schema, More Storage,
or Terminating Service" in Oracle Application Express Administration Guide.
Deleting the OEHR Sample Objects Application
Deleting the OEHR Sample Objects application and deinstalling the supporting objects
completely removes all associated objects and sample data.
To delete the OEHR Sample Objects application:
1. Log in to Oracle Application Express.
2. On the Workspace home page, click Application Builder.
The Application Builder home page appears.
3. Click the OEHR Sample Objects icon.
4. On the Tasks list, click Delete this Application.
The Deinstall page appears.
5. To remove all associated objects and sample data, select Remove Application
Definition and Deinstall Supporting Objects.
6. Click Deinstall.
Installing the Sample Objects
Getting Started with Oracle Application Express 3-5
Viewing Your Database Objects
Review the objects you just created by going to Object Browser. Object Browser
enables you to browse, create, and edit objects in your database.
To view the objects:
1. On the Workspace home page, click SQL Workshop.
2. Click Object Browser.
Object Browser appears.
Object Browser is divided into two sections:
■ Object Selection pane displays on the left side of the Object Browser page and
lists database objects of a selected type within the current schema.
■ Detail pane displays to the right of the page and displays detailed information
about a selected object.
3. From the list in the Object Selection pane on the left, select Tables.
The Detail pane lists the tables in your current schema.
4. In the Object Selection pane, click OEHR_EMPLOYEES from the list.
The Detail pane shows details about the table.
5. Click the Data tab at the top of the Details pane.
The data in the OEHR_EMPLOYEES table appears. Note that other tabs show
additional details about the object you select.
6. To search for an object name, enter a case-insensitive term in the Search field.
Installing the Sample Objects
3-6 Oracle Database 2 Day + Application Express Developer's Guide
7. To view all objects, leave the Search field blank.
See also: "Managing Database Objects with Object Browser" in
Oracle Application Express SQL Workshop Guide
Building Your Application 4-1
4
Building Your Application
In this tutorial, you create a Human Resources (HR) application for a fictitious
company called AnyCo Corp. This section shows you how to build the application,
modify it, and preview it at different points during the development process.
Before you begin this section:
■ Read "Getting Started with Oracle Application Express" on page 3-1.
■ Ensure you have installed the database objects as described in "Installing the
Sample Objects" on page 3-2.
Topics:
■ About the Application You Build in This Tutorial
■ Creating the Initial Application
■ Using an Interactive Report
■ Modifying the Departments Report
■ Adding an Employees Report and Form
■ Editing the Employees Report
■ Adding a Link on the Home Page to the Employees Report
■ Adding a Department Column to the Employees Report
■ Linking a Column Value to Another Page
■ Adding a List of Values to the Employees Report
■ Adding Your Company Name
■ Switching Themes to Change the User Interface
About the Application You Build in This Tutorial
This tutorial guides you through the development of a Human Resources (HR)
application for a fictitious company called AnyCo Corp. The application enables users
to view and modify employee information stored in the OEHR_DEPARTMENTS and
OEHR_EMPLOYEES tables.
When building the application, you perform these tasks:
1. Creating the Initial Application: Create an application that includes a Home page
and a basic Departments report.
Creating the Initial Application
4-2 Oracle Database 2 Day + Application Express Developer's Guide
2. Modifying the Departments Report: Expand the report to pull in data from other
tables.
3. Using an Interactive Report: Run and use the Interactive Report.
4. Adding an Employees Report and Form: Build an Employee form and report
based on the OEHR_EMPLOYEES table.
5. Editing the Employees Report: Add calculated fields to the Employees report.
6. Adding a Link on the Home Page to the Employees Report: Add a link on the
Home page.
7. Adding a Department Column to the Employees Report: Modify the Employees
report to enable the user to select a department.
8. Linking a Column Value to Another Page: Add a link on the Departments report
that navigates the user to the Employees report.
9. Adding a List of Values to the Employees Report: Add a report as a region that
displays department details.
10. Adding Your Company Name: Add your company name so that it appears on all
pages in your application.
11. Switching Themes to Change the User Interface: Switch the theme for your
application.
Creating the Initial Application
This first exercise guides you through creating your initial application. Using the
Create Application Wizard, you create two pages and specify basic application
functionality.
The pages created in this section include:
■ Home - This page is the first page displayed when the application is run. This
page contains a link to the Departments report.
■ Departments - This page is an interactive report of all departments.
There are two types of reports, interactive and classic. Interactive is the default
when creating applications, converting forms and creating report regions.
Interactive reports should be selected when you want to provide user
customizations such as filters, sorting, compilation and so on. If you have limited
need for this functionality, you are best served by using a classic report.
To create your initial application:
1. Log in to Oracle Application Express.
For instructions, see "Logging In To the Hosted Demo Environment" on page 2-3
or "Logging In To Your Local Instance" on page 2-7.
2. On the Workspace home page, click Application Builder.
3. Click the Create button.
The Create Application Wizard appears.
Creating the Initial Application
Building Your Application 4-3
Note that each page within a wizard displays the title of the wizard (for example,
Create Application). Each wizard page also displays a series of blocks on the left,
representing the sequence of steps. The highlighted block indicates the step you
are performing.
To orient you, each step in these exercises generally begin with the block label. For
example, the first step in the Create Application Wizard highlights the word
Method. Therefore, the next step orients you by beginning with the words For
Method.
4. For Method, select Database and click Next.
5. Accept the default, From Scratch, and click Next.
6. For Name:
a. Name - Enter AnyCo Corp
b. Application - Accept the default ID. The system provides a unique ID for each
application you create in this workspace.
c. Create Application - Accept the default, From scratch.
d. Schema - Select the schema that contains the objects for this tutorial (for
example, TWODAYPLUS).
e. Click Next.
Next, add a page to your application.
7. For Pages, specify the following in the Add Page section:
a. Select Page Type - Accept the default, Blank.
b. Page Name - Enter Home.
Tip: Explanatory information for each option in item Help. When
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.
Creating the Initial Application
4-4 Oracle Database 2 Day + Application Express Developer's Guide
c. Click Add Page.
The Home page is now listed in the top area. Also, note that the Add Page
area appears so you can continue adding pages to your initial application.
Next, add a report page on the OEHR_DEPARTMENTS table.
8. In the Add Page section, specify the following:
a. Select Page Type - Select Report.
b. Subordinate to Page - Select Home (1).
This sets up the hierarchy among the pages in your application.
c. Page Source - Accept the default, Table.
d. Table Name - Select OEHR_DEPARTMENTS.
The list shows all tables and views in the schema associated with the
application.
e. Implementation - Accept the default Interactive.
f. Click Add Page.
Notice that the pages listed in the Create Application section reflect the
hierarchy of the pages in your application.
Next, change the default page name, OEHR_DEPARTMENTS, to
Departments.
9. Click the Oehr Departments link.
10. Under Page Definition, change the Page Name to Departments and click Apply
Changes.
11. In the Create Application section, click Next.
Next, define some application-level settings.
12. For Tabs, select No Tabs and click Next.
13. For Shared Components, accept the default, No, and click Next.
Creating the Initial Application
Building Your Application 4-5
14. For Attributes, make these changes:
a. Authentication Scheme - Accept the default, Application Express.
Note that Application Express is the default Authentication Scheme. The
authentication scheme option enables you to control users’ access to the
application using Application Express authentication or another type of
authentication.
b. Language - Accept the default, English (en)
c. User Language Preference Derived From - Accept the default, Application
Primary Language.
d. Date Format - Click the up arrow and select 12-JAN-04.
e. Date Time Format - Click the up arrow and select 12-JAN-2004 1430.
f. Timestamp Format - Click the up arrow and select 12-JAN-2004 1430.
g. Timestamp Time Zone Format - Click the up arrow and select 12-JAN-2004
1430.
h. Click Next.
15. For User Interface, select Theme 2 and click Next.
16. For Confirm, verify the information and click Create.
The two pages you created, Home and Departments, appear as icons by default.
Notice that Oracle Application Express adds a Login page automatically to your
application.
Congratulations! You created your first application using Oracle Application Express.
Next, preview the application to see the application the way it would appear to end
users.
Previewing Your Application
You preview your application by running it. Running the application displays the
rendered version of the application as end users would see it.
As you create pages, you can view them by running the page individually or by
running the entire application. When you run a page or application, the Application
Express engine dynamically renders it into viewable HTML based on data stored in
the database.
Note: To view the pages in a report instead of as icons, click the
View Report icon. Your view setting is retained from session to
session.
Because these steps use the default setting, switch back to Icons view
before continuing.
Creating the Initial Application
4-6 Oracle Database 2 Day + Application Express Developer's Guide
You can run an application in the following ways:
■ To run the application, click the Run Application icon.
■ To run a specific page, click the Run Page icon.
To preview your application:
1. On the Application home page, click Run Application.
2. The first time you run an application you are prompted to enter a user name and
password. To continue, simply enter your workspace user name and password
and then click Login.
You can run the application using your Application Express login credentials
because you chose Application Express Authentication when creating the
application. For more information, see "Establishing User Identity Through
Authentication" in Oracle Application Express Application Builder User's Guide.
The Home page of your application appears.
Notice that a link to the Departments page appears on the Home page because you
set up that hierarchy.
Also notice the Developer toolbar at the bottom of the page. These links appear
when you run an application within the development environment. The
Modifying the Departments Report
Building Your Application 4-7
Developer toolbar offers a quick way to edit the current page, create a page,
region, or page control, view session state, or toggle in and out of Debug mode.
3. Click the Departments link.
The Departments report appears.
Modifying the Departments Report
When you run the Create Application Wizard, the wizard creates forms and reports
based on the selections you make. Behind the scenes, Application Express writes a
query for you to extract the appropriate data from the database.
In this exercise, you modify the query for the Departments report to include
information from the OEHR_EMPLOYEES table. Three columns from the OEHR_
EMPLOYEES table, Number of Employees, Manager Name and Location, are added to
the Departments report. The Manager Name column replaces the Manager Id column
and the Location column replaces the Location Id column.
To modify the Departments report:
1. Click the Edit Page 2 link on the Developer toolbar (at the bottom of the page).
The Page Definition for Page 2 appears.
A page is the basic building block of an application. Pages contain user interface
elements such as tabs, lists, buttons, items, and regions. To see the definition of
each page belonging to your application, you use the Page Definition page. For
more information, see "What Is Application Builder?" on page 3-2.
2. Under Page Rendering, locate Regions.
3. Expand Region and locate Departments.
Modifying the Departments Report
4-8 Oracle Database 2 Day + Application Express Developer's Guide
4. Right-click Departments and select Edit.
The Region Definition appears.
5. Scroll down to Source, and replace the default Region Source script with the
following:
SELECT d.DEPARTMENT_ID,
d.DEPARTMENT_NAME,
(select count(*)from oehr_employees where department_id = d.department_id)
"Number of Employees",
substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
c.COUNTRY_NAME "Location"
FROM OEHR_DEPARTMENTS d,
OEHR_EMPLOYEES e,
OEHR_LOCATIONS l,
OEHR_COUNTRIES c
WHERE d.LOCATION_ID=l.LOCATION_ID
AND l.COUNTRY_ID=c.COUNTRY_ID
AND d.DEPARTMENT_ID=e.department_id
AND d.manager_id=e.employee_id
AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_
SEARCH,d.department_name)))>0
6. Click Apply Changes.
7. Click Apply Changes to confirm.
8. Right-click Departments and select Edit Report Attributes.
9. Click the Edit icon next to Number of Employees.
10. Under Column Definition, select center for Column Alignment.
11. Click Apply Changes.
Tip: You can also double-click Departments.
Modifying the Departments Report
Building Your Application 4-9
Run the Report to View Added Columns
To see how the page looks to end users, run the page. When you run a page, the
Application Express engine dynamically renders and processes the page to display
viewable HTML.
To run the page:
1. Click the Run Page icon in the upper right corner.
The revised Departments report appears.
Note that the Manager Id and Location Id columns are not available. Also note
that the three new columns do not display on the revised report: Number of
Employees, Manager Name, and Location. The following steps are required to add
these columns to the display and reorder them.
2. To add the new columns, click the Actions menu.
The list of Action options appears.
Modifying the Departments Report
4-10 Oracle Database 2 Day + Application Express Developer's Guide
3. From the Actions menu, select Select Columns.
The columns that were added by the new SQL query are in the Do Not Display
box.
4. Click the Move All icon to add these three columns to the Display in Report box.
The Move All icon looks like the following figure.
5. Verify if the columns are in the following order. If they are not, click up and down
arrows to the right of the Display in Report to change the order:
■ Department ID
■ Department Name
■ Number of Employees
■ Manager Name
■ Location
6. Click Apply.
Using an Interactive Report
Building Your Application 4-11
The Departments page appears and includes the new columns.
Using an Interactive Report
This exercise guides you through some commonly used report options available to
users when they run interactive reports. These options enable the user to perform
certain tasks quickly.
Topics:
■ Save Report Settings as the Default
■ Create a Filter
■ Change the Sort Order
■ Add a Controlled Break
■ Display the Single Row View
■ Reset Report Settings
You already used the Select Column option from the Actions menu to add columns to
the report display. In this section, you run the application and use some of the various
search, sort, highlight, and reset features to manipulate the Departments interactive
report.
This tutorial only touches on some of the most prominent functionality available with
interactive reports. For complete details see "Customizing Interactive Reports" in
Oracle Application Express Application Builder User's Guide.
Save Report Settings as the Default
Both developers and end users can save an interactive report. However, only a
developer can save the report that initially displays, called the Primary Default report.
You can save four types of interactive reports:
■ Primary Default (Developer Only). The Primary Default is the report that initially
displays. Primary Default reports cannot be renamed or deleted.
■ Alternative Report (Developer Only). Enables developers to create multiple report
layouts. Only developers can save, rename, or delete an Alternative Report.
Using an Interactive Report
4-12 Oracle Database 2 Day + Application Express Developer's Guide
■ Public Report (End user). Can be saved, renamed, or deleted by the end user who
created it. Other users can view and save the layout as another report.
■ Private Report (End user). Only the end user that created the report can view,
save, rename or delete the report.
To save current report settings as the default:
1. Click the Actions menu.
2. Select Save Report.
The Save Report settings appear.
3. From Save, select As Default Report Settings.
The Default Report Type settings appear
4. Select Primary and click Apply.
The report will show all columns when the user selects the Reset option from the
Actions menu or when the page is reset by a Reset Interactive Report command,
RIR.
Create a Filter
This exercise shows you how to add a filter to the Departments report. This example
creates a filter to show all departments with the exception of departments with an Id
of 60.
There is more than one way a user can create a filter. The user can add a filter from the:
■ Search Bar
■ Actions Menu
■ Column Heading
The Actions menu offers more options than the others. For a quick filter using the
equals operator, the Search Bar or column heading are the quickest methods. For
anything else, use the Filter option from the Actions menu.
To create a filter for all departments not equal to 60:
1. Click the Actions menu.
2. Select Filter.
The filter settings display. Note you can also click the column heading link to
create a filter for that column. Creating a filter from the column heading menu,
however, is limited to the equal operator.
See Also: For a description of how to save as a report as a Named
Report see "Customizing Interactive Reports" in the Oracle Application
Express Application Builder User's Guide.
Using an Interactive Report
Building Your Application 4-13
3. From Operator, select the not equals operator (!=).
4. From Expression, select 60.
5. Click Apply.
The Departments report is displayed with the Department_Id!=60 filter applied.
All departments, accept for the IT department with a Department Id of 60, are
included in the report.
Notice the filter displays at the top of the report. You can edit, disable, or delete
this filter. To edit, click the filter name. To disable, deselect the check box. To
delete, click the Delete icon that looks like an X over the filter icon.
6. Deselect the check box next to the Department Id!=60 filter to disable it.
The report shows data for all Department Ids.
Change the Sort Order
In this exercise, you learn how the user can reorder the data for each column.
To change the sort order:
1. Click the Actions menu.
2. Select Format and then Sort.
The sort settings are displayed. Note this option is also available for each column
by clicking the column heading. The Actions menu is quicker when changing the
sort order for multiple columns.
3. Select Department Name for the first column entry.
Using an Interactive Report
4-14 Oracle Database 2 Day + Application Express Developer's Guide
4. Click Apply.
The report shows the Department Name column in ascending alphabetical order.
Notice the up arrow icon after the Department Name that indicates the column
has been sorted in ascending order. You can click this arrow to resort the column.
Add a Controlled Break
Another way to reformat a report is to create a controlled break. A controlled break
enables the user to delineate data into clearly visible groups.
To add a controlled break:
1. Click the Location column heading.
The column heading menu appears.
2. Click the Control Break icon as shown below.
Note this same option is also available from the Actions, Format menu.
Using an Interactive Report
Building Your Application 4-15
The report is reformatted to show a break after each group of rows that share the
same location.
Note the Location control break report setting displays under the Search bar.
Display the Single Row View
You can also display details for a row by selecting the Single Row icon.
To display the single row view:
1. Locate the Single Row View icon for the first row.
2. Select the Single Row View icon for the first row.
The details page for the first row appears.
Adding an Employees Report and Form
4-16 Oracle Database 2 Day + Application Express Developer's Guide
Reset Report Settings
You can revert the report settings back to the saved default settings. When an
interactive report is reset, the default settings are applied to the report. An interactive
report can be reset by the user and by the developer under the following conditions:
■ A user can reset an interactive report if the Reset option is available from the
Actions menu. This option is enabled or disabled by the developer. For further
details see "Customizing Interactive Reports" in the Oracle Application Express
Application Builder User's Guide.
■ A developer can specify that an interactive report be reset when it is called from
another page. The Reset Interactive Report command, RIR, is used for this
purpose. For further details see "Customizing Interactive Reports as a Developer"
in the Oracle Application Express Application Builder User's Guide.
To return to the default settings:
1. Click the Report View button.
2. Click the Actions menu and select Reset.
3. When prompted to restore the default settings, click Apply.
The report goes back to the default report settings saved in the previous section.
Adding an Employees Report and Form
In this exercise, you add a report to display employee information and a form to
create, update, and delete employee information.
To add an employee report and form:
1. Click the Application ID link on the Developer toolbar (at the bottom of the page).
The Application home page appears.
2. Click Create Page.
3. For Page:
a. Select Form and click Next.
b. Select Form on a Table with Report.
This option creates two pages, a report and form, based on a single table or
view.
c. Click Next.
4. For Page:
a. Table/View Owner - Accept the default. This specifies the schema owner.
Adding an Employees Report and Form
Building Your Application 4-17
b. Table/View Name - Select OEHR_EMPLOYEES.
c. Click Next.
5. For Define Report Page, make these changes:
a. Implementation - Select Interactive.
b. Page Name - Change to Employees.
c. Region Title - Change to Employees.
d. Regioni Template - Select Reports Region.
e. Breadcrumb - Select Breadcrumb.
The Create Breadcrumb Entry section appears.
f. For Select Parent Entry, click the Home link.
Home appears in the Parent Entry field.
g. Click Next.
6. For Define Report Page, accept the default, Do not use tabs, and click Next.
7. For Define Report Page:
a. Press the CTRL key and select only the following columns:
– EMPLOYEE_ID
– FIRST_NAME
– LAST_NAME
– HIRE_DATE
– SALARY
– COMMISSION_PCT
b. Click Next.
The columns you selected appear on the Report page when the page is run.
8. For Define Report Page, accept the defaults and click Next.
Note that the default Edit Link image resembles a small piece of paper and pencil.
Next, define the page and region information for the form.
9. For Define Form Page, edit the following:
a. Page Name - Enter Create/Edit Employee
b. Region Title - Enter Create/Edit Employee
c. Entry Name - Enter Create/Edit Employee
d. Click Next.
10. For Primary Key Type, accept the defaults and click Next.
11. In Define the source for the primary key columns, accept the default, Existing
trigger, and click Next.
Your table, OEHR_EMPLOYEES, already has a trigger that populates the primary
key.
12. In Select Column(s), select all columns and click Next.
These columns appear in the Create/Edit Employee form when the page is run.
Adding an Employees Report and Form
4-18 Oracle Database 2 Day + Application Express Developer's Guide
13. In Identify Process Options, accept the defaults and click Next.
These selections enable users to add, update, and create employee records.
14. For Confirm, verify the information and click Finish.
Previewing the Pages
To preview the two pages you just created, run the current page. From the Employees
report, you can then preview the form.
To preview the pages:
1. Click the Run Page icon.
The Employees report appears.
Note the following about the Employees report:
■ The Search Bar with the Actions menu appears at the top. For more
information, see "Using an Interactive Report" on page 4-11.
■ The column headings are links that enable the user to quickly perform various
sorting, filtering, and reformatting operations. For more information, see
"Using an Interactive Report" on page 4-11.
■ The Edit icon you selected appears in each row. You can click the Edit icon to
update the employee record. Since you created a form based on a table with a
report, the wizard automatically linked the report to the form. If you create
Editing the Employees Report
Building Your Application 4-19
just a report, as described in the previous section, "Creating the Initial
Application", the wizard automatically links the report to the single row view.
■ The Create button appears in the upper right corner. You can click the Create
button to add an employee to the underlying table.
■ The Employees report includes the six columns you specified. When you
selected the columns and applied the changes, Oracle Application Express
created the appropriate SQL code in the background to produce this result.
Your data might appear in a different order. If so, ignore this difference and
continue with these steps.
2. To review the form you also created, click the Edit icon in an Employee row.
The Create/Edit Employee form appears.
Note the following about the Create/Edit Employee form:
■ The form includes Cancel, Delete, and Apply Changes buttons.
■ The Hire Date field displays a calendar, which is included because the data
type of the underlying column is DATE.
■ Fields labeled in red require are mandatory and cannot be left blank or null.
Editing the Employees Report
In this exercise, you create a new column entitled Remuneration to display employee
salaries in the Employees report.
Topics:
■ Create a Function
■ Add a Column
■ Previewing the Page
■ Changing the Column Format
Create a Function
First, create a function to calculate employees’ salaries.
Editing the Employees Report
4-20 Oracle Database 2 Day + Application Express Developer's Guide
To create a function:
1. Click the Home link on the Developer toolbar.
2. Click SQL Workshop and then SQL Commands.
SQL Commands provides a window where you can run PL/SQL against the
database.
3. In the SQL editor, enter the following script:
CREATE OR REPLACE FUNCTION calc_remuneration(
salary IN number, commission_pct IN number)
RETURN NUMBER IS
BEGIN
RETURN ((salary*12) + (salary * 12 * nvl(commission_pct,0)));
END;
When copying and pasting this SQL, make sure there is not an empty line before
the first statement, CREATE OR REPLACE FUNCTION.
4. Click Run.
The Results section displays this message:
Function created.
Add a Column
Next, add a column to display the results of the employees’ salary calculations.
To add a column:
1. Navigate to the Page Definition for page 3:
a. Click the Application Builder tab.
b. Select the AnyCo Corp application.
c. Click 3 - Employees.
The Page Definition for page 3 appears.
2. Under Regions, locate Employees.
3. Right-click Employees and select Edit.
Editing the Employees Report
Building Your Application 4-21
The Region Definition appears.
4. Scroll down to Source, and replace the existing code with the following:
SELECT "EMPLOYEE_ID",
"FIRST_NAME",
"LAST_NAME",
"HIRE_DATE",
"SALARY",
"COMMISSION_PCT",
calc_remuneration(salary, commission_pct) REMUNERATION
FROM "#OWNER#"."OEHR_EMPLOYEES"
5. Click Apply Changes.
6. Click Apply Changes to confirm.
Previewing the Page
To preview the page, click the Run Page icon in the upper right corner. Notice the
Remuneration column does not appear. You must add the column by using the Select
Column action.
To display the Remuneration column:
1. Click the Actions menu.
2. Select the Select Columns action.
3. Move the Remuneration column from the Do Not Display box to the Display in
Report box.
Editing the Employees Report
4-22 Oracle Database 2 Day + Application Express Developer's Guide
4. Click Apply.
The Employees Report is displayed and includes the Remuneration column.
Changing the Column Format
Next, change the format of the columns that contain numeric values.
To change the column format:
1. Click Edit Page 3 on the Developer toolbar (at the bottom of the page).
The Page Definition appears.
2. Under Regions, right-click Employees and select the Edit Report Attributes.
The Report Attributes page appears.
3. Locate the Column Attributes section.
Next, change the format of the value in the Salary and Remuneration columns.
4. To edit the Salary format:
a. Click the Edit icon next to SALARY.
b. For Number/Date Format, select $5,234.10 from the list.
Notice that the appropriate format mask appears in the field.
c. Click Apply Changes.
5. Repeat the previous step for REMUNERATION.
6. Click the Run Page 3 icon in the upper right corner to preview the page.
Notice that the numbers in the Salary and Remuneration columns now appear
with the dollar sign and include commas and decimals.
Adding a Link on the Home Page to the Employees Report
Building Your Application 4-23
Adding a Link on the Home Page to the Employees Report
In this exercise, you add a link on the Home page to take users to the Employees
report. This link appears in the Navigation region on the left side of the Home page.
To add a link on the Home page to the Employees report:
1. Navigate to the Page Definition for the Home page, page 1:
a. Click the Application ID link on the Developer toolbar (at the bottom of the
page).
b. On the Application home page, click 1 - Home.
2. Under Regions, right-click Navigation and select Edit List.
3. On the Entries by List page, click the Create List Entry button.
4. On the Create/Edit List page, edit the following:
a. Sequence - Change to 20.
b. List Entry Label - Enter Employees
c. Page - Select 3.
5. Click Create.
6. Run the page.
Notice that your Home page now includes a link to Employees.
7. Click Employees to test the link.
The Employees report appears.
Adding a Department Column to the Employees Report
4-24 Oracle Database 2 Day + Application Express Developer's Guide
Adding a Department Column to the Employees Report
In this exercise, you link the Employees and Departments reports. When you complete
this section, the Employees report will have a Departments column showing which
department each employee belongs to.
To perform this task, you create a column on the Employees page to enable the user to
select a department. You then run the Employees report and add the Departments
column to the report. The report settings are saved as the default.
Topics:
■ Add a Column
■ Display Column in the Report
■ Save Report Settings as the Default
Add a Column
In order to filter the Employees report by department, add a Department column to
the report.
To add a column:
1. Click Edit Page 3 in the Developer toolbar.
2. Under Regions, right-click Employees and select Edit.
The Region Definition appears.
3. Scroll down to Source.
4. Replace Region Source with the following SQL:
SELECT e.EMPLOYEE_ID,
e.FIRST_NAME,
e.LAST_NAME,
e.HIRE_DATE,
e.SALARY,
e.COMMISSION_PCT,
calc_remuneration(salary, commission_pct) REMUNERATION,
e.DEPARTMENT_ID,
d.DEPARTMENT_NAME
FROM OEHR_EMPLOYEES e,
OEHR_DEPARTMENTS d
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID
5. Click Apply Changes.
6. Click Apply Changes to confirm.
The next section explains how to add the column to the report display and how to
use this column to filter employee reports.
Display Column in the Report
When you first run this report, use the Select Columns action to add the Department
Name column to the display. Once this column is visible on the report, make this
report the default and filter by department.
To add the Department Name column to the report:
1. Click the Run Page 3 icon.
Adding a Department Column to the Employees Report
Building Your Application 4-25
2. Click the Actions menu and select Select Columns.
3. Move Department Name from the Do Not Display box to the last entry in the
Display in Report box.
4. Click Apply.
The Employees Report is displayed and includes the Department Name column.
Next, make these report settings the default.
Save Report Settings as the Default
This section describes how to save the current report settings as the Primary Default
report. Report settings are any setting made through the Actions menu including:
selected columns, filters, sorting, rows per page, format setting such as sort control
breaks, highlighting, and so on, and flashback.
Both developers and end users can save interactive reports. However, only a
developer can save the report that initially displays, called the Primary Default report.
Once the default settings are saved, these columns are the default display in this
report.
To save the current report settings as the default:
1. Run the Employees report page.
The current report default does not include the Department Name column when
the report is displayed.
2. Click the Actions menu.
Linking a Column Value to Another Page
4-26 Oracle Database 2 Day + Application Express Developer's Guide
3. Select Save Report.
4. For Save, select As Default Report Settings from the list.
5. Accept the Default Report Type setting, Primary, and click Apply.
Linking a Column Value to Another Page
In this exercise, you add a link to the Number of Employees column on the
Departments report that navigates the user to the Employees report. It also sets the
focus to the selected department. The column link includes a Clear Interactive Report
command, CIR, to clear the Employees report, when shown, of any filters, control
breaks, highlights, aggregates, computed columns, chart settings and flashback
settings.
Topics:
■ Change Column Value to a Link
■ Run the Departments Report
■ Create a Highlight to Test Clear Interactive Report Command
Change Column Value to a Link
To make this change, you edit the report attributes on the Departments report to
change the value for the Number of Employees column to a link.
To change the column value to a link:
1. Click the Application ID link on the Developer toolbar.
2. Click 2 -Departments.
3. Under Regions, right-click Departments and select Edit Report Attributes.
The Report Attributes page appears.
4. Locate the Column Attributes section.
5. In the Column Attributes section, click the Edit icon next to Number of
Employees.
6. Scroll down to the Column Link section.
7. Under Column Link, make these changes:
a. Link Text - Select #Number of Employees#.
b. Page - Enter 3.
c. Select the Reset Pagination check box.
d. Clear Cache - Enter:
3,CIR
The 3 means the cache for page 3 is cleared and the string CIR clears the
Employees interactive report of any filters, control breaks, highlights,
Note: If a reset is performed on this page, these default report
settings are used. The Department Name column is displayed by
default.
Linking a Column Value to Another Page
Building Your Application 4-27
aggregates, computed columns, chart settings or flashback settings when the
report is displayed.
e. Item 1, Name - Enter IR_DEPARTMENT_ID
By prefixing the item name with IR_, the URL item will be associated with an
interactive report column rather than an item on the page.
f. Item 1, Value - Select #DEPARTMENT_ ID#
The Column Link section shows all the options you selected.
In summary, these options enable the following behavior when the end user
clicks the link:
– The end user is navigated to page 3 of the Employees report.
– On the Employees report, the selected department is in focus.
– The Employees report is cleared. This means any filters, control breaks,
highlights, aggregates, computed columns, chart settings or flashback
settings are removed.
– The user is returned to the first set of employee records when the user
selects another department.
g. Click Apply Changes.
Note that in the Column Attributes section, the Number of Employees row
contains a check mark in the Link column, reflecting the change you just
made.
Run the Departments Report
Now, run the report and test the Number of Employees column link from the
Departments report. The Employees report is displayed showing all employees that
belong to the selected department.
1. Click the Run Page 2 icon.
Note: If you want to reset the interactive report to the default report
settings instead of clear the report, replace CIR with RIR. The last
saved default report settings are applied to the interactive report.
Linking a Column Value to Another Page
4-28 Oracle Database 2 Day + Application Express Developer's Guide
The Departments report is displayed. Note that end users can now drill down on
the data in the Number of Employees column.
2. In the Number of Employees column, click the 3 for the Executive row.
All employees that belong to the Executive department appear in the Employees
report.
The Department Id filter is displayed because this report is filtered by the
Department Id value for the Executive department, 90, passed to this report from
the Departments report. Notice the Clear Interactive Report command is included
at the end of the URL in your browser, as shown in this example:
f?p=9257:3:3672543193352902::NO:RP,3,CIR:IR_DEPARTMENT_ID:90
Create a Highlight to Test Clear Interactive Report Command
This exercise demonstrates how to use the CIR command to clear a highlight before
displaying the Employees report. To show this, you highlight any rows containing the
first name of Neena, then display the Employees report from the Number of
Employees column on the Departments report.
To highlight rows in the Employees report:
1. Click the Actions menu and select Format and then Highlight.
The Highlight options appear.
2. For Highlight options make these changes:
a. Name - Enter Neena
Adding a List of Values to the Employees Report
Building Your Application 4-29
b. Background Color - Select [yellow]
c. Text Color - Select [red]
d. Column - Select First Name
e. Operator - Select =
f. Expression - Select Neena
g. Click Apply.
The Employees report appears with the row for Neena highlighted.
3. Next, reset the report.
a. Click the Home breadcrumb.
b. Click Departments link.
c. Click Number of Employees in the Executive row.
The Employees report is displayed with the highlighting cleared, due to the
CIR command specified in the column link.
4. Click the Remove Filter icon to the right of DepartmentID = 90.
5. Click Application ID on the Developer toolbar.
Adding a List of Values to the Employees Report
In this exercise, you add a list of values to the Employees page to display employees
by department. You also change the Employees report query, edit the column link on
the Department report, and turn off pagination.
Topics:
■ Add a List of Values
■ Add an Item to Display List of Values
■ Change Employees Report Query
■ Change Column Link on Department Report
■ Turning Off Pagination
Add a List of Values
In this section, you create a list of values that is used to select which department
details to view. This is a dynamic list that queries the OEHR_DEPARTMENTS table for a
list of all the department names.
Adding a List of Values to the Employees Report
4-30 Oracle Database 2 Day + Application Express Developer's Guide
To create a list of values:
1. Click Shared Components icon.
2. Under User Interface, select Lists of Values.
3. Click the Create button.
4. Accept the default, From Scratch, and click Next.
5. For Name and Type:
a. Name - Enter DEPARTMENTS
b. Type - Select Dynamic.
c. Click Next.
6. For the Query, enter the following SQL:
SELECT department_name d, department_id v
FROM oehr_departments
ORDER BY d
7. Click Create List of Values.
Add an Item to Display List of Values
Now, you create an item in the Department Details region to display the list of
departments.
To add an item:
1. Navigate to the Page Definition for page 3, Employees.
2. Under Region, right-click Employees and select Create Page Item.
3. Select Select List and click Next.
4. For Create Item:
a. Item Name - Enter P3_DEPARTMENT_ID
b. Sequence - Enter 10
c. Region - Select Employee (1) 10
d. Click Next.
5. For Item Attributes:
a. Label - Enter Department
b. Accept the remaining defaults and click Next.
6. For Settings:
a. Value Required - Select No.
b. Page Action when Value Changed - Select Redirect and Set Value.
c. Click Next.
7. For Identify List of Values, make these changes:
a. Named LOV - Select DEPARTMENTS
b. Display Null Option - Select Yes
c. Null Display Value - Enter:
Adding a List of Values to the Employees Report
Building Your Application 4-31
- No Department Assigned -
d. Null Return Value - Enter:
-1
e. Click Next.
8. Click Create Item.
Change Employees Report Query
Now, you change the Employees report query to display employees that belong to the
selected department.
To change the query:
1. Under Regions, right-click Employees and click Edit.
2. Enter the following SQL for Region Source:
SELECT e.EMPLOYEE_ID,
e.FIRST_NAME,
e.LAST_NAME,
e.HIRE_DATE,
e.SALARY,
e.COMMISSION_PCT,
calc_remuneration(salary, commission_pct) REMUNERATION,
e.DEPARTMENT_ID,
d.DEPARTMENT_NAME
FROM OEHR_EMPLOYEES e,
OEHR_DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+) AND
(e.DEPARTMENT_ID = :P3_DEPARTMENT_ID or
(e.DEPARTMENT_ID is null and nvl(:P3_DEPARTMENT_ID,'-1') = '-1'))
3. Click Apply Changes.
Change Column Link on Department Report
Because the Employees query has changed, the Number of Employees column link
needs to initialize the P3_DEPARTMENT_ID item to the value of DEPARTMENT_ID.
To change the column link for Number of Employees:
1. Click the Application ID breadcrumb.
2. Click 2 - Departments.
3. Under Regions, right-click Departments and select Edit Report Attributes.
4. Click the Edit icon for Number of Employees.
5. Scroll down to Column Link.
6. For Item 1, select P3_DEPARTMENT_ID.
7. Click Apply Changes.
Turning Off Pagination
Next, turn off pagination for the Department Details report, because only one record
(the department details report) displays at a time. If pagination is on, the region that
displays the department details includes the information row 1 - 1.
Adding Your Company Name
4-32 Oracle Database 2 Day + Application Express Developer's Guide
To turn off pagination:
1. Click the Application ID breadcrumb.
2. Click 3 - Employees.
3. Under Regions, right-click Employees and select Edit Report Attributes.
4. Scroll down to Pagination.
5. For Pagination Type, select - No Pagination Selected -.
6. Click Apply Changes.
7. Run the page.
8. From the Department list, select No Department Assigned.
Employee Kimberely Grant appears. This employee is not currently assigned to a
department.
Adding Your Company Name
You can add a graphical logo or text to your application so that it displays on each
page. Your page template determines the display location.
In this exercise, you add your company name to your application.
To add your company name to your application:
1. Navigate to the Application home page. Click Application ID on the Developer
toolbar (at the bottom of the page).
2. Click the Edit Application Properties button.
The Edit Application Definition page appears.
3. Under Logo:
a. Logo Type - Select Text.
b. Logo - Enter AnyCo Corp
c. Logo Attributes - Select Black Text from the pop up list.
4. Click Apply Changes.
5. Click the 1 - Home.
6. Click the Run Page icon in the upper right corner.
Tip: If no records initially display, reload the page and try again.
Switching Themes to Change the User Interface
Building Your Application 4-33
The company name now appears on each page in your application.
Switching Themes to Change the User Interface
In this exercise, you switch the current theme to another one for your application. A
theme is a collection of templates that defines the application user interface. Each
theme contains templates for every type of application component and page control,
including individual pages, regions, reports, lists, labels, menus, buttons, and list of
values.
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 the latest designs and other technological
advances without an application rewrite. Each theme provides a complete set of
templates that accommodate every user interface pattern that may be needed in an
application.
To switch the theme:
1. Click the Application link on the Developer toolbar (at the bottom of the page).
2. On the Application home page, click Shared Components.
3. Under User Interface, click Themes.
4. On the Themes page, click Create.
The Create Theme Wizard appears.
5. For Method, accept the default, From the Repository, and click Next.
6. For Identify Theme, select Theme 22 and click Next.
7. For Confirm, click Create.
8. Click Switch Theme.
Switching Themes to Change the User Interface
4-34 Oracle Database 2 Day + Application Express Developer's Guide
9. For Identify Theme, accept the default, 22.Bluejay, and click Next.
10. For Verify Compatibility, review the information, accept the defaults, and click
Next.
The wizard verifies that corresponding templates exist in the selected theme for all
templates used in your current application. If a template is missing, it warns you
with a message in the Status column. For this tutorial, you can ignore this message
because Oracle Application Express maps missing region templates to existing
ones.
11. For Confirm Switch, click Switch Theme.
12. Run the page.
The color scheme and display layouts are changed in all your pages.
About Deploying Your Application 5-1
5
About Deploying Your Application
This section describes basic information about deploying your Oracle Application
Express application. It also shows you how to create end users and to determine the
URL of your application.
Topics:
■ Overview of Deploying Your Application
■ Creating End Users
■ About Publishing the Application URL
Overview of Deploying Your Application
After building your application in your development environment, you need deploy it
so that end users can begin using it. Deploying involves the following steps:
1. Collect your supporting object definitions (CREATE object statements, sample
data, images, files, and so on). Review the Database Object Dependencies report to
determine which supporting objects to move. See "Viewing Dashboards and
Reports" in Oracle Application Express Application Builder User's Guide.
2. Define your supporting objects. This includes the object definitions you collected
in the previous step and other supporting objects, such as messages and
prerequisites.
3. Export the application with its supporting objects.
4. Import and install the exported files.
Using Oracle Application Express greatly simplifies the deployment process. It enables
you to migrate the application definition, associated files, and all supporting objects in
just a few steps.
Creating End Users
After moving your application to a production environment, you must now create
user accounts within Oracle Application Express for all your end users.
To create end user accounts:
1. Navigate to the Workspace home page.
2. Click Administration.
See also: "Deploying an Application" in Oracle Application Express
Application Builder User's Guide.
Creating End Users
5-2 Oracle Database 2 Day + Application Express Developer's Guide
3. Click Manage Users and Groups.
4. Click the Create User button.
The Create User page appears.
5. Under User Identification, enter:
a. Username - Enter the username used to log in to the system. Restrictions
include:
– Maximum length of 100 characters
– No spaces
– Only these special characters are permitted: ampersand (@) and period (.)
b. Email Address - Enter the valid email address for this user.
c. First Name - Enter the first or given name to further identify the user
(optional).
d. Last Name - Enter the last or family name to further identify the user
(optional).
e. Description - Enter comments about this user (optional).
6. Under Account Privileges:
a. Default Schemas - Specify the default schema used for data browsing,
application creation, and SQL script execution.
When using workspaces that have more than one schema available, this
schema is the default. This setting does not control security, only the user's
preference.
b. Accessible Schemas (null for all) - Leave this blank to enable the end user to
access all schemas associated with the workspace.
c. User is a workspace administrator - Specify if this user should have
workspace administrator privileges.
About Publishing the Application URL
About Deploying Your Application 5-3
Administrators are given access to all components. Additionally, they can
manage user accounts, groups, and development services. Components may
not be available if they are switched off by Instance Administrators.
d. User is a developer - Specify if this user should have developer privileges.
Developers must have access to either Application Builder, SQL Workshop, or
both. Components may not be available if they are switched off by Instance
Administrators.
e. Application Builder Access - Determines whether a developer has access to
the Application Builder.
f. SQL Workshop Access - Determines whether a developer has access to the
SQL Workshop.
g. Team Development Access - Determines whether a developer has access to
the Team Development.
h. Set Account Availability - Select Locked to prevent the account from being
used. Select Unlocked to allow the account to be used.
7. Under Password:
■ Password - Enter a case sensitive password.
■ Confirm Password - Enter the password again.
■ Require Change of Password On First Use - Select No to allow the user to use
the same password until it expires. Select Yes to require the user to change the
password immediately when logging in the first time.
8. Under User Groups, optionally select a user group.
9. Click Create User or Create and Create Another.
About Publishing the Application URL
After deploying your application, you must now publish its URL so end users know
how to access it.
To determine the URL:
1. Log in to Oracle Application Express on the production environment.
2. Select the application.
3. On the Application home page, position the mouse over the Run Application
icon.
4. Note the URL that displays in the status bar at the bottom of the page.
For example:
http://apex.oracle.com/pls/apex/f?p=11563:1:3397731373043366363
Where:
■ apex.oracle.com is the URL of the server.
■ pls is the indicator to use the mod_plsql.
See also: "Managing Application Express Users" in Oracle
Application Express Administration Guide
About Publishing the Application URL
5-4 Oracle Database 2 Day + Application Express Developer's Guide
■ apex is the database access descriptor (DAD) name. The DAD describes how
Oracle HTTP Server connects to the database server so that it can fulfill an HTTP
request. The default value is apex.
■ f?p= is a prefix used by Oracle Application Express.
■ 11563 is the application being called.
■ 1 is the page within the application to be displayed.
■ 3397731373043366363 is the session number, which is unique for every user's
access to the application. Do not include session numbers as a part of the URL
communicated to end users.
To run this example application, you would go to this URL:
http://apex.oracle.com/pls/apex/f?p=11563:1
See also: "Deploying an Application" in Oracle Application Express
Application Builder User's Guide
About Globalization 6-1
6
About Globalization
This section describes some basic information about globalization. It also leads you
through the steps to specify how the application determines global identification.
Topics:
■ About Language Identification and Rendering
■ Specifying the Method for Global Identification
About Language Identification and Rendering
You can develop applications in Application Builder that run concurrently in different
languages. This feature enables different users to view the application in their own
language and data in the appropriate formats.
When rendering a page in the application, Oracle Application Express first determines
the language for an application based on the method you specified. Then, the
Application Express engine alters the session language and territory settings for a
specific page request. It then looks for a translated application in the appropriate
language. If the Application Express engine finds that language, it renders the
application using that definition. Otherwise, it renders the application in the base (or
primary) application language.
Even if your application is not translated, the language display determines the format
for displaying data so that end users view numbers, dates, and currency in the
appropriate formats.
Specifying the Method for Global Identification
As part of the application definition settings, you can specify how the application
determines which language and formats to display. You can base it on the user's
browser language preference or restrict it to the language you specify.
To specify the method for determining global identification:
1. Click Application Builder and then select the application.
2. Click Shared Components.
3. Under Globalization, click Globalization Attributes.
4. Under Globalization, specify the following:
See also: "Managing Application Globalization" in Oracle Application
Express Application Builder User's Guide
Specifying the Method for Global Identification
6-2 Oracle Database 2 Day + Application Express Developer's Guide
a. Application Primary Language - Select the language you used when
developing the application.
b. Application Language Derived From - Select Browser (use browser language
preference).
This option enables users to view the application in the language specified in
their browsers.
c. Application Date Format - Determines the date format to be used in the
application.
d. Application Date Time Format - Specify the date time format to be used in the
application.
e. Application Timestamp Format - Determines the timestamp format to be used
in the application.
f. Application Timestamp Time Zone Format - Determines the timestamp with
time zone format to be used in the application.
g. Automatic Time Zone - Controls the setting of the database session time zone.
When set to Yes, the client time zone is derived from the client's Web browser
and set for the duration of the Application Express session.
h. Automatic CSV Encoding - Select Yes or No.
Automatic CSV Encoding controls the encoding of all comma-delimited (CSV)
report output in an application. If Automatic CSV Encoding is set to Yes, CSV
report output is properly converted to a character set compatible with
localized desktop applications. The character set for the CSV encoding is
determined by the Application Language Derived From setting.
For example, the end user whose browser setting is German (de) sees these changes on
the Employees page of AnyCo Corp:
■ The euro currency symbol appears for the amounts in the Salary and
Remuneration columns.
■ The amounts reflect the formatting used in Germany (commas and decimals).
Specifying the Method for Global Identification
About Globalization 6-3
Note that the application fields are not yet translated. For instructions on translating
your application, see "Managing Application Globalization" in Oracle Application
Express Application Builder User's Guide.
Specifying the Method for Global Identification
6-4 Oracle Database 2 Day + Application Express Developer's Guide
Next Steps 7-1
7
Next Steps
Now that you have completed this guide, you are ready to take advantage of other
resources to expand your knowledge of Oracle Application Express and boost your
productivity. This section describes those resources.
Additional Resources
To continue learning about Oracle Application Express, review these additional
resources designed for Application Express users.
Oracle Learning Library
Search for free online training content, including Oracle by Example (OBE), demos,
and tutorials. To access the Oracle Learning Library, go to:
http://www.oracle.com/technetwork/tutorials/index.html
Training Courses
You can attend training courses for Oracle Application Express. To review these
courses, go to the following Web site and enter Application Express in the search
criteria field:
http://education.oracle.com
Oracle Technology Network
Oracle Technology Network (OTN) is a central Web site for Oracle Application Express
resources and information. In addition to downloading the latest product version,
sample code, and packaged applications, the OTN site includes:
■ Discussion forum, where you can post questions and search for solutions
■ How-to documents, which are technical notes written by subject matter experts
■ Quick tour of the product
■ News and magazine articles related to Oracle Application Express
To access the Oracle Application Express page on OTN, go to the following Web site:
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.h
========================================================================
Contents
Preface ............................................................................................................................................................... vii
Topic Overview ......................................................................................................................................... vii
Audience.................................................................................................................................................... viii
Documentation Accessibility ................................................................................................................... viii
Related Documents .................................................................................................................................. viii
Conventions ................................................................................................................................................ ix
What’s New ..................................................................................................................................................... xi
What’s New in this Document for Release 4.1 ........................................................................................ xi
1 Introducing Oracle Application Express
About Oracle Application Express........................................................................................................ 1-1
Understanding Oracle Application Express Architecture................................................................ 1-1
About the Oracle Application Express Environment........................................................................ 1-3
About Workspaces ............................................................................................................................. 1-3
About Oracle Application Express User Roles................................................................................... 1-4
Overview of the Application You Build .............................................................................................. 1-5
2 Accessing Your Development Environment
Before You Begin ..................................................................................................................................... 2-1
Accessing the Hosted Demo Environment.......................................................................................... 2-2
Logging In To the Hosted Demo Environment ............................................................................. 2-3
Setting Up Your Own Local Environment .......................................................................................... 2-3
Logging In To Administration Services ......................................................................................... 2-3
Creating a Workspace....................................................................................................................... 2-5
Logging In To Your Local Instance ................................................................................................. 2-7
Creating Additional User Accounts...................................................................................................... 2-8
3 Getting Started with Oracle Application Express
About the Workspace Home Page......................................................................................................... 3-1
What Is Application Builder? ................................................................................................................ 3-2
Installing the Sample Objects................................................................................................................ 3-2
Downloading OEHR Sample Objects ............................................................................................. 3-3
Importing the OEHR Sample Objects ............................................................................................. 3-3
iv
Checking Your Workspace Storage Space............................................................................... 3-4
Deleting the OEHR Sample Objects Application ................................................................... 3-4
Viewing Your Database Objects....................................................................................................... 3-5
4 Building Your Application
About the Application You Build in This Tutorial ............................................................................ 4-1
Creating the Initial Application ............................................................................................................ 4-2
Previewing Your Application........................................................................................................... 4-5
Modifying the Departments Report ..................................................................................................... 4-7
Run the Report to View Added Columns ...................................................................................... 4-9
Using an Interactive Report................................................................................................................. 4-11
Save Report Settings as the Default.............................................................................................. 4-11
Create a Filter.................................................................................................................................. 4-12
Change the Sort Order.................................................................................................................... 4-13
Add a Controlled Break ................................................................................................................. 4-14
Display the Single Row View........................................................................................................ 4-15
Reset Report Settings ...................................................................................................................... 4-16
Adding an Employees Report and Form........................................................................................... 4-16
Previewing the Pages...................................................................................................................... 4-18
Editing the Employees Report ............................................................................................................ 4-19
Create a Function ............................................................................................................................ 4-19
Add a Column ................................................................................................................................ 4-20
Previewing the Page ....................................................................................................................... 4-21
Changing the Column Format ...................................................................................................... 4-22
Adding a Link on the Home Page to the Employees Report ........................................................ 4-23
Adding a Department Column to the Employees Report ............................................................. 4-24
Add a Column ................................................................................................................................ 4-24
Display Column in the Report ...................................................................................................... 4-24
Save Report Settings as the Default.............................................................................................. 4-25
Linking a Column Value to Another Page........................................................................................ 4-26
Change Column Value to a Link................................................................................................... 4-26
Run the Departments Report ........................................................................................................ 4-27
Create a Highlight to Test Clear Interactive Report Command............................................... 4-28
Adding a List of Values to the Employees Report .......................................................................... 4-29
Add a List of Values ....................................................................................................................... 4-29
Add an Item to Display List of Values......................................................................................... 4-30
Change Employees Report Query ............................................................................................... 4-31
Change Column Link on Department Report ........................................................................... 4-31
Turning Off Pagination .................................................................................................................. 4-31
Adding Your Company Name............................................................................................................. 4-32
Switching Themes to Change the User Interface............................................................................ 4-33
5 About Deploying Your Application
Overview of Deploying Your Application ......................................................................................... 5-1
Creating End Users.................................................................................................................................. 5-1
About Publishing the Application URL.............................................................................................. 5-3
v
6 About Globalization
About Language Identification and Rendering ................................................................................. 6-1
Specifying the Method for Global Identification.............................................................................. 6-1
7 Next Steps
Additional Resources .............................................................................................................................. 7-1
vi
vii
Preface
Oracle Database 2 Day + Application Express Developer's Guide introduces you to
application development using Oracle Application Express. This guide shows you
how to set up a development environment, or access a hosted demonstration
environment. It then walks you through building an initial application, modifying it,
and previewing it.
Through a series of exercises, you become familiar with the Oracle Application
Express user interface, basic product functionality, and some underlying concepts that
enable you to develop professional, fast, and secure applications.
Topics:
■ Topic Overview
■ Audience
■ Documentation Accessibility
■ Related Documents
■ Conventions
Topic Overview
This document contains the following sections:
Title Description
Introducing Oracle Application
Express
Describes Oracle Application Express, its architecture
and the environment for using the product. It also
introduces you to some concepts and terms used in this
guide.
Accessing Your Development
Environment
Leads you through the steps to access a development
environment for your use.
Getting Started with Oracle
Application Express
Introduces you to areas of the user interface. It also leads
you through installing the sample objects to use with this
guide.
Building Your Application Leads you through the steps for building an application,
modifying it, and previewing it at different points during
the development process.
About Deploying Your Application Describes some basic information about deploying your
application. It also leads you through the steps to create
end users and to determine the URL of your application.
viii
Audience
Oracle Database 2 Day + Application Express Developer's Guide is intended for:
■ Workspace administrators who want to set up the Oracle Application Express
development environment
■ Application developers who want to learn how to build database-centric Web
applications using Oracle Application Express
To use this guide, you must have a general understanding of relational database
concepts and the operating system environment under which Oracle Application
Express is running.
Documentation Accessibility
For information about Oracle's commitment to accessibility, visit the Oracle
Accessibility Program website at
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For
information, visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are
hearing impaired.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The
conventions for writing code require that closing braces should appear on an
otherwise empty line; however, some screen readers may not always read a line of text
that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or
organizations that Oracle does not own or control. Oracle neither evaluates nor makes
any representations regarding the accessibility of these Web sites.
Related Documents
For more information, see these Oracle resources:
■ Oracle Application Express Installation Guide
■ Oracle Application Express Release Notes
■ Oracle Application Express End User's Guide
About Globalization Describes some basic information about globalization. It
also leads you through the steps to specify how the
application determines global identification.
Next Steps Describes additional resources to expand your
knowledge of Oracle Application Express and boost your
productivity.
Title Description
ix
■ Oracle Application Express Application Builder User's Guide
■ Oracle Application Express SQL Workshop Guide
■ Oracle Application Express API Reference
■ Oracle Application Express Migration Guide
■ Oracle Application Express Administration Guide
■ Oracle Database Concepts
■ Oracle Database Advanced Application Developer's Guide
■ Oracle Database Administrator's Guide
■ Oracle Database SQL Language Reference
■ SQL*Plus User's Guide and Reference
■ Oracle Database PL/SQL Language Reference
For additional documentation available on Oracle Technology Network (OTN), visit
the Oracle Application Express Web site located at:
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html
For information about Oracle error messages, see Oracle Database Error Messages.
Oracle error message documentation is available only in HTML. If you have access to
the Oracle Database Documentation Library, you can browse the error messages by
range. Once you find the specific range, use your browser's "Find in Page" feature to
locate the specific message. When connected to the Internet, you can search for a
specific error message using the error message search feature of the Oracle online
documentation.
Many books in the documentation set use the sample schemas of the seed database,
which is installed by default when you install Oracle. See Oracle Database Sample
Schemas for information about how these schemas were created, and how you can use
them yourself.
For additional application examples, go to the Learning Library. Search for free online
training content, including Oracle by Example (OBE), demos, and tutorials. To access
the Oracle Learning Library, go to:
http://www.oracle.com/technetwork/tutorials/index.html
Printed documentation is available for sale in the Oracle Store at:
http://shop.oracle.com/
If you have a user name and password for OTN, then you can go directly to the
documentation section of the OTN Web site at:
http://www.oracle.com/technetwork/indexes/documentation/index.html
Conventions
The following text conventions are used in this document:
Convention Meaning
boldface Boldface type indicates graphical user interface elements associated
with an action, or terms defined in text or the glossary.
x
italic Italic type indicates book titles, emphasis, or placeholder variables for
which you supply particular values.
monospace Monospace type indicates commands within a paragraph, URLs, code
in examples, text that appears on the screen, or text that you enter.
Convention Meaning
xi
What’s New
This section summarizes updates made to Oracle Application Express Administration
Guide for release 4.1.
What’s New in this Document for Release 4.1
This section summarizes general updates, revised content, and new content added to
Oracle Database 2 Day + Application Express Developer's Guide for release 4.1.
General Updates
■ All content has been updated to reflect new functionality
■ Screen captures and graphics have been added and updated to reflect release 4.1
user interface enhancements
xii
Introducing Oracle Application Express 1-1
1
Introducing Oracle Application Express
This section describes Oracle Application Express, its architecture, and the
environment for using the product. It also introduces you to concepts and terms used
in this guide when discussing Oracle Application Express.
Topics:
■ About Oracle Application Express
■ Understanding Oracle Application Express Architecture
■ About the Oracle Application Express Environment
■ About Oracle Application Express User Roles
■ Overview of the Application You Build
About Oracle Application Express
Oracle Application Express is a rapid Web application development tool for the Oracle
database. Using only a Web browser and limited programming experience, you can
develop professional applications that are both fast and secure. 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.
From the end user’s perspective, the deployed applications require only a browser and
access to an Oracle database running Application Express.
Understanding 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 run 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 request or submit a
page.
The application session state is managed in the database tables within Oracle
Application Express. It does not use a dedicated database connection. Instead, each
Tip: To learn more about Oracle Application Express features, click
the Learn more... button on the Workspace home page.
Understanding Oracle Application Express Architecture
1-2 Oracle Database 2 Day + Application Express Developer's Guide
request is made through a separate database session, consuming minimal CPU
resources.
Understanding Web Server Options
In order to run, Oracle Application Express must have access to Oracle Application
Express Listener, Oracle HTTP Server and mod_plsql, or the embedded PL/SQL
gateway.
About Oracle HTTP Server (Apache) and Oracle Application Express Listener
Oracle HTTP Server uses the mod_plsql plug-in to communicate with the Oracle
Application Express engine within the Oracle database. Oracle Application Express
Listener communicates directly with the Oracle Application Express engine, thus
eliminating the need for the mod_plsql plug-in.
Both Oracle HTTP Server and the Oracle Application Express Listener function as
communication brokers between the Web server and the Oracle Application Express
objects in the Oracle database. More specifically, they map browser requests into
database stored procedure calls over a Oracle Net Services connection. The following
graphic illustrates the Oracle Application Express architecture using Oracle HTTP
Server and mod_plsql.
Note that this three tier architecture consists of the following components:
■ A Web browser
■ Oracle HTTP Server (Apache) with mod_plsql or Oracle Application Express
Listener
■ An Oracle database containing Oracle Application Express
Advantages of either Oracle HTTP Server (Apache) with mod_plsql or Oracle
Application Express Listener include:
■ Separation from mid-tier for the database tier
■ Appropriate for Oracle Real Application Clusters (Oracle RAC) environments
■ Versions prior to Oracle Database 11g Release 1 require Oracle Application Express
Listener or Oracle HTTP Server (Apache) with mod_plsql
About the Embedded PL/SQL Gateway
The embedded PL/SQL gateway installs with in the Oracle database. It 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 Oracle XML DB
HTTP server in the Oracle database and includes the core features of mod_plsql. The
following graphic illustrates the Oracle Application Express architecture using the
embedded PL/SQL gateway.
About the Oracle Application Express Environment
Introducing Oracle Application Express 1-3
As shown in the previous graphic, the embedded PL/SQL gateway is a simple two tier
architecture and consists of these components: a Web browser and an Oracle database
containing the embedded PL/SQL, and Oracle Application Express.
Advantages of the embedded PL/SQL gateway:
■ Ease of configuration
■ Included in the database
■ No separate server installation
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
About the Oracle Application Express Environment
Oracle Application Express enables a single Oracle database to become a shared
workgroup database service. Multiple users can access it using a Web browser without
installing additional software.
About Workspaces
The area where you develop applications is 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.
In a typical development environment, you might create a single workspace for all
your developers to share. However, you can also create dedicated workspaces for
specific developers or projects. Creating a dedicated workspace limits access to the
workspace objects to only those users associated with the workspace.
The following illustration shows the relationship among users and developers,
workspaces, and database schemas.
See Also: "Understanding Administrator Security Best Practice" in
Oracle Application Express Application Builder User's Guide
About Oracle Application Express User Roles
1-4 Oracle Database 2 Day + Application Express Developer's Guide
When you create a workspace, you associate it with a new or existing schema. A
schema is a logical container for database objects such as tables, views, and stored
procedures. A single schema can be associated with one or more workspaces.
About Oracle Application Express User Roles
When setting up Application Express users at a large organization, you assign roles
and privileges to specific users. The roles within Oracle Application Express include
the following:
■ End users have no development privileges. You define end users so that they can
access applications that do not use an external authentication scheme.
■ Developers are users who create and edit applications and modify database
objects. Developers can have their own workspace or share a workspace.
■ Workspace administrators are users who perform administrator tasks specific to a
workspace such as managing user accounts, monitoring workspace activity, and
viewing log files.
■ Instance administrators are superusers that manage an entire hosted instance
using the Application Express Administration Services application.
The following illustration shows multiple users with various roles accessing the Oracle
Application Express development environment, Application Express Administration
Services, and the published applications.
Overview of the Application You Build
Introducing Oracle Application Express 1-5
For this guide, you must have the privileges usually associated with three roles:
Instance Administrator, Workspace Administrator, and Developer. The following
illustration shows an Oracle Application Express instance with a single user.
Overview of the Application You Build
In "Building Your Application" on page 4-1, you create a simple Human Resources
(HR) application for a fictitious company called AnyCo Corp. The application manages
departmental and employee information stored in the department and employees
tables that you create. The tutorial shows you how to build the application, modify it,
and then preview it.
Overview of the Application You Build
1-6 Oracle Database 2 Day + Application Express Developer's Guide
Accessing Your Development Environment 2-1
2
Accessing Your Development Environment
This section guides you through the steps to access an Oracle Application Express
development environment for your use. Then, you and other developers can begin
building applications within Oracle Application Express.
Topics:
■ Before You Begin
■ Accessing the Hosted Demo Environment
■ Setting Up Your Own Local Environment
■ Creating Additional User Accounts
Before You Begin
Before you begin this section, do the following:
■ Install Oracle Application Express as described in Oracle Application Express
Installation Guide.
■ Read "Introducing Oracle Application Express" on page 1-1.
■ Confirm your Web browser supports JavaScript and the HTML 4.0 and Cascading
Style Sheet (CSS) 1.0 standards. The following browsers meet this requirement:
– Microsoft Internet Explorer 7.0 or later version
– Mozilla Firefox 3.6 or later version
– Google Chrome 4.0 or later version
– Apple Safari 4.0 or later version
■ Enable cookies for your Web browser.
■ Determine which environment you want to use:
– Oracle provides a hosted demonstration (demo) environment where you can
try out Oracle Application Express.
Using the demo environment is the fastest way to access a development
environment. You request a workspace from an Oracle Application Express
Web site and provide some basic user information. Shortly after you submit
your request, you receive your login credentials in an email. This option does
not require installing any software.
For instructions on using the demo environment, see "Accessing the Hosted
Demo Environment" on page 2-2.
Accessing the Hosted Demo Environment
2-2 Oracle Database 2 Day + Application Express Developer's Guide
– Set up your own local instance of Oracle Application Express. Istall Oracle
Application Express and then follow the steps in "Setting Up Your Own Local
Environment" on page 2-3.
Accessing the Hosted Demo Environment
To use Oracle Application Express without installing the product locally, go to the
hosted demo environment and sign up for a workspace.
To access the hosted demo environment:
1. In a Web browser, go to:
http://apex.oracle.com
2. Click the Get Started button.
3. On Welcome, review the information and click Next.
4. Identify the administrator for the service. Enter a first name, last name, and valid
email address and click Next.
The system generates an email with your login credentials and sends it to the
email address you enter.
5. For Workspace, enter a workspace name.
Workspace names must be unique. Requesting a duplicate name might delay your
request. In general, enter a name that is significant to you. For example, enter your
own name, your company name, or the name of the project or department to
which your applications pertain.
To use the same name for your workspace and your schema (defined in the next
step) use alphabetic characters when naming your workspace. Schema names are
restricted to alphabetic characters.
6. Specify a schema schema. Options include:
a. Use an existing schema from the database - Enter a the existing schema
name.
b. Request a new schema - Enter a name containing only alphabetic characters.
c. Initial Space Allocation - Select at least 10 MB to accommodate the space
needed for creating the objects required for this guide.
d. Click Next.
A survey appears.
7. Answer the survey questions and click Next.
8. Indicate why you are requesting this service and click Next.
A license agreement apears.
9. Review the agreement, select I accept the terms, and click Next.
10. Click Submit Request.
Note: Do not use the hosted demonstration environment for your
production work.
Setting Up Your Own Local Environment
Accessing Your Development Environment 2-3
An initial email containing a link is sent to you. Click the link within the email to
complete the workspace provisioning process. This step verifies that your email
address is valid before approving a workspace. After that, an email with your login
credentials is sent to you. Use those credentials to log in to the hosted demo
environment and begin using Oracle Application Express.
Logging In To the Hosted Demo Environment
Using the login credentials you received in an email, log in to your dedicated
workspace on the hosted demo environment.
To log in to the hosted demo environment:
1. In a Web browser, go to:
http://apex.oracle.com
2. On the Login page, enter your login credentials.
3. Click Login.
The Workspace home page appears. You may be prompted to change your
password. If not, you can change it manually by clicking the Change Password
link on the right side of the Workspace home page. Then, enter in the information
requested on the page that appears.
To continue with the tutorials, skip the rest of this section and go to "Getting
Started with Oracle Application Express" on page 3-1.
Setting Up Your Own Local Environment
Instead of using the hosted demo environment, you can install Oracle Application
Express locally. If you do a local installation, you must set up your development
environment. The general steps you must perform include:
1. Install Oracle Application Express as described in Oracle Application Express
Installation Guide.
2. Log in to Application Express Administration Services, as described in "Logging
In To Administration Services" on page 2-3.
3. Create a workspace, as described in "Creating a Workspace" on page 2-5.
4. (Optional) Create additional users with developer or administrator privileges, as
described in "Creating Additional User Accounts" on page 2-8.
Logging In To Administration Services
To set up your local development environment, you first must log in to Oracle
Application Express Administration Services. This is a separate application for
managing an entire Oracle Application Express instance.
To log in to the Oracle Application Express Administration Services:
Note: This tutorial explains a simplified method for setting up your
development environment so that you can begin using Oracle
Application Express. However, when you want to set up your
development environment to accommodate your real-life
development needs, review “Oracle Application Express Hosted
Instance Administration" in Oracle Application Express Administration
Guide.
Setting Up Your Own Local Environment
2-4 Oracle Database 2 Day + Application Express Developer's Guide
1. In a Web browser, go to the Oracle Application Express Administration Services
application. Depending on which setup you are using, Oracle Application Express
is installed in one of these locations:
■ If your setup uses the embedded PL/SQL gateway:
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.
– apex is the database access descriptor (DAD) defined in the configuration
file.
For users who have upgraded from earlier versions, 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 the Oracle Application Express Listener, go to:
http://hostname:port/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, for Oracle WebLogic Server this number is 7001, in OC4J this
number is 8888, in Oracle Glassfish Server this number is 8080. These
defaults are correct at the time this document was written. Please review
the related Web server documentation for the latest default port if
necessary.
– apex is the mount point defined in the Web Server configuration file.
■ If your setup uses the Oracle HTTP Server (Apache) with mod_plsql:
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. 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
See Also: "Verifying the Oracle XML DB HTTP Server Port" in Oracle
Application Express Application Builder User's Guide
See Also: Oracle Application Express Listener documentation
Setting Up Your Own Local Environment
Accessing Your Development Environment 2-5
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 versions, or who have a custom
configuration, this value may be htmldb or something else. Verify the
DAD with your Oracle Application Express administrator.
The Login page appears.
2. In Username, enter the username you specified when you installed Oracle
Application Express.
3. In Password, enter the Oracle Application Express administrator account
password you specified when you installed Oracle Application Express.
4. Click Login.
Oracle Application Express Administration Services appears.
Creating a Workspace
From Administration Services, create a workspace. A workspace is a shared work area
within the Oracle Application Express development environment that has a unique ID
and name. All users in the workspace can access all its applications and scripts. For
more information about workspaces, see "About Workspaces" on page 1-3.
To create a workspace:
1. Log in to Oracle Application Express Administration Services.
The Instance Administration Home page appears.
2. Click Manage Workspaces.
3. Under Workspace Actions, click Create Workspace.
See Also: "Managing Oracle Database Port Numbers" in Oracle
Application Express Installation Guide and ORACLE_BASE\ORACLE_
HTTPSERVER_HOME\Apache\modplsql\conf\dads.readme for
more information on database access descriptors
Setting Up Your Own Local Environment
2-6 Oracle Database 2 Day + Application Express Developer's Guide
The Create Workspace Wizard appears.
4. For Identify Workspace, enter the following:
a. Workspace Name - Enter a unique workspace name. This guide uses
2DayPlus for the workspace name.
In general, you want to enter a workspace name that is significant to you. For
example, you might enter your own name, your company name, or the name
of the project or department to which your application pertains.
If you want to use the same name for both your workspace and schema, enter
a workspace name that contains only alphabetic characters. Schema names are
restricted to alphabetic characters.
b. Workspace ID - Leave Workspace ID blank to have the new Workspace ID
automatically generated. A Workspace ID must be a positive integer greater
than 100000.
c. Workspace Description - Enter a workspace description.
d. Click Next.
5. For Identify Schema, enter the following:
a. Re-use existing schema - Select No if you are creating your first workspace.
A schema is a logical container for database objects. In this step, you are
creating the container for database objects you add later in this tutorial.
b. Schema Name - Enter a schema name. This guide uses TWODAYPLUS for the
schema name.
You must use only alphabetic characters in your schema name.
c. Schema Password - Enter a case-sensitive password.
d. Space Quota (MB) - Select at least 10 MB to accommodate the necessary space
for creating the objects required for this guide.
Setting Up Your Own Local Environment
Accessing Your Development Environment 2-7
e. Click Next.
6. For Identify Administrator, enter the following:
a. Administrator Username - Accept the default, ADMIN, or enter another user
name to be used in Oracle Application Express.
You are creating an account for only one user, yourself as an administrator. To
see instructions for creating additional users, see "Creating Additional User
Accounts" on page 2-8.
b. Administrator Password - Enter a case-sensitive password.
c. Enter the remaining information, including your valid email address, and click
Next.
The login credentials for accessing Oracle Application Express are sent to the
email address you enter here, if you have configured the email settings for
your Oracle Application Express environment.
7. Confirm your selections and click Create Workspace.
Logging In To Your Local Instance
To log in to your local instance of Oracle Application Express, you must have this
information:
■ URL where you can access Oracle Application Express
■ Workspace name you use to develop applications
■ Username set up for you for this workspace within Oracle Application Express
■ Password set up for you for this workspace within Oracle Application Express
If you set up your development environment, you have your login credentials.
Otherwise, contact the person who created your user account to supply the
information.
To log in to Oracle Application Express:
Creating Additional User Accounts
2-8 Oracle Database 2 Day + Application Express Developer's Guide
1. In a Web browser, go to the Oracle Application Express Login page. Depending on
your setup, Oracle Application Express installs to one of these locations:
■ If your setup uses the embedded PL/SQL gateway or the Oracle Application
Express Listener:
http://hostname:port/apex
■ If your setup uses the Oracle HTTP Server (Apache) with mod_plsql:
http://hostname:port/pls/apex
For an explanation of the URLs, see "Logging In To Administration Services" on
page 2-3.
The Login page appears.
2. Under Login, enter your login credentials.
3. Click Login.
Creating Additional User Accounts
As an administrator, you inherit all the privileges associated with the developer role. If
you want to start developing the application shown in this guide, skip this section and
go to "Logging In To Your Local Instance" on page 2-7.
However, you might want to create user accounts for other developers who share this
workspace. Associating users with the workspace grants them access to all
applications and scripts within that workspace. You can also create additional user
accounts for workspace administrators.
To create a user account:
1. Log in to the workspace where you want to add users. See "Logging In To the
Hosted Demo Environment" on page 2-3 or "Logging In To Your Local Instance"
on page 2-7.
The Workspace home page appears.
2. Click Administration.
3. Click Manage Users and Groups.
4. Click Create User.
The Create User page appears.
Creating Additional User Accounts
Accessing Your Development Environment 2-9
5. Under User Identification, enter:
a. Username - Enter the username used to log in to the system. Restrictions
include:
– Maximum length of 100 characters
– No spaces
– Only these special characters are permitted: ampersand (@) and period (.)
b. Email Address - Enter the valid email address for this user.
c. First Name - Enter the first or given name to further identify the user
(optional).
d. Last Name - Enter the last or family name to further identify the user
(optional).
e. Description - Enter comments about this user (optional).
6. Under Account Privileges:
a. Default Schema - Specify the default schema used for data browsing,
application creation, and SQL script execution.
When using workspaces that have more than one schema available, this
schema is the default. This setting does not control security, only the user's
preference.
b. Accessible Schemas (null for all) - Leave this blank to enable the end user to
access all applications in the workspace, or enter a colon-delimited list of
schemas for which this user has permissions when using the SQL Workshop.
c. User is a workspace administrator - Specify if this user should have
workspace administrator privileges.
Administrators are given access to all components. Additionally, they can
manage user accounts, groups, and development services. Components may
not be available if they are switched off by Instance Administrators.
d. User is a developer - Specify if this user should have developer privileges.
Creating Additional User Accounts
2-10 Oracle Database 2 Day + Application Express Developer's Guide
Developers must have access to either Application Builder, SQL Workshop, or
both. Components may not be available if they are switched off by Instance
Administrators.
e. Application Builder Access - Determines whether a developer has access to
the Application Builder.
f. SQL Workshop Access - Determines whether a developer has access to the
SQL Workshop.
g. Team Development Access - Determines whether a developer has access to
the Team Development.
h. Set Account Availability - Select Locked to prevent the account from being
used. Select Unlocked to allow the account to be used.
7. Under Password:
■ Password - Enter a case sensitive password.
■ Confirm Password - Enter the password again.
■ Require Change of Password On First Use - Select No to not require this user
to change his or her password at first log in. Select Yes to require the user to
change the password immediately after logging in the first time.
8. Under User Groups, optionally select a user group.
9. Click Create User or Create and Create Another.
See also: "Managing Application Express Users" in Oracle
Application Express Administration Guide
Getting Started with Oracle Application Express 3-1
3
Getting Started with Oracle Application
Express
This section introduces you to areas of the Oracle Application Express user interface. It
also shows you how to install the sample objects to use with this guide.
Before you begin, log in to Oracle Application Express. For instructions, see "Accessing
Your Development Environment" on page 2-1.
Topics:
■ About the Workspace Home Page
■ What Is Application Builder?
■ Installing the Sample Objects
About the Workspace Home Page
When you log in to Oracle Application Express, the Workspace home page appears.
Take a few minutes to explore the product. The following 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.
■ SQL Workshop. Use the SQL Workshop to access tools for viewing and managing
database objects.
■ Team Development. Use Team Development to track new features, non-feature to
do tasks, bugs, and milestones. Users can also provide real-time feedback which
then can be categorized into features, to do tasks, or bugs.
■ Administration links to the Workspace Administration page.
What Is Application Builder?
3-2 Oracle Database 2 Day + Application Express Developer's Guide
About the Learn More Link
Click the Learn More link in the About box to view an informative introduction to
Oracle Application Express. Topics covered include architecture, self service, security,
Websheets, application development, SQL Workshop, Team Development,
administration, and managing development.
What Is Application Builder?
You use the Application Builder component of Oracle Application Express to build
your application. Through wizards or your direct input, you can assemble an HTML
interface (or application) on top of database objects such as tables and procedures.
Each application is a collection of linked pages using tabs, buttons, or hypertext links.
A page is the basic building block of an application. Pages also contain user interface
elements such as tabs, lists, buttons, items, and regions. To see the definition of each
page belonging to your application, you use the Page Definition page.
The Page Definition contains three main areas:
■ Page Rendering lists user interface controls and logic that are executed when a
page is rendered. Page rendering is the process of generating a page from the
database.
■ Page Processing lists logic controls (such as computations and processes) that are
evaluated and executed when the page is processed.
■ Shared Components lists components used by the current page that can also be
referenced by other pages within your application.
Installing the Sample Objects
In Oracle Application Express, users log in to a workspace. You can think of each
workspace as a shared work area that separates your objects, data, and applications
into a virtual private database.
Before performing any tasks in this guide, you must create the appropriate sample
objects within your workspace. These sample objects are copies of the objects that are
typically installed in two schemas:
■ Human Resources (HR)
The HR schema contains information about the employees and the facilities where
they work. Each employee has an identification number, email address, job
identification code, salary, and manager. Employees are assigned to a department,
and each department is associated with one location that has a full address,
including the street name, postal code, city, state or province, and country code.
■ Order Entry (OE)
The OE schema tracks product inventories and sales of a company's products,
including the product identification number, the product name, the associated
associates product category, product descriptions, the weight group (for shipping
purposes), the warranty periods, the suppliers, the availability status, and a
minimum price.
To create the objects locally in your workspace, import the OEHR Sample Objects
application.
Topics:
■ Downloading OEHR Sample Objects
Installing the Sample Objects
Getting Started with Oracle Application Express 3-3
■ Importing the OEHR Sample Objects
■ Viewing Your Database Objects
Downloading OEHR Sample Objects
To import the OEHR Sample Objects application, you must download it from the Oracle
Technology Network (OTN).
To download the OEHR Sample Objects application from OTN:
1. In your Web browser, go to:
http://www.oracle.com/technetwork/testcontent/oehr-sample-objects-1
31098.zip
2. Under Sample Code, click OEHR Sample Objects.
3. Download and save the oehr_sample_objects.zip file to your computer.
4. Unzip and extract the oehr_sample_objects_installer.sql file:
■ Microsoft Windows - Double-click the oehr_sample_objects.zip file
■ UNIX or Linux - Enter the following command:
$ unzip oehr_sample_objects.zip
Importing the OEHR Sample Objects
After you download and unzip the OEHR Sample Objects application, import it into
Oracle Application Express. During the import process, specify installation of both the
application and the supporting objects. Installing the application creates the objects
and sample data needed to complete the exercises in this guide.
To import and install the OEHR Sample Objects application:
1. Log in to Oracle Application Express.
For instructions, see "Logging In To the Hosted Demo Environment" on page 2-3
or "Logging In To Your Local Instance" on page 2-7.
2. On the Workspace home page, click Application Builder.
The Application Builder home page appears.
3. Click the Import button.
4. For Specify File, specify the following:
a. Import file - Click Browse and navigate to the oehr_sample_objects_
installer.sql file.
b. File Type - Select Database Application, Page, or Component Export.
c. File Character Set - Select the correct character set.
d. Click Next.
Tip: In order to successfully import the objects associated with the
OEHR Sample Objects application, your Oracle database must include
Oracle Spatial. If your database instance does not include Oracle
Spatial, you can install it using Database Configuration Assistant. To
learn more, see the Oracle Application Express Installation Guide for your
operating environment.
Installing the Sample Objects
3-4 Oracle Database 2 Day + Application Express Developer's Guide
Next, you install the imported file.
5. To install an imported file, click Next.
The Install Database Application Wizard appears.
6. For Install Application, specify the following:
a. Parsing Schema - Select the schema you created when you set up your
development environment.
b. Build Status - Select Run and Build Application.
c. Install As Application - Select Auto Assign New Application ID.
d. Click Install.
Once you install the application, you must install the supporting objects.
7. For Install Supporting Objects, select Yes and click Next.
8. Confirm your selections by clicking Install.
9. After the installation is completed, click the Home breadcrumb link at the top of
the page.
The Application Builder home page appears.
Checking Your Workspace Storage Space
If you experience problems installing the OEHR Sample Objects application, verify the
available space in your workspace. If you do not have enough space, request
additional storage space.
If you are a workspace administrator, you can:
■ Determine if you have enough storage space. See "About the Workspace
Utilization Report" in Oracle Application Express Administration Guide.
■ Request additional storage space. See "Requesting a New Schema, More Storage,
or Terminating Service" in Oracle Application Express Administration Guide.
Deleting the OEHR Sample Objects Application
Deleting the OEHR Sample Objects application and deinstalling the supporting objects
completely removes all associated objects and sample data.
To delete the OEHR Sample Objects application:
1. Log in to Oracle Application Express.
2. On the Workspace home page, click Application Builder.
The Application Builder home page appears.
3. Click the OEHR Sample Objects icon.
4. On the Tasks list, click Delete this Application.
The Deinstall page appears.
5. To remove all associated objects and sample data, select Remove Application
Definition and Deinstall Supporting Objects.
6. Click Deinstall.
Installing the Sample Objects
Getting Started with Oracle Application Express 3-5
Viewing Your Database Objects
Review the objects you just created by going to Object Browser. Object Browser
enables you to browse, create, and edit objects in your database.
To view the objects:
1. On the Workspace home page, click SQL Workshop.
2. Click Object Browser.
Object Browser appears.
Object Browser is divided into two sections:
■ Object Selection pane displays on the left side of the Object Browser page and
lists database objects of a selected type within the current schema.
■ Detail pane displays to the right of the page and displays detailed information
about a selected object.
3. From the list in the Object Selection pane on the left, select Tables.
The Detail pane lists the tables in your current schema.
4. In the Object Selection pane, click OEHR_EMPLOYEES from the list.
The Detail pane shows details about the table.
5. Click the Data tab at the top of the Details pane.
The data in the OEHR_EMPLOYEES table appears. Note that other tabs show
additional details about the object you select.
6. To search for an object name, enter a case-insensitive term in the Search field.
Installing the Sample Objects
3-6 Oracle Database 2 Day + Application Express Developer's Guide
7. To view all objects, leave the Search field blank.
See also: "Managing Database Objects with Object Browser" in
Oracle Application Express SQL Workshop Guide
Building Your Application 4-1
4
Building Your Application
In this tutorial, you create a Human Resources (HR) application for a fictitious
company called AnyCo Corp. This section shows you how to build the application,
modify it, and preview it at different points during the development process.
Before you begin this section:
■ Read "Getting Started with Oracle Application Express" on page 3-1.
■ Ensure you have installed the database objects as described in "Installing the
Sample Objects" on page 3-2.
Topics:
■ About the Application You Build in This Tutorial
■ Creating the Initial Application
■ Using an Interactive Report
■ Modifying the Departments Report
■ Adding an Employees Report and Form
■ Editing the Employees Report
■ Adding a Link on the Home Page to the Employees Report
■ Adding a Department Column to the Employees Report
■ Linking a Column Value to Another Page
■ Adding a List of Values to the Employees Report
■ Adding Your Company Name
■ Switching Themes to Change the User Interface
About the Application You Build in This Tutorial
This tutorial guides you through the development of a Human Resources (HR)
application for a fictitious company called AnyCo Corp. The application enables users
to view and modify employee information stored in the OEHR_DEPARTMENTS and
OEHR_EMPLOYEES tables.
When building the application, you perform these tasks:
1. Creating the Initial Application: Create an application that includes a Home page
and a basic Departments report.
Creating the Initial Application
4-2 Oracle Database 2 Day + Application Express Developer's Guide
2. Modifying the Departments Report: Expand the report to pull in data from other
tables.
3. Using an Interactive Report: Run and use the Interactive Report.
4. Adding an Employees Report and Form: Build an Employee form and report
based on the OEHR_EMPLOYEES table.
5. Editing the Employees Report: Add calculated fields to the Employees report.
6. Adding a Link on the Home Page to the Employees Report: Add a link on the
Home page.
7. Adding a Department Column to the Employees Report: Modify the Employees
report to enable the user to select a department.
8. Linking a Column Value to Another Page: Add a link on the Departments report
that navigates the user to the Employees report.
9. Adding a List of Values to the Employees Report: Add a report as a region that
displays department details.
10. Adding Your Company Name: Add your company name so that it appears on all
pages in your application.
11. Switching Themes to Change the User Interface: Switch the theme for your
application.
Creating the Initial Application
This first exercise guides you through creating your initial application. Using the
Create Application Wizard, you create two pages and specify basic application
functionality.
The pages created in this section include:
■ Home - This page is the first page displayed when the application is run. This
page contains a link to the Departments report.
■ Departments - This page is an interactive report of all departments.
There are two types of reports, interactive and classic. Interactive is the default
when creating applications, converting forms and creating report regions.
Interactive reports should be selected when you want to provide user
customizations such as filters, sorting, compilation and so on. If you have limited
need for this functionality, you are best served by using a classic report.
To create your initial application:
1. Log in to Oracle Application Express.
For instructions, see "Logging In To the Hosted Demo Environment" on page 2-3
or "Logging In To Your Local Instance" on page 2-7.
2. On the Workspace home page, click Application Builder.
3. Click the Create button.
The Create Application Wizard appears.
Creating the Initial Application
Building Your Application 4-3
Note that each page within a wizard displays the title of the wizard (for example,
Create Application). Each wizard page also displays a series of blocks on the left,
representing the sequence of steps. The highlighted block indicates the step you
are performing.
To orient you, each step in these exercises generally begin with the block label. For
example, the first step in the Create Application Wizard highlights the word
Method. Therefore, the next step orients you by beginning with the words For
Method.
4. For Method, select Database and click Next.
5. Accept the default, From Scratch, and click Next.
6. For Name:
a. Name - Enter AnyCo Corp
b. Application - Accept the default ID. The system provides a unique ID for each
application you create in this workspace.
c. Create Application - Accept the default, From scratch.
d. Schema - Select the schema that contains the objects for this tutorial (for
example, TWODAYPLUS).
e. Click Next.
Next, add a page to your application.
7. For Pages, specify the following in the Add Page section:
a. Select Page Type - Accept the default, Blank.
b. Page Name - Enter Home.
Tip: Explanatory information for each option in item Help. When
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.
Creating the Initial Application
4-4 Oracle Database 2 Day + Application Express Developer's Guide
c. Click Add Page.
The Home page is now listed in the top area. Also, note that the Add Page
area appears so you can continue adding pages to your initial application.
Next, add a report page on the OEHR_DEPARTMENTS table.
8. In the Add Page section, specify the following:
a. Select Page Type - Select Report.
b. Subordinate to Page - Select Home (1).
This sets up the hierarchy among the pages in your application.
c. Page Source - Accept the default, Table.
d. Table Name - Select OEHR_DEPARTMENTS.
The list shows all tables and views in the schema associated with the
application.
e. Implementation - Accept the default Interactive.
f. Click Add Page.
Notice that the pages listed in the Create Application section reflect the
hierarchy of the pages in your application.
Next, change the default page name, OEHR_DEPARTMENTS, to
Departments.
9. Click the Oehr Departments link.
10. Under Page Definition, change the Page Name to Departments and click Apply
Changes.
11. In the Create Application section, click Next.
Next, define some application-level settings.
12. For Tabs, select No Tabs and click Next.
13. For Shared Components, accept the default, No, and click Next.
Creating the Initial Application
Building Your Application 4-5
14. For Attributes, make these changes:
a. Authentication Scheme - Accept the default, Application Express.
Note that Application Express is the default Authentication Scheme. The
authentication scheme option enables you to control users’ access to the
application using Application Express authentication or another type of
authentication.
b. Language - Accept the default, English (en)
c. User Language Preference Derived From - Accept the default, Application
Primary Language.
d. Date Format - Click the up arrow and select 12-JAN-04.
e. Date Time Format - Click the up arrow and select 12-JAN-2004 1430.
f. Timestamp Format - Click the up arrow and select 12-JAN-2004 1430.
g. Timestamp Time Zone Format - Click the up arrow and select 12-JAN-2004
1430.
h. Click Next.
15. For User Interface, select Theme 2 and click Next.
16. For Confirm, verify the information and click Create.
The two pages you created, Home and Departments, appear as icons by default.
Notice that Oracle Application Express adds a Login page automatically to your
application.
Congratulations! You created your first application using Oracle Application Express.
Next, preview the application to see the application the way it would appear to end
users.
Previewing Your Application
You preview your application by running it. Running the application displays the
rendered version of the application as end users would see it.
As you create pages, you can view them by running the page individually or by
running the entire application. When you run a page or application, the Application
Express engine dynamically renders it into viewable HTML based on data stored in
the database.
Note: To view the pages in a report instead of as icons, click the
View Report icon. Your view setting is retained from session to
session.
Because these steps use the default setting, switch back to Icons view
before continuing.
Creating the Initial Application
4-6 Oracle Database 2 Day + Application Express Developer's Guide
You can run an application in the following ways:
■ To run the application, click the Run Application icon.
■ To run a specific page, click the Run Page icon.
To preview your application:
1. On the Application home page, click Run Application.
2. The first time you run an application you are prompted to enter a user name and
password. To continue, simply enter your workspace user name and password
and then click Login.
You can run the application using your Application Express login credentials
because you chose Application Express Authentication when creating the
application. For more information, see "Establishing User Identity Through
Authentication" in Oracle Application Express Application Builder User's Guide.
The Home page of your application appears.
Notice that a link to the Departments page appears on the Home page because you
set up that hierarchy.
Also notice the Developer toolbar at the bottom of the page. These links appear
when you run an application within the development environment. The
Modifying the Departments Report
Building Your Application 4-7
Developer toolbar offers a quick way to edit the current page, create a page,
region, or page control, view session state, or toggle in and out of Debug mode.
3. Click the Departments link.
The Departments report appears.
Modifying the Departments Report
When you run the Create Application Wizard, the wizard creates forms and reports
based on the selections you make. Behind the scenes, Application Express writes a
query for you to extract the appropriate data from the database.
In this exercise, you modify the query for the Departments report to include
information from the OEHR_EMPLOYEES table. Three columns from the OEHR_
EMPLOYEES table, Number of Employees, Manager Name and Location, are added to
the Departments report. The Manager Name column replaces the Manager Id column
and the Location column replaces the Location Id column.
To modify the Departments report:
1. Click the Edit Page 2 link on the Developer toolbar (at the bottom of the page).
The Page Definition for Page 2 appears.
A page is the basic building block of an application. Pages contain user interface
elements such as tabs, lists, buttons, items, and regions. To see the definition of
each page belonging to your application, you use the Page Definition page. For
more information, see "What Is Application Builder?" on page 3-2.
2. Under Page Rendering, locate Regions.
3. Expand Region and locate Departments.
Modifying the Departments Report
4-8 Oracle Database 2 Day + Application Express Developer's Guide
4. Right-click Departments and select Edit.
The Region Definition appears.
5. Scroll down to Source, and replace the default Region Source script with the
following:
SELECT d.DEPARTMENT_ID,
d.DEPARTMENT_NAME,
(select count(*)from oehr_employees where department_id = d.department_id)
"Number of Employees",
substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
c.COUNTRY_NAME "Location"
FROM OEHR_DEPARTMENTS d,
OEHR_EMPLOYEES e,
OEHR_LOCATIONS l,
OEHR_COUNTRIES c
WHERE d.LOCATION_ID=l.LOCATION_ID
AND l.COUNTRY_ID=c.COUNTRY_ID
AND d.DEPARTMENT_ID=e.department_id
AND d.manager_id=e.employee_id
AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_
SEARCH,d.department_name)))>0
6. Click Apply Changes.
7. Click Apply Changes to confirm.
8. Right-click Departments and select Edit Report Attributes.
9. Click the Edit icon next to Number of Employees.
10. Under Column Definition, select center for Column Alignment.
11. Click Apply Changes.
Tip: You can also double-click Departments.
Modifying the Departments Report
Building Your Application 4-9
Run the Report to View Added Columns
To see how the page looks to end users, run the page. When you run a page, the
Application Express engine dynamically renders and processes the page to display
viewable HTML.
To run the page:
1. Click the Run Page icon in the upper right corner.
The revised Departments report appears.
Note that the Manager Id and Location Id columns are not available. Also note
that the three new columns do not display on the revised report: Number of
Employees, Manager Name, and Location. The following steps are required to add
these columns to the display and reorder them.
2. To add the new columns, click the Actions menu.
The list of Action options appears.
Modifying the Departments Report
4-10 Oracle Database 2 Day + Application Express Developer's Guide
3. From the Actions menu, select Select Columns.
The columns that were added by the new SQL query are in the Do Not Display
box.
4. Click the Move All icon to add these three columns to the Display in Report box.
The Move All icon looks like the following figure.
5. Verify if the columns are in the following order. If they are not, click up and down
arrows to the right of the Display in Report to change the order:
■ Department ID
■ Department Name
■ Number of Employees
■ Manager Name
■ Location
6. Click Apply.
Using an Interactive Report
Building Your Application 4-11
The Departments page appears and includes the new columns.
Using an Interactive Report
This exercise guides you through some commonly used report options available to
users when they run interactive reports. These options enable the user to perform
certain tasks quickly.
Topics:
■ Save Report Settings as the Default
■ Create a Filter
■ Change the Sort Order
■ Add a Controlled Break
■ Display the Single Row View
■ Reset Report Settings
You already used the Select Column option from the Actions menu to add columns to
the report display. In this section, you run the application and use some of the various
search, sort, highlight, and reset features to manipulate the Departments interactive
report.
This tutorial only touches on some of the most prominent functionality available with
interactive reports. For complete details see "Customizing Interactive Reports" in
Oracle Application Express Application Builder User's Guide.
Save Report Settings as the Default
Both developers and end users can save an interactive report. However, only a
developer can save the report that initially displays, called the Primary Default report.
You can save four types of interactive reports:
■ Primary Default (Developer Only). The Primary Default is the report that initially
displays. Primary Default reports cannot be renamed or deleted.
■ Alternative Report (Developer Only). Enables developers to create multiple report
layouts. Only developers can save, rename, or delete an Alternative Report.
Using an Interactive Report
4-12 Oracle Database 2 Day + Application Express Developer's Guide
■ Public Report (End user). Can be saved, renamed, or deleted by the end user who
created it. Other users can view and save the layout as another report.
■ Private Report (End user). Only the end user that created the report can view,
save, rename or delete the report.
To save current report settings as the default:
1. Click the Actions menu.
2. Select Save Report.
The Save Report settings appear.
3. From Save, select As Default Report Settings.
The Default Report Type settings appear
4. Select Primary and click Apply.
The report will show all columns when the user selects the Reset option from the
Actions menu or when the page is reset by a Reset Interactive Report command,
RIR.
Create a Filter
This exercise shows you how to add a filter to the Departments report. This example
creates a filter to show all departments with the exception of departments with an Id
of 60.
There is more than one way a user can create a filter. The user can add a filter from the:
■ Search Bar
■ Actions Menu
■ Column Heading
The Actions menu offers more options than the others. For a quick filter using the
equals operator, the Search Bar or column heading are the quickest methods. For
anything else, use the Filter option from the Actions menu.
To create a filter for all departments not equal to 60:
1. Click the Actions menu.
2. Select Filter.
The filter settings display. Note you can also click the column heading link to
create a filter for that column. Creating a filter from the column heading menu,
however, is limited to the equal operator.
See Also: For a description of how to save as a report as a Named
Report see "Customizing Interactive Reports" in the Oracle Application
Express Application Builder User's Guide.
Using an Interactive Report
Building Your Application 4-13
3. From Operator, select the not equals operator (!=).
4. From Expression, select 60.
5. Click Apply.
The Departments report is displayed with the Department_Id!=60 filter applied.
All departments, accept for the IT department with a Department Id of 60, are
included in the report.
Notice the filter displays at the top of the report. You can edit, disable, or delete
this filter. To edit, click the filter name. To disable, deselect the check box. To
delete, click the Delete icon that looks like an X over the filter icon.
6. Deselect the check box next to the Department Id!=60 filter to disable it.
The report shows data for all Department Ids.
Change the Sort Order
In this exercise, you learn how the user can reorder the data for each column.
To change the sort order:
1. Click the Actions menu.
2. Select Format and then Sort.
The sort settings are displayed. Note this option is also available for each column
by clicking the column heading. The Actions menu is quicker when changing the
sort order for multiple columns.
3. Select Department Name for the first column entry.
Using an Interactive Report
4-14 Oracle Database 2 Day + Application Express Developer's Guide
4. Click Apply.
The report shows the Department Name column in ascending alphabetical order.
Notice the up arrow icon after the Department Name that indicates the column
has been sorted in ascending order. You can click this arrow to resort the column.
Add a Controlled Break
Another way to reformat a report is to create a controlled break. A controlled break
enables the user to delineate data into clearly visible groups.
To add a controlled break:
1. Click the Location column heading.
The column heading menu appears.
2. Click the Control Break icon as shown below.
Note this same option is also available from the Actions, Format menu.
Using an Interactive Report
Building Your Application 4-15
The report is reformatted to show a break after each group of rows that share the
same location.
Note the Location control break report setting displays under the Search bar.
Display the Single Row View
You can also display details for a row by selecting the Single Row icon.
To display the single row view:
1. Locate the Single Row View icon for the first row.
2. Select the Single Row View icon for the first row.
The details page for the first row appears.
Adding an Employees Report and Form
4-16 Oracle Database 2 Day + Application Express Developer's Guide
Reset Report Settings
You can revert the report settings back to the saved default settings. When an
interactive report is reset, the default settings are applied to the report. An interactive
report can be reset by the user and by the developer under the following conditions:
■ A user can reset an interactive report if the Reset option is available from the
Actions menu. This option is enabled or disabled by the developer. For further
details see "Customizing Interactive Reports" in the Oracle Application Express
Application Builder User's Guide.
■ A developer can specify that an interactive report be reset when it is called from
another page. The Reset Interactive Report command, RIR, is used for this
purpose. For further details see "Customizing Interactive Reports as a Developer"
in the Oracle Application Express Application Builder User's Guide.
To return to the default settings:
1. Click the Report View button.
2. Click the Actions menu and select Reset.
3. When prompted to restore the default settings, click Apply.
The report goes back to the default report settings saved in the previous section.
Adding an Employees Report and Form
In this exercise, you add a report to display employee information and a form to
create, update, and delete employee information.
To add an employee report and form:
1. Click the Application ID link on the Developer toolbar (at the bottom of the page).
The Application home page appears.
2. Click Create Page.
3. For Page:
a. Select Form and click Next.
b. Select Form on a Table with Report.
This option creates two pages, a report and form, based on a single table or
view.
c. Click Next.
4. For Page:
a. Table/View Owner - Accept the default. This specifies the schema owner.
Adding an Employees Report and Form
Building Your Application 4-17
b. Table/View Name - Select OEHR_EMPLOYEES.
c. Click Next.
5. For Define Report Page, make these changes:
a. Implementation - Select Interactive.
b. Page Name - Change to Employees.
c. Region Title - Change to Employees.
d. Regioni Template - Select Reports Region.
e. Breadcrumb - Select Breadcrumb.
The Create Breadcrumb Entry section appears.
f. For Select Parent Entry, click the Home link.
Home appears in the Parent Entry field.
g. Click Next.
6. For Define Report Page, accept the default, Do not use tabs, and click Next.
7. For Define Report Page:
a. Press the CTRL key and select only the following columns:
– EMPLOYEE_ID
– FIRST_NAME
– LAST_NAME
– HIRE_DATE
– SALARY
– COMMISSION_PCT
b. Click Next.
The columns you selected appear on the Report page when the page is run.
8. For Define Report Page, accept the defaults and click Next.
Note that the default Edit Link image resembles a small piece of paper and pencil.
Next, define the page and region information for the form.
9. For Define Form Page, edit the following:
a. Page Name - Enter Create/Edit Employee
b. Region Title - Enter Create/Edit Employee
c. Entry Name - Enter Create/Edit Employee
d. Click Next.
10. For Primary Key Type, accept the defaults and click Next.
11. In Define the source for the primary key columns, accept the default, Existing
trigger, and click Next.
Your table, OEHR_EMPLOYEES, already has a trigger that populates the primary
key.
12. In Select Column(s), select all columns and click Next.
These columns appear in the Create/Edit Employee form when the page is run.
Adding an Employees Report and Form
4-18 Oracle Database 2 Day + Application Express Developer's Guide
13. In Identify Process Options, accept the defaults and click Next.
These selections enable users to add, update, and create employee records.
14. For Confirm, verify the information and click Finish.
Previewing the Pages
To preview the two pages you just created, run the current page. From the Employees
report, you can then preview the form.
To preview the pages:
1. Click the Run Page icon.
The Employees report appears.
Note the following about the Employees report:
■ The Search Bar with the Actions menu appears at the top. For more
information, see "Using an Interactive Report" on page 4-11.
■ The column headings are links that enable the user to quickly perform various
sorting, filtering, and reformatting operations. For more information, see
"Using an Interactive Report" on page 4-11.
■ The Edit icon you selected appears in each row. You can click the Edit icon to
update the employee record. Since you created a form based on a table with a
report, the wizard automatically linked the report to the form. If you create
Editing the Employees Report
Building Your Application 4-19
just a report, as described in the previous section, "Creating the Initial
Application", the wizard automatically links the report to the single row view.
■ The Create button appears in the upper right corner. You can click the Create
button to add an employee to the underlying table.
■ The Employees report includes the six columns you specified. When you
selected the columns and applied the changes, Oracle Application Express
created the appropriate SQL code in the background to produce this result.
Your data might appear in a different order. If so, ignore this difference and
continue with these steps.
2. To review the form you also created, click the Edit icon in an Employee row.
The Create/Edit Employee form appears.
Note the following about the Create/Edit Employee form:
■ The form includes Cancel, Delete, and Apply Changes buttons.
■ The Hire Date field displays a calendar, which is included because the data
type of the underlying column is DATE.
■ Fields labeled in red require are mandatory and cannot be left blank or null.
Editing the Employees Report
In this exercise, you create a new column entitled Remuneration to display employee
salaries in the Employees report.
Topics:
■ Create a Function
■ Add a Column
■ Previewing the Page
■ Changing the Column Format
Create a Function
First, create a function to calculate employees’ salaries.
Editing the Employees Report
4-20 Oracle Database 2 Day + Application Express Developer's Guide
To create a function:
1. Click the Home link on the Developer toolbar.
2. Click SQL Workshop and then SQL Commands.
SQL Commands provides a window where you can run PL/SQL against the
database.
3. In the SQL editor, enter the following script:
CREATE OR REPLACE FUNCTION calc_remuneration(
salary IN number, commission_pct IN number)
RETURN NUMBER IS
BEGIN
RETURN ((salary*12) + (salary * 12 * nvl(commission_pct,0)));
END;
When copying and pasting this SQL, make sure there is not an empty line before
the first statement, CREATE OR REPLACE FUNCTION.
4. Click Run.
The Results section displays this message:
Function created.
Add a Column
Next, add a column to display the results of the employees’ salary calculations.
To add a column:
1. Navigate to the Page Definition for page 3:
a. Click the Application Builder tab.
b. Select the AnyCo Corp application.
c. Click 3 - Employees.
The Page Definition for page 3 appears.
2. Under Regions, locate Employees.
3. Right-click Employees and select Edit.
Editing the Employees Report
Building Your Application 4-21
The Region Definition appears.
4. Scroll down to Source, and replace the existing code with the following:
SELECT "EMPLOYEE_ID",
"FIRST_NAME",
"LAST_NAME",
"HIRE_DATE",
"SALARY",
"COMMISSION_PCT",
calc_remuneration(salary, commission_pct) REMUNERATION
FROM "#OWNER#"."OEHR_EMPLOYEES"
5. Click Apply Changes.
6. Click Apply Changes to confirm.
Previewing the Page
To preview the page, click the Run Page icon in the upper right corner. Notice the
Remuneration column does not appear. You must add the column by using the Select
Column action.
To display the Remuneration column:
1. Click the Actions menu.
2. Select the Select Columns action.
3. Move the Remuneration column from the Do Not Display box to the Display in
Report box.
Editing the Employees Report
4-22 Oracle Database 2 Day + Application Express Developer's Guide
4. Click Apply.
The Employees Report is displayed and includes the Remuneration column.
Changing the Column Format
Next, change the format of the columns that contain numeric values.
To change the column format:
1. Click Edit Page 3 on the Developer toolbar (at the bottom of the page).
The Page Definition appears.
2. Under Regions, right-click Employees and select the Edit Report Attributes.
The Report Attributes page appears.
3. Locate the Column Attributes section.
Next, change the format of the value in the Salary and Remuneration columns.
4. To edit the Salary format:
a. Click the Edit icon next to SALARY.
b. For Number/Date Format, select $5,234.10 from the list.
Notice that the appropriate format mask appears in the field.
c. Click Apply Changes.
5. Repeat the previous step for REMUNERATION.
6. Click the Run Page 3 icon in the upper right corner to preview the page.
Notice that the numbers in the Salary and Remuneration columns now appear
with the dollar sign and include commas and decimals.
Adding a Link on the Home Page to the Employees Report
Building Your Application 4-23
Adding a Link on the Home Page to the Employees Report
In this exercise, you add a link on the Home page to take users to the Employees
report. This link appears in the Navigation region on the left side of the Home page.
To add a link on the Home page to the Employees report:
1. Navigate to the Page Definition for the Home page, page 1:
a. Click the Application ID link on the Developer toolbar (at the bottom of the
page).
b. On the Application home page, click 1 - Home.
2. Under Regions, right-click Navigation and select Edit List.
3. On the Entries by List page, click the Create List Entry button.
4. On the Create/Edit List page, edit the following:
a. Sequence - Change to 20.
b. List Entry Label - Enter Employees
c. Page - Select 3.
5. Click Create.
6. Run the page.
Notice that your Home page now includes a link to Employees.
7. Click Employees to test the link.
The Employees report appears.
Adding a Department Column to the Employees Report
4-24 Oracle Database 2 Day + Application Express Developer's Guide
Adding a Department Column to the Employees Report
In this exercise, you link the Employees and Departments reports. When you complete
this section, the Employees report will have a Departments column showing which
department each employee belongs to.
To perform this task, you create a column on the Employees page to enable the user to
select a department. You then run the Employees report and add the Departments
column to the report. The report settings are saved as the default.
Topics:
■ Add a Column
■ Display Column in the Report
■ Save Report Settings as the Default
Add a Column
In order to filter the Employees report by department, add a Department column to
the report.
To add a column:
1. Click Edit Page 3 in the Developer toolbar.
2. Under Regions, right-click Employees and select Edit.
The Region Definition appears.
3. Scroll down to Source.
4. Replace Region Source with the following SQL:
SELECT e.EMPLOYEE_ID,
e.FIRST_NAME,
e.LAST_NAME,
e.HIRE_DATE,
e.SALARY,
e.COMMISSION_PCT,
calc_remuneration(salary, commission_pct) REMUNERATION,
e.DEPARTMENT_ID,
d.DEPARTMENT_NAME
FROM OEHR_EMPLOYEES e,
OEHR_DEPARTMENTS d
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID
5. Click Apply Changes.
6. Click Apply Changes to confirm.
The next section explains how to add the column to the report display and how to
use this column to filter employee reports.
Display Column in the Report
When you first run this report, use the Select Columns action to add the Department
Name column to the display. Once this column is visible on the report, make this
report the default and filter by department.
To add the Department Name column to the report:
1. Click the Run Page 3 icon.
Adding a Department Column to the Employees Report
Building Your Application 4-25
2. Click the Actions menu and select Select Columns.
3. Move Department Name from the Do Not Display box to the last entry in the
Display in Report box.
4. Click Apply.
The Employees Report is displayed and includes the Department Name column.
Next, make these report settings the default.
Save Report Settings as the Default
This section describes how to save the current report settings as the Primary Default
report. Report settings are any setting made through the Actions menu including:
selected columns, filters, sorting, rows per page, format setting such as sort control
breaks, highlighting, and so on, and flashback.
Both developers and end users can save interactive reports. However, only a
developer can save the report that initially displays, called the Primary Default report.
Once the default settings are saved, these columns are the default display in this
report.
To save the current report settings as the default:
1. Run the Employees report page.
The current report default does not include the Department Name column when
the report is displayed.
2. Click the Actions menu.
Linking a Column Value to Another Page
4-26 Oracle Database 2 Day + Application Express Developer's Guide
3. Select Save Report.
4. For Save, select As Default Report Settings from the list.
5. Accept the Default Report Type setting, Primary, and click Apply.
Linking a Column Value to Another Page
In this exercise, you add a link to the Number of Employees column on the
Departments report that navigates the user to the Employees report. It also sets the
focus to the selected department. The column link includes a Clear Interactive Report
command, CIR, to clear the Employees report, when shown, of any filters, control
breaks, highlights, aggregates, computed columns, chart settings and flashback
settings.
Topics:
■ Change Column Value to a Link
■ Run the Departments Report
■ Create a Highlight to Test Clear Interactive Report Command
Change Column Value to a Link
To make this change, you edit the report attributes on the Departments report to
change the value for the Number of Employees column to a link.
To change the column value to a link:
1. Click the Application ID link on the Developer toolbar.
2. Click 2 -Departments.
3. Under Regions, right-click Departments and select Edit Report Attributes.
The Report Attributes page appears.
4. Locate the Column Attributes section.
5. In the Column Attributes section, click the Edit icon next to Number of
Employees.
6. Scroll down to the Column Link section.
7. Under Column Link, make these changes:
a. Link Text - Select #Number of Employees#.
b. Page - Enter 3.
c. Select the Reset Pagination check box.
d. Clear Cache - Enter:
3,CIR
The 3 means the cache for page 3 is cleared and the string CIR clears the
Employees interactive report of any filters, control breaks, highlights,
Note: If a reset is performed on this page, these default report
settings are used. The Department Name column is displayed by
default.
Linking a Column Value to Another Page
Building Your Application 4-27
aggregates, computed columns, chart settings or flashback settings when the
report is displayed.
e. Item 1, Name - Enter IR_DEPARTMENT_ID
By prefixing the item name with IR_, the URL item will be associated with an
interactive report column rather than an item on the page.
f. Item 1, Value - Select #DEPARTMENT_ ID#
The Column Link section shows all the options you selected.
In summary, these options enable the following behavior when the end user
clicks the link:
– The end user is navigated to page 3 of the Employees report.
– On the Employees report, the selected department is in focus.
– The Employees report is cleared. This means any filters, control breaks,
highlights, aggregates, computed columns, chart settings or flashback
settings are removed.
– The user is returned to the first set of employee records when the user
selects another department.
g. Click Apply Changes.
Note that in the Column Attributes section, the Number of Employees row
contains a check mark in the Link column, reflecting the change you just
made.
Run the Departments Report
Now, run the report and test the Number of Employees column link from the
Departments report. The Employees report is displayed showing all employees that
belong to the selected department.
1. Click the Run Page 2 icon.
Note: If you want to reset the interactive report to the default report
settings instead of clear the report, replace CIR with RIR. The last
saved default report settings are applied to the interactive report.
Linking a Column Value to Another Page
4-28 Oracle Database 2 Day + Application Express Developer's Guide
The Departments report is displayed. Note that end users can now drill down on
the data in the Number of Employees column.
2. In the Number of Employees column, click the 3 for the Executive row.
All employees that belong to the Executive department appear in the Employees
report.
The Department Id filter is displayed because this report is filtered by the
Department Id value for the Executive department, 90, passed to this report from
the Departments report. Notice the Clear Interactive Report command is included
at the end of the URL in your browser, as shown in this example:
f?p=9257:3:3672543193352902::NO:RP,3,CIR:IR_DEPARTMENT_ID:90
Create a Highlight to Test Clear Interactive Report Command
This exercise demonstrates how to use the CIR command to clear a highlight before
displaying the Employees report. To show this, you highlight any rows containing the
first name of Neena, then display the Employees report from the Number of
Employees column on the Departments report.
To highlight rows in the Employees report:
1. Click the Actions menu and select Format and then Highlight.
The Highlight options appear.
2. For Highlight options make these changes:
a. Name - Enter Neena
Adding a List of Values to the Employees Report
Building Your Application 4-29
b. Background Color - Select [yellow]
c. Text Color - Select [red]
d. Column - Select First Name
e. Operator - Select =
f. Expression - Select Neena
g. Click Apply.
The Employees report appears with the row for Neena highlighted.
3. Next, reset the report.
a. Click the Home breadcrumb.
b. Click Departments link.
c. Click Number of Employees in the Executive row.
The Employees report is displayed with the highlighting cleared, due to the
CIR command specified in the column link.
4. Click the Remove Filter icon to the right of DepartmentID = 90.
5. Click Application ID on the Developer toolbar.
Adding a List of Values to the Employees Report
In this exercise, you add a list of values to the Employees page to display employees
by department. You also change the Employees report query, edit the column link on
the Department report, and turn off pagination.
Topics:
■ Add a List of Values
■ Add an Item to Display List of Values
■ Change Employees Report Query
■ Change Column Link on Department Report
■ Turning Off Pagination
Add a List of Values
In this section, you create a list of values that is used to select which department
details to view. This is a dynamic list that queries the OEHR_DEPARTMENTS table for a
list of all the department names.
Adding a List of Values to the Employees Report
4-30 Oracle Database 2 Day + Application Express Developer's Guide
To create a list of values:
1. Click Shared Components icon.
2. Under User Interface, select Lists of Values.
3. Click the Create button.
4. Accept the default, From Scratch, and click Next.
5. For Name and Type:
a. Name - Enter DEPARTMENTS
b. Type - Select Dynamic.
c. Click Next.
6. For the Query, enter the following SQL:
SELECT department_name d, department_id v
FROM oehr_departments
ORDER BY d
7. Click Create List of Values.
Add an Item to Display List of Values
Now, you create an item in the Department Details region to display the list of
departments.
To add an item:
1. Navigate to the Page Definition for page 3, Employees.
2. Under Region, right-click Employees and select Create Page Item.
3. Select Select List and click Next.
4. For Create Item:
a. Item Name - Enter P3_DEPARTMENT_ID
b. Sequence - Enter 10
c. Region - Select Employee (1) 10
d. Click Next.
5. For Item Attributes:
a. Label - Enter Department
b. Accept the remaining defaults and click Next.
6. For Settings:
a. Value Required - Select No.
b. Page Action when Value Changed - Select Redirect and Set Value.
c. Click Next.
7. For Identify List of Values, make these changes:
a. Named LOV - Select DEPARTMENTS
b. Display Null Option - Select Yes
c. Null Display Value - Enter:
Adding a List of Values to the Employees Report
Building Your Application 4-31
- No Department Assigned -
d. Null Return Value - Enter:
-1
e. Click Next.
8. Click Create Item.
Change Employees Report Query
Now, you change the Employees report query to display employees that belong to the
selected department.
To change the query:
1. Under Regions, right-click Employees and click Edit.
2. Enter the following SQL for Region Source:
SELECT e.EMPLOYEE_ID,
e.FIRST_NAME,
e.LAST_NAME,
e.HIRE_DATE,
e.SALARY,
e.COMMISSION_PCT,
calc_remuneration(salary, commission_pct) REMUNERATION,
e.DEPARTMENT_ID,
d.DEPARTMENT_NAME
FROM OEHR_EMPLOYEES e,
OEHR_DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID(+) AND
(e.DEPARTMENT_ID = :P3_DEPARTMENT_ID or
(e.DEPARTMENT_ID is null and nvl(:P3_DEPARTMENT_ID,'-1') = '-1'))
3. Click Apply Changes.
Change Column Link on Department Report
Because the Employees query has changed, the Number of Employees column link
needs to initialize the P3_DEPARTMENT_ID item to the value of DEPARTMENT_ID.
To change the column link for Number of Employees:
1. Click the Application ID breadcrumb.
2. Click 2 - Departments.
3. Under Regions, right-click Departments and select Edit Report Attributes.
4. Click the Edit icon for Number of Employees.
5. Scroll down to Column Link.
6. For Item 1, select P3_DEPARTMENT_ID.
7. Click Apply Changes.
Turning Off Pagination
Next, turn off pagination for the Department Details report, because only one record
(the department details report) displays at a time. If pagination is on, the region that
displays the department details includes the information row 1 - 1.
Adding Your Company Name
4-32 Oracle Database 2 Day + Application Express Developer's Guide
To turn off pagination:
1. Click the Application ID breadcrumb.
2. Click 3 - Employees.
3. Under Regions, right-click Employees and select Edit Report Attributes.
4. Scroll down to Pagination.
5. For Pagination Type, select - No Pagination Selected -.
6. Click Apply Changes.
7. Run the page.
8. From the Department list, select No Department Assigned.
Employee Kimberely Grant appears. This employee is not currently assigned to a
department.
Adding Your Company Name
You can add a graphical logo or text to your application so that it displays on each
page. Your page template determines the display location.
In this exercise, you add your company name to your application.
To add your company name to your application:
1. Navigate to the Application home page. Click Application ID on the Developer
toolbar (at the bottom of the page).
2. Click the Edit Application Properties button.
The Edit Application Definition page appears.
3. Under Logo:
a. Logo Type - Select Text.
b. Logo - Enter AnyCo Corp
c. Logo Attributes - Select Black Text from the pop up list.
4. Click Apply Changes.
5. Click the 1 - Home.
6. Click the Run Page icon in the upper right corner.
Tip: If no records initially display, reload the page and try again.
Switching Themes to Change the User Interface
Building Your Application 4-33
The company name now appears on each page in your application.
Switching Themes to Change the User Interface
In this exercise, you switch the current theme to another one for your application. A
theme is a collection of templates that defines the application user interface. Each
theme contains templates for every type of application component and page control,
including individual pages, regions, reports, lists, labels, menus, buttons, and list of
values.
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 the latest designs and other technological
advances without an application rewrite. Each theme provides a complete set of
templates that accommodate every user interface pattern that may be needed in an
application.
To switch the theme:
1. Click the Application link on the Developer toolbar (at the bottom of the page).
2. On the Application home page, click Shared Components.
3. Under User Interface, click Themes.
4. On the Themes page, click Create.
The Create Theme Wizard appears.
5. For Method, accept the default, From the Repository, and click Next.
6. For Identify Theme, select Theme 22 and click Next.
7. For Confirm, click Create.
8. Click Switch Theme.
Switching Themes to Change the User Interface
4-34 Oracle Database 2 Day + Application Express Developer's Guide
9. For Identify Theme, accept the default, 22.Bluejay, and click Next.
10. For Verify Compatibility, review the information, accept the defaults, and click
Next.
The wizard verifies that corresponding templates exist in the selected theme for all
templates used in your current application. If a template is missing, it warns you
with a message in the Status column. For this tutorial, you can ignore this message
because Oracle Application Express maps missing region templates to existing
ones.
11. For Confirm Switch, click Switch Theme.
12. Run the page.
The color scheme and display layouts are changed in all your pages.
About Deploying Your Application 5-1
5
About Deploying Your Application
This section describes basic information about deploying your Oracle Application
Express application. It also shows you how to create end users and to determine the
URL of your application.
Topics:
■ Overview of Deploying Your Application
■ Creating End Users
■ About Publishing the Application URL
Overview of Deploying Your Application
After building your application in your development environment, you need deploy it
so that end users can begin using it. Deploying involves the following steps:
1. Collect your supporting object definitions (CREATE object statements, sample
data, images, files, and so on). Review the Database Object Dependencies report to
determine which supporting objects to move. See "Viewing Dashboards and
Reports" in Oracle Application Express Application Builder User's Guide.
2. Define your supporting objects. This includes the object definitions you collected
in the previous step and other supporting objects, such as messages and
prerequisites.
3. Export the application with its supporting objects.
4. Import and install the exported files.
Using Oracle Application Express greatly simplifies the deployment process. It enables
you to migrate the application definition, associated files, and all supporting objects in
just a few steps.
Creating End Users
After moving your application to a production environment, you must now create
user accounts within Oracle Application Express for all your end users.
To create end user accounts:
1. Navigate to the Workspace home page.
2. Click Administration.
See also: "Deploying an Application" in Oracle Application Express
Application Builder User's Guide.
Creating End Users
5-2 Oracle Database 2 Day + Application Express Developer's Guide
3. Click Manage Users and Groups.
4. Click the Create User button.
The Create User page appears.
5. Under User Identification, enter:
a. Username - Enter the username used to log in to the system. Restrictions
include:
– Maximum length of 100 characters
– No spaces
– Only these special characters are permitted: ampersand (@) and period (.)
b. Email Address - Enter the valid email address for this user.
c. First Name - Enter the first or given name to further identify the user
(optional).
d. Last Name - Enter the last or family name to further identify the user
(optional).
e. Description - Enter comments about this user (optional).
6. Under Account Privileges:
a. Default Schemas - Specify the default schema used for data browsing,
application creation, and SQL script execution.
When using workspaces that have more than one schema available, this
schema is the default. This setting does not control security, only the user's
preference.
b. Accessible Schemas (null for all) - Leave this blank to enable the end user to
access all schemas associated with the workspace.
c. User is a workspace administrator - Specify if this user should have
workspace administrator privileges.
About Publishing the Application URL
About Deploying Your Application 5-3
Administrators are given access to all components. Additionally, they can
manage user accounts, groups, and development services. Components may
not be available if they are switched off by Instance Administrators.
d. User is a developer - Specify if this user should have developer privileges.
Developers must have access to either Application Builder, SQL Workshop, or
both. Components may not be available if they are switched off by Instance
Administrators.
e. Application Builder Access - Determines whether a developer has access to
the Application Builder.
f. SQL Workshop Access - Determines whether a developer has access to the
SQL Workshop.
g. Team Development Access - Determines whether a developer has access to
the Team Development.
h. Set Account Availability - Select Locked to prevent the account from being
used. Select Unlocked to allow the account to be used.
7. Under Password:
■ Password - Enter a case sensitive password.
■ Confirm Password - Enter the password again.
■ Require Change of Password On First Use - Select No to allow the user to use
the same password until it expires. Select Yes to require the user to change the
password immediately when logging in the first time.
8. Under User Groups, optionally select a user group.
9. Click Create User or Create and Create Another.
About Publishing the Application URL
After deploying your application, you must now publish its URL so end users know
how to access it.
To determine the URL:
1. Log in to Oracle Application Express on the production environment.
2. Select the application.
3. On the Application home page, position the mouse over the Run Application
icon.
4. Note the URL that displays in the status bar at the bottom of the page.
For example:
http://apex.oracle.com/pls/apex/f?p=11563:1:3397731373043366363
Where:
■ apex.oracle.com is the URL of the server.
■ pls is the indicator to use the mod_plsql.
See also: "Managing Application Express Users" in Oracle
Application Express Administration Guide
About Publishing the Application URL
5-4 Oracle Database 2 Day + Application Express Developer's Guide
■ apex is the database access descriptor (DAD) name. The DAD describes how
Oracle HTTP Server connects to the database server so that it can fulfill an HTTP
request. The default value is apex.
■ f?p= is a prefix used by Oracle Application Express.
■ 11563 is the application being called.
■ 1 is the page within the application to be displayed.
■ 3397731373043366363 is the session number, which is unique for every user's
access to the application. Do not include session numbers as a part of the URL
communicated to end users.
To run this example application, you would go to this URL:
http://apex.oracle.com/pls/apex/f?p=11563:1
See also: "Deploying an Application" in Oracle Application Express
Application Builder User's Guide
About Globalization 6-1
6
About Globalization
This section describes some basic information about globalization. It also leads you
through the steps to specify how the application determines global identification.
Topics:
■ About Language Identification and Rendering
■ Specifying the Method for Global Identification
About Language Identification and Rendering
You can develop applications in Application Builder that run concurrently in different
languages. This feature enables different users to view the application in their own
language and data in the appropriate formats.
When rendering a page in the application, Oracle Application Express first determines
the language for an application based on the method you specified. Then, the
Application Express engine alters the session language and territory settings for a
specific page request. It then looks for a translated application in the appropriate
language. If the Application Express engine finds that language, it renders the
application using that definition. Otherwise, it renders the application in the base (or
primary) application language.
Even if your application is not translated, the language display determines the format
for displaying data so that end users view numbers, dates, and currency in the
appropriate formats.
Specifying the Method for Global Identification
As part of the application definition settings, you can specify how the application
determines which language and formats to display. You can base it on the user's
browser language preference or restrict it to the language you specify.
To specify the method for determining global identification:
1. Click Application Builder and then select the application.
2. Click Shared Components.
3. Under Globalization, click Globalization Attributes.
4. Under Globalization, specify the following:
See also: "Managing Application Globalization" in Oracle Application
Express Application Builder User's Guide
Specifying the Method for Global Identification
6-2 Oracle Database 2 Day + Application Express Developer's Guide
a. Application Primary Language - Select the language you used when
developing the application.
b. Application Language Derived From - Select Browser (use browser language
preference).
This option enables users to view the application in the language specified in
their browsers.
c. Application Date Format - Determines the date format to be used in the
application.
d. Application Date Time Format - Specify the date time format to be used in the
application.
e. Application Timestamp Format - Determines the timestamp format to be used
in the application.
f. Application Timestamp Time Zone Format - Determines the timestamp with
time zone format to be used in the application.
g. Automatic Time Zone - Controls the setting of the database session time zone.
When set to Yes, the client time zone is derived from the client's Web browser
and set for the duration of the Application Express session.
h. Automatic CSV Encoding - Select Yes or No.
Automatic CSV Encoding controls the encoding of all comma-delimited (CSV)
report output in an application. If Automatic CSV Encoding is set to Yes, CSV
report output is properly converted to a character set compatible with
localized desktop applications. The character set for the CSV encoding is
determined by the Application Language Derived From setting.
For example, the end user whose browser setting is German (de) sees these changes on
the Employees page of AnyCo Corp:
■ The euro currency symbol appears for the amounts in the Salary and
Remuneration columns.
■ The amounts reflect the formatting used in Germany (commas and decimals).
Specifying the Method for Global Identification
About Globalization 6-3
Note that the application fields are not yet translated. For instructions on translating
your application, see "Managing Application Globalization" in Oracle Application
Express Application Builder User's Guide.
Specifying the Method for Global Identification
6-4 Oracle Database 2 Day + Application Express Developer's Guide
Next Steps 7-1
7
Next Steps
Now that you have completed this guide, you are ready to take advantage of other
resources to expand your knowledge of Oracle Application Express and boost your
productivity. This section describes those resources.
Additional Resources
To continue learning about Oracle Application Express, review these additional
resources designed for Application Express users.
Oracle Learning Library
Search for free online training content, including Oracle by Example (OBE), demos,
and tutorials. To access the Oracle Learning Library, go to:
http://www.oracle.com/technetwork/tutorials/index.html
Training Courses
You can attend training courses for Oracle Application Express. To review these
courses, go to the following Web site and enter Application Express in the search
criteria field:
http://education.oracle.com
Oracle Technology Network
Oracle Technology Network (OTN) is a central Web site for Oracle Application Express
resources and information. In addition to downloading the latest product version,
sample code, and packaged applications, the OTN site includes:
■ Discussion forum, where you can post questions and search for solutions
■ How-to documents, which are technical notes written by subject matter experts
■ Quick tour of the product
■ News and magazine articles related to Oracle Application Express
To access the Oracle Application Express page on OTN, go to the following Web site:
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.h
No comments:
Post a Comment