User-Built ApplicationsBy David Peake
Oracle Application Express 4.0 Websheets enable end users to integrate, share, and maintain content.
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 apex.oracle.com. (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 WebsheetYou’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:
Your browser now displays the home page for a new Websheet application entitled Magazine. Next you’ll add some data to it.
Modifying Websheet PagesFollow these steps to add data from the sample database application’s Customers table to the Magazine application:
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:
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:
Figure 1: Navigation section in a Websheet application
Try clicking the navigation links to test their functionality.
Adding a Data GridData 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:
An updatable interactive report showing the events appears. Make the following changes to it:
Follow these steps to add a computational column to the data grid that compares sales with costs:
Sharing Websheet ApplicationsOracle 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.
ConclusionThe 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.
David Peake (firstname.lastname@example.org) is a principal product manager in Oracle’s Server Technologies division. He has been with Oracle since 1993.