Pretius is a company with a keen interest in Oracle Application Express technology. We have over 20 APEX developers (almost a fifth of our whole crew of IT experts). We’ve got some real APEX enthusiasts on this team, including people with Oracle ACE Associate and Oracle ACE titles. We’re also authors of the translate-apex.com project and we create and maintain some highly useful APEX plug-ins. In short – we know this technology inside out and we’re always ready to share our knowledge.

My name is Matt Mulvaney. At the start of my career, I started with Oracle Forms and PL/SQL. However, in 2014, I started using Oracle APEX and, for the last seven years, I specialise exclusively in Oracle Application Express.

This article will serve as a basic introduction to the Oracle APEX technology, highlighting its strengths, features, and other information (such as cost, for example). Reading it, you’ll learn:

1. What is Oracle APEX
2. Why is APEX worth knowing and what are its strengths
3. What is the technology’s position on the market
4. What is the Oracle RAD architecture and what place APEX has in it
5. Where can you run Oracle APEX
6. How much does APEX cost
7. What apps were created with the technology
8. How to create a simple application using Oracle APEX

What is Oracle APEX and why should it interest you?

Oracle APEX – origins and history

If you’re reading this article, you probably know what Oracle APEX is, but still, it’s a good idea to start by defining some of the basics.

Oracle is a large, multinational company that historically specializes in RDBMS (i.e., relational) databases. It’s the heart of what they do – year after year they’re improving that technology and coming up with new versions of the Oracle Database.

Oracle also has countless toolsets including JavaScript frameworks, cloud analytics (there’s even a whole cloud environment), toolkits, ERP platforms – all sorts of things. Two of these Oracle solutions are low-code. One is Oracle Visual Builder Cloud Service (VBCS for short), which is tied to some of Oracle’s other products, such as the Fusion middleware, and allows you to extend them in quite an easy way. The other is the topic of this article – Oracle Application Express, APEX.

If I was building a web-based data-driven application, I don’t think there’s any better tool than Oracle Application Express.

Oracle APEX has a strong history, starting with version 1.5, which came out in 2004 – it was known as HTML DB then (before it also had other names, like Flows and Project Marvel). Throughout its history it had many versions – you can see a brief summary of them on the screen below.

Oracle APEX – why is it good?

The creators of Oracle Application Express say it can help you develop enterprise apps up to 20 times faster and with 100 times less code. You can achieve this because Oracle APEX gives you Wizards to create various native components, just out of the box. You can spend your time customizing and testing them. You can concentrate more on functionality rather than the fine-grained elements of application development. There’s much less code to write in APEX.

Oracle APEX on the market – the Gartner Magic Quadrant

You may be familiar with such low-code frameworks as Powerapps (Microsoft), Outsystems, Mendix, Salesforce, or Appian. A research and advisory company called Gartner has identified those technologies as the leaders of the market – they’ve got high completeness of vision and a high ability to execute. Oracle Application Express is a challenger in this space, though I expect in the future it’ll move to the “leaders” section.

Oracle RAD architecture

Oracle APEX is a part of the Oracle RAD architecture and technology stack. What does it mean?

• “R” stands for REST, or rather ORDS – Oracle REST Data Services. ORDS is responsible for asking the database for the page and rendering it back to the client;
• “A” stands for APEX, Oracle Application Express, the topic of this article;
• “D” stands for Database, which is the place an APEX application resides in.

It’s important to note that all Oracle Application Express applications are web applications. So, you need a browser, either on a mobile device, a tablet, or on a PC. You request the page from the URL (it has a very standard structure), through ORDS, to your database – APEX exists as a component of the Oracle Database.

You can download APEX, ORDS and a Free DB (Express Edition) to run it on from oracle.com/downloads.

Above, you can see an example of a database. It’s a 12C database, which can be either dedicated or PDB. At the bottom, you’ve SQL for queries, which you’ll probably be familiar with. PL/SQL stands for procedural language SQL. In short, PL/SQL is a powerful, mature, and feature-rich programming language for the Oracle DB. There are packages for string manipulation, calling web service, and everything that you’d expect from a programming language.

The main programming language behind APEX is PL/SQL, although as they are web applications, you can enhance them with custom HTML, CSS, and JavaScript.

