Building a Websheet Application in Application Express 5.1

Purpose

This tutorial shows you how to build, use, and customize a Websheet Application in Oracle Application Express 5.1.

Time to Complete

Approximately 60 minutes

Background

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in the Oracle Cloud.

In this tutorial, you use Oracle Application Express Release 5.1 to create and modify a mobile web application.

Please keep in mind the following while running this tutorial:

• Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
  • Logging into Oracle Application Express in a Oracle Database Cloud Service:  Reference the Oracle Help Center for your Oracle Database Cloud Service. To do this, go to the Oracle Help Center for Cloud, and select Platform or Infrastructure (depending on your service). From here, select your Database Cloud Service and the Get Started page will appear.
  • Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
• Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
• Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service,  you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

What Do You Need?

• Visit Oracle Help Center for Cloud.
• Access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
• Oracle Application Express Release 5.1 into your Oracle Database (for on-premises only).
• At least one Oracle Application Express user provisioned. See Creating New User Accounts in Oracle Application Express Administration Guide.
• The files.zip file into your working directory.

Websheet applications are collections of pages that support wiki syntax. Each page can be organized into sections. Pages can also be organized into a hierarchy. Sections can be edited using a friendly editor.

Websheets provide the following functionality:

• Create and share content over the web using your browser.
• Organize web pages in a hierarchy and cross link pages (using [[ pagename ]] syntax).
• Create and manage tabular data using an embedded feature called Data Grids.
• Create interactive reports using SQL on existing data structures in your database.
• Expose Data Grid and Report data within pages as a chart or a report.
• Annotate pages with files, tags and notes.
• Associated images can be shown inline within page content (using [[image: filename]] syntax).
• Search page content (using search box in upper right hand).
• Manage who can log in and once logged in, who can read, write or administer the application (authentication and authorization).

To create a Websheet Application, perform the following steps:

1. In the Application Express login page, enter the following login credentials and click Sign In:

   • Workspace: obe

   • Username: obe

   • Password: oracle

   View Image

   

2. After you login to your workspace, click the down arrow in the Application Builder tab and select Websheet Applications.

   View Image

   

3. Click Create.

   View Image

   

4. Click Websheet. 

   View Image

   

5. You might optionally get the following dialog if the required websheet database objects are not already created for your Database Schema Service. Click Manage Websheet Database Objects >. Alternatively, if you do not see the below dialog, you can skip the steps 3 to 6 and continue from step 7 of this OBE directly.

   View Image

   

6. Click Next >.

   View Image

   

7. Click Create.

   View Image

   

8. The required websheet database objects are created. Now, click Create Websheet >.

   View Image

   

9. Click Create Websheet.

   View Image

   

10. Clcik Run Websheet.

    View Image

    

11. If prompted, enter the credentials you used to log in to your Oracle Application Express workspace and click Log In. The Application is created with a default Home page. 

    View Image

    

In this topic, you will create a text section called Overview in the Home page. The text to enter in the section is available in the overview.txt file. Perform the following steps:

1. Click the Create button and select New Section.

   View Image

   

2. Ensure Text is selected and click Next >.

   View Image

   

3. Enter Overview for Title. Open the overview.txt file and copy paste its contents to the Content field. Then, click Create Section.

   View Image

   

4. The Overview section is added to the Home page.

   View Image

   

5. Delete the Welcome to Websheets section. Click the edit icon and then click Delete.

   View Image

   

You can annotate any page in your application with files, tags, and notes.

You can include images in your pages by using the syntax [[IMAGE:<filename>]]. The image that you are referring to should be added to the files section of the page. To display an image in a page, perform the following section.

1. Click the Add File (+) icon in the Files section of the page.

   View Image

   

2. Click Choose File and locate the hr_logo image from the files folder. Then, click Add File.

   View Image

   

3. The hr_logo image is added to the page.

   View Image

   

4. Click the Edit icon for the Overview section.

   View Image

   

5. To display the image in this section, enter [[IMAGE:hr_logo.jpeg]] under the existing text in the Content field and click Apply Changes.

   View Image

   

6. The image is displayed in the page under the Overview section.

   View Image

   

Data Grids allow you to create tabular data. You can create a data grid from scratch or by copy-pasting data from a spreadsheet.

In this section, you will create a new page as a copy of the existing Home page and customize the new page. To create a new page, perform the following steps:

1. Click the Create drop-down menu and select New Page as a Copy.

   View Image

   

2. Enter Reports for New Page Name and click Copy Page.

   View Image

   

3. Click View New Page.

   View Image

   

4. Click the Edit icon for the Overview section.

   View Image

   

5. Delete the existing content and enter This page contains some useful reports. in the Content field and click Apply Changes.

   View Image

   

6. The section is updated.

   View Image

   

In this section, you will create a data section in the new page. Perform the following steps:

1. From the Control Panel section, click New Section.

   View Image

   

2. Select Data and click Next.

   View Image

   

