Oracle Application Express 18: Creating an Application from a Spreadsheet

Before You Begin

This 15-minute tutorial shows you how to create an application using data from a spreadsheet. When you run the Create Application Wizard and select From a Spreadsheet, the Create From a Spreadsheet Wizard appears. You can then upload spreadsheet data in two ways :

• From a File
  Select this option to upload spreadsheet data from a file as comma separated (*.csv) or tab delimited file and then create an interactive report on that data.
• Copy and Paste
  Select this option to copy and paste tab delimited data and then create report on that data.

In this tutorial, you use the second option (Copy and paste) to create an application.

Background

The Create Application Wizard enables you to design and quickly create basic Oracle Application Express applications. By using the Create Application Wizard, you can create a complete application containing multiple pages including reports, interactive reports, forms, tabular forms, and master detail forms. After you create an application using this wizard, you can modify pages and add additional pages using the Create Page Wizard.

Once you approve a page preview, the wizard loads the data into the database, creates a report and form on the data, and then displays the Create Application Wizard. A new interactive report with form page named after the table you imported displays under Pages. You then finish creating the application by naming the application, specifying the application appearance, editing the report, creating additional pages, selecting features, and configuring settings.

What Do You Need?

• Access to Oracle Application Express Release 18.1 or later.
• Install the sample dataset, Project Data as described in Oracle Application Express 18: Create Application Wizard (1 of 2) - Installing Sample Dataset.

Accessing Your Development Environment

How you sign in and access Oracle Application Express depends upon where Oracle Application Express resides. Oracle Application Express may reside in a local on-premises Oracle Database or in a hosted environment, such as the Oracle Cloud. The sign in credentials you use to sign in differ depending upon the installation type.

• Free Workspace: Give Oracle Application Express a test run by signing up for a free workspace. To request an evaluation workspace, go to apex.oracle.com, and click Get Started for Free.
• Oracle Cloud: Develop and deploy applications without worrying about infrastructure, repair, and downtime. Oracle Application Express is available in Exadata Express Cloud Service and Database Cloud Service. However, you need to manually customize your databases to install and enable Oracle Application Express. To learn more, see Oracle Database Cloud Service.
• Oracle Application Express On-premises: Install Oracle Application Express directly within any Oracle Database and then sign in to your workspace using your sign in credentials. For details on your sign in credentials, contact your administrator or see Oracle Application Express Installation Guide.
• Oracle Application Express Pre-Built VM: Install a Pre-Built Virtual Machine (VM) which includes an Oracle Database and Oracle Application Express 18.1. To learn more, see Hands-On Labs.
  Once the VM is installed, start the VM:
  • Click the big red circle labeled Start.
  • Click the APEX shortcut, or enter the following URL: http://localhost:8080/ords/f?p=4550:1
  • When prompted to sign in, enter the sign in credentials (unless given other credentials to use):
    • Workspace: obe
    • Username: obe
    • Password: oracle

Upload Data from a Spreadsheet

In this section, you select From a Spreadsheet option in the Create Application Wizard to load spreadsheet data and then create report on that data.

To load spreadsheet data by copying and pasting:

1. Sign in to Oracle Application Express by entering the credentials (workspace, username, and password).
2. On the workspace home page, click the App Builder icon.
3. On the application home page, click the Create button. The Create Application Wizard appears.
4. Select From a Spreadsheet.
5. When prompted how to load the spreadsheet data, select Copy and paste.
6. To use the sample data:
   
   • Expand the Sample Data Region at the bottom of the page
   • Select World Population.

     

     
     The sample data appears in the Copy and Paste Tab Delimited Data field.

     

7. Click Next. A preview page appears. This shows how your table will look.

   

   You can modify the suggested table name, change the column names or datatypes, or specify which columns to include.
8. In the Table Name field, enter World_Population_Report.
9. Click Load Spreadsheet. The wizard confirms the number of rows loaded.

   

10. Click Continue to Create Application Wizard.

Complete the Create Application Wizard

Once the data is loaded, the Create Application Wizard displays with an interactive report and form already defined. It appears in the Create Application Wizard under Pages, as Interactive Report with Form (World_Population_Report).

1. In the Create an Application page, enter the Application Name, World Population.
2. To choose the appearance of your application:
   
   • Click the Set Appearance icon.
   • For Theme Style, select Vita Slate.
   • For Navigation, select Side Menu.
   • Click Choose New Icon, to select a new icon.
   • Select the color of your icon and click Set Application Icon.

   

   • Click Save Changes.
3. Click and hold the Drag to reorder page icon to change the page order. The Drag to reorder page icon displays next to Edit.

   

4. Under Features, you have multiple options which you can include in your application. Click Check All.
5. Click Create Application. The wizard creates the application and displays the Application home page.

Execute and Review the Application

You developed a fully functional web-based application with multiple pages, such as Home, Report and Form, and Administration.

To run and edit the application:

1. From the Application Homepage, click Run Application



2. Enter you workspace username and password and click Sign In.
   The Application Wizard displays the home page, Dashboard, Report, and the Administration page for your application (World Population).

   

3. Click Dashboard to view two bar charts. One chart displays population by continent and the other chart displays population by region.

   

4. View the report and form:
   
   • Click Population Report in the left navigation menu to view the report and form.
   • To edit a row, click the Edit icon. The Population Report form appears. You can use this form to update the information.
   • Select Continent. You see that it is a free format text field.



5. To change Continent field to a select list:
   • Click Continent in the Population Report dialog.
   • Go to the Runtime Developer Toolbar at the bottom of the page, click Quick Edit.
   • Click the Continent field again. Page Designer appears displaying the page with item, P4_CONTINENT, selected.
   • In the Property Editor, update the following:
     • Identification: Type - Choose Select List
     • List of Values: Type - Select SQL Query
     • SQL Query: Enter the following:

      select distinct continent d, continent r 
     from world_population_report
     order by 1

     • Display Extra Value: Select No
     • Null Display Value: Enter -Select Continent-
     • Click Save.



Next, view your changes. Since Page 3, Population Report, is a dialog page, you cannot run it directly from Page Designer.

6. Run the application again to view your changes:
   • Click the Application ID breadcrumb at the top of the page.
   • On the Application home page, click Run Application.
   • From the left Navigation menu, select Population Report.
   • Click edit icon adjacent to any row.
   • In the Population Report dialog, click Continent. Notice that the Continent field now displays as a select list. You now have the option to select from the list of continents.

   

   • Select one of the valid values from the drop-down list.
   • Click Apply Changes.

   

7. Review the Administration page:
   • In the left Navigation menu, click Administration.
   • To change the theme style:
     • Click Theme Style Selection.
     • On the Appearance page, change the color by selecting a new Desktop Theme Style. Select Vita-Red.
     • Click Apply Changes.
   • To send Feedback:
     
     • In the Navigation bar in the upper right of the window, click the Feedback icon .
     • Type in your feedback and click Submit Feedback.
     • Click on the User Feedback to see the Feedback entries in the Manage Feedback dialog.
     • Click on the edit icon in the Manage Feedback dialog.
     • Add a reply in the Response field.
     • Close the Manage Feedback dialog.
8. Review the available Activity Reports. Under Activity Reports, view the following reports:
   
   • Dashboard - View application activity metrics.
   • Top Users - Report of page views aggregated by user.
   • Application Error Log - Report of errors logged by the application
   • Page Performance - Report of activity and performance by application page.
   • Page Views - Report of each page by user including date of access and elapsed time.

Want to Learn More?

• Creating Database Applications
• Loading Spreadsheet Data by Copying and Pasting