# Building an Advanced Demo Project Application Using Oracle Application Express 5.1 (v1.0.1)

## 1. Overview

#### Purpose

This tutorial for Oracle® Application Express release 5.1 (Application Express) demonstrates how to build an advanced application for managing projects and tasks using some of the latest features of Application Express 5.1.

#### Workshop Scenario

Your team tracks projects you are currently working on. Rather than using a spreadsheet or some commercial project tracking tool you have been tasked with building a Web application so that the team has a custom application that meets everyone's requirements. The DBA has created a script which creates various tables and populates them with the current data.
Optionally, part way through the development of this application you decide to move your development to the Oracle Database Cloud Service. In this way you can continue developing the application from anywhere, rather than only when you are in the office.
This is the second of a two part workshop series. In the first workshop a simple application was quickly built that allows everyone to maintain the data. In this second workshop you will greatly improve the application and utilize a number of advanced techniques to make a very polished application.
The first workshop Building a Basic Demo Project Application Using Oracle Application Express 5.1 should be completed first for those who have no prior experience with Application Express.

#### Time to Complete

Approximately 2 - 3 hours

#### Introduction

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 and deploy professional applications that are both fast and secure. Application Express is a fully supported, no cost option of the Oracle Database.

In this tutorial, you use Application Express to build a fully functioning database application to maintain team members and their tasks. Tasks may be assigned to milestones and are associated with a specific project.

#### Prerequisites

To run this tutorial, you need:

- Access to an Oracle Application Express 5.1.0 (or above) environment, such as an account on apex.oracle.com, or a local installation of Oracle Database 11g or later.
- An Application Express workspace and a development user.
- The apex-adv-demo-projects-2848027.zip file extracted into your working directory.

## 2. Loading the Basic Application and Data Structures

This topic is for those people who have not completed the Building a Basic Demo Project Application Using Oracle Application Express 5.0 workshop. If you have completed this workshop proceed to Topic 3.

In this topic, you import the completed application from the first workshop, which will also create the database objects, and populate the tables with sample data.

1. Sign in to your Application Express development environment.

2. An Application Express application can contain Supporting Objects. Supporting Objects allow you to define database object installation and upgrade scripts that are invoked when importing an application. In this way, Supporting Objects allow you to package both the application and database objects needed in a single file.
   Two scripts have been added to the application created in the previous workshop to build the database objects and populate the tables as part of the application import.

   From the Application Express Home Page, click App Builder, and select Import.

   Note: Some of the labels outlined below may be different based on the browser you are using.

   For Import File, click Choose File, where you extracted `apex-adv-demo-projects-2848027.zip`, open the `files` folder.
   Locate the `Advanced Projects App Export - Initial Import.sql` file, and double-click the file or click the file and then click Open.

   Verify that the File Type is Database Application, Page or Component Export
   Click Next.

   For the File Import Confirmation step, click Next.

   For the Install step:

   - Parsing Schema - Verify the schema is correct
   - Build Status - verify the selection is Run and Build Application
   - Install As Applications - verify the selection is Auto Assign New Application Id

   Click Install Application.

   A box with Installing Application ... will be displayed for a short period (< 1 minute).

   On the Supporting Objects step, verify the Install Supporting Objects is Yes.
   Click Next.
   Note: If you already have the tables created and populated, then select No on this step.

   For the Confirmation step, click Install.

   A box with Installing Supporting Objects ... will be displayed for a short period (< 1 minute).

   Once the application has been successfully installed, click Run Application.

3. The application log in screen is displayed.
   Enter your username and password credentials that you use to log into the App Builder.
   Click Log In.

4. Click on the menu options and naviagate around the various reports and forms.

   Given that you ran the application from the App Builder, there is a Developer Toolbar at the bottom of the screen. This toolbar allows developers to quickly navigate between runtime and various sections within the App Builder. The Developer Toolbar also allows developers to initiate and view runtime debugging, show the HTML grid, and run the Theme Roller.

   To return to the App Builder for your application, click Application xxxx in the Develop Toolbar.

In this topic you imported the basic projects application, which also created the database objects and populated them with data.

## 3. Adding Application Components

In this topic, you add an error handling function and a corresponding table to hold user defined error messages. You also add authentication and improve the Navigation Bar. Lastly you will create a database view.

1. When a database exception occurs an `ORA-xxxx` error message displays. This error message is not very informative for end users.
   To improve error handling you will create an Oracle Database function. This function will replace generic Oracle Database error messages, such as `ORA-0001 unique constraint (APEX_N00B.{table_name}) violated`, with a user friendly message. These user friendly messages will be maintained in a table you create called DEMO_PROJECT_CONSTRAINT_LOOKUP.

   Before updating the application to utilize the error handling procedure, run the application to see how the error messages are currently displayed.

   If you are not currently in the runtime environment, from App Builder click Run for the Demo Projects application.

   if you have not previously run the application the application log in screen is displayed.
   Enter your username and password credentials that you use to log into the App Builder.
   Click Log In.

   In the runtime environment, click Projects.
   In the Projects report, select the edit icon for Configure APEX Environment.

   On the Maintain Project form, for Name enter `Train Developers on Web development tool`, and click Save.
   Note: You can enter any existing project name to replicate this behavior.
   Verify that the page displays `1 error has occurred ORA-0001 unique constraint (APEX_N00B.DEMO_PROJECTS_UK) violated`.

   Click Home in the Developer Toolbar.

2. Create the table to hold the user friendly error messages.

   Use SQL Workshop > Object Browser to create a table.
   From the Application Express Home Page, click SQL Workshop, and select Object Browser.

   Click the Create ( + ) button, and select Table.

   For Table Name enter `DEMO_PROJECT_CONSTRAINT_MSGS`.
   For the table columns, enter the following:

   - For Column 1 -
     Column Name - enter `constraint_name`
     Type - select VARCHAR2
     Scale - enter `30`
     Not Null - check
   - For Column 2 -
     Column Name - enter `message`
     Type - select VARCHAR2
     Scale - enter `4000`
     Not Null - check

   Click Next.

   For Primary Key, leave the default No Primary Key.
   Click Next.

   On the Foreign Key step, click Next, as there are no foreign keys for this table.

   On the Constraints step, click Next, as there are no constraints for this table.

   Click SQL, to expand the SQL for creating the table.
   Verify the SQL is the same as in the screen shot below.
   Click Create Table.

3. The table is created, however, there are no records in the table.

   In Object Browser, with the DEMO_PROJECT_CONSTRAINT_MSGS table selected, click Data.
   Click Insert Row

   To insert a record, input the following:

   - Constraint Name - enter `DEMO_TEAM_MEMBERS_UK`
     Note: The constraint name entered into this table must exactly match the name of the constraint on the table, otherwise, the error message will not be displayed.
   - Message - enter `Username must be unique`

   Click Create and Create Another.

   Insert another record:

   - Constraint Name - enter `DEMO_PROJECTS_UK`
   - Message - enter ``Project Name must be unique
     Click Create and Create Another.

   Insert another record:

   - Constraint Name - enter `DEMO_PROJECTS_STATUS_CHK`
   - Message - enter `Status can only be Assigned, In-Progress, or Completed`
     Click Create and Create Another.

   Insert another record:

   - Constraint Name - enter `DEMO_TASKS_UK`
   - Message - enter `Task Name must be unique within the same project`
     Click Create.

4. Rather than creating a function from scratch, you will upload a script to create the required function.
   This function is copied from the Sample Database Application - Specifically from the package SAMPLE_PKG, and the function demo_error_handling within that package. The function has been updated to retrieve the error messages from the DEMO_PROJECT_CONSTRAINT_LOOKUP table instead of the DEMO_CONSTRINT_LOOKUP table used by the Sample Database Application. In that way, if the Sample Database Application is uninstalled the error function will still work.

   Use SQL Workshop to upload a script that creates the function.

   Click the up arrow ( ↑ ), before Object Browser.
   Click SQL Scripts.

   Click Upload.

   Click Choose File, where you extracted `apex-adv-demo-projects-2848027.zip`, open the `files` folder.
   Locate the Demo_Project_Adv_Func.sql file, and double-click the file or click the file and then click Open.

   Click Upload.

5. Click the Run icon to the right of the script you uploaded (top row).

   Click Run In Background.

6. Click the View Results icon for the script you just ran.

   At the bottom of the results page you should see "1" Statements Processed, "1" Successful, and "0" With Errors.

7. Now that the function and table are defined, update the application definition to utilize the error handling function.

   From SQL Workshop > SQL Scripts, click App Builder on the toolbar.
   From the list of applications, select Demo Projects.

8. Click Edit Application Properties.

9. In the Edit Application Definition page, click the Error Handling tab.
   For Error Handling Function enter `DEMO_PROJECTS_ERROR_HANDLING` (the name of the function you just created).
   Click Apply Changes.

10. Run the application to see how the error messages are displayed now.
    From App Builder click Run Application.

    In the runtime environment, click Projects.
    In the Projects report, select the edit icon for Configure APEX Environment.
    On the Maintain Project form, for Name enter `Train Developers on Web development tool`, and click Save.
    Verify that the page displays `1 error has occurred Project Name must be unique`.

    Click Application xxxx in the Developer Toolbar.

11. You use Shared Components to define various different types of objects in one place that can be used across an application. As a best practice, Oracle recommends defining the code once in a shared component wherever possible and then reference that component on the different pages.
    Note: Some of the Shared Components, such as Authentication Schemes, Lists, Navigation Menu, and Themes, can only be defined as Shared Components, while others, such as List of Values, can also be implemented directly on individual pages.

    From the application home page, click Shared Components.

12. Application security is very important in the majority of applications, except for "Public" applications. You must ensure users enter valid credentials, generally username and password (Authentication), and that once logged in that each user has appropriate rights within the application (Authorization).

    Application Express provides a number of built-in Authentication Schemes, including Single Sign-On, LDAP, HTTP Header Based (for use with OAM / IDM), and Application Express Users. For this exercise you will continue using the default configuration, which is Application Express users.

    Application Express also makes it easy to define Authentication Schemes, that can be utilized to define access to almost every component within Application Express including pages, regions, items, report columns, buttons, and processes. If you review the Packaged Apps, which are included within Application Express, you will see that the "productivty" applications include the ability to switch on authentication with access defined for Administrators, Contributors, and Readers. In these applications, only users defined as Administrators can access any of the administration pages. Further, Readers can not access the buttons for creating or updating any records in the application.

    Create an authorization scheme to ensure only people entered as Team Members can log into the application.

    Under Security, click Authorization Schemes.

13. Click Create.

    Verify Create Authorization Scheme is From Scratch.
    Click Next.

    For Details, input the following:

    - Name - enter `Team Members`
    - Schema Type - select Exists SQL Query
    - SQL Query - copy and paste the following:

      `select 1
      from demo_team_members
      where upper(username) = upper(:APP_USER)`

    - Identify error message displayed when scheme violated - enter `You must be a team member to use this application.`

    Click Create Authorization Scheme.
    Note: `:APP_USER` is a built-in application item that returns the username of the currently logged in user.

14. Rather than applying the authorization scheme to every page, apply the scheme to the application properties.

    Click the Shared Components breadcrumb entry.
    Alternatively, on the toolbar, click the Shared Components button, which is located in the top-right and contains a triangle, circle, and square in its icon.

    Under Security, click Security Attributes.

    Click the Authorization tab.
    For Authorization scheme, select Team Members.

    Click Apply Changes.

15. In order to test the authorization scheme it is necessary to create a new user account.

    In the Application Express toolbar, click Administration, identified by a user icon with a spanner, then select Manage Users and Groups.

    Click Create User.

    To create a user, input the following:

    - Username - enter `unauthorized`
    - Email Address - enter `unauthorized@email.com`
    - Password - enter `unauthorized`
    - Confirm Password - enter `unauthorized`
    - Require Change of Password on First Use - `No`

    Click Create User.

    Navigate to the runtime environment (tab or window).
    Click Logout in the navigation bar (top left).

    On the Log In page, for Username enter `unauthorized`, and for Password enter `unauthorized`.
    Click Log In.
    Verify the access denied message is displayed.

    Click OK.
    On the Log In page, enter your username and password credentials that you use to log into the App Builder.
    Click Log In.

16. When you create an application, a Navigation Bar is created to allow entries to be placed on the top right of all pages (on the same line as the application name). The Navigation Bar is defined by a list.

    Update the Navigation Bar list entries.

    Navigate to the Application Express Builder (tab or window).
    Under Navigation, click Lists.

    In the report, click the Desktop Navigation Bar list.

    Click Create List Entry.

    To create a new list entry, input the following:

    - Image/Class - enter `fa-user`
    - List Entry Label - enter `&APP_USER.`
    - Target Type - select URL
    - URL Target - enter `#`
    - User Defined Attributes 2 (List Item CSS Classes) - enter `has-username`

    Click Create List Entry.

    Note: To determine the User Defined Attributes, click List Template Attributes.
    For Theme select Universal Theme 42, and for List Template select Navigation Bar.
    Verify that #A02# Description is `List Item CSS Classes`.

    Note: For Image/Class you can click the up arrow at the end of the field ( ↑ ), to invoke the list of images.
    Review the library of images, provided by Font Awesome (fa). These images can be utilized throughout the application to improve aesthetics, and provide a visual indicator, rather than just text.
    In the Basic Demo Project application various images were added to the Navigation Menu.

17. Place the Logout entry under the new Application User entry.

    In the report, click the Log Out list entry.

    For Parent List Entry, select &APP_USER. .
    Click Apply Changes.

18. Navigate to the runtime environment (tab or window).
    Refresh the page to see the new navigation bar entries.

19. A database view is a logical table based on one or more tables (base tables) or views. A view contains no data itself, but derives its data from the base tables on which it is based. Views are a convenient way to combine columns from different tables, and perform operations, such as sum, on one or more of the columns.

    Use SQL Workshop > Object Browser to create a view.
    From the runtime environment, click Home in the Developer Toolbar.
    Click the down arrow ( ↓ ) next to SQL Workshop, and then select Object Browser.

    Click the Create ( + ) button, and select View.

    For View Name enter `demo_projects_view`.
    For Query cut and paste the following code:

    `select p.id
    , p.name
    , tm.full_name project_lead
    , p.completed_date
    , (select count(*)
    from demo_tasks t
    where t.project_id = p.id
    and nvl(t.is_complete_yn,'N') = 'Y'
    ) tasks_completed
    , (select count(*)
    from demo_tasks t
    where t.project_id = p.id
    and nvl(t.is_complete_yn,'N') = 'N'
    ) tasks_incomplete
    , ( (select count(*)
    from demo_milestones m
    , demo_tasks t
    where t.project_id = p.id
    and t.milestone_id = m.id
    and nvl(t.is_complete_yn,'N') = 'Y'
    and t.end_date <= m.due_date
    )
    + (select count(*)
    from demo_tasks t
    where t.project_id = p.id
    and t.milestone_id is null
    and nvl(t.is_complete_yn,'N') = 'Y'
    and t.end_date <= p.completed_date
    )
    ) tasks_on_time
    , ( (select count(*)
    from demo_milestones m
    , demo_tasks t
    where t.project_id = p.id
    and t.milestone_id = m.id
    and nvl(t.is_complete_yn,'N') = 'Y'
    and t.end_date > m.due_date
    )
    + (select count(*)
    from demo_tasks t
    where t.project_id = p.id
    and t.milestone_id is null
    and nvl(t.is_complete_yn,'N') = 'Y'
    and t.end_date > p.completed_date
    )
    ) tasks_late
    , (select count(*)
    from demo_milestones m
    where m.project_id = p.id
    and not exists (select t.id
    from demo_tasks t
    where t.milestone_id = m.id
    and nvl(t.is_complete_yn,'N') = 'N'
    )
    ) milestones_completed
    , (select count(*)
    from demo_milestones m
    where m.project_id = p.id
    and exists (select t.id
    from demo_tasks t
    where t.milestone_id = m.id
    and nvl(t.is_complete_yn,'N') = 'N'
    )
    ) milestones_incomplete
    , (select count(*)
    from demo_milestones m
    where m.project_id = p.id
    and not exists (select t.id
    from demo_tasks t
    where t.milestone_id = m.id
    and nvl(t.is_complete_yn,'N') = 'Y'
    and t.end_date > m.due_date
    )
    ) milestones_on_time
    , (select count(*)
    from demo_milestones m
    where m.project_id = p.id
    and exists (select t.id
    from demo_tasks t
    where t.milestone_id = m.id
    and nvl(t.is_complete_yn,'N') = 'Y'
    and t.end_date > m.due_date
    )
    ) milestones_late
    , p.created
    from demo_projects p
    , demo_team_members tm
    where tm.id = p.project_lead
    order by p.created
    `

    Click Next.

    Click Create View.

20. WIthin Object Browser, click Data to see the data in the view.

In this topic you improved the error handling, security, and navigation bar, and added a view.

## 4. Updating the Home Page

In this topic, you create a dashboard by adding new components to the Home page of the application.

Note: If you have not completed the previous topic, import the Advanced Demo Projects App Export - Topic 3.sql file into your workspace to continue working from this topic forward. You can find the export file in the `/files` subdirectory where you unzipped the original file. See Appendix A - Importing an Application.

1. Within Application Express you can utilize plug-ins, which are custom components used to extend native functionality. The most common plug-ins are custom item types, and custom regions.
   The Application Express Development Team provides a number of plug-ins that you can readily utilize within your own application, by either importing it into your application, or subscribing to another application which has the plug-in installed. The latest Development Team plug-ins are utilized within the Packaged Apps, especially applications such as Sample Charts.
   There are also over 100 plug-ins developed by the Application Express community which you can utilize. To review the community developed plug-ins go to http://apex.world > Plug-Ins.
   The Development Team also provides some older plug-ins at http://apex.oracle.com/plugins. A collection of more than 200, generally older release plug-ins, are also available at http://www.apex-plugin.com/

   Install the Sample Charts application so that you can subscribe to the badge list plug-in.

   In the main toolbar, click Packaged Apps and select Sample Apps.

   Locate the Sample Charts application. Click the Sample Charts application.

   Click Install Packaged App.

   On the Install Packaged Application page, click Next.

   Click Install Packaged App.

   A box with Installing Application ... will be displayed for a short period (< 2 minutes).

   Note: Unlike the Productivity Apps, the Sample Apps are unlocked by default. As such these applications do not need to be unlocked in order to edit the application.

2. Add the required plug-ins, from the Sample Charts application, into your application.

   Click App Builder.
   On the App Builder home page, click the Demo Projects application.

   Click Shared Components.
   Under Other Components, click Plug-ins.

   Click Create.
   For Create Plug-In, select As a Copy of an Existing Plug-in, and click Next.

   Select xxxx Sample Charts, and click Next.

   At the bottom of the page, click Deselect All.

   Locate the Copy? column:

   - Badge List - select Copy and Subscribe.

   Click Copy Plug-ins.

