Joel Kallman Day 2023: APEX Automated Backups #joelkallmanday

 I'm writing a short blog post today as part of the Joel Kallman Day 2023.

Some time ago (I think it was around the APEX v20.1 release), APEX got a new built-in backup feature that takes an automatic backup of your applications each day. Only applications that have changed will be backed up, and a maximum of 30 days of backups are kept (this is configurable, it can be disabled, or you can set a retention period of between 1 and 30 days).

You can view the list of backup by clicking on "Manage Backups" on the Application Builder main page.

 

This gives you a list of your applications, and information about when the last backup was taken, and the number of backups that have been taken so far (remember that this only increments if there have been actual changes in the application, and that there is a maximum number of backups per app, typically 25 or 30).

 

By clicking on the application name, you can see the details of each backup, including when it was taken. There is a dropdown menu which allows you to see additional details of each backup (what was changed in the app that caused it to be backed up).

From here, you can also download the app, or restore it (either as a new app, or to replace the existing version of the app).

 

This automated backup feature is very convenient, and allows you to recover from mistakes by rolling back to a previous version of the app.

IMPORTANT NOTE: You should not rely on this backup feature as the only way to keep your application definitions safe. Remember that the backups are stored in the same database as the rest of your development environment, so if something happens to that database and you are not able to recover it, you will not be able to restore your application backups either. I would only rely on this backup feature to quickly recover from mistakes, but the recommended way to keep your application definitions is still to do a (manual or automated) export of the app to a file (or split into multiple files), and then to store those files in a version control system like Git (and backup your Git repos!).

Restrict access to APEX Builder based on client IP when using Apache and ORDS

 When running Oracle Application Express (APEX) in production, the best practice is to deploy the "Runtime-Only" environment, instead of a "Full Development" environment:

From the docs:

As with any software development life cycle, Oracle strongly recommends that you have different environments for development, testing/QA, and production. For testing and production instances, Oracle APEX supports the ability to install just a runtime version of Oracle APEX. This runtime environment minimizes the installed footprint and privileges and improves application security since in a runtime instance developers cannot inadvertently update a production application.

An Oracle APEX runtime environment enables you to run production applications, but it does not provide a Web interface for administration. A runtime environment only includes the packages necessary to run your application, making it a more hardened environment. You administer the Oracle APEX runtime environment using SQL*Plus or SQL Developer and the APEX_INSTANCE_ADMIN API.

If you DO NOT follow this best practice, and for whatever reason decide to install a full development environment in production, at least you can harden it somewhat by restricting access to the APEX Builder and internal administration apps to your own IP address.

You can do this by setting the APEX instance parameter "RESTRICT_IP_RANGE". Again, according to the docs:

To restrict access to the APEX development environment and Administration Services to a specific range of IP addresses, enter a comma-delimited list of IP addresses. If necessary, you can use an asterisk (*) as a wildcard, but do not include additional numeric values after wildcard characters. For example, 138.*.41.2 is not a valid value.

 

However, the above will not work if you are using a proxy (such as Apache HTTPD) in front of ORDS. The reason is that your real client IP (for example 1.2.3.4) is not forwarded from Apache to ORDS, so to APEX the "REMOTE_ADDR" appears as the server's address (typically "127.0.0.1" if Apache and ORDS are on the same machine), and not the real client IP.

To work around this, there is another option available from APEX 20.1: The instance parameter "RESTRICT_DEV_HEADER". Note that this instance parameter is undocumented at the time of this writing, but Christian Neumuller on the APEX team has confirmed that this is a doc bug that will be fixed soon.

RESTRICT_DEV_HEADER works by setting a custom header in your Apache setup, which is then forwarded to ORDS and APEX. If this header is present (the actual value does not matter), the APEX Builder and internal administration apps are blocked and return a "403 Forbidden" error if you try to access them.

To get this working, you have to modify your Apache configuration, check the real client IP, and set the header accordingly:

 

Remember to restart Apache to pick up the new settings.

Then set up the following in APEX:

Then try to access your APEX Builder from a non-trusted IP, and you should get a "403 Forbidden" error.

References:

• APEX_INSTANCE_ADMIN package, available parameters: docs
  
• Apache RequestHeader: docs
  
• If/Then/Else syntax in Apache: Blog post and official docs
  

 

 

 

