Developers can create applications with data loading capability that enable end users to dynamically import data into a table within any schema to which the they have 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.
Topics:
See Also:
"Data Loading Wizard Examples" in Oracle Application Express End User Guide to learn about using the Data Load Wizard.
To create a Data Load Wizard, an application developer creates 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.
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.
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.
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).
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.
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.
Supported Data Types
Data Load Wizards support the following data types:
VARCHAR2
DATE
TIMESTAMP
NUMBER
Unsupported Data Types
Data Load Wizards do not support the following data types:
Large objects (BLOB
and CLOB
)
Complex types (XMLTYPE
and SDO_GEOMETRY
)
CHAR
See Also:
To create a Data Load Wizard:
Tip:
After 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.
See Also:
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:
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: