1 About these Tutorials

Oracle Database Application Express Advanced Tutorials contains a series of tutorials that explain how to use Oracle Application Express to create applications and application components. The goal of this book is to help you understand how to use Oracle Application Express through hands-on experience.

This section contains the following topics:

What this Book Is Not
Tutorial Topics
About Loading Sample Objects
About Application Authentication

What this Book Is Not

Oracle Database Application Express Advanced Tutorials is a collection of tutorials with step-by-step instructions. The objective of these tutorials is to demonstrate how to build a particular type of application or application component using the Oracle Application Express development environment.

Where appropriate, this book describes concepts relevant to understanding or completing a task. However, this book is not intended to be a complete discussion of Oracle Application Express concepts. For this type of information, see Oracle Application Express online Help or Oracle Database Application Express User's Guide.

If you are new to Oracle Application Express, please review the Oracle Database 2 Day + Application Express Developer's Guide. This guide introduces you to application development using Oracle Application Express. It leads your through the process of setting up your development environment and walks you through building an initial application.

Tutorial Topics

This document contains the following tutorials:

Title	Description
How to Create a Tabular Form	Illustrates how to create a tabular form within a new application and how to change one of the updatable columns from a text field to a select list.
How to Create a Parameterized Report	Illustrates how to create an interactive report and explains how a user can customize the report.
Using Advanced Report Techniques	Highlights some of the more advanced reporting techniques, such as linking from one interactive report to another and creating declarative filters within a URL.
How to Control Form Layout	Explains how to create a data input form and then change the form layout by editing the region and item attributes.
How to Work with Check Boxes	Illustrates the different ways in which you can create and process check boxes within an application.
How to Implement a Web Service	Explains how to call a Web service from within an application.
How to Create a Stacked Bar Chart	Explains how to create a stacked bar chart within an application.
How to Upload and Download Files in an Application	Illustrates how to create a form and report with links for file upload and download.
How to Incorporate JavaScript into an Application	Describes some usage scenarios for JavaScript and includes details about how to implement them in your application.
How to Build an Access Control Page	Explains how to build an Access Control Administration to restrict access to an application.
How to Review a Packaged Application	Explores the OEHR Sample Objects packaged application. By reviewing the supporting objects behind this application, you can learn how the Supporting Object Utility works so that you can create your own packaged applications.
How to Create a Master Detail PDF Report	Explains how to create a master detail form, define a report query and RTF template, and then create a button to expose the new report.
How to Design an Issue Tracking Application	This tutorial describes how to plan, design and populate data objects for an example Issue Tracking application.
How to Build and Deploy an Issue Tracking Application	Provides step-by-step instructions on how to create and deploy an application that tracks the assignment, status, and progress of issues related to a project.

About Loading Sample Objects

In Oracle Application Express, users log in to a workspace. Think of each workspace as a shared work area that separates your objects, data, and applications into a virtual private database.

Before you can start these exercises, you need to 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 supplier, the status availability, and a minimum price.

To create the objects locally in your workspace, you need to import the OEHR Sample Objects application.

This section contains the following topics:

Downloading OEHR Sample Objects
Importing and Installing OEHR Sample Objects
Viewing Database Objects

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 Database Installation Guide for your operating environment.

Downloading OEHR Sample Objects

To import the OEHR Sample Objects application, you first need to download it from the Oracle Technology Network (OTN):

In your Web browser go to:

http://www.oracle.com/technology/products/database/application_express/packaged_apps/oehr_sample_objects.zip

Locate the OEHR Sample Objects application.
Download the oehr_sample_objects.zip file to your computer.
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 and Installing OEHR Sample Objects

After you download the OEHR Sample Objects application, you need to import it into Oracle Application Express. During the import process, specify that you also want to install both the application and the supporting objects. Installing the application creates the objects and sample data needed to complete the exercises in Oracle Application Express Advanced Tutorials.

To import and install the OEHR Sample Objects application:

Log in to Oracle Application Express. See "Logging In To Oracle Application Express" in Oracle Database Application Express User's Guide.
On the Workspace home page, click Application Builder.

The Application Builder home page appears.
Click the Import button.
For Specify File, specify the following:
1. Import file - Click Browse and go to the oehr_sample_objects_installer.sql file.
2. File Type - Select Application, Page, or Component Export.
3. Verify that File Character Set is correct.
4. Click Next.
Now that you have imported the file, you want to install it.
To install an imported file, click Next.

The Install Application Wizard appears.
In the Install Application Wizard, specify the following:
1. Parsing Schema - Select a schema.
2. Build Status - Select Run and Build Application.
3. Install As Application - Select Auto Assign New Application ID.
4. Click Install.
For Supporting Objects, select Yes and click Next.
Confirm your selections by clicking Install.
Click the Home breadcrumb link at the top of the page.

The Application Builder home page appears.

Checking Available Space in Your Workspace

If you experience problems installing the OEHR Sample Objects application, verify the available space in your workspace. You many need to request additional storage space.

If you are a workspace administrator, you can:

Determine if you need additional storage space. See "Viewing the Workspace Overview Report" in Oracle Database Application Express User's Guide.
Request additional storage space. See "Requesting Additional Storage" in Oracle Database Application Express User's Guide.

Deleting the OEHR Sample Objects Application

Deleting the OEHR Sample Objects application and selecting to deinstall the supporting objects completely removes all associated objects and sample data.

To delete the OEHR Sample Objects application:

Log in to Oracle Application Express.
On the Workspace home page, click Application Builder.

The Application Builder home page appears.
Select the OEHR Sample Objects application.

The Application home page appears.
On the Tasks list, click Delete this Application.

The Deinstall page appears.
To remove all associated objects and sample data, select Remove Application Definition and Deinstall Supporting Objects.
Click Deinstall.

Viewing Database Objects

Now, take a look at 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:

On the Workspace home page, click SQL Workshop.
Click Object Browser.

As shown in Figure 1-1, Object Browser appears.

Figure 1-1 Object Browser

Description of "Figure 1-1 Object Browser"

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.
  
  The list of objects that appears depends upon the available objects in the current schema. Note that any object having a red bar adjacent to it is invalid.
- Detail pane displays to the right of the page and displays detailed information about the selected object.
From the Object Selection list, select Tables.
In the Object Selection pane, click OEHR_EMPLOYEES from the list.

The Detail pane shows details about the table.
Click the Data tab in the row 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.
To search for an object name, enter a case insensitive term in the Search field.
To view all objects, leave the Search field blank.

About Application Authentication

As you create new pages, you can view them by running the page individually or by running an 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.

By default, all the applications you create in these tutorials use Application Express Authentication. Application Express Authentication is a built-in authentication scheme that uses the same internal user accounts you use to log in to a workspace.

The first time you run a page in 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.

When you create your own applications, you can choose from a number of preconfigured authentication schemes or build your own.