Subscribe

Technology

Community

Features

Up Front

Comment

Share

Share

Database Developer

From Low Code to High Control

Go no code, low code, and high control with Oracle Application Express 5.1.

By Joel Kallman

September/October 2017

Oracle Application Express, the low-code framework of Oracle Database, enables the easy creation of modern and responsive web apps with no code. For many application requirements, however, you will need to go outside the bounds of the declarative framework and define custom logic, business rules, or an enhanced user interface. And you do this with code.

With Oracle Application Express, you can use SQL, PL/SQL, HTML, JavaScript, or CSS to extend your applications. And regardless of what you use to extend your applications, Oracle Application Express provides a gradual transition from no code to low code to high control (more code).

In this Oracle Magazine article, you’re going to build a web application on top of a table populated with employment data from the US Bureau of Labor Statistics (total nonfarm employees from 1990 to 2017). You will initially create a web application that features a chart on this table, and you will write no code in the process. You will then add some dynamic capability to this chart by writing a small amount of JavaScript. And then to complete the application, you will add a moderately complex piece of JavaScript to specify the legend of two separate charts.

This article’s sample application is built in Oracle Application Express 5.1. If you’re not already running Oracle Application Express 5.1 or later locally, you can request a free workspace at apex.oracle.com. Alternatively, you can download the Database App Development Virtual Machine from Oracle Technology Network, which includes a preconfigured Oracle Database 12c Release 2 Enterprise Edition instance, Oracle Application Express 5.1, Oracle REST Data Services, Oracle SQL Developer, and Oracle SQL Developer Data Modeler. You will also need to download and unzip the SQL script for this article to create the sample database objects.

Creating the Sample Database Objects

Begin your exploration of low-code Oracle Application Express 5.1 development by first creating the sample database objects for your application.

  1. In a web browser, log in to Oracle Application Express, click the SQL Workshop icon, and then click the SQL Scripts icon.
  2. Click the Upload button, choose the sample_table_lowcode_apex.sql file (that you downloaded and unzipped in the previous section) from your local computer, and click the Upload button.
  3. Click the Run icon in the same row as the sample_table_lowcode_apex.sql script, and then click the Run Now button.

You’ve thus created the EMPLOYMENT table in your schema and populated it with data.

Creating the Application

Now create the initial application, which features a simple chart:

  1. In Oracle Application Express, click the App Builder tab.
  2. Click the Create icon and then the Desktop icon.
  3. Enter Low Code for Name, and click Next.
  4. Click the Create Application button twice.
  5. Click the Create Page button, click the Chart icon, and click the Line with Area icon.
  6. Enter Chart for Page Name, and click Next.
  7. Select Create a new navigation menu entry, and click Next.
  8. For Table / View Name, select EMPLOYMENT and click Next.
  9. Choose MEASUREMENT_DATE for Label Column and EMPLOYEES for Value Column, and click Create.

If you were to run your new application now, it would look a bit confusing. But with a few minor changes, it can look spectacular.

To start the changes, first ensure that you are in the Application Builder of Oracle Application Express, editing page 2.

  1. In the tree view on the left side of Page Designer, select Chart.
  2. In the list of properties on the right, in the Identification section, change Title to Employment, and in the Advanced section, enter linechart for Static ID.
  3. In the tree view on the left side of Page Designer, select Attributes under Chart.
  4. In the list of properties on the right, in the Layout section, remove any value for Height (if one is present).
  5. In the Settings section, change Time Axis Type to Mixed Frequency.
  6. In the Legend section, choose No for Show.
  7. In the tree view on the left side of Page Designer, select Series 1.
  8. In the list of properties on the right, append an ORDER BY clause to the query, changing SQL Query to
      
    select * from "EMPLOYMENT" 
order by state, measurement_date asc
  9. In the Column Mapping section, choose STATE for Series Name.
  10. In the Label section, choose No for Show.

You’ve now created a responsive Oracle Application Express application with an area line chart, showing the nonfarm employment data for four states in the US from 1990 to 2017.

Click the Run Application icon in the upper right to run your application. Log in with the same credentials you used to log in to Oracle Application Express. Hover over the lines in the chart to see the data values for each series.

Your application should look similar to Figure 1.

From Low Code to High Control, Figure 1

Figure 1: The initial chart application page

Extending with Low Code

The primary data visualization engine of Oracle Application Express is based on the Oracle JavaScript Extension Toolkit (Oracle JET), which includes a set of complete and flexible JavaScript APIs.

Using just a small amount of JavaScript, now add some dynamic interactivity for the chart in your new application.

  1. In the developer toolbar at the bottom of the page, click the Edit Page 2 link.
  2. In the component gallery at the center bottom of Page Designer, select Buttons and then drag and drop a Text button up to the Copy region position in the Employment region.
  3. In the properties on the right, change Button Name to Toggle, change Label to Toggle Orientation, and in the Behavior section, change Action to Defined by Dynamic Action.
  4. Move your cursor over the Toggle button in the right-side tree view of Page Designer, click the right mouse button, and choose Create Dynamic Action.
  5. In the properties on the right, change Name to Toggle.
  6. In the tree view at the left of Page Designer, click Show, to show the properties of the True action of the dynamic action. This action will be executed when the condition is true, namely when the Toggle button is clicked.
  7. In the properties on the right, change Action to Execute JavaScript Code.

    For the Code attribute, enter
      
    if ($("#linechart_jet").ojChart("option","orientation") == "vertical") {
    $("#linechart_jet").ojChart({orientation: 'horizontal'});
}
else {
    $("#linechart_jet").ojChart({orientation: 'vertical'});
}
  8. Click the Run icon in the upper right of Page Designer to run your page. Click the Toggle button.

Your application page now provides a button for toggling the orientation of the line chart from vertical to horizontal. The static ID attribute you entered in the Creating the Application section gives you the ability to reference the Oracle JET object in JavaScript (by appending _jet to the static ID attribute) and employ all elements of the Oracle JET API. Using a very small amount of JavaScript, you quickly and easily added interactivity to your application.

Your application should look similar to Figure 2.

From Low Code to High Control, Figure 2

Figure 2: The application with the ability to toggle the chart orientation

Adding a Second Chart and a Custom Legend

With Oracle Application Express, you can also go outside the bounds of the framework and achieve high control by including large amounts of code where necessary. This code can include CSS, JavaScript, HTML, SQL, or PL/SQL.

Now add a second chart and a custom legend to control both charts via slightly more-complex JavaScript.

  1. In the developer toolbar at the bottom of the page, click the Edit Page 2 link.
  2. In the component gallery at the bottom of Page Designer, ensure that Regions is selected. Select the Chart icon, drag it to the right of the Employment region in the Page Layout area, and drop it.
  3. In the properties on the far right of Page Designer, for Title enter Average Employment, and in the Advanced section, enter piechart for Static ID.
  4. Select Attributes under Average Employment in the tree view on the left side of Page Designer. Change Type to Pie, and in the Legend section, change Show to No.
  5. In the tree view on the left side of Page Designer, select New under Series.
  6. In the properties on the far right, change Name to Average Employment.
  7. For SQL Query, enter
      
    select avg(employees), state 
from employment 
group by state 
order by state
  8. In the Column Mapping section, choose STATE for Label and AVG(EMPLOYEES) for Value. In the Label section, set Show to Yes.
  9. In the component gallery at the bottom of Page Designer, drag a Static Content region above the Employment region in the Page Layout area and drop it.
  10. In the properties on the right, change Title to Legend; in the Appearance section, set Template to Buttons Container; and in Source, enter
      
        <div id="legend" 
         aria-controls="piechart linechart"
         style="height:25px;">
    </div>
  11. In the left-hand tree, click Page 2: Chart at the top.
  12. In the properties on the right side of Page Designer, in the JavaScript region, for the property Execute when Page Loads, include the contents of the apex_lowcode_legend.js file (which is part of the zip file for this article that you downloaded earlier).
  13. Click the Run icon in the upper right.

Your application should look similar to Figure 3. You now have an application with two charts and a custom legend. Click the states in the legend to show and hide the series from both charts. This additional functionality was provided by the 39 lines of JavaScript from the apex_lowcode_legend.js file, which makes extensive use of the Oracle JET API.

From Low Code to High Control, Figure 3

Figure 3: The application with a second chart and a single legend to control the series

Summary

With no coding, Oracle Application Express enables you to create powerful and responsive web apps. When you need to step outside of the no-code framework of Oracle Application Express to extend your application, you can use code—SQL, PL/SQL, HTML, JavaScript, or CSS. The Oracle Application Express framework enables you to customize applications with a little code or, when more control is desired, a little more code.

Next Steps

DOWNLOAD
Oracle Application Express 5.1.
the SQL script for this article.

READ more about low code with Oracle Application Express.

TRY Oracle Database Cloud Services.

Photograph by iStock.com/Nikada

Joel Kallman

Joel R. Kallman is Senior Director of Software Development for Oracle's Server Technologies division at Oracle America, Inc.  He is responsible for the development and product management of Oracle Application Express.  Joel has been at Oracle since 1996 and is the co-creator of Oracle Application Express.  He is a contributing author of several books on Oracle technology, including Expert One on One Oracle, Beginning Oracle Programming, and Mastering Oracle PL/SQL: Practical Solutions.   Joel holds a Bachelor of Science degree in Computer and Information Science from the College of Engineering, Ohio State University.

Oracle

Integrated Cloud & Platform Services

Integrated Cloud and Platform Services