Advanced Deployment of Your Application: Part 3

Overview

    Purpose

    This tutorial covers how to complete, test and deploy the application you built in part 2. You will examine how to manipulate a list on Page 0, add a drop down list to your tabs, add error handling and debug your application, build and deploy a packaged application.

    Time to Complete

    Approximately 60 minutes

    Introduction

    Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle APEX is a fully supported, no cost option of the Oracle database. 

    In this tutorial, you use Oracle APEX Release 4.1.1 to complete and deploy the application you built in Enhancing Your Oracle Application Express Application: Part 2 of this workshop.

    Prerequisites

    Before starting this tutorial, you should:

    • Have access to an Oracle Database 11g database that has the sample schema installed.
    • Have installed Application Express Release 4.1.1 into your Oracle Database 11g database.
    • Create a workspace called HR that is based off the HR schema with the admin username of HR and a password of oracle.
    • Perform the Building an Application using Oracle Application Express: Part 2 tutorial or import the f100_result.sql application which contains the application that was created in this tutorial.
    • Download and unzip the files.zip into your working directory.

Creating a List

    In this topic, you create a list.

    From the Applications page, click Shared Components.

    Under Navigation, click Lists.

    Click Create.

    Accept the default and click Next.

    Enter Menu for Name and click Next.

    Enter the following information and click Next.

    List Entry Label Target Page ID
    Maintain Departments
    12
    Maintain Employees
    1
    Maintain Projects
    3
    View Project Schedule
    5
    Load Project Data
    7

    Click Create.

    Your list was created successfully. You want to show the list on every page in your application. In order to do this, you need to create Page 0 and add the list to this page. Click the Application breadcrumb.

Creating Page Zero

    In this topic, you create Page Zero. Page Zero contains all the regions and processing that you want to perform on every page unless otherwise specified. In this case, you want to add the list you created in the previous section on the right side of each page in your application. Perform the following steps:

    From the Application page, click Create Page.

    Select the Page Zero page type and click Next.

    Click Finish.

    You may get the screenshot below. If so, click OK.

Adding the List to Page Zero

    In this topic, you add the list you previously created to Page Zero. Perform the following steps:

    On Page Zero, right-click Regions and select Create.

    Select the List region type and click Next.

    Enter Menu for Title, select Navigation Region for Report Template and select Page Template Region Position 3 for Display Point and click Next.

    Select Menu for the List and click Create List Region.

    The region was created successfully. Click Run Application.

    You may get the Login page. Notice that it contains the menu region from Page Zero. Enter your password and click Login.

    Notice the Menu on the right side of the page. Click the Projects tab.

    You also see the Menu on the right side of the page. Select the Maintain Departments link from the Menu.

    The Departments tab is selected and the page is displayed. Note that the menu is also displayed on this page. You can exclude certain pages from showing the menu. Click Application <n> from the Developer Toolbar.

    Select Page 0.

    Under Regions, double-click Menu.

    Select the Conditions subtab.

    Select Current Page is NOT in Expression 1 (comma delimited list of pages) for Condition type and enter 101,1 in the Expression 1 field. This means that for pages 101 and 1 the menu will not be displayed. Click Apply Changes.

    Click Run Application.

    Notice that the menu is no longer displayed on page 1. Click the Projects tab.

    Notice that the Menu is displayed on the Projects page (3). Click the Logout link.

    The menu is no longer displayed on the Login page (101). In the next section, you make changes to the tabs. Click Application <n> in the Developer Toolbar.

Modifying Tabs

    In this topic, you reorganize and modify the tabs. Perform the following steps:

    Click Shared Components.

    Under Navigation, select Tabs.

    A list of tabs and the pages that are active for the tab are listed. Click the Manage Tabs subtab.

    You want to move the Departments tab so that it is first in the list and change its name. Select the Departments link.

    Click the edit icon in front of the Departments tab.

    Change the Sequence to 5 and change the Tab Label to Maintain Departments. Then click Apply Changes.

    Notice that the tab is now the first one. To change more than one tab at the same time, click the Edit Standard Tabs subtab.

    Change the Labels as indicated below in bold and change the Chart sequence number to 2 so that it is first. Then click Apply Changes.

    Sequence
    Name
    Label
    5 Departments
    Maintain Departments
    10
    T_EMPLOYEES
    Maintain Employees
    20
    T_PROJECTS
    Maintain Projects
    30
    Calendar
    View Project Schedule
    40
    Data Load
    Load Project Data
    2
    Chart
    Home


    Click the Run Page icon.

    Enter your password and click Login.

    Notice that the tabs sequence and labels have changed. Page 1, Maintain Employees, is the current tab. You want to change this to the Home tab. Click the Logout link.

    Click Application <n> in the Developer Toolbar.

    Click Edit Application Properties.

    Click the Security tab.

    Change the page number for the Home Link to 11 and click Apply Changes.

    Click Run Application.

    Enter your password and click Login.

    Notice that now the Home tab is active and page 11 is displayed. In the next section, you examine how to setup Error Handling for your application. Select Edit Page <n> in the Developer Toolbar.