Finally, the red oval shapes at the top are your schemas – this is where your data lives, and Oracle Application Express can access it. It can also access things outside the database (I’ll cover that later).

Where can you run Oracle APEX?

Oracle Cloud:

You can run APEX in an Autonomous Database (ADB) – an elastic database that you can scale up. It’s self-driving, self-healing, and can repair and upgrade itself. It comes in two flavours:

• Autonomous Transaction Processing (ATP) – basically transaction processing, it’s where APEX sees most use;
• Autonomous Data Warehouse (ADW) – for more query-driven APEX applications. Reporting data is also a common use of Oracle APEX.

You can also use the new Database Cloud Service (DCS) – an APEX-only solution. For a fee, you can have a commercial application running on a database cloud service.

On-premise or Private Cloud

You can also run Oracle APEX on-premise or in a Private Cloud – anywhere where a database runs. It can be a physical, dedicated server, a virtualized machine, a docker image (you can run it on your laptop, fire it up on a train or a plane – it’s very popular among Oracle Application Express developers). You can also use it on Exadata – a super-powerful APEX physical server on cloud services.

Oracle APEX – how much will it cost you?

I’ve said a lot about how great APEX is, but some of you probably wonder how much this technology will cost you. Well, I’ve got some good news in this regard: you can download both APEX and ORDS for free. Of course, that doesn’t mean APEX is free, per se. I’d rather say it doesn’t incur an additional cost on top of your Database license.

How to use APEX for free:

• Autonomous Always Free – you can choose the Autonomous Always Free option, running either on ATP or AWS. It’s free for commercial use, but it doesn’t benefit from the scalability of the autonomous databases.
• Oracle Express Free Edition – you can also run a free version, which is called Oracle Express Free Edition, on-premise, but in this case, there’s a limit on how much data you can store there.
• Fan-made and official containers – there are also various fan-made and official containers with APEX installed available on the Internet.

When I say APEX is Oracle’s best-kept secret, the fact that it’s more or less free to use, and you can build really powerful applications with it, is actually what I mean by that sentence. It’s a really great technology and I feel it should always be considered when thinking about data-driven applications, which it excels at.

Oracle APEX – examples of use

Aria People

Aria People is the HR tool for the Oracle Corporation. You can look up the name and job title of anybody who works in all the offices and companies, as well as find information on their manager and even their manager’s manager. It can also be used to log in holidays, time off, and do other tasks.

The software has around 46k users a day and gets over 1.5 million page views on an average day (though I’ve heard on one day it exceeded 3 million), which makes it a great example of APEX’s scalability – it can easily scale up to these workloads. In short, if your database can support so many users, then so can APEX.

Oracle Store

Another example is the Oracle Store, where you can click to buy items – software, hardware. There are thousands of dollars of products available for purchase.

Covid-19 applications

And finally, over the past year, we’ve seen an influx of Covid-19-related applications. These were built extremely quickly.

• In the middle you see the propagation of the coronavirus, the screen is showing the areas heavily affected by it. There are lots of different stats, buttons, and navigation options you can click.
• On the left, there’s a Croatian website called “For each other” where you can request help or offer it to people in need.
• On the right side, you see an APEX application with a map that shows the distribution of personal protective equipment to volunteers, nurses, and other healthcare professionals.

Overall, there are 31 such applications. You can see them all if you go to apex.world, which is a hub for all things Oracle APEX, including news, tweets, and so on.

There’s also a Slack channel you can join to get help when you’re getting started with the technology. There are many useful things there, so I encourage you to use these resources.

Oracle APEX – how to work with it?

For starters, it’s worth noting that with Oracle Application Express the programming experience is 100% web-based. Sure, you can use other tools, but in reality, APEX usually provides everything you might need, so you never need to leave the web browser. You develop on the web, you deploy on the web, and your users also run the app on the web.

Oracle APEX is a full spectrum technology. It can be used by so-called citizen developers, who can use the wizard to create some simple applications to get going. However, these people can team up with a technical developer to create a more complex application together, and in such a case it also goes full spectrum – code by code, line by line, back-end development, front-end development, database development. If you get a perfect mix of front-end and back-end developers, then you can create a truly great APEX application.

Below, you can see the typical window which you use to access Oracle APEX. There are lots of buttons that help you develop areas within the Oracle Application Express framework and create applications.

If you want to try this out, you can go to apex.oracle.com and you can sign up for a free workspace (i.e., a collection of applications tied to one or more database schemas).

Oracle APEX tutorial – a demo application

Now, I’m gonna show you how to create applications in Oracle Application Express. I’ll use the example of an app I made recently when I moved to Poland.

Before I decided to travel here, I was advised to take a look at the air quality. I didn’t think that much of it, but it turned out it’s a frequently discussed topic in Poland, so I thought I’d build a quick app to visualize the data about air quality in the country (or, more specifically, in Warsaw). Ok, so how do I create such an app?

• I sign in to the APEX workspace, click the Create button, and choose the New application option. I called my app “Warsaw Air Quality Log”.
• For features, I select an About Page, Configuration Options, Activity Reporting, and Theme Style Selection.
• I leave the rest of the fields blank for now and instead, I just click Create Application. As you’ll see when you check it out for yourselves, creating a basic app is very quick. Of course, I could’ve added more pages there, ticked more options – but that’s what we need for now.

The screen you see below is my application. I’ve got 15 pages – I can edit them in Page Designer or I can just run them straight away. I can:

• Run my app – I have to sign in to it first, and I can use the application bar to sign out.
• Click the About Page in the help section, or go to an Admin page.
• I can change the theme style there since it was one of the options I checked when I created the app.

Now, let’s add that web service. It’ll be added as a data source, and then I’ll build a chart based on that data source.

• I go to Shared Components, which is where the data sources are kept, and then I select REST Data Sources and click the Create button in the corner.
• I select From scratch, paste in my REST endpoint, and give the data source a name – “Air Quality”. After I click Next, I can see the base URL.
• No authentication is required for this, though I could use OAuth2 or some basic authentication if I wanted to.
• Oracle APEX shows me the data and everything’s looking good, exactly what I need, so I click the Create REST Data Source button.

Now, let’s go back to our first page and start building that chart. Below you can see Page Designer. There’s the page number, a save button, and a run button, among other things.

• The chart should go to the content body, so I create a series, give it a name (once again, “Air Quality”), and select the type (chart).
• The Wizard immediately highlights something for my attention in red, which means it wants me to fill in some additional details, such as Table Name, Location, Label, and Value. I do that, specifying the series as Min.
• Then I right-click it and select the Duplicate option to easily create the Max series, by simply changing a few details in the copy.
• It’s worth noting you can also specify the chart type – I select Line with Area. I also click the Stack slider to get both series together. I save the database.

And voila, here’s my page now:

As you can see, there are various levels of air quality, as well as different days, in the last days of March and the beginning of April 2021. The highest number is 43, the lowest is 21. It’s a nice visualization of a week’s worth of data and it was created very easily and quickly thanks to the Wizard.

I’ve decided I also want to add a Log so that I can write my own observations on the air quality in Warsaw. I need to create a table to store my thoughts.

• I go to my app’s main page in Oracle APEX and choose Utilities -> Quick SQL. It’s a really quick way of recording air quality.
• I call it matt_air_quality_obs. I give it columns: log_date, log_comments and log_notes, and click the Generate SQL button.

It worked out the date correctly. I can also specify the maximum number of characters for entries in the comment and notes columns, by hanging the variable string. The max for an Oracle column is 4000 characters and that’s what I decide to use.

• I click the Settings button and select the Audit Columns field – thanks to that, I’ll know who created and updated records in my app.
• I Save the SQL Script with the name “matAir” and select the Review and Run option, so it actually creates a table in my database.
• I click Run and it’s a success – no errors were detected, which is always nice to see.

Next, I go back to my app’s main page and create a page – two pages, in fact. It’ll be a Report with Form called Air Quality Log.

• You can specify a Normal or Modal Dialog page mode, and in this case, I prefer the Modal Dialog.
• I click Next and choose Create a new navigation menu entry under Navigation Preference.
• In the Data Source tab, I choose my table from the drop-down menu. I don’t want all my columns here, so I send only those I need.
• I click Select Primary Key Column(s) and choose ID (Number). The Primary Key Column is mostly used for updates, but can also be useful for deletes and inserts.

