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.
The application developer creates the Data Load Wizard by creating a Data Load type page with the Create Page Wizard. The creation process allows developers to specify the upload table and its unique columns, table lookups and data transformation rules. See "Creating a Data Load Wizard"
The Create Page Wizard creates a Data Load Table that defines the Data Load Wizard. The developer edits the Data Load Wizard's definitions, such as table lookups and data transformation rules, by finding and clicking on the corresponding Data Load Table under the application's Shared Components. See "Editing the Data Load Wizard".
To learn about using the Data Load wizard, see "About Uploading Data" in Oracle Application Express End User Guide.
The Create Page Wizard is used by the developer to create a Data Load Wizard that is several pages. The Data Load Wizard can be defined to include:
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, define data transformation rules. If the import file includes column data with both upper and lowercase and the upload table requires all uppercase, define a data transformation rule to insert only uppercase into that column during data upload.
Table lookups - If data exists in the import file that must be mapped to data that can be found 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 - End users may have issues with correctly identifying the columns to upload based on the table's column names. Therefore, you can define aliases for each column. These are displayed to the end user while uploading the spreadsheet data. If a list of values is created, the aliases is automatically picked up by the application during data column mapping. Otherwise, the application uses the table column names. Once created, the list of values can also be edited and a column can be removed from the list.
Manage Concurrency - By default the last user to submit data, updates the record. This option gives the developer the ability to choose a column to track the record versions. This is particularly important when dealing with a table that is regularly updated by different users. Every time the user tries to upload data, the data loading feature uses this column to track the version of the record. If while uploading the value of this column is found o have changed, the record is flagged and not directly updated, instead letting the end user know before he or she makes any further action.
To create a Data Load Wizard:
On the Workspace home page, click the Application Builder icon.
The Application Builder home page appears.
Select an application.
The Application home page appears.
Click Create Page.
Select Data Loading and click Next.
For Data Load Table, specify the following then click Next:
Data Load 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 - If the selected column for Unique Column 1 is case sensitive, select Yes. Otherwise, select No.
Unique Column 2 - If the unique key definition of the data load table is a compound key consisting of 2 or more columns, select the column name to use as the second unique key column during the data load process.
Case Sensitive - If the selected column for Unique Column 2 is case sensitive, select Yes. Otherwise, select No.
Unique Column 3 - If the unique key definition of the data load table is a compound key consisting of 3 or more columns, select the column name to use as the third unique key column during the data load process.
Case Sensitive - If the selected column for Unique Column 3 is case sensitive, select Yes. Otherwise, select No.
For 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.
Repeat the previous step for each transformation rule you want to add, then click Next.
For Table Lookups (optional), specify the following then click Add.
Column Name - Select the column in the import file you want to replace with the associated value.
Owner - Select the owner of the lookup table.
Table Name - Select the name of the lookup table. This is the table containing the associated value to be used in place of the value from the import file.
Return Column - Select the name of the column returned by the table lookup. This value is 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 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. It may be possible that Department IDs are different from SALES departments in NEW YORK and SAN FRANCISCO. To retrieve the value, you would need to pass second column which is LOCATION in this case.
Upload Column 3 - Select the name of the third column to be uploaded to uniquely identify the return column if necessary.
Repeat the previous step for each Table Lookup you want to add, then 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 that is being created, then click Next.
For Tab Options, select the tab type for the wizard pages, and click Next.
For Breadcrumb, select the type of breadcrumb to be used by the data load wizard pages and click Next.
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.
Click Edit Page to modify the Data Load Wizard definitions, otherwise click Run Page, to test the Data Load Wizard.
The Add/Edit Data Load Table page enables you to modify the upload table column and unique key definitions, the Table Lookups and Transformation Rules. Table Lookups and Transformation Rules can be modified, added and removed.
To edit a Data Load Wizard:
On the Workspace home page, click the Application Builder icon.
The Application Builder home page appears.
Select an application.
The Application home page appears.
Click Shared Components.
Under Logic, click Data Loading.
Click the Data Load Definition you want to edit.
The Data Load Table Details page appears.
Make changes to the following data load table details:
Data Load Table - Edit the data load table name and add, remove or edit unique columns.
Transformation Rules - Add, remove or edit transformation rules.
Table Lookups - Add, remove or edit table lookups.
Column Name Aliases - Edit the List of Values for Data Loading column aliases.
Manage Concurrency - Edit the column which is used as the concurrency management column by the data loading feature.
Comments - Edit comments for this data load table.
Click Apply Changes.