Adding Error Handling to Your Application

    In this topic, you add error handling to your application. You create a table that stores all the constraints with the error that should be displayed when it occurs. In addition, you create a function that processes the error. In this example, you create a unique constraint on the department name. Perform the following steps:

    Uploading and Executing Necessary Error Handling Scripts

      You need to upload some scripts and execute them. Select SQL Workshop > SQL Scripts.

      Click Upload.

      Browse and select the constraint_lookup.sql file and click Upload.

      This script creates a new table which stores the name of the constraint and its message to display when the constraint is raised.

      create table CONSTRAINT_LOOKUP
      (
      CONSTRAINT_NAME VARCHAR2(30) primary key,
      MESSAGE VARCHAR2(4000) not null
      );

      You want to execute the script, click the Run icon.

      Click Run Now.

      Click the View Results icon.

      The table was created successfully. Select the SQL Scripts breadcrumb.

      You want to upload another script. Click Upload.

      Browse and select the error_handling_example_func.sql file and click Upload.

      This script creates an error handling function that will process the error and displays the message from the constraint_lookup table.

      You want to execute the script, click the Run icon.

      Click Run Now.

      Click the View Results icon.

      The function was created successfully. Select the SQL Scripts breadcrumb.

      You want to upload another script. Click Upload.

      Browse and select the dept_name_uk.sql file and click Upload.

      This script creates a unique constraint on the DEPARTMENT_NAME column in the DEPARTMENTS table.

      alter table DEPARTMENTS
      add constraint DEPT_DNAME_UK unique (DEPARTMENT_NAME);

      You want to execute the script, click the Run icon.

      Click Run Now.

      Click the View Results icon.

      The unique constraint was created successfully. Select the SQL Scripts breadcrumb.

      You want to upload one more script. Click Upload.

      Browse and select the insert_constraint_lookup.sql file and click Upload.

      This script inserts a row in the constraint_lookup table that contains the message to display when the constraint is raised.

      insert into CONSTRAINT_LOOKUP
      (CONSTRAINT_NAME, MESSAGE)
      values
      ('DEPT_DNAME_UK', 'Name is already in use!');

      You want to execute the script, click the Run icon.

      Click Run Now.

      Click the View Results icon.

      The row was inserted successfully. Select the Application Builder tab. Now you associated the error handling function with the page or application you want to run it in.

    Associating Error Handling Function with Your Page

      Click the Project Tasks Application icon.

      In this case, you want to use the error handling function just for a particular page. Click the Update Departments page (12).

      Double click the Update Departments page definition.

      Select the Error Handling subtab.

      Enter apex_error_handling_example for Error Handling Function and click Apply Changes. In the next topic, you test the application.

    Testing the Error Handling for your Page

      Now you are ready to test the error handling on this page. Click Run.

      If you receive the login window, enter your password and click Login.

      Click the Maintain Departments tab.

      Scroll down and click Add Row.

      Enter Finance for Department, select any of the Managers from the list and 1700 for Location Id. Then click Submit.

      Notice that the error you receive isn't the same as the unique constraint on Department Name. This is because this error is a validation on the Manager column. You want to turn off this validation temporarily so the error handling will execute. Click Edit Page <n> in the Developer Toolbar.

      Under Validations, double-click Manager only assigned to one department.

      Click the Conditions subtab.

      Select the Never quick pick and click Apply Changes.

      Run the page again. Click Run.

      Scroll down and click Add Row again.

      Enter Finance for Department Name, select a Manager and enter location again and click Submit.

      Notice this time the error handling executes and displays the error message from the constraint_lookup table. In the next section, you debug your application. Click Application <n> in the Developer Toolbar.

Debugging Your Application

    In this topic, you review your application in debug mode. Perform the following steps:

    From the Applications page, click Edit Application Properties.

    Select Yes for Debugging and click Apply Changes.

    Click the Run Page icon to run the page.

    Click Debug in the Developer Toolbar.

    At the bottom of the tabular form, click Add Row.

    Enter Finance for Department Name, select a Manager and enter a Location, then click Submit.

    You receive the error from the error handling procedure. You can view this information in debug mode. Click View Debug in the Developer Toolbar.

    Each time the page is rendered, a debug SHOW script is created and each time the page is submitted, a debug ACCEPT script is created. Since you just submitted the page, that script should be the first one in the list. Select the first View Identifier link.

    Notice the first line in the list says ACCEPT. You see the statements and messages that were processed. Scroll down the list.

    Notice that the Error Handling procedure was executed to produce the error in the constraint_lookup table. When you are done reviewing this page, close the debug window.

    Click Application <n> in the Developer Toolbar.

