User-Built Applications

Oracle Application Express 4.0 Websheets enable end users to integrate, share, and maintain content.

By David Peake

September/October 2010

The Websheets feature in Oracle Application Express 4.0 gives business users a quick and simple way to integrate, interact with, and share data and other content on the Web. By using SQL tags, declarative database reports, and data grids, users can control the content and structure of Websheet applications without relying on developers.

In this column, you’ll build a Websheet application that integrates existing data from the sample database application in Oracle Application Express with data you enter locally in a data grid. You can run through these steps on the hosted instance of Oracle Application Express 4.0 at (You must request a free workspace to use this hosted instance.)

If the sample database application is not already installed, go to Application Builder -> Create, select Sample Applications, and click Install next to the Sample Database application image.

Creating a Websheet You’ll start by creating a Websheet and adding existing database content to it via SQL tags as well as a declarative report. To create a Websheet, follow these steps:
  1. From the Oracle Application Express home page, select Administration.
  2. Click the Websheet Database Objects task.
  3. If the Create Websheet Database Objects option appears, click that option, click Continue, and then click Create. (If instead you see options to remove or validate Websheet database objects, do nothing on this page.)
  4. Select Application Builder -> Create.
  5. Select Websheet, and click Next. Enter Magazine for Name, and enter Customers for Title for the Home Page Section.
  6. Click Next, and then click Create.
  7. Click the Edit Properties image.
  8. In the SQL section, select Yes for Allow SQL Tags and Reports and then click Apply Changes.
  9. Click the Run icon.
  10. Enter your Oracle Application Express developer credentials, and click Login.

Your browser now displays the home page for a new Websheet application entitled Magazine. Next you’ll add some data to it.

Modifying Websheet Pages Follow these steps to add data from the sample database application’s Customers table to the Magazine application:
  1. From your Websheet application home page, click Edit in the top right corner of the Customers region.
  2. Enter
    [[SQL: select * from demo_customers]] 

    in the Content box, and click Apply Changes. The home page now displays a customer report.

  3. To improve the look of the report, click Edit in the top right corner of the Customers region. Replace the query in the Content box with
    [[SQL: select 
    cust_first_name||' '||
    cust_last_name Name, 
    cust_city City, 
    cust_state State, 
    phone_number1 Phone, 
    cust_email Email 
    from demo_customers 
    order by cust_last_name]]

    and click Apply Changes.

Now you’ll add a report to the Websheet. The report’s datasource is the sample database application’s Products table. Follow these steps to create the report:

  1. From the menu, click Data -> Create.
  2. Select Report, and click Next.
  3. Select Table for Report Source. Click the up arrow to the right of Table or View Name, and select DEMO_PRODUCT_INFO, enter Product Information for Report Name, enter PROD for Report Alias, and click Next.
  4. Click Create. An interactive report listing products appears.
  5. To limit which columns are displayed, click Actions -> Select Columns. Select Product Id, Product Image, Mimetype, Filename, and Image Last Update. Click < to move these columns into the Do Not Display region, and click Apply.
  6. To reorder the records, click Actions -> Format -> Sort, select Category for Column 1, select Product Name for Column 2, and click Apply.
  7. Save the report as the default report. Click Actions -> Save Report, select As Default Report Settings for Save, and click Apply.

Return to the Customers page by selecting Page -> Home Page from the menu. Now you’ll add two new sections to the home page. One will contain the Product Information report you just created, and the other will contain automatically generated navigation links for the application. Follow these steps:

  1. In the Page list on the right-hand side, click New Section, click Data, select Report for Data Selection Source, select Product Information for Report, click Next, click Next, and click Create.
  2. Click New Section, select Navigation, select Section Navigation, click Next, enter 1 for Sequence, and click Create. The Navigation section, which appears at the top of the page, looks like the one in Figure 1.
sep/oct 2010 browser-based image 1

Figure 1: Navigation section in a Websheet application

Try clicking the navigation links to test their functionality.

Adding a Data Grid Data grids enable users to maintain data that’s specific to the current Websheet application. Data grids are automatically maintained within Oracle Application Express, eliminating the need for users to create tables with primary keys, sequences, and triggers. To create a data grid, you can either specify columns declaratively or import a spreadsheet as the basis for the data grid.

Follow these steps to create a data grid that contains information about sales events:

  • From the Magazine application’s menu, click Data -> Create.
  • Select Data Grid, click Next, select From Scratch, and click Next. Enter Events for Name. Enter the following column names and corresponding types: Event Date (Type: Date), Name (Type: String), Location (Type: String), Cost (Type: Number), and Sales (Type: Number).
  • Click Create.
  • Click Add Row.
  • Use the date picker to select Event Date, enter values for the remaining fields, and click Save and Add Another.
  • Repeat these steps to add three more records with different costs and sales, and then click Save.

An updatable interactive report showing the events appears. Make the following changes to it:

  1. Edit a date by clicking a specific date and selecting a new date from the date selector that pops up. The new value will be saved to the data grid.
  2. Edit the other fields by clicking each field and performing an inline edit. Click any other field to save the update to the data grid.
  3. Edit an individual row by clicking the Edit icon for that record.
  4. To add an attachment, click Add File, click the Browse button and locate the file, and then click Apply. To add a sticky note, click Add Note, enter some text, and click Apply. To add a URL, click Add Link, enter a name for Link Name, enter a target for URL Target, and click Apply.
  5. Click Apply Changes to return to the report view.
  6. To display the annotations you created in Step 3, click Actions -> Select Columns; select Files, Notes, Links, and Tags; then click > to move those columns into the Display in Report region; and click Apply.

Follow these steps to add a computational column to the data grid that compares sales with costs:

  1. Click Actions -> Format -> Compute, enter Sales v Cost for Column Heading, select $5,432.10 for Format Mask, enter E - D for Computation, and click Apply.
  2. Save the data grid as the default report. Click Actions -> Save Report, select As Default Report Settings for Save, and click Apply.


Return to the Customers page by clicking Home in the breadcrumbs. Your final addition to the home page will be a chart generated from the data grid’s new Sales v Cost column.
  1. Click New Section.
  2. Click Chart.
  3. Click Horizontal Bar.
  4. Select Data Grid for Data Selection Source, select Events for Data Grid, and click Next. Click Next again.
  5. Select Event Date for Chart Label, select **Sales V Cost for Chart Value, select Label - Ascending for Sort, and click Create.
  6. Click the new Events link in the home page Navigation section to view the chart you’ve created.
Sharing Websheet Applications Oracle Application Express enables you to define authentication and authorization for Websheet applications. For each application, the application administrator can choose Oracle Application Express account credentials, single sign-on, LDAP, or custom values for authentication. Applications can also allow read-only access to a Websheet for users who don’t log in to the application. If administrators use Oracle Application Express account authentication, they can implement custom access control lists (ACLs) to define which users have administrator, contributor, and reader rights to the application. Conclusion The development and runtime environments for Oracle Application Express Websheets are merged, enabling users to define their own application content. They can build Web pages that incorporate text, images, internal links, and URLs; run SQL queries against their schemas’ Oracle Database tables; and manage data locally in data grids. Websheet applications are a boon to business users who want to collaborate by communicating data, images, and textual information efficiently.
Next Steps

READ more Browser-Based

LEARN more about Oracle Application Express

DOWNLOAD Oracle Application Express

VISIT the Oracle Application Express discussion forum


Photography bySérgio Rola,Unsplash