Oracle APEX Tutorial: Uncover The Best Low-Code Secrets
Today, low-code development platforms are key to creating functional apps with minimal coding and Oracle APEX is one of the best environments to do just that!
Join the DZone community and get the full member experience.Join For Free
This article will serve as a basic introduction to the Oracle APEX, highlighting its strengths, features, and other information such as cost, runtime, etc. Let's get started!
What is Oracle APEX?
Application Express – APEX – is a low-code solution created by Oracle, best known for its database technology. It is used to build web-based, data-driven applications with much more ease and speed than using traditional methods (with around 100 times less code), due to its intuitive Wizard tools. It originally came out in 2004 as HTML DB and has matured considerably since then.
Oracle RAD Architecture
Oracle APEX is a part of the Oracle RAD architecture and technology stack. What does it mean?
R – “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 – “A” stands for APEX, Oracle Application Express, the topic of this article.
D – “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 through ORDS to your database – APEX exists as a component of the Oracle Database.
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.
Where Can You Run Oracle APEX?
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 forms:
Autonomous Transaction Processing (ATP) – basically transaction processing, it’s where APEX sees the most use.
Autonomous Data Warehouse (ADW) – for more query-driven APEX applications. Reporting data is also a common use of Oracle APEX.
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, etc. You can also use it on Exadata – a super-powerful APEX physical server on cloud services.
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.
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 and 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 a 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 and remembering the data – 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 an 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.
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. Oracle APEX is going to get a lot more powerful, and that’s enough of a reason to learn to use it.
Published at DZone with permission of Matt Mulvaney. See the original article here.
Opinions expressed by DZone contributors are their own.