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

Enter the following URL in your browser to log in to Oracle Application Express.

http://localhost:8080/apex

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name> 
Username: <your username>
Password: <your password>

Select SQL Workshop > SQL Scripts.

Click Upload.

Click Choose File.

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

Click Upload.

Your script was uploaded successfully. click the Run icon.

Click Run Now.

Your script ran, click the View Results icon.

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

Select Application Builder > Database Applications.

Click Create.

Select Database and click Next.

Select Instant Application.

Change the Name to Data Loading and click Create.

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

Select Create > Region on this page.

Select the Report icon.

Alternatively, you could select the Report radio button and click Next.

Make sure Interactive Report is selected and click Next.

Enter Dependents List for Title and click Next.

Enter the following SQL SELECT statement and click Create Region.

select FIRST_NAME,
       LAST_NAME,
       BIRTHDATE,
       RELATION,
       GENDER,
       RELATIVE_ID
from DEPENDENTS

Your region was created successfully. Click Run to run the page.

Login as a valid user and click Login.

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. 

Click Create Page.

For Page type, click the Data Loading icon.

Alternatively, you could select the Data Loading radio button and click Next.

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

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.

The lookup for RELATIVE_ID was created, click Next.

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

.

The transformation was created. Click Next.

The four pages in the screenshot will be created as a result of this Data Load. Click Next.

To make it easier, you want to create a tab so that you can easily go to the correct page. Alternatively, you could create a button on the report to navigate to the Data Load wizard page. Select Use an existing tab set and create a new tab within the existing tab set option.

Enter Load Data for New Tab Label and click Next.

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.

Click Finish to create the wizard pages for the data load. In the next section, you test the Data Load Wizard pages that were created.

Click Run Page.

Enter your login information and click Login.

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

Click Choose File.

Select the dependent_data1.txt file and click Open.

Click the First Row has Column Names checkbox and click Next.

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.

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.

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

The Home page is now displayed and the report contains the data that you just loaded. You want to add more data, click the Load Data tab.

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

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

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.

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.

Click Next to apply the lookup and transformation rule.

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.

The data load results are displayed. Click Finish to return to the Home page.

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.

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

Under Logic, click Data Loading.

Click the Load Dependents definition.

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