3. On the toolbar, click Edit Page 1, to navigate to Page Designer for Page 1 - Home Page.
   Note: If you are not on Page 1, enter `1` into the Page Selector on the toolbar, and click Go.

   ### About Page Designer

   The Page Designer is a powerful IDE that includes a toolbar and multiple panes, used to maintain and enhance pages withiin Oracle Application Express applications.
   The toolbar across the top of the page has a number of capabilities:

   - Page Selector - Displays the current page. You can enter a page number directly, use the up and down arrows to navigate one page, or click on the popup list of values to select a page.
   - Page Lock - Indicates if the page is unlocked (clear), locked by you (green), or locked by another developer (red). By clicking on this icon you can lock or unlock the page, providing it is not locked by another developer.
   - Undo Changes - Click to undo the previous update you made within Page Designer.
   - Redo Changes - Click to reapply the last update that was undone using Undo Changes.
   - Create - Create new pages, regions, shared components, develop comments and team development entries.
   - Utilities - Perform a check, delete, export, or review the history for the current page. You can also access the attribute dictionary, application utilities or upgrade the application.
   - Settings - Switch between two pane and three pane mode, or reset the layout back to the default.
   - Team Development - Show the Features, ToDos, Bugs and Feedback entries for this page. You can also drill into filtered reports for each entry type.
   - Developer Comments - Drill into the developer comments entered for the current page. You can also create new comments or delete comments from the modal window that is displayed.
   - Shared Components - Navigate to the Shared Components page.
   - Save - Save all changes you have made on the current page.
   - Save and Run Page - Save all changes and then run the current page. Note: You will not be able to run modal and non-modal pages directly using Save and Run Page. Instead you will need to navigate to a normal page and run the page from there and then navigate within the running application to the modal or non-modal page.

   Of these capabilities you will most often use Page Selector, Undo Changes, Shared Components, Save, and Save and Run Page.

   There are three main panes within Page Designer:
   - Left Pane - Includes tabs for Rendering, Dynamic Actions, Processing, and Shared Components. Each tab displays a list of the corresponding component types and components created on the current page.
     Right-click to access context sensitive menus. You can also drag components up and down within the trees to change the position or sequence of the selected component.
   - Central Pane - Includes tabs for Layout, Component View, Messages, Page Search, and Help.
     Layout shows a visual representation of the page. You can add new components to a page by dragging them from the Gallery pane, at the bottom, and dropping them in Layout.
     Messages displays current errors and warnings. Clicking on a message changes the focus within Property Editor to the corresponding attribute associated with the error or warning.
     Page Search enables you to search for any text within the current page.
     Help displays attribute specific help. Click on the attribute name within Property Editor to see information and examples for that attribute.
   - Right Pane - Displays the Property Editor. Use the Property Editor to update attributes for the selected component(s).
     When you select multiple components the Property Editor only displays common attributes. Updating a common attribute will update that attribute for all of the selected components.

   Note: Tabs can be dragged right or left to change the order of the tabs or move a tab into a different pane.

   You can change the size of each pane by selecting the dividers and sliding them left or right. Change the size of Layout and Gallery by sliding the divider between them up and down.

   You can access further information from the App Builder.
   In Page Designer, click Help, shown as a question mark on the toolbar, and then select Getting Started in Page Designer.

   Within most panes there are also a number of useful icons, generally for manipulating the content displayed in that pane.
   Of these, the icons within the Property Editor (right pane), will generally be utilized most often.
   The Property Editor Icons include:

   - Show Common - Only the most used attributes, and non-common attributes that have non-default values, are displayed.
   - Show All - Displays all attributes.
   - Collapse All - Collapses all groups.
   - Expand All - Expands all groups.
   - Go to Group - Navigate to, and expand if necessary, the selected group.

   The Filter Properties at the top of the Property Editor, can be used to quickly find a specific group or attribute.
   Note: If you are having issues finding a specific attribute, click Ctrl + F to search the page for the required attribute name.

   Page Designer also provides a number of keyboard shortcuts that can improve developer productivity.
   Click Alt + Shift + F1, or click Help on the toolbar, and then select Shortcuts.
   For example, using Go to Rendering (Alt + 1 on Windows / Option + 1 on Mac) and Go to Property Editor (Alt + 6 on Windows / Option + 6 on Mac) are useful for navigating between different page components, such as regions or items, and then updating attributes using only the keyboard, rather than having to move the mouse left to right and right to left continually.

4. Add additional regions to Page 1, starting with the badge list component.

   Page Designer includes the Layout in the cenral pane. The Layout is a representation of how the components will be positioned on the page. Existing regions, items, and buttons can be moved relative to other components by simply clicking on the component and dragging it to a new location. For example, items can be placed next to each other by dragging the second item to the end of the first item, and dropping it in the yellow box that appears when you hover in the desired location. New components can quickly be added to an existing page by dragging the component from the Gallery up to the desired position within the Layout.

   Use Page Designer's drag-and-drop functionality to quickly add a Badge List from the Gallery to the Layout.

   In Page Designer, within the Gallery (directly below the Layout), click Regions, and locate Badge List.
   Click and hold Badge List [Plug-In] and drag it into Content Body, to a position directly above the Project Tasks region. It should appear as a darkened tile before you drop it into place.
   Note: When you drag the region up, and hover over the small yellow section, below Content Body, the yellow section will expand. A darker yellow section, with a black box around it, will indicate where the region will be placed.

   In the Property Editor:

   - Identification: Title - enter `Summary`
   - Source: SQL Query - copy and paste the following: `select 'Open Projects' label
     , count(*) value
     from demo_projects
     where status != 'Completed'
     UNION ALL
     select 'Upcoming Milestones' label
     , count(*) value
     from demo_milestones
     where due_date > sysdate
     UNION ALL
     select 'Open Tasks' label
     , count(*) value
     from demo_tasks
     where is_complete_yn = 'N'
     `

   Note: This query has three sub-queries, separated by UNION ALL, that will be displayed as three separate badges within the region.

   In the Property Editor, click the Template Options button (Use Template Defaults, Scroll - Default) and input the following:

   - General - enable Remove Body Padding
   - Header - select Hidden but accessible

   Click OK.
   Note: The Template Options button display changes in the Property Editor to reflect its current settings.

   In the Rendering tree, under the Summary region, click Attributes.
   In the Property Editor:

   - Settings: Top Label - select LABEL
   - Settings: Value - select VALUE
   - Settings: Color - select Yes

   In Page Designer, click Save.

5. The application already has a Project Status region, however, this chart can be improved by using the view created in the previous section.

   In the Rendering tree, click the Project Status region.
   In the Property Editor:

   - Identification: Title - enter `Project Tasks`
   - Source: SQL Query - copy and paste the following: `select id
     , name
     , tasks_completed value
     , 'Completed' series
     from demo_project_view
     union all
     select id
     , name
     , tasks_incomplete value
     , 'Incomplete' series
     from demo_project_view
     union all
     select id
     , name
     , tasks_on_time value
     , 'On-TIme' series
     from demo_project_view
     union all
     select id
     , name
     , tasks_late value
     , 'Late' series
     from demo_project_view
     `

   In the Rendering tree, click Attributes, under the Project Tasks region.
   In the Property Editor:

   - Appearance: Orientation - select Vertical
   - Appearance: Select - select No

   In the Rendering tree, click the Series Completed Tasks, under the Project Tasks region.
   In the Property Editor:

   - Identification: Name - enter `Series`
   - Source: Type - select Region Source
   - Column Mapping: Series - select SERIES
   - Column Mapping: Value - select VALUE

   In the Rendering tree, right-click the Series Incomplete Tasks, and select Delete.

6. It would be valuable to also show a similar chart for milestones.

   In the Rendering tree, right-click Project Tasks, and select Duplicate.

   In the Property Editor:

   - Identification: Title - enter `Project Milestones`
   - Layout: Start New Row - select No

   Click the edit icon for Source: SQL Query, to bring up the Code Editor.
   Click the replace icon (fourth icon from the left).
   For Find enter `tasks`, and for Replace enter `milestones`.
   Click Replace All. Click Ok.

   In the Rendering tree, click the Axes y, under the Project Milestones region.
   In the Property Editor:

   - Identification: Title - enter `Milestones`

   Click Save.
   Navigate to the runtime environment (tab or window).

7. The last region to add to the Home page is a stylized classic report. It needs specific column names that are used by the Timeline region template.

   In the Rendering tree, right-click My Outstanding Tasks region and select Duplicate.

   In the Property Editor:

   - Identification: Title - enter `Recent Projects`
   - Source: SQL Query - remove the current code, then copy and paste the following:
     `
     select p.id project_id
     , t.id
     , null event_modifiers
     , null event_attributes
     , null user_color
     , dbms_lob.getlength('PHOTO_BLOB') user_avatar
     , t.full_name user_name
     , nvl(p.completed_date, p.updated) event_date
     , (case p.status
     when 'Assigned' then 'is-removed'
     when 'In-Progress' then 'is-updated'
     when 'Completed' then 'is-new'
     end) event_status
     , p.status event_type
     , (case p.status
     when 'Assigned' then 'fa fa-clock-o'
     when 'In-Progress' then 'fa fa-refresh'
     when 'Completed' then 'fa fa-check'
     end) event_icon
     , p.name event_title
     , p.description event_desc
     from demo_projects p
     , demo_team_members t
     where p.project_lead = t.id
     order by p.updated desc
     `
   - Layout: Start New Row - select No

   Note: The columns in this query use specific column names that are required in order to match those specified in the Timeline template.

   In the Rendering tree, under the Recent Projects region, click Attributes.
   In the Property Editor:

   - Layout: Number of Rows - enter `5`
   - Appearance: Template - select Timeline
   - Pagination: Type - select No Pagination (Show All Rows)

   Click the Template Options button.
   For Style select Compact, and click OK.

   Note: There are significant differences between the Region template options, set in the previous step, and the Attribute template options set above. The region settings are used to modify how the region is displayed, whereas, attribute settings (where available) modify how the contents of the region are displayed.

8. Update the columns for this region to display an image and change the format mask of selected items.

   In the Rendering tree, under the Recent Projects region, expand the Columns folder.

   Click the USER_AVATAR column.
   In the Property Editor:

   - Identification: Type - select Display Image
   - BLOB Attributes: Table Name - select DEMO_TEAM_MEMBERS
   - BLOB Attributes: Blob Column - select PHOTO_BLOB
   - BLOB Attributes: Primary Key Column 1 - select ID
   - BLOB Attributes: Mime Type Column - select PHOTO_MIMETYPE
   - BLOB Attributes: Filename Column - select PHOTO_FILENAME
   - BLOB Attributes: Last Updated Column - select PHOTO_LAST_UPDATED

   Click the EVENT_DATE column, and for Appearance: Format Mask enter `SINCE`.
   Click Save.

9. Run the application to see how the Home page looks now.

The Home page is now complete!

## 5. Updating the Team Member Pages

In this topic, update the Team Member pages by replacing the Interactive Report with a card region. This will provide a directory of your team members' contact info and roles as though you had a stack of their business cards at hand.

Note: If you have not completed the previous topic, import the Advanced Demo Projects App Export - Topic 4.sql file into your workspace to continue working from this topic forward. You can find the export file in the `/files` subdirectory where you unzipped the original file. See Appendix A - Importing an Application.

1. From the runtime environment, click Edit Page 1 in the Developer Toolbar.
   Click on the Page Finder on the toolbar and click 2 to navigate to Page 2 - Team Members.

2. Update the page properties for Team Members..

   In the Property Editor, with the page selected, use the Filter Properties to find CSS: Inline, by entering `css`
   Copy and paste the following code:

   `.t-Card-icon img {
   width: 48px;
   height: 48px;
   }`

   Note: This CSS ensures that the images are all the same size (48 pixels) irrespective of the size of the image uploaded.

3. Interactive Reports are the default report type when generating report pages from the Create Application Wizard. However, for this page we want to convert the report type to a Classic Report type, so that we can utilize one of the built-in templates.

   Update the Interactive Report to a Classic Report, and apply the Cards template to display the team members in a more visually pleasing manner.

   In the Rendering tree, click the Team Members region.
   In the Property Editor:

   - Identification: Type - select Classic Report
   - Source: SQL Query - enter the following (replacing the existing code):
     `select id
     , full_name card_title
     , profile card_text
     , nvl(email,' ') card_subtext
     , dbms_lob.getlength('PHOTO_BLOB') card_initials
     , apex_util.prepare_url('f?p='||:APP_ID||':3:'||:APP_SESSION||'::::P3_ID:'||id) card_link
     , photo_filename
     , photo_mimetype
     , photo_charset
     , photo_last_updated
     from demo_team_members
     order by created`

   Note: This SQL query uses specific column names, such as card_title, which correspond to the columns required for the Card template.

4. The report region has the required Type and SQL Source, now the report needs to be enhanced to display an image and utilize the correct report template.
   The `DEMO_TEAM_MEMBERS` table includes columns which store an image and the image's associated properties. You can use this information to show the team Member's stored image, instead of the member's initials in the cards report.

   Locate the Rendering tree. Under Team Members, expand the Columns folder, click the CARD_INITIALS item.
   In the Property Editor:

   - Identification: Type - select Display Image
   - BLOB Attributes: Table Name - select DEMO_TEAM_MEMBERS
   - BLOB Attributes: BLOB Column - select PHOTO_BLOB
   - BLOB Attributes: Primary Key Column 1 - select ID
   - BLOB Attributes: Mime Type Column - select PHOTO_MIMETYPE
   - BLOB Attributes: Filename Column - select PHOTO_FILENAME
   - BLOB Attributes: Last Updated Column - select PHOTO_LAST_UPDATED

   Locate the Rendering tree. Under Team Members, click Attributes, and input the following:

   - Appearance: Template - select Cards

   Click the Template Options button and select the following:

   - Style - select Basic
   - Icons - select Display Initials
   - Layout - select 4 Columns
   - Body Text - select 3 Lines

   Click OK.

5. Click Save and Run Page to see the new cards.

The Maintain Team Members page was enhanced in the Basic Demo Project workshop. Therefore, the Team Member pages are now complete.

## 6. Adding the Comments Table and Pages

In this topic, a new table will be created for adding comments to projects. As is often the case, new requirements require a new data table to be created and new pages integrated into the current application.

Note: If you have not completed the previous topic, import the Advanced Demo Projects App Export - Topic 5.sql file into your workspace to continue working from this topic forward. You can find the export file in the `/files` subdirectory where you unzipped the original file. See Appendix A - Importing an Application.

1. The SQL required to create the DEMO_PROJECT_COMMENTS table have already been written. Therefore, you will use SQL Commands within SQL Workshop to implement the new table.

   From the runtime environment, click Home.
   Click SQL Workshop.
   Click SQL Commands.

   Create the table.
   In SQL Commands, copy and paste the following:

   `create table demo_project_comments (
   id number not null
   constraint demo_project_comments_pk
   primary key,
   project_id number not null,
   comment_text varchar2(4000) not null,
   created timestamp with local time zone not null,
   created_by varchar2(255) not null,
   updated timestamp with local time zone not null,
   updated_by varchar2(255) not null )
   /`

   Click Run.
   In the Results tab you should see `Table created`.

   Create a foreign key constraint to the DEMO_PROJECTS table.
   In SQL Commands, click Clear Command, and then copy and paste the following:

   `alter table demo_project_comments add constraint demo_project_comments_proj_fk
   foreign key (project_id) references demo_projects (id)
   on delete cascade;`

   Click Run.
   In the Results tab you should see `Table altered`.

   Create an index on the foreign key column to improve performance.
   In SQL Commands, click Clear Command, and then copy and paste the following:

   `create index demo_project_comments_proj_idx on demo_project_comments (project_id);`

   Click Run.
   In the Results tab you should see `Index created`.

   Create a trigger on the table to populate the primary key and audit columns for inserts and updates.
   In SQL Commands, click Clear Command, and then copy and paste the following:

   `create or replace trigger biu_demo_project_comments
   before insert or update on demo_project_comments
   for each row
   begin
   if :new.id is null then
   :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
   end if;
   if inserting then
   :new.created := localtimestamp;
   :new.created_by := nvl(wwv_flow.g_user,user);
   end if;
   :new.updated := localtimestamp;
   :new.updated_by := nvl(wwv_flow.g_user,user);
   end;
   /`

   Click Run.
   In the Results tab you should see `Trigger created`.

2. Generally, when initially adding a new data table there will be no data in the new table. However, to make development easier, you will upload and run a package which will populate the DEMO_PORJECT_COMMENTS table with data.
   Given the trigger you just implemented on the table, records are inserted with today's date and your username for CREATED and CREATED_BY. The script updates each inserted record and modifies the audit details to mimic different users entering comments on different dates.

   Note: The script uses the Project Names listed below in order to insert the comments. If the Project Name is not exactly as specified, no comments will be inserted for that project.

   - Configure Web Development Tool Environment [3 comments]
   - Train Developers on Web development tool [2 comments]
   - Migrate Legacy Applications [6 comments]

   In SQL Workshop, navigate to SQL Scripts.
   Click Upload.

   In Upload Scripts, click Choose File, where you extracted `apex-adv-demo-projects-2488027.zip`, open the `files` folder.
   Locate the Demo_Project_Adv_Data.sql file, and double-click the file or click the file and then click Open.

   Click Upload.

   Click the Run icon to the right of the script you uploaded (top row).
   Click Run in Background.

   Click the View Results icon for the script you just ran.
   At the bottom of the results page you should see `"3" Statements Processed, "3" Successful, and "0" With Errors`.

   In SQL Workshop, navigate to Object Browser.
   Under Tables, click DEMO_PROJECT_COMMENTS.
   Click the Data tab. You should see 11 rows with different CREATED and CREATED_BY values.

