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


Options



Before You Begin

Purpose

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

Time to Complete

Approximately 20 minutes.

Overview

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in an Oracle Database Cloud Service.

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 look-ups and transformation rules that are executed when the Data Load Wizard runs.

In this tutorial, you create a series of Data Load Wizard pages declaratively. You will also define table look-up and data transformation rules while creating the Data Load Wizard. For more information on using a Data Load Wizard, reference the Data Loading Wizard Examples in the Oracle Application Express Application Builder User's Guide.

Please keep in mind the following while running this tutorial:

  • Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
    • Logging into Oracle Application Express in a Oracle Database Cloud Service:  Reference the Oracle Help Center for your Oracle Database Cloud Service. To do this, go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
    • Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
  • Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
  • Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service,  you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

What Do You Need?

Before starting this tutorial, you should:

  • Have access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
  • Install Oracle Application Express Release 5.0 into your Oracle Database (for on-premises only).
  • Download and unzip the files.zip into your working directory.
  • Provision at least one Oracle Application Express user. See Creating New User Accounts in Oracle Application Express Administration Guide.

Creating 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. From the Oracle Application Express home page, click the down arrow next to SQL Workshop, and select SQL Scripts.

    Description of this image
  2. Click Upload >.

    Description of this image
  3. Click Choose File.

    Description of this image
  4. Locate the dependents.sql file in your working directory, and click Open. Once the dependents.sql is chosen for File, click Upload.

    Description of this image
  5. Locate the entry for the script uploaded in the previous step, and click the Run icon.

    Description of this image
  6. Click Run Now.

    Description of this image
  7. The script completed execution. To view the results, click the View Results icon.

    Description of this image
  8. The script results show that all the statements in the script processed successfully.

    Description of this image

Creating an Application with a Report on DEPENDENTS table

In this section, you create a database application with an interactive report that shows the entire data of the DEPENDENTS table. Perform the following steps:
  1. Click the down arrow next to the Application Builder tab and select Database Applications.

    Description of this image
  2. Click the Create icon.

    Description of this image
  3. Accept the default, and click Next >.

    Description of this image
  4. Enter Data Loading Sample for Name, and click Next >.

    Description of this image
  5. Accept the default, and click Next >.

    Description of this image
  6. Select Application Express Accounts for Authentication Scheme, and click Next >.

    Description of this image
  7. Click Create Application.

    Description of this image
  8. Data Loading Sample application created successfully. You want to add a report region to the Home page, click 1 - Home.

    Description of this image
  9. Click the Regions tab in the gallery, located under the Grid Layout.

    Description of this image
  10. Drag Interactive Report from the Regions gallery, and drop it into CONTENT BODY in the Grid Layout.

    Description of this image
  11. In its property editor, under Identification, enter Dependents List for Title, and click the Code Editor: SQL query icon.

    Description of this image
  12. Enter the following SQL query in the Code Editor - SQL Query, and click the Validate icon.
    select FIRST_NAME,
           LAST_NAME,
           BIRTHDATE,
           RELATION,
           GENDER,
           RELATIVE_ID
    from DEPENDENTS

    Description of this image
  13. Click OK.

    Description of this image
  14. Click Save and Run Page.

    Description of this image
  15. Enter your Oracle Application Express credentials, and click Log In.

    Description of this image
  16. Data Loading Sample application displays the Home page with an empty report, because there are no records in DEPENDENTS table. To enable the end-user load data into this table, you create a Data Load Wizard. Click Application<n> in the developer toolbar.

    Description of this image

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 enable the end-user to load data into the DEPENDENTS table. Perform the following steps:
  1. Click Create Page >.

    Description of this image
  2. Click the Data Loading icon.

    Description of this image
  3. Enter Load Dependents for Definition Name, select the following values for the remaining fields, and click Next >.

    Table Name DEPENDENTS (table)
    Unique Column 1 FIRST_NAME (Varchar2)
    Unique Column 2 LAST_NAME (Varchar2)
    Description of this image
  4. You want to add a Transformation Rule which ensures that all the values of the GENDER column are always stored in Upper Case. Enter Uppercase Gender for Rule Name, move GENDER (Varchar2) to the selected group for Select Column(s), select To Upper Case for Type, and click Add.

    Description of this image
  5. The new transformation rule is created. Click Next >.

    Description of this image
  6. You want to create a Table Lookup such that, when you give a name for the RELATIVE_ID column in the data load file, that name is matched with ENAME in EMP table, and the corresponding EMPNO will be stored in the RELATIVE_ID column of the DEPENDENTS table. Select the following values, and click Add.

    Column Name RELATIVE_ID (Number)
    Table Name EMP (table)
    Return Column EMPNO (Number)
    Upload Column ENAME (Varchar2)
    Description of this image
  7. The Table Lookup is created. Click Next >.

    Description of this image
  8. The details of the four pages to be created as part of the Data Load Wizard are displayed. Accept the defaults, and click Next >.

    Description of this image
  9. Select Identify an existing navigation menu entry for this page for Navigation Preference, Home for Existing Navigation Menu Entry, and click Next >.

    Note: By selecting "Identify an existing Navigation Menu Entry for this Page" for Navigation Preference, the new page will be highlighted on an existing, top level, menu entry.

    Description of this image
  10. You want to branch to the Home page, if the user clicks the Cancel of Finish buttons on the Data Load Wizard. Click the List of Values icon for Cancel Button Branch to Page.

    Description of this image
  11. Select 1 Home.

    Description of this image
  12. Enter 1 for Finish Button Branch to Page, and click Next >.

    Description of this image
  13. Click Create.

    Description of this image
  14. Data Load Source page, which is the first page of the Data Load Wizard opens. Click Save and Run Page.

    Description of this image

Using the Data Load Wizard to Load the Data from a File

In this section, you use the Data Load wizard created in the previous section, to load data into the DEPENDENTS table from a file. Perform the following steps:
  1. In the Data Load Source step of the Data Load Wizard, select Upload file, comma separated (*.csv) or tab delimited for Import From.

    Description of this image
  2. Click Choose File.

    Description of this image
  3. Locate the dependents_data1.txt file in your working directory, and click Open. Once the dependents_data1.txt is chosen for File Name, click Next.

    Description of this image
  4. The next step in the Data Load Wizard is Data/Table Mapping. In this step, the tab delimited data from the file is mapped against each column name of the DEPENDENTS table. Notice that some of the values of the GENDER column are in lower case, and all the values for RELATIVE_ID are names instead of IDs. Check the data/table mapping, and click Next.

    Description of this image
  5. The Data Validation step of the wizard appears. All the transformation rules and table lookups are applied in this step.Notice that all the GENDER values are in upper case and the RELATIVE_ID values are replaced by corresponding EMPNObased on the table lookup created earlier. Click Load Data.

    Notice that the RELATIVE_ID value for Pierre and CLAUDE is not replaced with the corresponding EMPNO from EMP table. This is because, a suitable match is not found for these values in the ENAME column of EMP table.

    Description of this image
  6. The next step in the wizard displays the Data Load Results. Notice that two records failed because no match is found for the RELATIVE_ID value in the EMP table. Click Finish.

    Description of this image
  7. The report displays all the records inserted into the DEPENDENTS table. You want to add a button on this page which opens the Data Load Wizard, when clicked. Click Edit Page 1 in the developer toolbar.

    Description of this image

Loading Data Using Copy-Paste Option of the Data Load Wizard

In this section, you add a button to the Home page which, upon clicking, opens the Data Load Wizard. Once the Data Load Wizard opens, you load data into the DEPENDENTS table, using the copy and paste option of the Data Load Wizard. Perform the following steps:
  1. In the Rendering tab, right-click Dependents List, and select Create Button.

    Description of this image
  2. In the property editor of the new button, under Identification, enter P1_Load_Dependents for Button Name and Load Dependents for Label. Under Layout, select Above Region for Button Position. Under Behavior, select Redirect to Page in this Application for Action, and click No Link Defined for Target.

    Description of this image
  3. Click the List of Values icon for Page.

    Description of this image
  4. Click 2.

    Description of this image
  5. Click OK.

    Description of this image
  6. Click Save and Run Page.

    Description of this image
  7. Notice the Load Dependents button on the Home Page. Click Load Dependents.

    Description of this image
  8. The Data Load Wizard opens. Select Copy and Paste for Import From.

    Description of this image
  9. Locate and open dependent_data2.txt from your working directory. Select the entire contents, and copy them to the clipboard.

    Description of this image
  10. Paste the copied contents in the text area provided for Copy and Paste Delimited Data.

    Description of this image
  11. Click Next.

    Description of this image
  12. Notice that the third column is not mapped to any column of the DEPENDENTS table. Select BIRTHDATE - date for the third column.

    Description of this image
  13. Click Next.

    Description of this image
  14. Notice that the GENDER values are all converted to uppercase and all the RELATIVE_IDs are mapped with EMPNO. Click Load Data.

    Description of this image
  15. The Data Load Results displays. Click Finish.

    Description of this image
  16. All the new records are now seen in the report. Click Application<n> in the developer toolbar.

    Description of this image

Review the Data Load Definition

In this section, you review the Data Load definition which can be modified, if desired. Perform the following steps:
  1. Click Shared Components.

    Description of this image
  2. Under Data References, click Data Load Definitions.

    Description of this image
  3. Notice that Load Dependents is listed as an existing Data Load Definition. To review its definition, click Load Dependents.

    Description of this image
  4. The entire definition for Load Dependents displays.

    Description of this image
  5. Scroll down, and notice that you can create new Transformation Rule, Table Lookup, or Column Aliases List of Values by clicking the corresponding buttons on this screen. In this case, you are not modifying this Data Load Definition. Therefore, click Cancel.

    Description of this image

Summary

In this tutorial, you have learned how to:

  • Create a Data Load Wizard using Data Loading Page Wizard in Oracle Application Express 5.0
  • Load data into the table using the File Upload option of the Data Load Wizard
  • Load data into the table using the Copy-Paste option of the Data Load Wizard

Want to Learn More?

Credits

  • Curriculum Developer: Anjani Pothula

  • Other Contributors:Drue Swadener