Caching von PL/SQL Funktionsaufrufen

| Dec 20, 2019
Mohamed Houri zeigt in seinem Blog einen nützlichen Trick: er stellt den Fall einer Query vor, in der ein Wert mit dem Ergebnis eines Funktionsaufrufs verglichen wird:
a.xy_bat_id = f_get_id('BJOBD176')
Dieser Zugriff ruft beim Abrufen von 18605 Datensätzen aus der zugehörigen Tabelle 18605 recursive calls hervor - und darüber hinaus sehr viele consistent gets. Eine Untersuchung mit SQL Trace zeigt, dass fast die gesamte Laufzeit auf diesen wiederholt ausgeführten Funktionsaufruf entfällt. Offenbar (more...)

Friday Funny – Festival of serious yahoos

| Dec 12, 2019
I don't care what you all think, this is my blog, and I continue to laugh at this SQL joke.

select column_value
from apex_string.split('YAHOO~SERIOUS~FESTIVAL', '~')
order by dbms_random.value

If you're a Simpsons fan, you may recall this clip

Yahoo Serious Festival - The Simpsons
If you're not aware, Yahoo Serious is an Australian actor from the movie of the same name. He made it onto Time magazine, then nothing.

But how nifty (more...)

PostgreSQL Creating Schema

| Dec 10, 2019

The process of creating a schema requires you grant the CREATE ON DATABASE privilege to the user as the postgres user. You use the following syntax:


As the student user, you create the app schema with the following syntax:


Then, you can query the result as follows:

FROM     pg_catalog.pg_namespace
ORDER BY nspname;

You should see the following:

      nspname       | nspowner |               nspacl                

Where would we be if we just believe?

| Dec 10, 2019
As a science aficionado, there are certain phrases that ... catch the eye.

Recently on twitter there was an interesting thread that continued from Michelle Skamene's post on Top 15 Tuning Tips for APEX.  Michelle provided a wonderful follow-up post summarising the outcomes of the thread.

Point 9 suggests we avoid HTML in our queries, and use HTML expressions. This is undeniably good practice, but there was a question regarding how much performance is gained. (more...)

Translation enhancements in APEX 18 and 19

| Dec 8, 2019
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 (more...)

New Snowflake Community Advocate Program

| Dec 5, 2019
I am happy to announce that The Snowflake Community—a hub for Snowflake users to connect, share, and learn from each other both offline and online—is launching a new program called the Select Star Program. What is a Select Star? The Select Star program recognizes and rewards our most engaged community members who go above and […]


| Dec 3, 2019

This demonstrates how you insert results from a common table expression (CTE) in a leading WITH clause. I thought it would be a nice add since the existing tutorials didn’t have an example.

Create the message table, like this:

( message_id    SERIAL
, message_text  VARCHAR );

Now, here’s a CTE with a two fabricated rows:

( SELECT 'x-ray' AS msg
  SELECT 'MRI' AS msg )
INSERT INTO  (more...)

My Presentations at #UKOUG #techfest19

| Dec 3, 2019
I just finished my second presentation at TechFest19 in sunny Brighton, England. The conference this year is great from every point of view: venue, people, content, conversations… Thanks to those who came to my talks for your benevolent attention and excellent questions/remarks! Both my presentations are now on SlideShare. I beg you to download them […]

Include new APEX templates in an older APEX instance

| Dec 3, 2019
Have you seen that super awesome theme in the new APEX version, then wondered how long it will it be before your site upgrades so you can actually use it?

What if I told you that your current version could be retrofitted to use that template?

I really like the look of the Content Row template, I think that's going to serve many developers good purpose.
Sure, I could create something similar now, but if (more...)

Interpreted code in APEX

| Nov 28, 2019
A few years ago I posted a comparison between plugin code left in the source attribute, vs code that has been transferred to a PL/SQL package.

In the interests of good science, and I wanted to chat about it at next week's Office Hours, I wanted to repeat this test.

I had a little difficulty working out how I got the metrics, I think APEX debugging has changed a little since I ran (more...)

B*Tree Index Optimierungen in Postgres 12