Dynamic Content Region in Oracle APEX 22.2

There's a new version of Oracle Application Express (APEX) in town, and one of the new "features" is a new region type called "Dynamic Content" that allows you to output content using PL/SQL.

What happened to the existing "PL/SQL Dynamic Content" region type that does exactly this, you might ask? It's still there, but now it's marked as "Legacy" and requires an extra mouse click just to be able to select it, and to make you feel bad for using it.

So why a new region type to replace the old one? The stated reason is to allow the region to be refreshed, but as readers of my blog will know, there are already ways of doing that, as demonstrated in this region plugin that I released in 2012.

In order to make the region refreshable, the APEX team had to change the way you use it. You no longer call the "HTP" package to add your content to a global output buffer. Instead, you have to concatenate and return your content as a string (varchar2 or clob). The way it's suggested in the online help is like this:

There are several things I don't like about this:

• You have to declare a local variable
• You have to clutter the code with concatenation
• You cannot split your code into subroutines unless you pass around your local variable holding the final result
  
• You have to rewrite every piece of existing code (if you want to use the new region type)
  

Instead, I want to be able to (continue to) write code like this:

Notice the use of "apex_htp" in the above code; this is a package that does not (yet) exist, but read on!

Or, to give a more complex and real-world example which uses several subprocedures that all write out to the global buffer:

What I think the APEX team should have done

When the APEX team decided that they needed a new region type with a different architecture, instead of just throwing the existing region type into the murky "legacy" territory, they should have made the transition as smooth as possible, by offering a drop-in replacement to the existing HTP package.

I immediately suggested this when I saw the latest APEX version, and as I said on Twitter I think it's a strange decision to hide away the existing method as "legacy" when you are replacing it with a worse dev experience that is not yet fully baked.

To fix this, I've submitted an idea to the APEX Ideas app, to suggest that the APEX team include a new package in the next APEX version that can be used as a drop-in replacement for the HTP package. Using this package with existing code should be as easy as replacing "htp.p" with "apex_htp.p" and adding a single RETURN statement at the end of the region process code.

The spec for such a package could look like the below image. I've already made a working prototype for myself, calling it "xtp", and with a (temporary) synonym I can also call it using "apex_htp", but the point of this blog post (and the submitted idea) is to get this included in APEX itself, where it should have been from day 1 of APEX 22.2. Go and vote today for this idea if you are using PL/SQL Dynamic Content regions!

For those interested in the (just a quick prototype) package body, here it is on GitHub.

Joel Kallman Day 2022: APEX Caching #joelkallmanday

 I'm writing a short blog post today as part of the Joel Kallman Day.

"Server-Side Caching" is probably an under-utilized feature of Oracle APEX. It's very easy to use, and can have an amazing impact on the performance of your APEX application, and make it (even) more scalable.

 

What is APEX Server Caching?

Here's how the help text explains it:

"Select how the Oracle APEX engine can cache HTML text on the server side. If caching can be used, it emits the cached result instead of computing it again. This cuts down on expensive computation time on the server. This feature is unrelated to the browser's caching capabilities."

In other words, if you have a report, or a chart, or some content generated dynamically by PL/SQL, the APEX engine can store the final result (the HTML text) of that content and just display that instead of having to re-run a query or execute your code.

What can be cached?

You can cache whole Pages, or individual Regions.

 

Cache settings

You can choose how to cache (by session, by user, or for everyone) and how long to cache. You can also select one or more items; if the value of these items change, the cache will automatically be invalidated and refreshed on the next view. You can also combine this with a more complex Condition.

APEX API packages related to caching

The following packages are related to caching:

• APEX_PAGE.PURGE_CACHE (doc link)
• APEX_REGION.PURGE_CACHE (doc link)
• APEX_UTIL.CACHE_* (doc link)
  

APEX Dictionary View

The view APEX_APPLICATION_CACHING gives you information about caching, useful for debugging and monitoring.

Bonus: Tip

On pages where you use caching, create a button to clear the cache immediately (and perhaps conditionally display the button for admins/developers). This will make your life as a developer easier. Remember that any changes you as a developer make to reports, etc. will no longer be immediately visible if they are cached!

Joel Kallman Day 2021: Oracle Database Express Edition (XE) 21c