3. Select Deadlines for Data Grid and select Primary Report for Report Settings to Use. Enter Deadlines For This Month in the Title field and select Next >.

   View Image

   

4. Click Create Section.

   View Image

   

5. A report on the Deadlines data gird is created in a new section.

   View Image

   

In this section, you create a chart section based on data from a data grid. Perform the following steps:

1. Click Create and select New Section.

   View Image

   

2. Select Chart and click Next >.

   View Image

   

3. Accept the default for chart type and click Next >.

   View Image

   

4. Select Tasks for Data Grid and Primary Report for Report Settings to Use. Enter Cost Per Project for Section Title and click Next >.

   View Image

   

5. Select PROJECT for Chart Label and COST for Chart Value. Enter Projects for Axis Title for Label and Cost for Axis Title for Value. Then, click Next >.

   View Image

   

6. Click Create Section.

   View Image

   

7. A chart is created in a new section in the page.

   View Image

   

In this section, you will create a section navigation for all the sections in the Reports page. Perform the following steps:

1. From the Control Panel, select New Section.

   View Image

   

2. Select Navigation and click Next >.

   View Image

   

3. Select Section Navigation and click Next >.

   View Image

   

4. Enter Sections for Title and click Create Section.

   View Image

   

5. The navigation section is created, but it is the last section on your page. You want to move the navigation section to the top of the page. Click Edit Sections from the Control Panel.

   View Image

   

6. Click the up icon for Sections till it is the topmost section in the list and click Apply Changes.

   View Image

   

7. The navigation section is moved to the top of the page.

   View Image

   

In this section, you will view the Reports page in Presentation mode. Perform the following steps:

1. Click the Presentation Mode icon from the top-right toolbar. Alternatively, you can click View and select Presentation Mode. 

   View Image

   

2. The first section in the page is displayed. You can move to the next section by clicking the next icon.

   View Image

   

3. To close the presentation mode, click the cross (X) icon. 

   View Image

   

In this section, you create a page navigation section on the Home page. Perform the following steps:

1. Click New Section from Control Panel.

   View Image

   

2. Select Navigation and click Next >.

   View Image

   

3. Ensure that Page Navigation is selected for Navigation Type and click Next >.

   View Image

   

4. Enter Pages for Title and select Created Date for Order Siblings By. Click Create Section.

   View Image

   

5. The page navigation section is created.

   View Image

   

In this section, you will view the history for a page. Perform the following steps:

1. Click the View history link at the bottom of the page. Alternatively, you can click View and select Page History. 

   View Image

   

2. The history for the page is displayed. Click the Home breadcrumb to return to the Home page.

   View Image

   

In this section, you will search the entire websheet for the word "Projects". Perform the following steps:

1. Enter Projects in the top-right search bar and click Enter. 

   View Image

   

2. A report listing all the occurances of the search string is displayed. Click the Home breadcrumb to return to the Home page.

   View Image

   

In order to share Websheets, it is necessary to define appropriate access control for the other Oracle Application Exprses users. In this topic, you will create three Oracle Application Express users. Perform the following steps:

1. Click the Administration icon and select Manage Users and Groups.

   View Image

   

2. Click the Create User >.

   View Image

   

3. Enter WS_ADMIN for Username and ws_admin@oracle.com for Email Address. Ensure that User is an administrator is set to Yes and enter oracle for Password (not shown in the screenshot). Then, click Create and Create Another.

   View Image

   

4. Enter WS_CONTRIBUTOR for Username and ws_contributor@oracle.com for Email Address. Select No for User is an administrator and enter oracle for Password (not shown in the screenshot). Then, click Create and Create Another.

   View Image

   

5. Enter WS_READER for Username and ws_reader@oracle.com for Email Address. Select No for User is an administrator, No for User is a Developer and enter oracle for Password (not shown in the screenshot). Then, click Create User.

   View Image

   

In this section, you will create an Access Control List for the websheet. Perform the following steps:

1. Click the Applicaiton Builder tab and select Websheet Applications.

   View Image

   

2. Click the websheet application run icon.

   View Image

   

3. Click Administration and select Access Control.

   View Image

   

4. Click Create Entry >.

   View Image

   

5. Enter ws_reader for Username and click Create and Create Another.

   View Image

   

6. Enter ws_contributor for Username, select Contributor for Privilege, and click Create and Create Another.

   View Image

   

7. Enter ws_admin for Username, Administrator for Privilege, and click Create.

   View Image

   

8. Now you need to change your Authorization Scheme to use the Access Control List you have just created. Click the websheet application edit icon.

   View Image

   

9. Click the Authorization region and select Custom. Then click Apply Changes.

   View Image

   

10. Click Run.

    View Image

    

11. Login as the ws_contributor user.

    View Image

    

12. Click Administration and note that only the option to change the password is available.

    View Image

    

13. Login again as the ws_reader user.

    View Image

    

14. Note that the options to create and edit content are disabled.

    View Image

    

• Creating Mobile Applications with jQuery Mobile Support

• Oracle Application Express Learning Library