Tuesday, October 11, 2022

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 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!

Monday, October 11, 2021

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

Thursday, July 29, 2021

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...



Sunday, November 8, 2020

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! :-)

Sunday, December 8, 2019

Translation enhancements in APEX 18 and 19

The Oracle Application Express (APEX) documentation summarizes the translation process thus:

"To translate an application developed in App Builder, you must map the primary and target language, seed and export text to a translation file, translate the text, apply the translation file, and publish the translated application."

When you go to Shared Components and then click "Translate Application", this process is illustrated by a list:

So the "default" approach is to download the XLIFF file and use a third-party tool to do the translation by editing that file.

However, it is also possible to edit the translations directly from within APEX. Click on "Translation Repository" under Translation Utilities to access this editor.

A few simple, but very useful enhancements have been made to the Translation Repository pages in the last couple of APEX versions.

 In APEX 19, there is now a new column available called "Translated" which indicates whether the original string has been translated or not. Using this column it is easy to filter out the strings already translated.

The popup edit window for the translation has a checkbox, added in APEX 18, that allows you to update all existing occurrences of the source string with a new translation. This is useful if you have, for example, 100 buttons with the label "Save Changes" because you only need to do the translation once.

Also in APEX 18, a "Grid Edit" page was added so you could edit the translations using a tabular form. In APEX 19, this tabular form was replaced with an interactive grid.

This interactive grid also includes the "Translated" column that can be used to filter out the translations which are already done.

Note that sorting by the "Translate From" or "Translate To" columns in the interactive grid is not possible (if you try you'll get an "ORA-00932: inconsistent datatypes: expected - got NCLOB" error). But the list of available columns include "Translate From (Partial)" and "Translate To (Partial)" which extracts the first part of the string as a regular varchar2 (usually the strings to translate are not that long so you'll see the full string in the "Partial" column). If you need to sort the strings (I find it useful to sort alphabetically by the source string in order to find identical strings and use copy/paste to update the translation), use these "Partial" columns to set up the sorting.

Thanks to Joel Kallmann for taking the time to add these small but very useful enhancements into APEX based on my feedback.

Sunday, June 16, 2019

APEX custom Theme Style performance

When you create an application in Oracle Application Express (APEX) using the Universal Theme, the application comes with a few default "Theme Styles" which are essentially various stylesheets that you can switch between to go from the default blue (called "Vita") to, for example, the dark grey style (called "Vita - Slate").

You can use the Theme Roller in Oracle Application Express (APEX) to customize the colors and other settings, and by saving these you create your own Theme Styles. In this video, Shakeeb from the Oracle APEX team demonstrates how to use the Theme Roller, and he also briefly mentions that the generated stylesheet for custom theme styles can be placed into the filesystem for better performance.

I recorded a video myself to discuss the details of how custom Theme Styles affect the performance of your APEX application, and how you can move the Theme Style generated stylesheets to a web server to maximize performance and minimize bandwidth consumption:

Another issue that I did not mention in the video is that when you pick an icon for your application, APEX creates an app-icon.svg file (for the logo itself) and app-icon.css (that references the svg file), and places both those files in your Static Application Files.

Now since these files (and all other Static Application Files) are stored in the database, they require a database hit to retrieve, instead of fetching them from (and caching them on) the web server filesystem.

So if you want to maximize performance, I suggest that you remove the reference to the logo CSS file from your application (and delete the files from the Static Application Files).

If you really want that logo icon, you could of course place the .svg and .css files on the web server and reference them from there instead.

Thursday, April 25, 2019

Thoth Gateway version 1.4.3 now available

I've updated the Thoth Gateway (a PL/SQL web gateway similar to mod_plsql and ORDS, but written in .NET that allows you to run APEX and PL/SQL Web Toolkit applications using Microsoft IIS web server).

The latest release is version 1.4.3. It fixes a few minor issues, but more importantly it is compiled against the latest ODP.NET Managed Driver.

Among other things, this version of the Managed Driver supports Oracle Advanced Security Option (ASO) encryption; without this certain operations such as APEX file uploads would cause "OracleInternal.Network.NetworkException: ORA-12537: Network Session: End of file" errors against an Oracle 18c database.

When upgrading to Thoth Gateway v1.4.3 or later, please make sure you also copy the Oracle.ManagedDataAccess.dll file in addition to the PLSQLGatewayModule.dll file to make sure you have the correct library.