Creating a Data Load Wizard for Your Application with Oracle Application Express 5.0


Options



Last updated: 04/17/15 01:30 pm EDT

Before You Begin

Purpose

This tutorial shows you how to create a Data Loading Wizard for your application so that end users can add data into a table.

Time to Complete

Approximately 15 minutes

Introduction

Applications with data loading capability allow end users to dynamically import data into a table within any schema to which the user has access. To do this, end users run a Data Load Wizard that uploads data from a file or copies and pastes data entered by the end user directly into the wizard.

With Oracle Application Express 5.0, you can create a series of data load wizard pages in your application using the new Data Loading page wizard. The wizard allows you to add table lookups and transformation rules that are executed when the Data Load Wizard runs.

In this tutorial, you create a DEPENDENTS table, create an application with a report, use the Data Loading page wizard to create the Data Load wizard pages in your application that the end user uses to load the data. The Data Loading definition contains a table lookup for the relative name and a data transformation rule to make sure that Gender is stored in uppercase.

Prerequisites

Before starting this tutorial, you should:

  • Have install Oracle Database 11g or later.
  • Have install Oracle Application Express Release 5.0.
  • Download and unzip the files.zip file into a working directory to use during this tutorial.
  • Create a workspace and user to perform this tutorial.

Create the DEPENDENTS table using a SQL Script

In this section, you upload a SQL Script and run it to create the DEPENDENTS table. Perform the following steps:

  1. To log in to Oracle Application Express, enter the following details, and click Sign In.
    Workspace: <your workspace name>
    Username: <your username>
    Password: <your password>

    alt description here
  2. Select SQL Workshop > SQL Scripts.

    alt description here
  3. Click Upload >.

    alt description here
  4. Click Browse.

    alt description here
  5. Select dependents.sql from the files you downloaded from the prerequisites section of this tutorial and click Open.

    alt description here
  6. Click Upload.

    alt description here
  7. Your script was uploaded successfully. click the Run icon.

    alt description here
  8. Click Run Now.

    alt description here
  9. Your script ran, click the View Results icon.

    alt description here
  10. The script ran successfully. In the next section you create an instant application and a report region on the Home page.

    alt description here

Create an Application with a Report

In this section, you create an instant database application and create an interactive report that shows all the data in the DEPENDENTS table. Perform the following steps:

  1. Select Application Builder > Database Applications.

    alt description here
  2. Click Create >.

    alt description here
  3. Change the Name to Data Loading and click Create Application.

    alt description here
  4. Click Create Application.

    alt description here
  5. Your application was created successfully. You want to create a report region on the Home page. Select the Home page icon.

    alt description here
  6. Right-click Regions and select Create Region.

    alt description here
  7. Enter Dependents List for Title and select Interactive Report for Type.

    alt description here
  8. Enter the following SQL SELECT statement and click Save and Run Page.
    select FIRST_NAME,
    LAST_NAME,
    BIRTHDATE,
    RELATION,
    GENDER,
    RELATIVE_ID
    from DEPENDENTS

    alt description here
  9. Login as a valid user and click Login.

    alt description here
  10. The Home page displays the report. The reason you see the “No data found” message is because the table is currently empty. In the next section, you use the Data Loading Page wizard to create a Data Load wizard. You will then use the Data Load wizard to load data into the DEPENDENTS table. After the data is loaded into the table, this report will display the loaded data. Click the Application link in the developer toolbar.

    alt description here

Creating a Data Load Wizard Using the Data Loading Page Wizard

