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

   

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

   

3. Click Create.

   

4. Click Websheet. 

   

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.

   

6. Click Next >.

   

7. Click Create.

   

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

   

9. Click Create Websheet.

   

10. Clcik Run Websheet.

    

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. 

    

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.

   

2. Ensure Text is selected and click Next >.

   

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

   

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

   

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

   

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.

   

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

   

3. The hr_logo image is added to the page.

   

4. Click the Edit icon for the Overview section.

   

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.

   

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

   

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.

   

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

   

3. Click View New Page.

   

4. Click the Edit icon for the Overview section.

   

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

   

6. The section is updated.

   

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.

   

2. Select Data and click Next.

   

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

   

4. Click Create Section.

   

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

   

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.

   

2. Select Chart and click Next >.

   

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

   

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

   

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

   

6. Click Create Section.

   

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

   

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.

   

2. Select Navigation and click Next >.

   

3. Select Section Navigation and click Next >.

   

4. Enter Sections for Title and click Create Section.

   

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.

   

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

   

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

   

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. 

   

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

   

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

   

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

1. Click New Section from Control Panel.

   

2. Select Navigation and click Next >.

   

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

   

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

   

5. The page navigation section is created.

   

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. 

   

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

   

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. 

   

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

   

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.

   

2. Click the Create User >.

   

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.

   

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.

   

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.

   

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.

   

2. Click the websheet application run icon.

   

3. Click Administration and select Access Control.

   

4. Click Create Entry >.

   

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

   

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

   

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

   

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.

   

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

   

10. Click Run.

    

11. Login as the ws_contributor user.

    

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

    

13. Login again as the ws_reader user.

    

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

    

• Creating Mobile Applications with jQuery Mobile Support

• Oracle Application Express Learning Library