Chapter 7. Building a Sample Application with Oracle Application Express

It is better for a leader to make a mistake in forgiving than to make a mistake in punishing. - Prophet Muhammad

This chapter provides a brief introduction to Oracle Application Express (APEX) and its components. We will also learn how to create a workspace followed by a sample application. Step-by-step instructions will show how to load/unload data to/from the database using APEX. At the end of the chapter you should be able to develop small applications using APEX and get familiar with all the APEX components. The topics that will be covered in this chapter are as follows:

• What is Oracle Application Express (APEX)?

• Getting started with Oracle Application Express

• Application Express components

• Prerequisites of a sample application

• Creating a sample application

• Adding a page to an existing application

• Creating application users

• Loading and unloading data to a text file

• Application Express Dashboard

What is Oracle Application...

What is Oracle Application Express (APEX)?

APEX is a browser-based Rapid Application Development (RAD) tool that helps you to create rich, interactive Oracle-based web applications. APEX is installed with Oracle Database 11g XE by default. APEX is a no-cost option for the Oracle database. Instead of downloading APEX you can sign up for an account on Oracle's hosted version of APEX at http://apex.oracle.com.

Getting started with Oracle Application Express

To start using APEX, we must create a workspace. Each workspace represents a private container in which developers create and deploy their APEX applications. A workplace is a group of applications, each containing one or more pages.

The first step in building an application using APEX is to create a workspace. To create a workspace in a freshly installed Oracle Database 11g XE, follow these steps:

1. 1. Launch the Get Started page.

2. 2. Click on Application Express.

3. 3. Create a new application express user apex_hr for the existing HR database user...

Creating application users

As an administrator you manage workspace administrators, application developers, and end users.

A workspace administrator can create and modify applications and database objects. The administrator also manages users and groups. A developer can create and modify applications and database objects, while the end user can only access applications.

An example of creating an application user is as follows:

1. 1. Navigate to Application Express Home | Administration | Manage Users and Groups.

2. 2. Click on the Create User button.

3. 3. Enter the User identification details.

4. 4. Select No for the User is a workspace administrator option and Yes for User is a developer, as shown in the following screenshot. Click on the Create User button:

Similarly, you can create workspace administrators and end users.

Loading and unloading data to a text file

This section describes how to use Oracle Application Express utilities to load and unload data from Oracle Database. Unloading is a process of copying table data to external files while loading is a process of copying data from external files to a database table.

Unloading the EMP table to a text file

You can use the Unload page to export table data as a text file. To unload the EMP table to a text file, follow this process:

1. 1. Navigate to the Unload page (Workspace home page | SQL Workshop | Utilities | Data workshop | Data Unload | To Text)

2. 2. Select the HR schema and click on the Next button.

3. 3. Select the EMP table from the dropdown list and click on the Next button.

4. 4. Select all the columns.

5. 5. Choose the default values for Separator, select Include Column Names and other columns, and click on the Unload Data button.

6. 6. The file download window appears. Click on Save to download the file.

Loading text file to the EMP_LOAD table

You can use the Load...

Application Express Dashboard

The Application Express Dashboard presents a summary view of various APEX activities. Using the Dashboard, you can view the summary of a following:

• Workspace applications

• Workspace attributes and statistics

• Workspace users

• Workspace user activity

• Developer application and page changes

• Page performance

• Database objects by schema

To navigate to the dashboard go to Application Express Home | Administration | Dashboard. A pictorial view of the dashboard is shown in the following screenshot:

Summary

In this chapter we used Object Browser to create database objects, the Administration component to manage Application Express users, and Application Builder to create a sample application. We have covered all the major Application Builder topics in this chapter and I hope you are now geared up to dive deeper and explore more features of Application Express.

In the next chapter, we will talk about Oracle's memory and database structure. We will discuss the types of memory available in Oracle such as System Global Area (SGA) and Process Global Area (PGA). A database is made up of a set of files. The next chapter will guide you to understand the various types of physical files available in an Oracle database.

References

Refer to the following Oracle documentation to learn more about Oracle Application Express:

• Application Express Administration Guide

• Application Builder User's Guide

• Application Express SQL Workshop Guide