Skip Headers
Oracle® Application Express Application Builder User's Guide
Release 4.1

Part Number E21674-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Creating Applications with Data Loading Capability

Applications with data loading capability allow end users to dynamically import data into a table within any schema to which the user has access. To do this, 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".

Topics:

Creating a Data Load Wizard

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.

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

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

To create a Data Load Wizard:

  1. Navigate to the Workspace home page.

  2. Click the Application Builder icon.

  3. Select an application.

    The Application home page appears.

  4. Click Create Page.

  5. Select Data Loading and click Next.

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

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

    • Column Name (of key value) - Select the column from the table lookup that contains the associated key value.

    • Uploaded Column - Select the column from the table lookup that contains the display value that will be uploaded to the upload table.

  8. Repeat previous step for each Table Lookup you want to add, then click Next.

  9. For Transformation Rules (optional), specify the following then click ADD.

    • Column Name - Select the name of the column that requires transformation.

    • Name - Enter a name for this transformation rule.

    • Sequence - Specify the sequence for the transformation rule. The sequence determines the order of evaluation.

    • Type - Select the type of transformation rule you want to perform.

  10. Repeat previous step for each Transformation Rule you want to add, then click Next.

    The Create Data Load Wizard page appears providing a short description of each page of the Data Load Wizard being created.

  11. For Breadcrumb, select the type of breadcrumb to be used by the wizard pages and click Next.

  12. For Tab Options, select the tab type for the wizard pages, and click Next.

  13. For Buttons and Branching, specify the branching for the buttons on the wizard pages and click Next.

  14. Review the Confirm page and click Finish.

  15. Click Edit Page to modify the Data Load Wizard definitions, otherwise click Run Page, to test the Data Load Wizard.

Editing the Data Load Wizard

The Add/Edit Data Load Table page allows 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:

  1. Navigate to the Workspace home page.

  2. Click the Application Builder icon.

  3. Select an application.

    The Application home page appears.

  4. Click Shared Components.

  5. Under Logic, click Data Loading.

  6. Click the Data Load Definition you want to edit.

    The Add/Edit Data Load Table page appears.

  7. Make changes to the data load table, including:

    • Edit data upload table definitions.

    • Add, edit or remove table lookups.

    • Add, edit or remove transformation rules.

  8. Click Apply Changes.