Applications with data loading capability enable end users to dynamically import data into a table within any schema to which the user has access. To import data, 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. This section explains how to create a Data Load Wizard.
See Also:
"Data Loading Wizard Examples" in Oracle Application Express End User Guide to learn about using the Data Load wizard.Note:
A Data Load Wizard is not designed or intended to load hundreds of thousands of rows of data. While it is possible to use a Data Load Wizard to load this high volume of data, you may encounter performance issues with both transmitting and loading large data files. Tools like Oracle SQL Developer and Oracle SQL*Loader are better suited to loading large volumes of data.An application developer creates a Data Load Wizard by creating a Data Load page with the Create Page Wizard. During the creation process, developers can specify the upload table and its unique columns, table lookups, and data transformation rules. The Create Page Wizard creates a Data Load table that defines the Data Load Wizard.
The Data Load Wizard includes support for the following:
Table Definitions - This definition specifies the data upload table name with its unique key columns.
Data Transformation Rules - For formatting transformations such as changing import data to uppercase, lowercase, and so on, you must define data transformation rules. For example, if the import file includes column data with both upper and lowercase and the upload table requires all uppercase, you can define a data transformation rule to insert only uppercase into the target column.
Skip Validation - You can improve data loading performance when uploading a large number of records by skipping the validation step. If uploading thousands of records, the end user might not be interested in validating each record. If you are certain that each record will be inserted as new record, the data loading process does not need to check for duplicates.
Table Lookups - If data existing in the import file must be mapped to data in another table, specify a table lookup to perform the mapping. For example, if the import file contains a department name for the DEPTNO column but the upload table requires a number for that column, use a table lookup rule to find the corresponding department number for that department name in another table.
Column Name Aliases - There are many situations when a developer does not wish to expose the table column names to the end user, or to expose all columns to the end user. In those situations, you can create a column aliases for the columns that need to exposed.
Manage Concurrency - If multiple users are uploading data at the same time, developers can use extra column to track the version of data in the underlying table. The Data Load Wizard can use this column to check and signal the end user if anyone else is working with the same data at the same time. This is particularly important if uploading into a table that is regularly updated.
Multiple Spreadsheet Columns - There are many situations when a spreadsheet to be uploaded has multiple columns that the developer wants to concatenate and upload in to one table column (for example FirstName and LastName on spreadsheet can be uploaded into ENAME of the EMP table).
The newly generated Data Load Wizard consists of four pages that provide users with the ability to upload data from a file or by copy and paste, define data and table mappings, validate the data, and finally to upload the data to the table. The developer can later edit the Data Load Wizard's definitions such as table lookups and data transformation rules, by accessing Shared Components, Data References, Data Load Definitions.
To create a Data Load Wizard:
On the Workspace home page, click the Application Builder icon.
Select the application.
Click Create Page and click Next.
For Create a Page:
User Interface - Select a user interface for the page.
Select a page type - Select Data Loading.
Click Next.
For Data Load Table:
Definition Name - Enter the name of this data load definition.
Owner - Select the owner of the data loading table.
Table Name - Select the table to use for data loading, also referred to as the upload table.
Unique Column 1 - Select the column name to use as the primary unique key column during the data load process. At least one unique key column is required.
Case Sensitive - Identify whether the selected unique key column is case sensitive. By default, this is set to No.
Unique Column 2 - If the unique key definition of the data load table is a compound key consisting of two or more columns, select the column name to use as the second unique key column during the data load process.
Case Sensitive - Identify whether the selected unique key column is case sensitive. By default, this is set to No.
Unique Column 3 - If the unique key definition of the data load table is a compound key consisting of two or more columns, select the column name to use as the third unique key column during the data load process.
Case Sensitive - Identify whether the selected unique key column is case sensitive. By default, this is set to No.
Click Next.
For Add Transformation Rules (optional), specify the following then click Add.
Sequence - Specify the sequence for the transformation rule. The sequence determines the order of execution.
Rule Name - Enter a name for this transformation rule.
Select Columns - Select the name of the column that requires transformation and move to the right.
Type - Select the type of transformation rule you want to perform.
Click Next.
Repeat the previous step for each transformation rule you want to add, then click Next.
For Table Lookups (optional):
Column Name - Identify the column on which the table lookup definition is to be based.
Owner - Select the owner of the lookup table.
Table Name - Identify the table to be used for this table lookup definition.
Return Column - Select the name of the column returned by the table lookup. This value will be inserted into the load column specified, and is generally the key value of the parent in a foreign key relationship.
Upload Column - Select the name of the column end users will upload instead of the return column. This is the column that contains the display value from the lookup table.
Upload Column 2 - Select the name of the second column to be uploaded to uniquely identify the return column if necessary.
Upload Column 3 - Select the name of the third column to be uploaded to uniquely identify the return column if necessary.
Click Next.
Repeat the previous step for each Table Lookup you want to add and click Next.
The Page Attributes page appears providing a short description of each page of the Data Load Wizard being created.
For Pages Attributes, you can optionally change the default page names, page numbers and region names for each page of the data load wizard and click Next.
For Navigation Menu, specify the type of navigation to include on this page and click Next. The navigation options depend upon the current application theme.
For Buttons and Branching, specify the branching for the buttons on the data load wizard pages and click Next.
Review the Confirm page and click Create.
Page Designer appears.
Click Save and Run Page to test the Data Load Wizard.
Tip:
IAfter creating Data Load Wizard pages, if you wish to make changes, Oracle recommends re-creating new pages without deleting the data loading definitions as described in the next section.If you wish to make changes to your Data Load Wizard pages, Oracle recommends re-creating new pages without deleting the data loading definitions.
To re-create Data Load Wizard pages without deleting the data loading definitions:
Navigate to the Data Load Definitions page:
On the Workspace home page, click Application Builder.
Select an application.
On the Application home page, click Shared Components.
The Shared Components page appears.
Under Data References, click Data Load Definitions.
Click the Data Load Definition you want to re-create.
The Data Load Table Details page appears.
From Tasks, click Re-create Data Load Pages.
The Page Attributes page of the Create Data Load Wizard appears.
For each page, edit the appropriate attributes and click Next.
To learn more about any attributes, see field-level Help.
Click Confirm.
A Data Load Definition is comprised of a data load table, table rules, and lookup tables used by the Data Load Wizard in your application. A data load table is an existing table in your schema that has been selected for use in the data loading process, to upload data.
To edit a Data Load Definition:
Navigate to the Data Load Definitions page:
On the Workspace home page, click Application Builder.
Select an application.
On the Application home page, click Shared Components.
The Shared Components page appears.
Under Data References, click Data Load Definitions.
Click the Data Load Definition you want to edit.
The Data Load Table Details page appears. To learn more about any attributes, see field-level Help.
For Data Load Definition:
Name - Name for the Data Load Definition.
Data Load Table - Edit the data load table name and add, remove or edit unique columns.
Unique column 1 - The column name used as the primary unique key column during the data load process.
Case Sensitive - Identify whether the selected unique key column 1 is case sensitive. By default, this is set to No.
Unique column 2 - If the unique key definition of the selected table is a compound key, consisting of 2 or more columns, this column name is used as the second unique key column during the data load process.
Case Sensitive - Identify whether the selected unique key column 2 is case sensitive. By default, this is set to No.
Unique column 3 - If the unique key definition of the selected table is a compound key, consisting of 2 or more columns, this column name is used as the third unique key column during the data load process.
Case Sensitive - Identify whether the selected unique key column 3 is case sensitive. By default, this is set to No.
Skip Validation - One step in data loading is to validate actions to be taken on records to be uploaded. Select Yes to skip validation.
Transformation Rules lists previously defined transformation rules.
To create a new rule:
Click Create Transformation Rule.
Edit the attributes.
To learn more about an attribute, see field-level Help.
Click Create.
To edit an existing rule:
Click the rule name.
Edit the attributes.
Click Apply Changes.
Table Lookups map data in the import file to data found in another table.
To create a new Table Lookups:
Click Create Table Lookup.
Edit the attributes.
Click Create.
To edit an existing Table Lookup:
Click column name.
Edit the attributes.
Click Apply Changes.
Column Name Aliases define aliases to help users correctly identify the columns to upload.
To add Column Name Aliases:
Click Edit List of Values.
Edit the attributes.
Click Create.
From Concurrency Column Name, select a column to be used for concurrency management.
Concurrency gives the developer the option to select a column to check the version of the data in the underlying table. This is particularly important if uploading into a table that is regularly updated.
Click Apply Changes.