For the next steps I:

• Create that pair of pages and I’m immediately transferred to the Page Designer.
• Select Run straight away and start recording some data.
• I also specify my observations for today (“Cloudy and Hazy”) and Log Notes (“Recorded at Night”), since it’s nighttime here, while I do this.
• I can also mark my observations for yesterday (“Clear Day” and “No Problems”), and thanks to that I already have two records.

It’s worth noting that the report is interactive. There are several things I can do with it:

• I can search for specific words, like “Day” or “Hazy”, to see corresponding records, which could be very useful when the list starts to grow bigger and bigger.
• I also download the reports in various formats, such as CSV, HTML, Excel, PDF, and RTF.
• I can display a chart, change the format or number of records per page, highlight things, and control breaks.

I test the last option by writing a second record for the 3rd of April (“It’s Night” and “No problems”). As you can see in the screenshot below, everything is presented in a nice and clean way, divided by days. It’s a nice way of formatting data and remember – all of this is for the user’s control now.

The user can save that as their private report, and as the app’s developer, I can save it for everybody, as Primary or Alternative. For example, if I save this report as Alternative called “By Day” I now have two reports – the primary one and the newly created By Day. I can switch between them freely. I can also modify the notes and comments in each of the reports and click Apply Changes to save whatever I changed.

We’re not quite done yet. Since I like the Create button on my chart, I want to copy it to my home page. Here’s how I do this:

• I use the Quick Edit option you can see at the bottom of the screen. I select the button with my mouse and edit the entry.
• I right-click Create on the list on the left and specify the target page (the dashboard – page 1). I then select where on the page I want to put it – I select the Air Quality region.
• On the page 1 settings, I change that button’s position to Edit, change its appearance to Text with Icon, and select the fa-notebook icon for it.
• Finally, I place the icon on the left side of the label. Here’s how my app looks when all is said and done:

Anytime I want, I can press the Create button to add some logs for the day. Now I only have a couple of things I need to do before I can call my test app done.

• First of all, let’s make it so that I don’t have to manually specify dates every time I use it. Once again, I use the Quick Edit function.
• I modify the P3_LOG_DATE item by changing its Default to Expression and specifying it as system date by writing T0_CHAR(SYSDATE) in the PL/SQL Expression field.
• I change the Log and Comment sections to be a required field (I change the Template under Appearance to Optional – Floating and check the Value Required field under Validation).

Now, when I click Create on the first page, the date displayed is the current one set on my system, and both Comment and Log fields are marked as required – I have to fill them out if I want to add a record.

Oracle APEX – an extremely powerful tool

As you can see, Oracle APEX is an extremely powerful tool that allows you to easily create simple-to-powerful apps, and gives you a lot of control over their functions and appearance. You have many different components available, like charts, different types of reports, mobile layouts, REST Web Services, faceted search, card regions, and many more.

And the cool thing is, it’s going to get even better with time. Oracle’s roadmap for the technology is extensive and mentions things such as:

• Runtime application customization
• More analytics
• Machine Learning
• Process modeling
• Support for MySQL
• Native map component (you’ll be able to create a map like those you saw in these Covid-19 apps I mentioned natively – right now you have to use additional tools for that, like JavaScript or a map plug-in).
• Oracle JET-based components (JavaScript Extension Toolkit – it’s definitely not low-code, but it’s got nice data visualizations)
• Expanded capabilities in APEX Service Cloud Console
• REST Service Catalog (I had to google around for the one I used, but in the future, you’ll have a catalog of freely available options to choose)
• Integration with developer lifecycle services
• Improved printing and PDF export capabilities

As you can see, there’s a lot of things that are worth waiting for. Oracle APEX is going to get a lot more powerful, and that’s even more of a reason to get to know it and start using it.

Do you need Oracle Apex developers?

Pretius has a lot of experience with Oracle APEX. We’ve got some talented developers on our team, including people with Oracle ACE and Oracle ACE Associate titles. If you’re interested in software created using Oracle’s low-code solution, write us at hello@pretius.com (or use the contact form below). We’ll get back to you in 48 hours and tell you how we can help.