Social Sign-in Authentication Scheme

Recently I was involved in setting up a Social Sign-in Authentication Scheme, so despite the doom & gloom of this post, we are breaking some interesting rock.

I say 'involved', since I had the support of one of the nerdiest nerds in Perth, for all the server tinkering. I just had to paste in some URLs and other config settings within APEX.

He expected every error, and it always seemeed just due process (more...)

APEX 18.x Application Session Sharing

For quite some time I've been crafting multiple applications that, to the end user, appear as one.

This is possible with some Session Sharing attributes in relevant the Authentication Scheme, a feature agnostic to the Scheme Type.

This ultimately means you can set up two different applications that use two different methods of authentication, thereby letting in two different sets of users in the 'same' application - you just need to make some UX decisions.


Dokumentation für Statspack

Oracle ist ziemlich gut bei der Entwicklung von relationalen Datanbankmanagementsystemen. Weniger gut ist die Firma beim Dokumentieren der eigenen Software - und bei der geeigneten Präsentation dieser Dokumentation im Internet. Ein Stück Dokumentation, das ich auch schon gelegentlich gesucht und nicht gefunden habe, liefert Pierre Forstmann in seinem Blog: die Dokumentation für Statspack. Dass man eine komplexe Dokumentation auch über viele Releases in angemessener Form im Netz präsentieren kann, beweist übrigens Postgres.

How to download the latest GitHub repo release via command line

I just fiddled around a bit to find out how to download the latest GitHub release via the command line. Turns out that GitHub doesn’t provide a universal download URL to release binaries like it does for the release browser page itself. The latest release page can always be reached via, e.g. Unfortunately that … Continue reading "How to download the latest GitHub repo release via command line"

ORA-22905 when calling ODCI Table functions from PL/SQL

Back in 2015 I experimented using ODCITable* functions to dynamically parse delimited text (see blog post here.)

Now blog reader Daniel Cabanillas points out, that it raises an error when used in PL/SQL. Silly me - I had only tried it out in SQL (my favorite language) and had completely missed that it failed in PL/SQL.

Lucky for me, the classic workaround of dynamic SQL works here too 😁

Look in the old blog (more...)

Participant list for Oracle Dev Gym SQL Championship 2018

2018 is over. Throughout the year people have participated in the "Select for SQL" Tournament on Oracle Dev Gym. It's time for the Top 50 to try their skills in the Annual SQL Championship!

Everybody who tried the quizzes have hopefully had fun as well as exercised SQL skills. Maybe even learned a little :-) I hope you'll all keep working your mental muscles in 2019 as well - get your friends and colleagues to (more...)

Data Lake Ingestion strategies

Numbers don’t lie. They empower you to be smart and stay decisive. Recently, I stumbled upon the bookmetrix portal that publishes by-chapter metrics of a book. Metrics are straight enough to discover book’s most loved chapters. Although the numbers were not skewed by high margin, I realized the fact that “Data Lake Ingestion Strategies” has … Continue reading "Data Lake Ingestion strategies"

Erweiterte pg_stat_statements_reset Funktion in Postgres 12

Auf eine interessante Ergänzung der Funktion pg_stat_statements_reset in Postgres 12 weist Daniel Westermann hin: war es bisher nur möglich, die pg_stat_statements Datenbasis komplett zu löschen, erhält die Funktion in der kommenden Version zusätzliche Parameter, die eine Löschung auf den Ebenen userid, dbid und queryid erlauben. Dadurch wird dann eine bessere Kontrolle der Statistiken zu den im System ablaufenden Queries möglich. Da die pg_stat_statements für mich das zentrale Werkzeug der Performance-Analyse in Postgres darstellt, sind solche (more...)

Thursday Thought: Imposter!

Ever feel like you're struggling to keep up? With anything at all?

I'm feeling a bit of that with APEX right now.

Partially because I've been at a site that is a version or so behind, but who isn't, right?

But those fine specimens on the Oracle APEX team keep producing so many nifty things, I'm finding it increasing difficult to keep up. Same with the database itself. So many great new practical development features (more...)

How to install Python 3 on Oracle Linux

You can install Python 3 on your Oracle Linux 7 environment with three simple steps: sudo yum install -y yum-utils sudo yum-config-manager --enable *EPEL sudo yum install -y python36 As a first step, in case you don’t have it yet on your system, is to install the yum-utils package. This package includes the yum-config-manager which allows you … Continue reading "How to install Python 3 on Oracle Linux"

Splitting strings before they’re CLOBs!

After I tokenized two CLOBs in response to a forum question, Andy Sayer mentioned that they were probably files first. So I smacked my forehead and used external tables.

Splitting strings when they are CLOBs

Like many of you, I have enjoyed using SQL to make data processing go hundreds of times faster. My new record is: 7000 times faster :-)

Upgrade pip Utility

You should always have the most current version of pip installed when working with Python. You can upgrade the pip utility with the following command:

sudo pip install --upgrade pip

It should print the following to the console:

Collecting pip
  Downloading (1.3MB)
    100% |████████████████████████████████| 1.3MB 971kB/s 
Installing collected packages: pip
  Found existing installation: pip 9.0.3
    Uninstalling pip-9.0.3:
      Successfully uninstalled pip-9.0.3
Successfully installed pip-18.1

New Year, New You

Well maybe not so much a new you. More like getting your personal “play” environments refreshed.

If you are anything like me, you want the latest and greatest in the setup where you play with great tech.

I have always had database, ORDS, APEX, SQLPLUS and so on installed in a VirtualBox VM. It has worked great and while I was hesitant to jump on the Docker train it is clear that not taking advantage (more...)

Using SQL Translation Framework to access package variables

My last post had code to rewrite SELECT statements that tried to access public package variables. Here I'll integrate the code into the SQL Translation Framework. Use this only in development environments.

Upgrade to macOS 10.14

It was a forced upgrade to run TurboTax. The upgrade was simple because I work on a Mac Pro 2012 (with 64 GB of memory and 12 TB of storage. As you can tell from Apple’s support article, you must upgrade the video card.

I bought the SAPPHIRE Radeon PULSE RX 580 8 GB GDDR5 but when I went to install it after upgrading to macOS 10.13 (High Sierra), there was a catch. (more...)


Onine DDL operations are much more polite than offline DDL operations. They usually wait patiently for transactions that hold resources they need until these transactions end, and they do not block new DML statements.
As I wrote in the past, adding a constraint as Enabled and Validated (which is the default for new constrtaints) is an offline operation, but if we split it into two DDL statements – one for adding the constraint as (more...)

Make public package variables available to SQL

I recently posted an experimental approach to accessing package constants from SQL. Here is a more robust approach that includes all variables.

Make public package constants available to SQL

This is an enhancement request on ODC and a recent Twitter topic. Here is a homemade solution using the SQL Translation Framework.

SQL Templates and multiple REPLACE

I frequently use what I call "SQL templates", where I replace multiple placeholders by calculated values. Here are two methods that don't require a stored procedure.