In this section, you use the Data Loading page wizard to create four pages in your application that an end user will run to load data into the DEPENDENTS table. Perform the following steps:

  1. Click Create Page >.

    alt description here
  2. For Page type, click the Data Loading icon.

    alt description here
  3. You need to specify the table you want to load data into and what columns uniquely identify the dependent in the table. In this case, the concatenation of FIRST_NAME and LAST_NAME uniquely identify a Dependent in the DEPENDENTS table. Specify and/or select the following and click Next >.

    Data Load Definition Name Load Dependents
    Table Names DEPENDENTS
    Unique Column 1 FIRST_NAME (Varchar2)
    Unique Column 1 LAST_NAME (Varchar2)
    alt description here
  4. You want to create a transformation rule to make sure that when the data is stored, the data for the GENDER column is stored in Upper Case. Specify and/or select the following and click Add.

    Column Name Gender
    Name Uppercase Gender
    Type
    To Upper Case
    alt description here
  5. The transformation was created. Click Next >.

    alt description here
  6. You want to add a lookup for this data load. In this case, the relative_id should correspond to the id of the EMP table so that it is a valid employee of this dependent. Specify and/or select the following and click Add.

    Column Name RELATIVE_ID
    Table Name EMP
    Column Name(of key value)
    EMPNO
    Uploaded Column
    ENAME
    alt description here
  7. The lookup for RELATIVE_ID was created, click Next >.

    alt description here
  8. The four pages in the screenshot will be created as a result of this Data Load. Click Next >.

    alt description here
  9. For navigation preference, select Identify an existing navigation menu entrE for this page option. Select Homefor Existing Navigation Menu Entry Click Next >.

    alt description here
  10. You want to branch to the Home page if the user selects the Cancel or Finish buttons. Enter 1 for both Branch to Page fields and click Next >.

    alt description here
  11. Click Create to create the wizard pages for the data load. In the next section, you test the Data Load Wizard pages that were created.

    alt description here

Testing Your Data Load Wizard

In this section, you test your data load wizard pages by loading some data. Perform the following steps:

  1. Click Save and Run Page.

    alt description here
  2. Enter your login information and click Login.

    alt description here
  3. You want to load the data using a tab delimited file. Click the Upload file option for Import From.

    alt description here
  4. Click Browse.

    alt description here
  5. Select the dependent_data1.txt file and click Open.

    alt description here
  6. Click Next >.

    alt description here
  7. Notice that there are some values for the GENDER column that are lower case and that the values for RELATIVE_ID are the employee name that the dependent is a relative of. When you click Next, the Lookup and Transformation Rules will be applied. Click Next >.

    alt description here
  8. Notice now that all the values for GENDER are uppercase and the EMPNO of the relative is applied for each ENAME that if found in the EMP table. Notice that there are two names it did not find so an error will be generated when the Load takes place. Click Load Data.

    alt description here
  9. Notice that 9 rows were inserted and the 2 rows for the lookup that were not found failed. Click Finish.

    alt description here
  10. The Home page is now displayed and the report contains the data that you just loaded.

    alt description here
  11. This time you want to use the Copy and Paste option instead of uploading a file. Click Copy and Paste.

    alt description here
  12. Open the dependent_data2.txt file and copy the entire contents of the file to your clipboard (ctrl+c).

    alt description here
  13. Paste the contents into the Copy and Paste Delimited Data area, make sure the First Row has Column Names is still checked and click Next >. Note that the third column name is set to BIRTHDAY instead of the column name in the DEPENDENTS table which is BIRTHDATE.

    alt description here
  14. Because the column name did not match, you need to select the column name from the list. Select BIRTHDATE from the list for the third column.

    alt description here
  15. Click Next to apply the lookup and transformation rule.

    alt description here
  16. Notice the values for GENDER are in uppercase and all the EMPNO values were found in the EMP table. All the data will be inserted and one row will be updated because it had the same FIRST_NAME and LAST_NAME as the previous load. Click Load Data.

    alt description here
  17. The data load results are displayed. Click Finish to return to the Home page.

    alt description here
  18. The report is updated with the additional and revised data. In the next section, you review the data load definition. Click the Application link in the developer toolbar.

    alt description here

Review the Data Load Definition

In this section, you review the Data Load definition so that if desired, you can make changes. Perform the following steps:

  1. Notice that four Data Load pages were created in your application. To see the Data Load definition for these pages, Click Shared Components.

    alt description here
  2. Under Data References, click Data Load Definitions.

    alt description here
  3. Click the Load Dependents definition.

    alt description here
  4. The definition is displayed. Note that you can create or modify any existing Table Lookups or Transformation Rules from this page.

    alt description here
    alt description here

Want to Learn More?

In this tutorial, you learned to:

  • Create a Data Loading wizard
  • Test your Data Loading wizard by loading data into a table
  • View the Data Loading definition

Resources

Credits

  • Lead Curriculum Developer: Dimpi Sarmah