6.4 Creating a Database Application from a File

Run the Create Application Wizard and select From a File to upload a file and then create an application on the uploaded data.

• About Creating an Application from a File
  Upload a CSV, XLSX, XML, TXT, or JSON file, or copyand paste CSV data and then create your application.
• Creating an Application by Uploading File
  Upload a file to the database and then create an application based on the new table.
• Creating an Application by Copying and Pasting from a File
  Copy and paste column delimited data from a CSV file, or selecting a sample and then create an application based on the new table.

6.4.1 About Creating an Application from a File

Upload a CSV, XLSX, XML, TXT, or JSON file, or copyand paste CSV data and then create your application.

When you run the Create Application Wizard and select From a File, the Load Data Wizard appears. On the Load Data , you can choose to:

• Upload a File. Select this option to load data from a CSV, XLSX, XML, TXT or JSON file.

• Copy and Paste. Select this option to copy and paste column delimited data. You have the option of selecting a sample data set from the Select Sample list.

Once you load data into the database table, the wizard provides an option to Continue to the Create Application Wizard. When you continue, a report with form and dashboard pages based on the new table display under Pages. You then finish the application creation process by naming the application.

See Also:

"Loading Data" in Oracle Application Express SQL Workshop Guide

6.4.2 Creating an Application by Uploading File

Upload a file to the database and then create an application based on the new table.

To create an application from a file:

1. On the Workspace home page, click the App Builder icon.
2. Click the Create button.

   The Create Application Wizard appears.

   
   Description of the illustration create_app_file.png
3. Click From a File.

   The Load Data page appears with the Upload a File tab selected.

   
   Description of the illustration load_data_file.png
4. In the Upload a File tab, you have two options:
   • Drag and Drop - Drag and drop the file to the region in the center of the page.
   • Choose File - Click Choose File and then navigate to and select the file.

   The Load Data page appears containing the sections Settings, Preview, and Load to Table. Note that the Load Data page that appears differs slightly depending upon the selected file type.

   
   Description of the illustration load_data_file2.png
5. Under Settings:
   1. Column Headers - Select this option if your data contains column names in the first row.
   2. Separator - Select a column delimiter character (if CSV or TXT file).
   3. Enclosed By - Select a column enclosed by character (if CSV or TXT file). This character deliminates the starting and ending boundary of a data value. If you specify a delimiter character, the wizard ignores white space occurring before the starting and ending boundary of a data value.
   4. File Encoding (if applicable) - Choose the characterset in which the file is encoded.
6. Preview displays the first 10 coumns and 5 rows. To view the full preview, configure data load settings, and set which columns to load, click the Configure button.

   Two tabs appear: Preview and Columns to Load.

7. To configure the columns to load:
   1. Click Columns to Load.
   2. Rows to Sample - Select the maximum number of rows to be used to sample column types.
   3. Treat as Null - The entered case insensitive value will be treated as NULL. For example, "tbd", "n/a", "unknown", "-".
   4. Columns to Load - Select or deselect the appropriate columns. Column types are automatically detected based on Rows to Sample. For larger data sets, it might make sense to increase the Rows to Sample to get more accurate column types.
   5. Click Save Changes.
8. Under Load to Table:
   1. Table Owner - Select the database schema you would like to create and load data into. 
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load. By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically..
   4. Primary Keys - Select how to set the primary key. Options include SYS_GUID or Identity Column (if Oracle Database 12c or later). To learn more, see field-level Help.
   5. Use Column Data Types - Select to use the column types parsed from the file. If un-checked, the table columns will be created as VARCHAR2(4000).
9. Click Load Data.
   A confirmation page appears indicating the table and rows have been created. You have the the following options:

   • Drop Table and Reload - Drops the table and re-starts the wizard so you can try the data load again (if there are any errors).
   • Open in Object Browser - Exits from the current wizard and opens Object Browser so you view the error table (if there are any errors).
   • View Table - Exits from the current wizard and opens Object Browser so you can view the newly created table.
   • Continue to the Create Application Wizard - Loads the Create Application Wizard. If all rows fail to load, this button does not display.
10. Click Continue to Create Application Wizard.

    The Create Application Wizard appears. Report with form and dashboard pages based on the new table display under Pages. From here you can edit page name or create additional pages based on the imported data.

    Tip:

    The steps that follow summarize how to use the Create Application Wizard. For more information, see field-level help.
11. For Name, enter the name used to identify the application to developers.
12. For Appearance, you can either Accept the default (Vita, Side Menu), or change the appearance by clicking the Set Appearance icon and edit the attributes in the Appearance dialog.
13. To add a page, click Add Page and select the desired page type. The user interface changes based on the selected page type.

    Pages you create display under Add Page. You can edit existing pages as follows:

    • Change the page order. To change the order in which pages appear in your application, click and hold the Drag to reorder page icon and drag and drop it to a new location in the list.

      The Home page always displays first and cannot be reordered.

    • Edit a page. To edit a page click Edit. In the dialog, edit the page name, change the icon, specify if the page is a Home Page or Administration Page, or define Page Help.

    • Delete a page. To delete a page, click Edit and the click Delete .

14. For Features, select features to include with your application. Features provide application-level functionality and can only be added once per application. To learn more, see Help.

    Tip:

    Click the Check All button to select all features.
