3 Populating Your Workspace

To download the .zip file:

1. Navigate to the Tutorials page: http://docs.oracle.com/database/apex-5.1/nav/tutorials.htm
2. Click Download sample tables and data zip file.
   The download begins.
3. Extract the .zip file to an easily accessible location on your local machine.

To import the sample tables into your workspace:

1. Sign in to your Oracle Application Express workspace.
2. Click SQL Workshop and select SQL Scripts.
   
   
   Description of the illustration GUID-B5C0E4F9-D3A8-4D94-B276-47D0679AB8BA-default.png
   
3. Click the Upload button.
   The Upload Script dialog displays.
4. For Import file, click the appropriate button to browse your system for a file (for example, in Chrome the Choose File button).
5. Navigate to the directory where you extracted the .zip file and open the files folder or subdirectory.
6. Select Demo_Project_Basic_Tables.sql and click Open.
7. Click Upload.
8. In the SQL Scripts list, click the Edit icon (pencil) adjacent to the script.
   The Script Editor appears.
9. In the Script Editor, review the raw code of the script.
   
   
   Description of the illustration GUID-1FB3BD07-B4BE-411C-9EFE-384332AC123C-default.png
   

   The script creates the following objects:

   • DEMO_TEAM_MEMBERS - Stores the details for the users of the application including a photo. Includes:

     • Primary Key - ID

     • Unique Key - USERNAME

     • Before Insert or Update Trigger - Populates ID with Globally Unique Identifier (GUID); Populates audit columns; Sets USERNAME to uppercase

   • DEMO_PROJECTS - Stores the details for the projects, including the lead, status and completed date. Includes:

     • Primary Key - ID

     • Unique Key - NAME

     • Foreign Key - PROJECT_LEAD must match an ID in DEMO_TEAM_MEMBERS

     • Index - PROJECT_LEAD to improve query performance on the foreign key column

     • Before Insert or Update Trigger - Populates ID with GUID; Populates audit columns

   • DEMO_MILESTONES - A child table under projects for milestones, including the due date. Includes:

     • Primary Key - ID

     • Foreign Key - PROJECT_ID must match an ID in DEMO_PROJECTS

     • Index - PROJECT_ID to improve query performance on the foreign key column

     • Before Insert or Update Trigger - Populates ID with GUID; Populates audit columns

   • DEMO_TASKS - A child table under projects for tasks, optionally for a given milestone, and including an assignee and dates. Includes:

     • Primary Key - ID

     • Foreign Key - ASSIGNEE must match an ID in DEMO_TEAM_MEMBERS

     • Foreign Key - PROJECT_ID must match an ID in DEMO_PROJECTS

     • Foreign Key - MILESTONE_ID must match an ID in DEMO_MILESTONES

     • Indexes - ASSIGNEE, PROJECT_ID, MILESTONE_ID to improve query performance on the foreign key columns

     • Before Insert or Update Trigger - Populates ID with GUID; Populates audit columns

   • DEMO_TO_DOS - A child table under tasks for to dos, and including an assignee and dates. Includes:

     • Primary Key - ID

     • Foreign Key - PROJECT_ID must match an ID in DEMO_PROJECTS

     • Foreign Key - TASK_ID must match an ID in DEMO_TASKS

     • Foreign Key - ASSIGNEE must match an ID in DEMO_TEAM_MEMBERS

     • Indexes - PROJECT_ID, TASK_ID, ASSIGNEE to improve query performance on the foreign key columns

     • Before Insert or Update Trigger - Populates ID with GUID; Populates audit columns

   • DEMO_LINKS - A child table under tasks for links. Includes:

     • Primary Key - ID

     • Foreign Key - PROJECT_ID must match an ID in DEMO_PROJECTS

     • Foreign Key - TASK_ID must match an ID in DEMO_TASKS

     • Indexes - PROJECT_ID, TASK_ID to improve query performance on the foreign key columns

     • Before Insert or Update Trigger - Populates ID with GUID; Populates audit columns

10. Click the Run button.
    The Run Script dialog appears.
11. Click Run Now to run the Demo_Project_Basic_Tables.sql script.
    The Manage Script Results page appears.
12. Click the View Results icon (magnifying glass).
    Your results should indicate 37 Statements Processed, 37 Successful, and 0 With Errors.

1. In the SQL results summary page, click the SQL Scripts breadcrumb.
   
   
   Description of the illustration GUID-33B3457B-A248-41ED-9580-7722534B43A5-default.png
   
2. Click Upload.
3. For Import file, click the appropriate button to browse your system for a file (for example, in Chrome the Choose File button).
4. Navigate to the directory where you extracted the .zip file and open the files folder or subdirectory.
5. Select the SQL script file named Demo_Project_Basic_Data.sql and click Open.
6. Click Upload.
7. In the SQL Scripts list, click the Run icon (pencil) adjacent to the Demo_Project_Basic_Data.sql script.
   The Run Script dialog appears.
8. Click Run Now.
   The Manage Script Results page appears.
9. Click the View Results icon (magnifying glass).
   Your results should indicate 2 Statements Processed, 2 Successful, and 0 With Errors.

To run an Oracle Database package and populate the sample tables with the sample data:

1. In the SQL Scripts Results page, click the SQL Workshop tab at the top of the page to return to the SQL Workshop home page.
   
   
   Description of the illustration GUID-87CC5D94-1AD4-4F9C-8699-D58D5B1826C5-default.png
   
2. Click SQL Commands.
3. Enter the following code:

   begin
      demo_projects_data_pkg.load_sample_data;
   end;
   

   
   
   Description of the illustration GUID-71349455-8BD6-4D29-9977-597246A5CF8D-default.png
   
4. Click Run.
   When the package is finished, the Results tab displays Statement processed.

To review the package that you created in the Object Browser:

1. Click SQL Workshop to return to the SQL Workshop home page.
2. Click Object Browser.

   Tip:

   For quicker navigation, click the arrow next to SQL Workshop to display a drop-down menu.
3. In the list of tables, select DEMO_TEAM_MEMBERS.
4. Click the Data tab. The Data tab displays the raw information of the DEMO_TEAM_MEMBERS table.
   
   
   Description of the illustration GUID-379665CD-823C-4561-B87D-CE33C1353478-default.png
   

   Note:

   You can also click the Edit icon on the Data report to edit the current data directly. However, leave the data unchanged for now.
5. In the left pane, click Tables to view a drop-down list and select Packages.
   A list of all packages present in your workspace schema displays.
6. Select DEMO_PROJECTS_DATA_PKG.
7. Select the Body tab to review the primary PL/SQL code that you executed to populate your data.

   This package includes complex PL/SQL code to insert images and replicate users entering in records. It is not important that you understand the PL/SQL code in this package, as you do not normally have to populate data in this matter. Normally, you would create the tables with no data and then use the application you build to insert the records.

8. When you finish, click the Oracle logo in the top-left of the page to return to the Workspace home page.