The "Oracle Tech Network Appreciation Day" has been renamed to "Joel Kallman Day" in honor of Joel Kallman, who along with Mike Hichwa was one of the original developers of Oracle Application Express (APEX). Joel suddenly and tragically passed away during the pandemic earlier this year.

Joel at APEX World in Rotterdam, 2019

 

The "Joel Kallman Day" is about appreciating some piece of Oracle tech. In 2016, I wrote about the free Oracle Express Edition (XE) database. It just so happens that the latest Oracle XE version, 21c, was released for Linux a month ago, and for Windows just a few days ago.

Therefore, my blog post for Joel Kallman Day 2021 is about Oracle Express Edition, a great (and free!) way to get started with Oracle, PL/SQL, APEX and databases in general. You can download the software from oracle.com/xe and there's also docs and videos showing how to install and get started.

To give the new version a test drive, I created a Windows Server VM on Microsoft Azure, and then downloaded and installed XE 21c along with APEX and the Thoth Gateway for IIS.

The Azure VM size I selected for this test was "Standard B2s (2 vcpus, 4 GiB memory)". In Azure it costs about USD 40 per month.

Just for fun, here are some timings:

Time to...
... provision new VM in Azure: 5 minutes
... first login to VM: 2 minutes
... download a real browser: 1 minute
... download 21c XE: 30 seconds
... unzip 21c XE: 30 seconds
... install 21c XE: 15 minutes

... download APEX 21.1: 1 minute
... unzip APEX 21.1: 3 minutes
... install APEX 21.1: 8 minutes

... install IIS: 2 minutes
... install Thoth Gateway: 1 minute

... first login in APEX (total time): 39 minutes

Here's a screenshot showing APEX 21.1 in action on Oracle Database XE 21c on Windows Server 2019 with IIS and Thoth Gateway. The page rendering time is not too shabby! :-)

So, a big thanks to Oracle for providing a great, free database, and thanks to Joel and everybody on the APEX team for their excellent work. #letswreckthistogether

Ten years since Mythbusters: Stored Procedures Edition

Today it's exactly ten years since I published my blog post called Mythbusters: Stored Procedures Edition. 

Using "stored procedures" (ie storing and executing code in your database, next to the data, instead of in a middle tier or client) is a practice that is provably both efficient and secure, yet it is often dismissed as "wrong".

My original blog post looks at the usual reasons given against the use of stored procedures for "business logic" (or for anything at all, really) and explains why they tend to be myths (or outright lies), repeated so many times that they are taken as the truth. Take a moment to read the article now.

Publishing the article proved that advocating for the use of stored procedures, which had gone out of fashion already by 2011, was controversial. It caused an immediate spike in my blog traffic, receiving almost 20 000 page views in a single day (at the time, my blog had perhaps a few hundred page views per day on average). The article was picked up by Hacker News and generated a lively discussion there.

So, where are we in 2021, ten years later?

Mainstream development (still) uses imperative, object-oriented 3GL languages (Java, .NET, etc) running in the middle tier or client, dragging data out from the database to process it, with data access usually abstracted through an ORM. Which, predictably, results in suboptimal performance.

The myths about stored procedures are still prevalent. Ask a random Java/.NET developer today, and I bet he will claim that it's impossible to version control stored procedures (myth #1). In that case, please send him a link to that 10-year old article...

 

 

New version of Quick PL/SQL, a code generator for PL/SQL

I've released a new version of Quick PL/SQL, the code generator for PL/SQL based on the same concept as QuickSQL in APEX, but for PL/SQL code. The initial version was released in August 2018.

The online version is available (as before) here: tinyurl.com/quickplsql2

The source code is available on my GitHub.

This release (version 1.2) has a few improvements. The first is syntax highlighting of the generated code (right hand side of the page). This relies on the CodeMirror library which is bundled with APEX. Unfortunately it only supports SQL, not PL/SQL, so not all code is properly highlighted, but it's better than nothing.

The second improvement is a "Quick CRUD" button which opens a dialog that allows you to specify a table name (and optionally the name of the primary key column and the package name):

Based on this it creates the markup required for a complete "CRUD package" in the Input section, which saves you from remembering the markup syntax, and saves you from typing a lot of boilerplate markup as well.

Then, as before, just click on "Generate" to have the final PL/SQL generated for you.

Enjoy! :-)