15. For Settings:
    1. Application ID - Enter a unique, numeric identifier for your application. This field contains an automatically generated identifier by default. Application IDs between 3000 to 9000 are reserved for internal use by Oracle Application Express.
    2. Schema - Select the database schema which stores the database objects you want to use in this application. Each application obtains its privileges by parsing all SQL as a specific database schema.
    3. Application Languages - The primary language used in the app. To change the primary language, click the Select Languages icon.
    4. Advanced Settings - Click the icon adjacent to the Advanced Settings to edit the application definition settings and preferences and security and globalization attributes. To learn more, see Help.
    5. Authentication - Select how authenticate users.
    6. User Interface Defaults - Click the icon to apply User Interface Defaults to this application.
16. Click Create Application.

See Also:

• "Available Page Types in the Create Application Wizard"

• "Available Features in the Create Application Wizard"

• "Available Settings in the Create Application Wizard"

6.4.3 Creating an Application by Copying and Pasting from a File

Copy and paste column delimited data from a CSV file, or selecting a sample and then create an application based on the new table.

To create an application by copying and pasting column delimited data from a CSV file, or selecting a sample:

1. On the Workspace home page, click the App Builder icon.
2. Click the Create button.

   The Create Application Wizard appears.

   
   Description of the illustration create_app_file2.png
3. Click From a File.

   The Load Data page appears

4. Select the Copy and Paste tab.
   
   Description of the illustration copy_paste.png
5. To load data, do one of the following:
   • Select a Sample - Select a sample database from the Select Sample list.
   • Copy and Paste - Copy and paste CSV data.
   The Pasted Data appears.
6. Click Next

   The Load Data page appears.

   
   Description of the illustration load_data.png
7. Under Settings:
   1. Column Headers - Enable First Line Contains if your data contains column names in the first row.
   2. Column Delimiter - Select a column delimiter character.
   3. Enclosed By - Select a column enclosed by character . This character deliminates the starting and ending boundary of a data value. If you specify a delimiter character, the wizard ignores white space occurring before the starting and ending boundary of a data value.
   4. File Encoding - Choose the characterset in which the file is encoded.
8. Preview displays a sample of columns and rows. The exact number varies based on the type of file. To view the full preview, configure data load settings, and set which columns to load, please click Configure button. On Configure:
   1. Click Columns to Load.
   2. Rows to Sample - Select the maximum number of rows to be used to sample column types.
   3. Treat as Null - The entered case insensitive value will be treated as NULL. For example, "tbd", "n/a", "unknown", "-".
   4. Columns to Load - Select or deselect the appropriate columns. Column types are automatically detected based on Rows to Sample. For larger data sets, it might make sense to increase the Rows to Sample to get more accurate column types.
   5. Click Save Changes.
9. Under Load to Table:
   1. Table Owner - Select the database schema you would like to create and load data into. 
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load. By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key. Options include SYS_GUID or Identity Column (if Oracle Database 12c or later). To learn more, see field-level Help.
   5. Use Column Data Types - Select to use the column types parsed from the file. If un-checked, the table columns will be created as VARCHAR2(4000).
10. Click Load Data.
    A confirmation page appears indicating the table and rows have been created. You have the following options:

    • Drop Table and Reload - Drops the table and re-starts the wizard so you can try the data load again (if there are any errors).
    • Open in Object Browser - Exits from the current wizard and opens Object Browser so you view the error table (if there are any errors).
    • View Table - Exits from the current wizard and opens Object Browser so you can view the newly created table.
    • Continue to the Create Application Wizard
11. Click Continue to Create Application Wizard.

    The Create Application Wizard appears. Report with form and dashboard pages based on the new table display under Pages. From here you can edit page name or create additional pages based on the imported data.

    Tip:

    The steps that follow summarize how to use the Create Application Wizard. For more information, see field-level help.
12. For Name, enter the name used to identify the application to developers.
13. For Appearance, you can either Accept the default (Vita, Side Menu), or change the appearance by clicking the Set Appearance icon and edit the attributes in the Appearance dialog.
14. To add a page, click Add Page and select the desired page type. The user interface changes based on the selected page type.

    Pages you create display under Add Page. You can edit existing pages as follows:

    • Change the page order. To change the order in which pages appear in your application, click and hold the Drag to reorder page icon and drag and drop it to a new location in the list.

      The Home page always displays first and cannot be reordered.

    • Edit a page. To edit a page click Edit. In the dialog, edit the page name, change the icon, specify if the page is a Home Page or Administration Page, or define Page Help.

    • Delete a page. To delete a page, click Edit and the click Delete .

15. For Features, select features to include with your application. Features provide application-level functionality and can only be added once per application. To learn more, see Help.

    Tip:

    Click the Check All button to select all features.
16. For Settings:
    1. Application ID - Enter a unique, numeric identifier for your application. This field contains an automatically generated identifier by default. Application IDs between 3000 to 9000 are reserved for internal use by Oracle Application Express.
    2. Schema - Select the database schema which stores the database objects you want to use in this application. Each application obtains its privileges by parsing all SQL as a specific database schema.
    3. Application Languages - The primary language used in the app. To change the primary language, click the Select Languages icon.
    4. Advanced Settings - Click the icon adjacent to the Advanced Settings to edit the application definition settings and preferences and security and globalization attributes. To learn more, see Help.
    5. Authentication - Select how authenticate users.
    6. User Interface Defaults - Click the icon to apply User Interface Defaults to this application.
17. Click Create Application.