Update: This article was originally published in August 2021. In September 2023, it was updated with new information about APEX and a refreshed tutorial that shows how to create your first app.

Pretius is a company with a keen interest in Oracle APEX technology. We have dozens of low-code developers, with some real APEX enthusiasts among them – including several 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 this technology.

This article will serve as a basic introduction to the Oracle APEX, 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 developing new versions of the Oracle Database. 

Oracle also has countless toolsets, including JavaScript frameworks, cloud analytics (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 APEX.

If I were building a web-based, data-driven application, I don’t think there’s any better tool than APEX.

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, and later it was called Oracle Application Express for a while). 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 APEX say it can help you develop enterprise apps up to 20 times faster and with 100 times less code. You can achieve this because it 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 low-code frameworks like Powerapps (Microsoft), Outsystems, Mendix, Salesforce, or Appian. A research and advisory company called Gartner has identified those technologies as the market leaders – they have a high completeness of vision and a high ability to execute. Oracle APEX is a challenger in this space, though I expect in the future, it’ll move to the “leaders” section.

💡 Get to know more about the Low-Code Gartner Magic Quadrant

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, 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 APEX applications are web-based 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, programming language for the Oracle DB. There are packages for string manipulation, calling web service, and everything you’d expect from a programming language.

The main programming language behind APEX is PL/SQL, although as 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 APEX 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 a database runs. It can be a physical, dedicated server, a virtualized machine, or a docker image (you can run it on your laptop or fire it up on a train or a plane – it’s very popular among APEX 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. Well, I’ve got some good news: 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 – three options

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 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 one of the HR tools 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.

A few years back, the software had around 46k users a day and got 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 and hardware. There are thousands of dollars of products available for purchase.

Covid-19 applications

And finally, during the pandemic, 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, and the screen shows 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 were many such applications (over 30). You can see them all if you go to apex.world, 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 APEX, the programming experience is 100% web-based. Sure, you can use other tools, but in reality, the platform 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 you use to access Oracle APEX. There are lots of buttons that help you develop areas within the 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 APEX workspace (i.e., a collection of applications tied to one or more database schemas).

Oracle APEX tutorial – a demo application

I’ve told you a little bit about APEX, and now it’s time to show you how it works in practice. The best way to do that is by building a functional app really fast… in, say, around 10 minutes? But what kind of an app should it be? Well, I’ve got an idea: why don’t we ask ChatGPT to give us a challenge? I wrote the following request:

Give me an example of a requirement a UK business might have for a data-driven modern web application.

Here’s what it came up with:

Well, ChatGPT, that’s quite a requirement! Let’s see how we get on in 10 minutes… and perhaps afterwards, we can spend more time completing the rest of the requirements.

So, it’s asking for Real-time Data Integration? Well, lucky me… I just happen to have a simulation of real-time data right here in a REST API as you can see, it shows the latest orders and order items. Isn’t it beautiful?

Looking at the above, I see Order 206, which has 3 Order Items of various quantities.

First, we need a data model for our APEX application, and as luck would have it, there’s a similar data structure built right into APEX.

Click on SQL Workshop > Utilities  > Sample Datasets and Install the Customer Orders Dataset. 

When it finishes the installation, click the magic Create Application button.

And then click the Create Application button.

Click Create Application again.

Click Run the Application, log in, and have a click around. Hey, it’s not bad, is it? And look at those Dashboards. They even resemble the requirements.

If you click on Administration > Stores, you can see various stores – they even have Latitude & Longitude coordinates. 

Let’s show that on a map.

Click your App in the Developer bar and then click on page 1.

Drag the Map region from the Gallery (bottom-middle of the screenshot below) and drop it under the Page Navigation region – exactly like in this picture.

Click on the New (it’s turned red, because it needs your attention) and set the Table Name, Geometry Column Data Type, Longitude Column & Latitude Column exactly as per the screenshot below.

Click the Play button and… Nice!

Now, let’s see our orders by clicking on Orders in the navigation menu sidebar.

Hmm, no orders for the last day? Let’s hook this up to our real-time sales feed and get that sorted.

First, we will create a REST Data Source, which is kind of like a shortcut or bookmark that we can use when accessing the real-time sales feed in APEX.

Click on your App in the Developer bar and click the little down-chevron next to App Builder and right-click Import to open it in another tab – see picture below.

Download Pretius Real-Time Sales Simulation.sql, select it in the file browser and then select REST Source Catalog, per the picture below. 

What we are doing here is importing a pre-prepared catalog of a single REST Service, which consumes the Real-Time Sales endpoint, saving you a bit of time manually configuring it.

Click Next and Next again.

Since all catalogs reside in groups, enter Pretius Group and click Import REST Catalog.

Once it’s imported, close the browser tab (we don’t need it any more), returning to the previously open tab.

Click on Shared Components (in the middle of the page). Then finally, click on REST Data Sources.

Click Create > From a Rest Source Catalog, click Next and then click the RealtimeSales over to the right (so it looks like the picture below). Then, click Next again.

Finally, click the Create REST Data Sources button. 

After that, click Realtime Sales to review it.

What we are going to do next is set up a REST Synchronization. This basically means creating a local table to sync the same records with the real-time sales feed every 10 minutes. Since the feed only provides new sales, we just need to append those records.

Click Manage Synchronization on the right-hand side.

Enter the table to sync with as below and click Save.

Click the Create Table button.

In the Synchronization Schedule, click the wrench icon, set the interval to 10 minutes and click Set Execution Interval.

Click Save and Run and Examine the log.

Woah, we have records entering the synchronization table.

Now let’s add a trigger to the sync table so that records fly into the main tables.

A trigger runs when data events occur on the table. We will create a before-insert trigger to read the contents of the record being inserted and then use that information to insert a version of that record into the ORDERS and ORDER_ITEMS tables.

Click on SQL Workshop, right-click Object Browser and then choose Open Link in new window.

Use the plus icon to add a Trigger.

Complete the form so that it looks like in the screenshot below.  

Click Create Trigger.

The trigger will open and display its code contents. Paste over the code you see below and click Save and Compile.

CREATE OR REPLACE TRIGGER "SALES_SYNC_T"
BEFORE INSERT OR UPDATE OR DELETE ON "SALES_SYNC"
FOR EACH ROW
DECLARE
   json_data CLOB DEFAULT :new.orderitems;
   l_order_id orders.order_id%TYPE DEFAULT NULL;
BEGIN
   INSERT INTO orders(ORDER_DATETIME, CUSTOMER_ID, ORDER_STATUS, STORE_ID)
   VALUES(:new.ORDERDATETIME, :new.CUSTOMERID, :new.ORDERSTATUS, :new.STOREID)
   RETURNING order_id INTO l_order_id;
   FOR rec IN (
      SELECT jt.lineitemid, jt.productid, jt.unitprice, jt.quantity
      FROM JSON_TABLE(
         json_data, '$[*]'
         COLUMNS (
            lineitemid NUMBER PATH '$.lineitemid',
            productid NUMBER PATH '$.productid',
            unitprice NUMBER PATH '$.unitprice',
            quantity NUMBER PATH '$.quantity'
         )
      ) jt
   )
   LOOP
      INSERT INTO order_items(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
      VALUES(l_order_id, rec.lineitemid, rec.productid, rec.unitprice, rec.quantity);
   END LOOP;
END;
/

Close the browser tab (we don’t need it any more), returning to the REST Synchronization tab. We are all set up, but hey, let’s not wait until the next 10-minute run – just hit the Save and Run button again and check out those juicy orders entering our application in real-time.

By now, you’ve probably noticed that Customer, Store and Product are all numerical IDs rather than useful descriptions. Let’s change that.

Click on Page 4 in the Developer bar to edit the page.

Expand the Orders region, click on CUSTOMER_ID, and change the Identification Type, List of Values Type and List of Values, as per the picture below.

Click the Play icon to save and run the page, and now the customer’s full name is displayed. We’ve struck gold here, as APEX has already created a Full Name List of Values (LOV) we could use. Unfortunately, there is no such equivalent LOV for Store ID, but of course, there is an easy fix.

Edit Page 4 again. With the Orders region expanded, click on STORE_ID and change the Identification Type and List of Values Type as per the picture below.

Now, click the little icon on the bottom right of the picture (i.e., within the SQL Query section) to Open the Code Editor and immediately click the Hammer icon to access the Query Builder shown in the picture below.

Click STORES, then STORE_NAME and finally STORE_ID. The order you click them is important: the first column you click is the description, and the second is the hidden returning ID.

Click Return, OK and then Run the page. You can see you are now developing in APEX, and you are enriching the user experience.

To fix the PRODUCT_ID in the Order Items region, perform exactly the same steps as above for STORE ID, but this time choose the PRODUCT table and then columns PRODUCT_NAME and PRODUCT_ID in that order.

Run the page. You’ve got to admit it looks mighty fine.

Oracle APEX – A potent low-code development platform

As you can see, Oracle APEX is a potent low-code development environment with world-class features. It allows you to create simple-to-powerful apps easily 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. With a bit of experience behind your belt, some PL/SQL and SQL commands, and some additional knowledge and skills, you can get truly astounding results. And if you need more information on APEX, check out the other articles on our blog:

1. Oracle APEX new features – the low-code platform keeps evolving
2. Why low-code? How Oracle APEX development helped Ukrainian refugees
3. How a low-code framework helped Munich Re HealthTech build a data-driven SaaS application – in 4 months & across multiple countries
4. Google Identity Services – a quick guide on using the new library in Oracle APEX applications
5. What is Oracle APEX? Possibilities and career paths for low-code developers

Do you need Oracle APEX developers?

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