Checking Your Application Using the Advisor

    In this topic, you use the Advisor to check your Application for errors and quality checks. Perform the following

    From the Application page, click the Application Utilities icon in the upper right.

    Click Advisor.

    Accept the defaults and click Perform Check.

    Your application was checked for all of the rules specified on the screenshot in the previous step. Notice that 18 Quality Assurance checks were identified. To fix a violation, click the View link. In the case below, you will add help text for P2_SALES_AMOUNT item.

    Click the Help Text subtab.

    Enter something in the Help Text field and click Apply Changes.

    Your help text was saved. Click the Advisor Results tab.

    Click Check Again.

    Notice that the number of Quality Assurance violations is now 17. In the next section, you examine how build options work.

Adding and Using Build Options

    Build options enable you to conditionally display specific functionality within an application. Build Options have two possible values: INCLUDE and EXCLUDE. If you specify an attribute to be included, then the Application Express engine considers it at run time. However, if you specify an attribute to be excluded, then the Application Express engine treats it as if it did not exist.

    In this topic, you add a exclude build option and then associate it with page 7 and 8 and the Load Project Data tab in your application.

    Click Shared Components.

    Under Logic, select Build Options.

    Click Create.

    Enter Next Release for Build Option, make sure Exclude is selected for status and enter a comment. Then click Create.

    The Build Option was created. Select the Application breadcrumb.

    You want to exclude the Data Load functionality on page 7 and 8. Click Page 7.

    Double-click the Data Load Source page definition.

    Select the Configuration subtab.

    Select Next Release from the Build Option drop down and click Apply Changes.

    Click the right arrow to navigate to page 8.

    Double-click the Data /Table Mapping Page Definition.

    You should be directed to the Configuration subtab automatically. Select Next Release for Build Option and click Apply Changes.

    You also want to apply the Build Option to the Load Project Data tab. Under Shared Components, expand Tab Set: TS1 and double-click Load Project Data.

    Select Next Release for Build Option and click Apply Changes.

    Select the Application breadcrumb.

    Click Run Application.

    Notice that the Load Project Data tab is not displayed. In the Menu, select Load Project Data.

    Notice that you receive an error because page 7 is not found or excluded using the build option. Click Application <n> in the Developer Toolbar. In the next section, you learn how to create a packaged application.

Creating a Packaged Application

    From a user's perspective, importing and installing an application is a complicated process. First, you create the target database objects and seed data. Second, you import and install the application definition and all related files, including images, themes, and any other required static files.

    Creating a packaged application using the Supporting Objects utility greatly simplifies this process. Instead of performing numerous steps to create the database objects and then import and install the application and all supporting files, you can define the supporting objects so that the application and supporting files can be migrated in a few easy steps.

    In this topic, you create a packaged application for the Project Tasks Application you have been working on.

    Click Supporting Objects.

    You can specify that you want the build options to be included. Under Installation, select Build Options.

    Select the Next Release (exclude) checkbox and click the Install tab.

    Since this application uses the HR schema which is installed with the database, you only have to create the scripts to install the other tables you use. The DDL and DML scripts for the Project and Tasks tables have already been created. You also want to add the scripts you ran in the Error Handling section of this tutorial. Click Create.

    Select Create from File and click Next.

    Enter constraint_lookup for Name and click Next.

    Select the constraint_lookup.sql script for the File and click Create Script.

    Notice that the script was added to the list. Let's add the error_handling function script. Click Create.

    Select Create from File and click Next.

    Enter error handling function for Name and click Next.

    Select the error_handling_example_func.sql file and click Create Script.

    You could add the other scripts if desired. When done, click the Export tab.

    Make sure that Include Supporting Object Definitions in Export is set to Yes and click Apply Changes.

    You can export the application now. Select Export Application from the Tasks menu.

    If you want to export to the Export Repository, select Database for File Format and Click Export Application.

    Select Manage Export Repository from the Tasks Menu.

    Notice the export appears in the list. You may install this export in another instance or within the same instance.

Summary

    In this tutorial, you have learned how to:

    • Create a List
    • Create Page Zero and add the list to this page
    • Modify your tabs
    • Add error handling to your application
    • debug your application
    • check the application using the advisor
    • Add and use a build option
    • Create a packaged application

    Resources

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.