CTEs ohne Materialisierung in Postgres 12

Jonathan S. Katz weist in einem Artikel auf eine wichtige Verbesserung hin, die mit Postgres 12 verfügbar werden soll: CTEs werden dann nicht mehr automatisch materialisiert. In der commit message findet man dazu folgende Beschreibung:
By default, we will inline [CTEs] into the outer query (removing the optimization fence) if they are called just once. If they are called more than once, we will keep the old behavior by default, but the user can override (more...)

Variable Scope

Microsoft SQL Server provides T-SQL with the ability to assign values from the queries to local variables. While T-SQL returns a SELECT-list, there are rules for how you can assign the SELECT-list values. The process is more or less an all or nothing approach when assigning values to a local variable. The rule is quite simple for scalar variables because the SELECT-list may contain multiple values but assignments must be made one (more...)

Erweiterte Analytische Funktionen in Postgres 11

Markus Winand zeigt, welche Verbesserungen Postgres 11 im Bereich der analytischen Funktionen bringt: insbesondere werden jetzt "Frame Units" in der OVER clause unterstützt - also Einschränkungen wie:
row between unbound preceeding and current row.
Wobei neben "row" auch "range" und "groups" als Einheit erscheinen können. Insbesondere "groups" ist dabei eine interessant Ergänzung, die nicht die Anzahl der Datensätze, sondern die der distinkten Werte berücksichtigt. Eine weitere wichtige Neuerung, die bisher nur Postgres anbietet, ist die (more...)

APEX Plugin: Execute PL/SQL code and return content to page (updated)

Oracle APEX has a built-in Dynamic Action called "Execute PL/SQL Code" that executes a block of PL/SQL code on the server via an Ajax call (ie does not do a regular submit and reload of the whole page).

But what if you want to do something on the server AND also return some content back to the client? Back in 2012 I released an APEX Dynamic Action plugin called "Execute PL/SQL Code and Return Content" (more...)

Oracle USER vs sys_context

This post was inspired by the fact I couldn't find many good references for what I thought was 'previously discovered truth'. So what does any good science-nut do? Add their own contribution.

So here are two simple performance suggestions. The second was an added bonus I realised I could demonstrate simply.

1) Stop using USER


I'm using USER far less frequently anyway, since it has no context in Oracle APEX, but it is still a (more...)

csv2db – the little CSV command line loader you always wanted

It was over the last holiday season that I wanted to take some time and play around a bit with data. The outcome was rather different than I initially expected. Rather than having worked with the data, I spent a lot of time trying to get some CSV data loaded into a database. I knew … Continue reading "csv2db – the little CSV command line loader you always wanted"

Customising APEX Session Expiry

It's nearly 8am, you're holding your favourite morning beverage, and you open yesterdays APEX tab, only to find this:


This is the current default expiry page. I'd like to tart it up.

I've used the following technique for so long, I've forgotten what it used to look like to drive me to this solution. Ultimately, you end up with a similar result, but you really can customise it to behave however you like.

Under Shared (more...)

Formatierungsoption hint_report für dbms_xplan

Eine schöne Ergänzung für die dbms_xplan.display%-Funktionen in Oracle 19 hat Nigel Bayliss im Oracle Optimizer Blog angesprochen: die Formatierungsoption hint_report. Diese liefert, was bisher nur über Trace Events wie 10053 zu erkennen war: eine Information dazu, welche explizit gesetzten Hints bei der Generierung eines Ausführungsplans tatsächlich berücksichtigt wurden. Die Option liefert einen Abschnitt "Hint Report" unter dem Ausführungsplan (und unterhalb der "Predicate Information") und in diesem Report erscheinen vor den Hints auf einzelne Buchstaben (more...)

Authentication – Switch in Session

It's only taken a year, but I've finally checked out the ability to switch authentication schemes at runtime with 18.x. It's mentioned in this 18.1 new features slide deck, and the new features list in the documentation.

Such a frequent request in the forums is to either share authentication between sessions, or dynamically change the authentication scheme - which hasn't been possible until APEX 18.1.

I've set up a sample (more...)

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.

(more...)

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 https://github.com/ORGANIZATION/REPO/releases/latest, e.g. https://github.com/gvenzl/csv2db/releases/latest. 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.