| Nov 28, 2019
Laurenz Albe erläutert in seinem Artikel im Cybertec Blog ein paar interessante Optimierungen für B*Tree Indizes, die mit Postgres 12 eingeführt wurden. Ein erster Punkt ist, dass Indizes, die als non-unique definiert sind, in Postgres 12 deutlich kompakter erstellt werden. Ursache dafür ist, dass die TID (also die tuple Id, sprich: die physikalische Satzadresse) in den Schlüssel aufgenommen wurde, was eine bessere interne Sortierung der Einträge mit sich bringt und dafür sorgt, dass page splits (more...)

On the PL/SQL you don’t write when using APEX.

| Nov 26, 2019
Fancy joining in on a discussion with PL/SQL and Oracle APEX community members from around the world?

I'm honoured join Karen Cannell and Scott Spendolini, to be hosted by Steven Feuerstein in the next AskTom PL/SQL Office Hours on December 3, 2019.

It seems a few people haven't heard of these "office hours" sessions, but they're worth a go - more than just your average webinar. And they're all recorded for later viewing.

This (more...)

Human readable JSON, stored in BLOB

| Nov 26, 2019

Currently Oracle (we're using Oracle 18c at the moment) is still recommending to store your JSON document in a BLOB column. More efficient, no character conversions and probably some more advantages.

When there is a need to look at the JSON document, having it as a BLOB is not very useful.

An easy way to make the JSON Blob readable is to use the following query:

select json_query (i.json_payload, '$' returning clob pretty)
from (more...)

PostgreSQL Upsert Advanced

| Nov 25, 2019

Nine years after writing how to use the MERGE statement in Oracle, I am writing how you implement an UPSERT statement in PostgreSQL. I wrote an initial post going over the basics of PostgreSQL’s upsert implementation of the INSERT statement with an DO UPDATE clause and a DO NOTHING clause.

I thought it was interesting that the PostgreSQL Upsert Using INSERT ON CONFLICT Statement web page didn’t cover using a subquery as the source for (more...)

PostgreSQL Upsert Intro

| Nov 24, 2019

Oracle and SQL Server use the MERGE statement, MySQL uses the REPLACE INTO statement or ON DUPLICATE KEY, but PostgreSQL uses an upsert. The upsert isn’t a statement per se. It is like MySQL’s INSERT statement with the ON DUPLICATE KEY clause. PostgreSQL uses an ON CONFLICT clause in the INSERT statement and there anonymous block without the $$ delimiters.

The general behaviors of upserts is covered in the PostgreSQL Tutorial. It has the (more...)

APEX Low code expressions

| Nov 19, 2019
The following where clause expression would normally return false, therefore the query would deliver no rows.
select * from dual where null != 'X'

no rows returned
A null can never equal a value, and hence cannot be determined to be disimilar to another value. False is the expected condition.

Turns out when using it as what appears to be the  declarative low code alternative, it returns true – and renders.

So if you want (more...)

So you want to submit a conference abstract?

| Nov 19, 2019
It used to be our annual Perth conference that got me thinking about abstract ideas, but now it's the Kscope abstract submission deadlines that get my boat rocking.

But every day is a good day to think about the topics I'd like to might like to share with to your local community.

I've also been trying to convince a collegue to do their first presentation, and the questions in the ODTUG submission page got me (more...)

Agile Oracle Database Modeling and Development (#AgileOracleDatabase) – I’m a speaker at #DOAG2019

| Nov 18, 2019

We try to be agile in developing and merging our features into branches when they are ready for use.

Test Driven Development is on everyone’s lips with Java and other languages.

Whether we develop ADF or JET or Spring, we try to apply these techniques in many places.

But what is the real basis of most of our applications?
This is the Oracle database with its data model and programming language PL/SQL!

Again and again (more...)

Reading External File

| Nov 13, 2019

I’m working on items for migrating my database class from Oracle to PostgreSQL. I ran into an interesting limitation when I tried using the COPY command to read an external CSV file.

I had prepared the system by creating a new directory hierarchy owned by the postgres user on top of a /u01/app mount point. I set the ownership of the directories and files with the following command from the /u01/app mount point:

chown -R  (more...)

PostgreSQL and LPAD

| Nov 12, 2019

While porting my Oracle code to PostgreSQL I encountered a little quirk. It’s probably not a quirk except for the fact that I’ve worked in Oracle so long. Oracle implicitly type casts so well that we seldom notice.

PostreSQL doesn’t work like Oracle. It does type cast sometimes but not very often. I tried porting the following segment from my Oracle stored procedure to PostgreSQL:

/* Add account number with zeros. */
FOR j IN  (more...)