3. Create a report and form for Comments.

   Return to the App Builder and select the Demo Projects application.
   On the application home page, click Create Page.

   Click Form.

   Accept the default, Report with Form on Table, click Next.

   For Page Attributes, input the following:

   - Report Page Name - enter `Comments`
   - Form Page Name - enter `Add Comment`
   - Form Page Mode - select Modal DIalog
   - Breadcrumb - select Breadcrumb

   Click Next.

   For Navigation Menu, select Create a new navigation menu entry
   Click Next.

   For Data Source, input the following:

   - Table / View Name - select DEMO_PROJECT_COMMENTS (table)

   Click Next.

   For Form Page, select the following columns:

   - PROJECT_ID (Number)
   - COMMENT_TEXT (Varchar2)
   - CREATED (Timestamp(60 With Local Time Zone)
   - CREATED_BY (Varchar2)

   Click the right arrow ( > ) to move the columns from the left pane to the right pane.

   Click Create.

4. Improve the appearance of the Comments report (Page 11).
   Hide unwanted columns.

   In Page Designer, click ❯ (Save and Run) to run the Comments page.
   In the Interactive Report, click the Id column heading.
   Click the Hide Column icon (third from left).

   In the Interactive Report, click the Actions menu in the toolbar, and select Columns.

   In the Select Columns window, select Updated and Updated By.
   Click the left arrow ( < ) to move the columns from the right pane ot the left pane.
   Select Created By and click the up arrow ( ↑ ) twice, to position the column under the Project Id.
   Click Apply.

   Now the report only shows the appropriate columns

5. Create list of values (LOVs) to improve the appreance of the Project Id and Created By columns
   From the runtime environment, click Application xxxx (for example Application 96569) in the Developer Toolbar.
   Click Shared Components.
   Under Other Components, click List of Values.

   Click Create.

   For Source, leave the default Create List of Values: From Scratch, click Next.

   For name and type input the following:

   - Name - enter `Projects`
   - Type - select Dynamic

   Click Next.

   For Query, input the following: `select name d, id r from demo_projects order by 1`

   Click Create List of Values.

   Create another list of values with the following properties:

   - Create List of Values - select From Scratch
   - Name - enter `Created By`
   - Type - select Dynamic
   - For Query, input the following:
   `select full_name d, username r
   from demo_team_members
   order by 1
   `

Click Create List of Values.

6. Update the Comments page to utilize the list of values and improve the Created column.

   From Shared Components, click Edit Page 11 to return to Page Designer.

   In the Rendering tree, under Report 1, expand Columns, and select PROJECT_ID
   In the Property Editor:

   - Identification: Type - select Palin Text (based on List of Values)
   - List of Values: List of Values - select PROJECTS
   - Heading: Heading - enter `Project`

   In the Rendering tree, under Report 1, select CREATED
   In the Property Editor:

   - Appearance: Format Mask - enter `SINCE`

   In the Rendering tree, under Report 1, select CREATED_BY
   In the Property Editor:

   - Identification: Type - select Palin Text (based on List of Values)
   - List of Values: List of Values - select CREATED BY

7. Given that existing records can not be updated or deleted, the link should go to the single row view and the icon should be updated.

   In the Rendering tree, under Report 1, select Attributes.
   In the Property Editor:

   - Link: Link Column - select Link to Single ROw View
   - Link: Link Icon - use the select list to select the magnifying glass (the last icon)

   Update the ID, UPDATED, and UPDATED BY columns to be hidden so they don't display on the SIngle Row View
   In the Rendering tree, under Report 1, select ID.
   While holding the Control (Ctrl) key, also click UPDATED, and then click UPDATED_BY.
   In the Property Editor:

   - Identification: Type - select Hidden Column

8. Update the label and position of the Create button.

   In the Rendering tree, select the CREATE button.
   In the Property Editor:

   - Identification: Label - enter `Add Comment`
   - Layout: Region - select Breadcrumb
   - Layout: Button Position - select Create

   Click Save.

9. Run the report.
   You can see the report is complete, except the records are in a random order, and need to be sorted.

   Click Actions, Select Data, and then select Sort.

   For Column select Created, and for Direction select Descending.
   Click Apply.

   Save the runtime report layout.
   Click Actions, select Report, and then select Save Report.

   For Save, select Save Default Report Settings.
   For Default Report Type, accept the default, Primary.
   Click Apply.

10. The Add Comment page (Page 12) now needs attention.

    In the runtime environment, from the Comments page, click Add Comment.
    Within the Developer Toolbar, click Quick Edit.
    Hover over the Project Id column until a blue box appears around the element, and then click the blue box.

    Page Designer will be displayed with the Pxx_PRODUCT_ID item selected.
    In the Proerty Editor:

    - Identification: Type - select Select List
    - Label: Label - enter `Project`
    - List of Values: Type - select Shared Component
    - List of Values: List of Values - select PROJECTS
    - List of Values: Display Extra Values - select No
    - List of Values: Null Display Value - enter `- Select Project -`

    The Created and Created By columns should be hidden when adding a record.
    In the Rendering tree, click Pxx_CREATED.
    While holding the Control (Ctrl) key, also click the Pxx_CREATED_BY column.
    In the Property Editor:

    - Server-side Condition: Type - select Item is NOT NULL
    - Server-side Condition: Item - select Pxx_ID

    Click Save.

11. Improve the Navigation Menu by moving Comments to above Calendar and adding an icon.

    Click Shared Components in the breadcrumb.

    Under Navigation, click Lists.

    In the list, select Desktop Navigation Menu.

    In the list, click Comments.
    Input the following:

    - Entry: Sequence - enter `35`
    - Entry: Image/Class - enter `fa-comment`

    Click Apply Changes.

12. Run the application. The Comments page is now complete

13. Update Maintain Project (Page 5) to include the comments.

    Navigate to Page 5 - Maintain Project.
    In the Rendering tree, select Page 5: Project Details.
    In the Property Editor:

    - CSS: Inline - copy and paste the following code: `.a-MediaBlock-graphic img {
      width: 100%;
      }
      .t-Comments-userIcon {
      border-color: rgba(0,0,0,.25);
      }`

    Note: This CSS code is used to style the team member's image that will be displayed in the Comments report.

14. Create the Comments region.

    In Page Designer, within the Gallery (directly below the Layout), click Regions, and locate Classic Report.
    Click and hold Classic Report and drag it into Content Body, to a position directly below the Tasks region. It should appear as a darkened tile before you drop it into place.

    In the Property Editor:

    - Title - enter `Comments`
    - SQL Query - copy and paste the following code:

      `select tm.id id
      , c.id comment_id
      , ' ' icon_modifier
      , c.comment_text
      , c.created comment_date
      , dbms_lob.getlength(tm.photo_blob) user_icon
      , tm.full_name user_name
      , null actions
      , null attribute_1
      , null attribute_2
      , null attribute_3
      , null attribute_4
      from demo_team_members tm
      , demo_project_comments c
      where c.project_id = :P5_ID
      and upper(c.created_by) = upper(tm.username)
      order by c.created desc`

    - Source: Page Items to Submit - select P5_ID

    Note: The columns in this SQL query use specific names required by the Comments template.

    Note: It is critical that you set the Page Items to Submit in order for the page to operate correctly.

15. Update the columns and region attributes for the Comments region.

    In the Rendering tree, expand the Columns node, select COMMENT_DATE.
    In the Property editor:

    - Appearance: Format Mask - enter `SINCE`.

    In the Rendering tree, under Comments, select USER_ICON.
    In the Property Editor:

    - Identification: Type - select Display Image
    - BLOB Attributes: Table Name - select DEMO_TEAM_MEMBERS
    - BLOB Attributes: BLOB Column - select PHOTO_BLOB
    - BLOB Attributes: Primary Key Column 1 - select ID
    - BLOB Attributes: Mime Type Column - select PHOTO_MIMETYPE
    - BLOB Attributes: Filename Column - select PHOTO_FILENAME
    - BLOB Attributes: Last Updated Column - select PHOTO_LAST_UPDATED

    In the Rendering tree, under Comments, click Attributes.
    In the Property Editor:

    - Appearance: Template - select Comments
    - Appearance: Show Null Values as - enter `&nbsp;`
    - Messages: When No Data Found - enter `No Comments`

16. Add a buttons to the Comments region, to allow a comment to be added to the current project.

    In the Rendering tree, right-click Comments, and select Create Button.

    In the Property Editor:

    - Identification: Button Name - enter `ADD_COMMENT`
    - Identification: Label - enter `Add comment`
    - Layout: Button Position - select Edit
    - Behavior: Action - select Redirect to Page in this Application
    - Behavior: Warn on Unsaved Changes - select Page Default
    - Advanced: Static ID - enter `add-comment`

    For Behavior: Target, click the Link Builder button (currently No Link Defined).
    In the Link Builder, input the following:

    - Page - enter `12`
    - Set Items: Name - select P12_PROJECT_ID
    - Set Items: Value - select P5_ID
    - Clear Cache - enter `12`

    Click OK.

17. Create a Dynamic Action so that the Maintain Project is properly refreshed after a comment is added.

    In the left pane, click the Dynamic Actions tab (its icon is a lightning bolt).
    Right-click the Dialog Closed folder, and select Create Dynamic Action.

    For the new Dynamic Action, in the Property Editor:

    - Identification: Name - enter `Add Comments`
    - When: Selection Type - select Button
    - When: Button - select ADD_COMMENT

    In the Dynamic Actions tree, under Add Comments, click the action within the True folder (currently labeled X Refresh)
    In the Property Editor:

    - Affected Elements: Region - select Comments

    Click the Save and Run Page button on the toolbar.
    Add a comment to ensure the Maintain Project page refreshes immediately with the lastest data.

In this topic you enhanced the application to add a completely new feature, Comments.

## 7. Updating the Calendar Page

In this topic, improve the Calendar by adding a filter region. Also replace the existing Calendar query with a complex SQL which shows various colors for different statuses.

Note: If you have not completed the previous topic, import the Advanced Demo Projects App Export - Topic 6.sql file into your workspace to continue working from this topic forward. You can find the export file in the `/files` subdirectory where you unzipped the original file. See Appendix A - Importing an Application.

1. Before updating the SQL query for the Calendar region, install a package which contains three functions. These functions will return a CSS color and an icon for projects, milestones, and tasks, which can then be utilized in the calendar.

   In the App Builder Toolbar, click the down arrow next to SQL Workshop ( ↓ ), then select SQL Scripts.

   Click Upload.

   Click Choose File, where you extracted `apex-adv-demo-projects-2488027.zip`, open the `files` folder.
   Locate the Demo_Project_Adv_Pkg.sql file, and double-click the file or click the file and then click Open.
   Click Upload.

2. Click the Run icon to the right of the script you uploaded (top row).

   Click Run Now.

3. Click the View Results icon for the script you just ran.

   At the bottom of the results page you should see `"2" Statements Processed, "2" Successful, and "0" With Errors`.

4. Add a region to the calendar that contains filter controls to make the page easier for end users to use.

   In the App Builder, navigate to Page 6 - Calendar of your application.
   In the Rendering tree, right-click the Content Body folder and select Create Region.

   In the Property Editor:

   - Title - enter `Filter Bar`
   - Template - select Buttons Container

   In the Rendering tree, click and hold Filter Bar and drag it above Calendar.

5. Create the filter items.

   In the Gallery, click the Items tab.
   Click and hold Select List and drag it to the Filter Bar region and drop it into the Items placeholder.

   In the Property Editor:

   - Identification: Name - enter `P6_PROJECT_ID`
   - Label: Label - enter `Project`
   - List of Values: Type - select Shared Component
   - List of Values: List of Values - select PROJECTS
   - List of Values: List of Values: Display Extra Values - select No
   - List of Values: Null Display Value - enter `- All Projects -`

   In the Layout under ITEMS, right-click P6_PRODUCT_ID.
   Select Copy To, select Filter Bar, select Items, select P6_PRODUCT_ID, and select After.

   In the Property Editor:

   - Identification: Name - enter `P6_MILESTONE_ID`
   - Label: Label - enter `Milestone`
   - List of Values: Type - select SQL Query

   - List of Values: SQL Query - copy and paste the following code into SQL Query:

     `select name d, id r
     from demo_milestones m
     where m.project_id = :P6_PROJECT_ID
     order by m.due_date desc`

   - List of Values: Null Display Value - enter `- All Milestones -`
   - Cascading LOV Parent Item(s) - select P6_PRODUCT_ID

6. Add a Dynamic Action to the P6_PROJECT_ID and P6_MILESTONE_ID items so that the application automatically refreshes the calendar when either item changes.

   In the Rendering tree under Content Body, under the Items folder, right-click P6_PROJECT_ID, and select Create Dynamic Action.

   In the Property Editor:

   - Identification: Name - enter `Refresh Calendar`
   - When: Item(s) - select P6_MILESTONE_ID, which will add the selected item after the current entries (P6_PRODUCT_ID).

   Note: This one dynamic action will fire whenever either the Project or the Milestone items are updated. This dynamic action will also be displayed under both items in the Rendering tree.

   In the Rendering tree, under P6_MILESTONE_ID, click the action within the True folder (currently labeled X Show).
   In the Property Editor:

   - Action - select Refresh
   - Selection Type - select Region
   - Region - select Calendar

   Click Save.

   Note: The True action under P6_PRODUCT_ID is also updated to show Refresh, as it is showing the same dynamic action.

7. Update the calendar region with a new SQL Source, and associate the filter items.

   Return In the Rendering tree, select Calendar.
   In the Property Editor:

   - Source: SQL Query - remove the current SQL, then copy and paste the following:

     `select p.id
     , p.name
     , '[Project] ' || p.name ||
     '; Project Lead: ' || (select full_name from demo_team_members tm where p.project_lead = tm.id) ||
     '; Description: ' || p.description ||
     ' {Note: Projects will only show if there are Tasks with no Milestone}' description
     , demo_projects_color_pkg.project_color
     ( p_project_id => p.id
     , p_completed_date => p.completed_date
     ) css_class
     , p.completed_date start_date
     , p.completed_date end_date
     from demo_projects p
     where p.completed_date is not null
     and p.id = nvl(:P6_PROJECT_ID, p.id)
     and exists (select 'Tasks with no Milestone'
     from demo_tasks t
     where p.id = t.project_id
     and t.milestone_id is null
     )
     UNION
     select m.id
     , m.name
     , 'Project: ' || p.name ||
     '[Milestone] ' || m.name || '; Description: ' || m.description description
     , demo_projects_color_pkg.milestone_color
     ( p_milestone_id => m.id
     , p_due_date => m.due_date
     ) css_class
     , m.due_date start_date
     , m.due_date end_date
     from demo_milestones m
     , demo_projects p
     where p.id = m.project_id
     and m.project_id = nvl(:P6_PROJECT_ID, m.project_id)
     and m.id = nvl(:P6_MILESTONE_ID, m.id)
     UNION
     select t.id
     , t.name
     , 'Project: ' || p.name ||
     '; [Task] ' || t.name ||
     '; Assignee: ' || (select full_name from demo_team_members tm where t.assignee = tm.id) ||
     '; Complete? ' || decode(t.is_complete_yn, 'Y', 'Yes', 'No') ||
     '; Description: ' || t.description description
     , demo_projects_color_pkg.task_color
     ( p_task_id => t.id
     , p_completed_date => p.completed_date
     , p_due_date => null
     ) css_class
     , t.start_date start_date
     , t.end_date end_date
     from demo_tasks t
     , demo_projects p
     where p.id = t.project_id
     and t.project_id = nvl(:P6_PROJECT_ID, t.project_id)
     and t.milestone_id is null
     UNION
     select t.id
     , t.name
     , 'Project: ' || p.name ||
     '; Milestone: ' || m.name ||
     '; [Task] ' || t.name ||
     '; Assignee: ' || (select full_name from demo_team_members tm where t.assignee = tm.id) ||
     '; Complete? ' || decode(t.is_complete_yn, 'Y', 'Yes', 'No') ||
     '; Description: ' || t.description description
     , demo_projects_color_pkg.task_color
     ( p_task_id => t.id
     , p_completed_date => null
     , p_due_date => m.due_date
     ) css_class
     , t.start_date start_date
     , t.end_date end_date
     from demo_tasks t
     , demo_projects p
     , demo_milestones m
     where p.id = t.project_id
     and m.id = t.milestone_id
     and t.project_id = nvl(:P6_PROJECT_ID, t.project_id)
     and t.milestone_id = nvl(:P6_MILESTONE_ID, t.milestone_id)
     `

   - Source: Page Items to Submit - select P6_PROJECT_ID and P6_MILESTONE_ID

     Warning: If you do not update the region so that it submits the filter items as part of the region definition, then any changed values will not be utilized by the calendar region when it executes the query.

   - Appearance: Template - select Blank with Attributes

   Note: This is a complex SQL query that returns records from different tables unioned together.
   Each record is optionally restricted in the where conditions by page items (`:P6_PROJECT_ID` and `:P6_MILESTONE_ID`). The query also utilizes functions to return a CSS Class for the color and an icon for each record.
   This complex SQL is included in this lab to show the power of SQL queries and what is possible.

   In the Rendering tree, under Calendar, click Attributes. In the Property Editor:

   - Settings: Supplemental Information - enter `&DECRIPTION.`
   - Settings: Multiple Line Events - select No
   - Settings: Drag and Drop - select No
   - Settings: CSS Class - select CSS_CLASS

   Note: Given that the SQL Query now references records from different tables Drag and Drop must be disabled.
   The CSS_CLASS is determined for each record by calling one of three functions in the DEMO_PROJECTS_COLOR_PKG package: project_color, milestone_color, or task_color. These functions incorporate logic to check task completion and compare various dates. For further details, review the DEMO_PROJECTS_COLOR_PKG package body in SQL Workshop > Object Browser.

   Remove the values for Settings: View / Edit Link.

   In the Property Editor, click the link builder for View / Edit Link (currently Page 5).
   Click Clear.

8. Add a Legend below the calendar so users know what the different colors mean. This region requires CSS styling to display nicely.

   In the Rendering tree, click Page 6: Calendar.
   In the Property Editor:

   - CSS: Inline - copy and paste the following:

     `.fc-event.fa:before { display: inline-block; float: left; margin-right: 4px; }
     .fc-eventlist-desc { display: block; font-size: 11px; margin-top: 8px; color: #707070; }
     .fc-agendaList-eventDetails { width: 80%; }
     .legend-list {list-style: none; margin: 0;}
     .legend.fc-event { display: inline-block; padding: 0 4px; width: 72px; text-align: center; }`

   In the Rendering tree, right-click the Calendar region and select Create Region.

   In the Property Editor:

   - Identification: Title - enter `Legend`
   - Source: Text - copy and paste the following:

     `<ul class="legend-list fc">
     <li><strong class="legend fc-event apex-cal-red">Red</strong>&nbsp;&nbsp;Milestone in the past and tasks ended after due date.</li>
     <li><strong class="legend fc-event apex-cal-orange">Orange</strong>&nbsp;&nbsp;Milestone in the past and <em><strong>incomplete</strong></em> tasks.</li>
     <li><strong class="legend fc-event apex-cal-green">Green</strong>&nbsp;&nbsp;Milestone in the past and all tasks ended before due date.</li>
     <li><strong class="legend fc-event apex-cal-yellow">Yellow</strong>&nbsp;&nbsp;Milestone in the future and Tasks scheduled to finish <u><strong>after</strong></u> due date.</li>
     <li><strong class="legend fc-event apex-cal-blue">Blue</strong>&nbsp;&nbsp;Milestone in the future and Tasks scheduled to finish on or before due date.</ li>
     </ul>`

   - Appearance: Template - select Collapsible

   Note: This HTML code uses the CSS classes added to the page CSS Code to achieve the desired presentation.

9. Click the Save and Run Page button and review the calendar.
   Notice the different types of records indicated by different icons for projects, milestones, and tasks.
   Select different projects and milestones using the filter items. Also hover over different records to see the tooltip.

   Notice how the Milestone item, within the Filter Bar, is right up against the Product item.
   Within the Developer Toolbar, click Quick Edit.
   Hover over the Milestone item until a blue box appears around the element, and then click the wrench (top right corner).

   Using Live Template Options you can now modify any template options and see the visual difference in the page immediately.
   For Advanced: Left Margin select Large, and notice how the item moves to the right of the Product item.
   Click Save.

   Note: When you save Live Template Options any changes are saved back into the meta data reprository, as though you had made the changes directly in Page Designer.

   Click on List (top right) to see the entries listed.

The calendar is now complete.

## 8. Summary

Note: If you choose, you can import the Demo Projects App Export - Topic 7 (Final).sql file into your workspace, which is the completed application. You can find the export file in the `/files` subdirectory where you unzipped the original file.

In this tutorial, you learned how to:

- Implement an error handling function to show user friendly messages.
- Define an authorization scheme such that only Team Members can log into the application.
- Create a view.
- Install a Packaged App and subscribe to plug-ins that are then used within the application.
- Utilize specialized region templates, such as Timeline, Cards, and Comments.
- Display images in report regions.
- End-to-end steps for adding a new data table into an existing application.
- Implement an advanced calendar with filters, and CSS classes.

#### Resources

- APEX OTN Home Page
- To learn more about Application Express, refer to additional OBEs in the Oracle Learning Library
- Oracle Application Express: Developing Web Applications
- Oracle University

#### Credits

- Lead Curriculum Developer: David Peake
- Other Contributors: Shakeeb Rahman

## Appendix A - Importing an Application

If you wish to import an application export into your environment follow these procedures:
Note: These procedures will overwrite your existing application definition with the file you are importing.

1. Navigate to the App Builder Home Page, by clicking the App Builder tab, or the up arrow.

Note: Some of the labels outlined below may be different based on the browser you are using.

2. Click Import.

   For Import File, click Choose File, where you extracted `apex-adv-demo-projects-2848027.zip`, open the `files` folder.
   Locate the appropriate Advanced Projects App Export file, and double-click the file or click the file and then click Open.

   Verify that the File Type is Database Application, Page or Component Export.
   Click Next.

   For the File Import Confirmation step, click Next.

   For the Install step:

   - Parsing Schema - verify the schema is correct
   - Build Status - verify the selection is Run and Build Application
   - Install As Application - select Change Application Id
     Note: If you don't want to overwrite your existing application but create a new application, select Auto Assign New Application ID.
   - New Application - enter the Application Id for your existing application.
     Note: The New Application text field will only be displayed after the Change Application Id option has been selected.

   Click Install Application.

   On the Confirm Replace Application step, click Replace Application.
   Note: If this step doesn't display then you did not enter the existing Application Id on the previous step.

   A box with Installing Application ... will be displayed for a short period (< 1 minute).

   These Advanced App exports include Supporting Objects. If the tables from the Basic application, such as DEMO_PROJECTS already exist, then only the appropriate additional database objects will be installed.

   For Supporting Objects, accept the default for Install Supporting Objects of Yes, and click Next.

   Click Install.

   A box with Installing Supporting Objects ... will be displayed for a short period (< 1 minute).

   The installation of database objects and seed data may succeed or fail, depending on what database objects are already created.
   If installation fails, click Install Summary, and review the errors.
   The errors should relate to objects already existing, such as `ORA-00955: name is already used by an existing object`, or inserts failing due to unique constraints being violated.

   If installation succeeds, then click Edit Application.

3. Continue with the appropriate topic!

## Appendix B - Migrating your Application Development between Environments

#### Overview

An application written with Application Express can readily be moved from one environment to another. It is normal development practice to move an application from development to test to production. This generally involves exporting the application from development and importing it into the test or production environment. If the latest development also requires changes to database object structures, such as creating a new table or adding a new column, then a separate Database Definition Language (DDL) script should be written for updating the other environments. Similarly, if data needs to be manipulated, such as inserting or updating records, then a Data Manipulation Language (DML) script should be written.

Migrating where you do your application development, for example from on-premise to the cloud, is much the same as the steps required for rolling out a new version of your application to test or production. However, rather than just providing scripts for the deltas between the current production and development environments, you need to create scripts for all of the database objects (DDL) and you need to move all of the data from development into the new environment.
This section covers the steps required to move your development environment from one Oracle Application Express installation to another.

Oracle Database Cloud Service: There are currently three Oracle Database Cloud services available - Database Schema Service, Database as a Service (DBaaS), and Exadata Service.
With the Schema Service you are subscribing to a slice (single schema) of an Oracle Database, which you share with many other tenants. For the security of all tenants, external access to the service is restricted to RESTful Web Services and Application Express applications. Oracle SQL Developer 4.0, and above, enables you to define a RESTful connection to your Schema Service. This connection can be used to easily move database objects and table data from any Oracle Database you can access to the cloud. However, this RESTful connection can not be used to move data from your Schema Service to an external Oracle Database.
Database as a Service and Exadata Service both provide full access to the underlying Oracle Database. For security reasons, most ports, such as 1521 which is generally used for database communication, are not accessible. However, port 22 is available and together with SSH Tunneling can be used to define a connection to the database in your service. Using SQL Developer 4.0, and above, you can move databse objects and data into or out of your Database as a Service and Exadata Services.

#### Warning:

You cannot import an application into an earlier release of Application Express.
For example, if you are exporting an application from the Oracle Database Cloud - Schema Service, which is currently Application Express 5.0, you can not import that application into an on-premise installation that is only running Application Express 4.2.
You will need to update the target Application Express environment prior to importing the application.

Check the version of Application Express in both your current development environment and the environment you are migrating to.

In any release of Application Express, check the bottom right corner for the version information.

In Application Express 5.0 and above, you can also click Help and then select About.

#### Migration Steps

Up until this point, you may have been developing on a local development instance, and now want to move your development to the Oracle Database Cloud service to continue.

Migrating the environment where you develop your application involves the following:

1. Exporting the application from the current environment, and importing it into the new environment
2. Recreating all of the database objects, such as tables, packages and so forth, and then populating the tables with the data from the current environment
3. Testing the application in the new environment

1. Migrating the Application

   An application written in Application Express is stored as meta-data in the Oracle Database. As you define applications, pages, regions, items, and so forth, the definitions are saved in various tables defined within the Application Express Engine schema. When you export an application a single SQL file is created by extracting the application definitions from the meta-data tables. When importing the application, records are inserted (or updated if overwriting an existing application) into the Application Express meta-data tables, of the environment being imported into. Therefore, once an application is imported, all of the application definitions can be reviewed from the App Builder, and the application can be run immediatley. However, running the application will produce errors if the required database objects are not available in the workspace.

   Note: The export only captures the application definition, and does not export any of the underlying database objects or data that may be associated with the application. Application Express does include the ability to define installation and update scripts within Supporting Objects. However, developers must manually define these scripts. The use of Supporting Objects is not covered in this workshop.

   1. From your current development environment, navigate to the Home Page for your current application.
      Click Export / Import.

      Click Export.

      For Export Preferences set the following:

      - Export Private Interactive Reports - select Yes
      - Export with Original IDs - select Yes
        Note: Exporting with the Original IDs will allow an export from the target environment to be reimported back into this current environment, if necessary at some later date.

      Click Export.

      In the Save Dialog, click Save.
      Note: It is good practice to rename the export file and include a version or date so that subsequent exports do not overwrite previous exports.
      Creating a subdirectory, specifically for all of the files required for the migration, makes it easier to locate the files when using them within the target environment.

   2. Log into your target Application Express environment.

      Navigate to the App Builder Home Page.

      Note: Some of the labels outlined below may be different based on the browser you are using.

      Click Import.

      For Import File, click Choose File. In the operating system File Browser, navigate to the subdirectory where you saved the export file.
      Locate the export file, and double-click the file or click the file and then click Open.

      Verify that the File Type is Database Application, Page or Component Export
      Click Next.

      For the File Import Confirmation step, click Next.

      For ease of reference it is preferable to keep the same Application Id in the target development environment. However, this may not be viable, in which case assigning a new application Id will need to be used.

      For the Install step, verify the Parsing Schema is correct, and verify the Build Status is Run and Build Application,
      For Install As Applications, select Reuse Application ID xxxxx From Export File.
      Click Install Application.

      Note: The majority of Application Express workspaces are only associated with a single schema. As such the Parsing Schema will not need to be updated as it will default to the only associated schema.

      - If you are installing into the Orcle Database Cloud - Schema Service then there are restrictions on the ranges for Application Ids.
        Return to the previous step and for Install As Application, select Auto Assign New Application ID.

      - The existing Application Id from your current development environment may already be used by another application in the target environment. This Application Id may exist in your workspace or another workspace in the same environment.
        Return to the previous step and for Install As Application, select Auto Assign New Application ID.

      A box with Installing Application ... will be displayed for a short period (< 1 minute).

      Once the application has been successfully installed, click Edit Application.

   You have installed the application in your target environment. However, until you have imported the database objects (tables) and data, you will get errors when you try and run the application.

2. Migrating Database Objects and Data

   The two primary ways to move the database objects and data from one environment to another, for a single application, is using either Oracle SQL Developer, or Application Express > SQL Workshop.
   Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer is familiar to most professional Oracle developers and DBAs, but perhaps not to business developers.
   Oracle Application Express includes SQL Workshop which also provides capabilities for managing database objects. SQL Workshop is designed for application developers who do not have direct access to the underlying Oracle Database, such as in hosted environments such as http://apex.oracle.com.

   Note: Other techniques are available if moving complete development environments, or whole workspaces. For example, if using Oracle Database 12c Multitenant Option, you may be able to simply move the Pluggable Database (PDB) from one environment to another using Oracle Enterprise Manager 12c. In addition, there are a number of techniques available to move tables with large data volumes between environments.

   In order to use SQL Developer you must be able to connect to the Oracle Database schemas for both the development environment and the target environment. You need to connect to the data schema associated with your workspace.
   Once connected to both schemas, it is simply a matter of dropping the required database objects from the current schema into the Cart, where you can also include the table data, and then moving it to the target schema.

   To determine your Oracle Database schema name associated with your Application Express workspace, log into Application Express.
   Click SQL Workshop, and review the schema name listed.

   Determine if you can use SQL Developer, by answering the following questions:

   1. Can you connect to the Oracle Database schema in the current environment?
      - On-Premise - MAYBE: If you have, or can obtain, database credentials for the current schema from your Oracle Database Administrator
      - apex.oracle.com - NO: SQL access is not provided for this public facing service
      - Oracle Database Cloud Service - Database as a Service (DBaaS) / Exadata Service - YES: The required credentials are provided
      - Oracle Database Cloud Service - Database Schema Service - NO: Although you can connect to this services using SQL Developer, you can not use the Cart feature to move objects out of the cloud environment
      - Local Install - YES: If you have installed the Oracle Database directly on to your laptop or desktop, you should be able to determine, or reset the
      - Oracle Database Cloud Service - Databas as a Service (DBaaS) / Exadata Service - YES: Although you can connect to these services using SQL Developer, you can not use the Cart feature to move objects out of the cloud environment
      - Public Hosting Service - MAYBE: If you have, or can obtain, database credentials for the current schema from your Hosting Provider
   2. Can you connect to the Oracle Database schema in the target environment?
      - On-Premise - MAYBE: If you have, or can obtain, database credentials for the target schema from your Oracle Database Administrator
      - apex.oracle.com - NO: SQL access is not provided for this public facing service
      - Oracle Database Cloud Service - YES: SQL Developer can be connected and used to move data into these cloud environments
      - Local Install - YES: If you have installed the Oracle Database directly on to your laptop or desktop, you should be able to determine, or reset the password for the data schema associated with your workspace.
      - Public Hosting Service - MAYBE: If you have, or can obtain, database credentials for the target schema from your Hosting Provider
   Note:Oracle Database Cloud Service currently includes three distinct services - Database Schema Service, Database as a Service (DBaaS), and Exadata Service.

   If you answered "YES" to both questions above, then follow the steps outlined in B-1 : Migrating using SQL Developer.
   If you could not answer both questions "YES", then follow the steps outlined in B-2 : Migrating using Application Express Only.

   ### B-1 : Migrating using SQL Developer

   1. Connecting to SQL Developer

      If SQL Developer is not currently installed on your computer, download the latest version from Oracle Technology Network (OTN) here.

      You will need to define two connections - one to the current and one to the target Oracle Database schemas, associated with your Application Express workspaces.

      If you are connecting SQL Developer to an On-Premise, local install, or Public Hosting Service, then start SQL Developer.
      Click Add (New Connection) and enter the following:

      - Connection Name - enter a meaningful name for the schema
      - Username - enter the schema name associated with the Application Express workspace
      - Password - enter the password for the schema
      - Check Save Password
      - Hostname - enter the appropraite Hostname for your environment
      - Port - enter the appropriate port for your environment (normally 1521)
      - SID / Service Name - enter either the SID or Service Name for your environment

      Click Test to ensure your connection details are correct.
      Click Save.

      If you are connecting SQL Developer to the Oracle Database Cloud - Database Schema Service then following this documentation:
      Using Oracle Database Cloud - Database Schema Service > ... > Configuring Oracle SQL Developer Cloud Connection
      and Using Oracle Database Cloud - Database Schema Service > ... > Setting Up Secure FTP Account.
      Warning: You must be using SQL Developer 4.0 or above. The instructions in the lab are based on SQL Developer 4.1.
      Note: With Database Schema Service you get a slice (schema) of a fully managed Oracle Database running on Exadata. However, you do not have access to the operating system or direct access to the underlying Oracle Database. For this reason, connection to this service is via RESTful Web services over HTTPS.
      You will create a connection using the Service SFTP User Name details.

      If you are connecting SQL Developer to the Oracle Database Cloud - Database as a Service (DBaaS) / Exadata Service then following this lab:
      Oracle Database Cloud Service - Advanced Hands On Labs: Lab 2.
      Warning: You must be using SQL Developer 4.0 or above. The instructions in the lab are based on SQL Developer 4.1.
      Note: With DBaaS and Exadata Service you have access to the entire database, with root, and DBA level access. However, as this service is available over the Internet, not all ports are open, including port 1521. Port 1521 is the default port the database listener services connection request with. For this reason, connection to these services is via SSH Tunneling, using port 22 which is open.
      You will create a connection on port 22 of the DBaaS / Exadata Service virtual machine running your database. The SSH Tunneling then forwards the traffic from SQL Developer to port 1521 on the same virtual machine.

   2. Moving Database Objects and Data

      If your target Oracle Database Schema is On-Premise, a local install, or a Public Hosting Service, then follow this documentation:
      SQL Developer User's Guide > 1. SQL Developer Concepts and Usage > 1.15 Using the Cart.

      If your target Oracle Database Schema is Oracle Database Cloud - Database Schema Service, then follow this documentation:
      Using Oracle Database Cloud - Database Schema Service > ... > Creating and Deploying a Cart of Objects.

      If your target Oracle Database Schema is Oracle Database Cloud - Database as a Service (DBaaS) / Exadata Service, then follow this documentation:
      SQL Developer User's Guide > 1. SQL Developer Concepts and Usage > 1.15 Using the Cart.
      Note: Becuase you have a standard connection to your cloud service, via SSH Tunneling, then you follow the SQL Developer documentation. The documentation for the Database Schema Service is specific to that service, as it utilizes the Secure FTP server to transfer the files.

      You need to load the following database objects into the SQL Developer Cart:

      - Table DEMO_MILESTONES - Including data
      - Table DEMO_PROJECTS - Including data
      - Table DEMO_TASKS - Including data
      - Table DEMO_TEAM_MEMBERS - Including data
      - Trigger BIU_DEMO_MILESTONES
      - Trigger BIU_DEMO_PROJECTS
      - Trigger BIU_DEMO_TASKS
      - Trigger BIU_DEMO_TEAM_MEMBERS
      Note: You do not need to include the package DEMO_PROJECTS_DATA_PKG as the data will be included in the SQL Developer cart.

### B-2 : Migrating using Application Express Only

The SQL Workshop within Application Express provides all of the tools required for migrating database objects and data. However, you will need to perform more steps to accomplish this task than simply using the SQL Developer Cart, and dragging and dropping database objects.

1. Preparing the Database Objects and Data Files

   1. Log into your current Application Express development environment.

   2. Use SQL Workshop to create a script file, for creating the table definitions.

      Click SQL Workshop.
      Click Utilities.

   3. Locate Generate DDL. Click Generate DDL.

   4. Click Create Script.

      Verify the Schema name is correct, and click Next.

      For Output select Save As Script File, and for Object Type check Table.
      Click Next.

      Select the following tables:

      - DEMO_MILESTONES
      - DEMO_PROJECTS
      - DEMO_TASKS
      - DEMO_TEAM_MEMBERS

      Click Generate DDL.

      For Script Name, enter a meaningful name. Optionally enter a description.
      Click Create Script.

      After a short wait, generally less than a minute, you will be returned to SQL Scripts. You will see the scripts that were uploaded earlier, then the page will refresh and will show the new script just created.
      Click the Edit icon (pencil) on the recently created script.
      Click Download.

      Click Save.

   5. Use SQL Workshop to create a script file, for creating the trigger definitions.

      Click SQL Workshop.
      Click Utilities.

      Locate Generate DDL. Click Generate DDL.

      Click Create Script.

      Verify the Schema name is correct, and click Next.

      For Output select Save As Script File, and for Object Type check Trigger.
      Click Next.

      Select the following triggers:

      - BIU_DEMO_MILESTONES
      - BIU_DEMO_PROJECTS
      - BIU_DEMO_TASKS
      - BIU_DEMO_TEAM_MEMBERS

      Click Generate DDL.

      For Script Name, enter a meaningful name. Optionally enter a description.
      Click Create Script.

      After a short wait, generally less than a minute, you will be returned to SQL Scripts. You will see the scripts that were uploaded earlier, then the page will refresh and will show the new script just created.
      Click the Edit icon (pencil) on the recently created script.
      Click Download.

      Click Save.

   6. Use SQL Workshop to create XML files for your data.

      In the Application Express main toolbar, click the SQL Workshop down arrow ( ↓ ), select Utilities and then select Data Workshop.

   7. Under Data Unload locate to XML. Click to XML.

      Verify the value for Table Owner is the correct schema.
      For Table select DEMO_MILESTONES.
      For Columns click on ID, hold down the Ctrl key, and then click on UPDATED_BY, such that all of the columns are selected.
      Click Unload Data.

      For Save As enter a meaningful name.
      Click Save.

      After saving the file you will be returned to the Unload to XML - Columns dialog.
      For Table select DEMO_PROJECTS.
      For Columns click on ID, hold down the Ctrl key, and then click on UPDATED_BY, such that all of the columns are selected.
      Click Unload Data.

      For Table select DEMO_TASKS.
      For Columns click on ID, hold down the Ctrl key, and then click on UPDATED_BY, such that all of the columns are selected.
      Click Unload Data.
      Note: You will need to scroll down in the list of columns to see the UPDATED_BY column.

      For Table select DEMO_TEAM_MEMBERS.
      For Columns click on ID, hold down the Ctrl key, and then click on UPDATED_BY, such that all of the columns are selected.
      Click Unload Data.
      Note: You will need to scroll down in the list of columns to see the UPDATED_BY column.

      Click Cancel, to exit the dialog.

2. Recreating the Database Objects and Migrating the Data

   1. Log into your target Application Express development environment.

   2. Use SQL Workshop to load and run the script file, for creating the table and trigger definitions.

      Click SQL Workshop.
      Click SQL Scripts.

   3. Upload the script to create the tabes first.

      Click Upload.

      For File, click Choose File. In the operating system File Browser, navigate to the subdirectory where you saved the table script file.
      Locate the table script file, and double-click the file or click the file and then click Open.

      Click Upload.

   4. Click the Run icon to the right of the script you uploaded.

      Click Run Now.

   5. Click the View Results icon for the script you just ran.

      At the bottom of the results page you should see "9" Statements Processed, "9" Successful, and "0" With Errors.

   6. Upload the script to create the triggers.

      Click Upload.

      For File, click Choose File. In the operating system File Browser, navigate to the subdirectory where you saved the table script file.
      Locate the table script file, and double-click the file or click the file and then click Open.

      Click Upload.

   7. Click the Run icon to the right of the script you uploaded.

      Click Run Now.

   8. Click the View Results icon for the script you just ran.

      At the bottom of the results page you should see "8" Statements Processed, "8" Successful, and "0" With Errors.

   9. Currently the tables you created do not have any data. Use the XML files you created to populate the tables.

      Note: The order in which the tables are populated is crucial, to ensure referential integrity does not prevent records loading.
      For example, loading any records into DEMO_PROJECTS before loading the records into DEMO_TEAM_MEMBERS will fail, as the ASSIGNEE column in DEMO_PROJECTS must correspond to an existing record in DEMO_TEAM_MEMBERS.

      In the Application Express main toolbar, click the SQL Workshop Down Arrow ( ↓ ), select Utilities and then select Data Workshop.

      Under Data Load, locate XML Data. Click XML Data.

      Verify the value for Schema is correct.
      For Table, select DEMO_TEAM_MEMBERS. For File, click Choose File, locate the file for DEMO_TEAM_MEMBERS, and double-click the file or click the file and then click Open.

      Click Load Data.

      Under Data Load, click XML Data.
      For Table, select DEMO_PROJECTS. For File, click Choose File, locate the file for DEMO_PROJECTS, and double-click the file or click the file and then click Open.

      Under Data Load, click XML Data.
      For Table, select DEMO_MILESTONES. For File, click Choose File, locate the file for DEMO_MILESTONES, and double-click the file or click the file and then click Open.

      Under Data Load, click XML Data.
      For Table, select DEMO_TASKS. For File, click Choose File, locate the file for DEMO_TASKS, and double-click the file or click the file and then click Open.

   10. Review the tables in SQL Workshop > Object Browser to ensure the data has loaded successfully in all four tables.

- Test the Migrated Application

  The target development environment should now have everything you need to continue developing the application.

  Note: Before running the application, especially if you are using an Oracle Database Cloud Service, you may need to create a runtime user.
  In the main Application Express Toolbar, click Administration (on the right), and select Manage Users and Groups.
  Click Create User, enter the same username you use in the current development environment.

  In the App Builder Home Page, click the Run icon for the Demo Projects application.
  Note: If the My Outstanding Tasks report has no data in your target environment, this is caused by logging into the runtime application with a different username to the one used in your current development environment. Go to Administration and create a new user.

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

Topic List:
  Click a topic to navigate to that section.
Expand All Topics:
  Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
  Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
  Click the button to print the content. The content that is currently displayed or hidden is printed.

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