25.3 Managing Applications with Legacy Data Loading Capability
Applications with legacy data loading capability enable end users to dynamically import data into a table within any schema to which they have access.
- Comparing Legacy Data Loading and New Data Loading
Learn about the differences between legacy data loading and new data loading - About Creating a Legacy Data Load Wizard
Create a Data Load Wizard by running the Create Page Wizard and creating a Data Load page. - Creating a Legacy Data Load Page
To create a legacy Data Load Wizard, a developer creates a Data Load page with the Create Page Wizard. - Re-creating Legacy Data Load Pages
To make changes to existing legacy Data Load pages, Oracle recommends re-creating new pages without deleting the data loading definitions. - Editing a Legacy Data Load Definition
Edit the Data Load Definition in Shared Components.
Parent topic: Managing Application Legacy Components
25.3.1 Comparing Legacy Data Loading and New Data Loading
Learn about the differences between legacy data loading and new data loading
Oracle Application Express includes two types of data loading: new data loading and legacy data loading. The new data loading has many benefits over legacy data loading.
Benefits of new data loading include:
-
New application data loading supports
CSV
,XLSX
,XML
, andJSON
formats. -
Column mapping occurs at design time, removing the burden for end users.
-
Flexible column mappings based on simple names or regular expressions.
-
Data conversion with transformation rules or lookup queries.
-
Easy work flow for end users: upload the file, verify the preview, and load data.
-
CSV
,XLSX
,XML
, andJSON
data formats can be loaded to tables or collections. -
Configure data loading to Append, Merge or Replace data, with or without Error Handling.
-
Simple new Process Type Data Loading: Customize Data Loading pages as you wish.
-
APEX_DATA_LOADING
PL/SQL API available for custom processing. -
Maximum number of columns to load is 300. Note that Legacy data load supports up to 45.
25.3.2 About Creating a Legacy Data Load Wizard
Create a Data Load Wizard by running the Create Page Wizard and creating a Data Load page.
During the process of creating the Data Load page, 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
andLastName
on spreadsheet can be uploaded intoENAME
of theEMP
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
andCLOB
) -
Complex types (
XMLTYPE
andSDO_GEOMETRY
) -
CHAR
25.3.3 Creating a Legacy Data Load Page
To create a legacy Data Load Wizard, a developer creates a Data Load page with the Create Page Wizard.
To create a legacy Data Load Wizard:
- On the Workspace home page, click the App Builder icon.
- Select the application.
- Click Create Page.
- For Create a Page:
- For Data Load Table:
- Legacy Data Load - Choose whether to create a new or to re-use an existing Legacy Data Load definition.
- Definition Name - Enter the name of this data load definition.
- Owner - Select the owner of the table on which the form will be based..
- Table Name - Select the table to use for data loading (also known as the upload table).
- Unique Column 1 - Identify the column name(s) to be used as the primary unique key column during the data load process. You can define up to 3 unique key columns.
- Case Sensitive - Identify whether the selected unique key column is case sensitive. By default, this is set to No.
- Define additional Unique Columns. You can define up to 3 unique key columns.
- Click Next.
- For Add Transformation Rules (optional) - Transformation Rules enable you to
change the data being uploaded before it is inserted into the base table. Select
the column to transform and then the desired rule to apply to it.
- Select Column(s) to create a transformation rule - Select the column on which the transformation rule definition is to be based and move them to the right.
- Rule Name - Enter a name for this transformation rule.
- Sequence - Specify the sequence for the transformation rule. The sequence determines the order of execution.
- Type - Select the type of transformation rule you want to perform.
- Provide additional details based on the transformation type you have chosen.
- Click Add Transformation.
- Click Next.
- For Table Lookups - Match an uploaded value against another table and use the
associated key value, instead of the uploaded value.
- Add new table lookup for Column (optional) - Identify the column on which the table lookup definition is to be based.
- Lookup Table Owner - Select the owner of the lookup table.
- Lookup 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
(for example:
DEPTNO
). - 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 (for example:
DNAME
). - Upload Column 2 - Select the name of the second column to be uploaded to uniquely identify the return column if necessary. For example, to uniquely identify a State Code it may be necessary to upload the State Name and Country.
- Upload Column 3 - Select the name of the third column to be uploaded to uniquely identify the return column.
- Click Add lookup to add the lookup definition. Repeat the previous steps to add additional table lookups.
- Click Next finish creating lookups.
- For Page Attributes:
- Step 1, Step 2, Step 3, and Step 4 - Enter a page name and page number for each step.
- Page Number - Enter a page number for each step.
- Page Mode - Identify the page mode. To learn more, see field-level Help.
- Page Group - Identify the name of the page group you would like to associate with this page.
- Breadcrumb - Select whether you want to use a breadcrumb navigation control on your page and which breadcrumb navigation control you want to use.
- Click Next.
- For Navigation Menu:
- Navigation Preference - Select how you want this page integrated into the Navigation Menu. To learn more, see field-level Help.
- Click Next.
- For Buttons and Branching, specify the branching for the buttons on the data load wizard pages:
- New Button Label - Enter text to display on the Next button.
- Previous Button Label - Enter text to display on the Previous button.
- Cancel Button Label - Enter text to display on the Cancel button.
- Cancel Button Branch to Page - Specify the page number to branch to when the user clicks Cancel.
- Finish Button Label - Enter text to display on the Submit button.
- Finish Button Branch to Page - Specify the number of the page to branch to. You can choose to branch back to the same page or any other page in your application.
- Click Create.
- Click Save and Run Page to test the 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.
25.3.4 Re-creating Legacy Data Load Pages
To make changes to existing legacy Data Load pages, Oracle recommends re-creating new pages without deleting the data loading definitions.
To re-create legacy Data Load Wizard pages without deleting the data loading definitions:
25.3.5 Editing a Legacy Data Load Definition
Edit the Data Load Definition in Shared Components.
To edit a Data Load Definition: