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

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.

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.
   

   

2. Click Upload >.

   

3. Click Choose File.

   

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

   

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

   

6. Click Run Now.

   

7. The script completed execution. To view the results, click the View Results icon.

   

8. The script results show that all the statements in the script processed successfully.

   

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.

   

2. Click the Create icon.

   

3. Accept the default, and click Next >.

   

4. Enter Data Loading Sample for Name, and click Next >.
   

   

5. Accept the default, and click Next >.

   

6. Select Application Express Accounts for Authentication Scheme, and click Next >.

   

7. Click Create Application.

   

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

   

9. Click the Regions tab in the gallery, located under the Grid Layout.
   

   

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

    

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

    

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
    

    

13. Click OK.

    

14. Click Save and Run Page.

    

15. Enter your Oracle Application Express credentials, and click Log In.

    

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.

    

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 >.
   

   

2. Click the Data Loading icon.

   

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)

   

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.
   

   

5. The new transformation rule is created. Click Next >.

   

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)

   

7. The Table Lookup is created. Click Next >.

   

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 >.

   

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.
   

   

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.

    

11. Select 1 Home.

    

12. Enter 1 for Finish Button Branch to Page, and click Next >.
    

    

13. Click Create.

    

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

    

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.
   

   

2. Click Choose File.

   

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.

   

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.
   

   

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.

   

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.

   

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.

   

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.
   

   

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.

   

3. Click the List of Values icon for Page.

   

4. Click 2.
   

   

5. Click OK.

   

6. Click Save and Run Page.

   

7. Notice the Load Dependents button on the Home Page. Click Load Dependents.

   

8. The Data Load Wizard opens. Select Copy and Paste for Import From.

   

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

   

10. Paste the copied contents in the text area provided for Copy and Paste Delimited Data.

    

11. Click Next.

    

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

    

13. Click Next.

    

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

    

15. The Data Load Results displays. Click Finish.

    

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

    

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

1. Click Shared Components.
   

   

2. Under Data References, click Data Load Definitions.

   

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

   

4. The entire definition for Load Dependents displays.
   

   

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.

   

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

• Data Loading Wizard Examples

• Oracle Cloud Home Page

• Other tutorials on Oracle Database Cloud Service - Database Schema Service 

• Curriculum Developer: Anjani Pothula

• Other Contributors:Drue Swadener