Collation in 12cR2 – when AA equals Å (or not?)

Collation in Oracle 12cR2 gives some more finegrained possibilities for setting up how your data is to sorted and compared linguistically. A lot we could do before with NLS_SORT and NLS_COMP and the likes, but the collation features can both be simpler to use as well as offer more detailed control.

But do some testing for your specific language cases - you might find some small surprises like I did when trying it out with (more...)

Carbonated Java & JavaScript Stored Procedures

Carbonated Java Stored Procedures

For accessing JSON Collections and documents without any knowledge of SQL, Oracle furnishes the SODA for Java API. It allows a convenient access and navigation using the dot notation.

How to use SODA for Java in Java Stored Procedures? I have posted the steps, the code samples and scripts on GitHub.

Carbonated JavaScript Stored Procedures 

Nashorn allows interoperability between Java and javaScript. By leveraging such interoperability, I've bee able to reuse (more...)

Semantik des parallel(n) Hints

Jonathan Lewis weist in seinem Blog darauf hin, dass der Hint /*+ parallel(n) */ keineswegs einen parallen Plan erzwingt, auch wenn eine ausreichende Anzahl an parallelen Ausführungsprozessen zur Verfügung steht. Tatsächlich weist der Hint den Optimizer nur dazu an, die Kosten der parallelen Ausführung für jeden überprüften Plan zu berücksichtigen - aber letztlich wählt der Optimizer den Plan mit den niedrigsten Kosten (sofern kein Bug im Spiel ist) und das kann durchaus ein serieller Plan (more...)

Spam killed

After I moved my blog to self-hosting, it has attracted more and more spam. It started slow and very manageable. After my last blog post it got really bad. What started as a few spam messages a week, culminated with 30-40 spam comments per day. At that point I had completely lost any ability to sort through it.

‘I was so disheartened by this that blogging didn’t happen as I knew I had to solve (more...)

Oracle EBS User Experience Makeover Contest

Young plant growing in the morning light and green bokeh background  , new life growth ecology concept

As is our tradition, when Spring begins and rebirth is in the air, we like to offer our customers a renewal of their own. This year, we thought it was time to give some love to our Oracle E-business suite (EBS) customers, as we’ve had some incredible success this year modernizing EBS applications. You can read about our digital transformations in Forbes Magazine here.  

We want your pain points!

We are putting it out there to (more...)

Online Partitionierung einer existierenden Tabelle in 12.2

Eine sehr schöne Ergänzung der Partitionierungs-Optionen in 12.2 beschreibt Maria Colgan in ihrem Blog: die Möglichkeit, eine nicht partitionierte Tabelle ohne downtime - also online - in eine partitionierte Tabelle umzuwandeln. Die Syntax dazu sieht etwa folgendermaßen aus:

alter table t
partition by ...
partition p1 ...,
partition p2,
update indexes online

Das sieht für mich sehr intuitiv und vor allem kompakt aus. Dabei dient "update Indexes" wie üblich dazu, die Indizes während (more...)


The RETURNING INTO clause is one of my favorite PL/SQL features. It allows to write less code, improves readability and reduces context switches between PL/SQL and SQL.
In this post I’d like to highlight some less-known characteristics of the RETURNING INTO clause and emphasize differences that exist when it is used in different DML statements.

Supported Statements

The RETURNING INTO clause is supported by the UPDATE, DELETE, and single-table single-row (“values-based”) INSERT statements.
It is (more...)

Comparing Expression Lists (not tuples)

This post is a long-winded answer to a question Bryn Llewellyn asked me on Twitter about comparing expression lists.

Snowflake and Spark, Part 2: Pushing Spark Query Processing to Snowflake

This post provides the details of Snowflake’s ability to push query processing down from Spark into Snowflake.

Developer Cloud Service May Update

In the May Update, Developer Cloud Service adds over 30 new features including; a new top-level feature to track Releases, tools to provision Oracle Cloud services in Builds, new webhooks for external build systems, enhancements to Agile tools, and language aware Code Search.


Software Releases


Organize Developer Cloud Service project artifacts into software releases through the new top level Releases feature. Releases can associate git repositories, branches, and tags, Maven artifacts, build artifacts, (more...)

Installing PIP for Python

If you’re on a Mac running macOS Sierra, you can install PIP to add packages. PIP stands for either of the following:

  • PIP installs Packages
  • PIP installs Python

You use the following to install the PIP utility:

sudo easy_install pip

It should return the following:

Searching for pip
Best match: pip 9.0.1
Processing pip-9.0.1.tar.gz
Writing /tmp/easy_install-ryxjDg/pip-9.0. (more...)

Cloud Analytics Conference – London!

Join Snowflake and The Data Warrior in London on June 1st for a Cloud Analytics Conference

Snowflake and Spark, Part 1: Why Spark? 

Snowflake Computing is making great strides in the evolution of our Elastic DWaaS in the cloud. Here is a recent update from engineering and product management on our integration with Spark: This is the first post in an ongoing series describing Snowflake’s integration with Spark. In this post, we introduce the Snowflake Connector for Spark (package […]

Adding a Column with a Default Value and a Constraint

The Constraint Optimization series:

In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a (more...)

postgres Statistiken: pg_stat_all_tables

Alexey Lesovsky hat bereits eine ganze Reihe interessanter Artikel im Rahmen einer Serie "Deep dive into postgres stats" veröffentlicht. Bisher hatte ich mich davor gedrückt, diese Ausführungen zusammenzufassen, aber heute ist ein ruhiger Tag und die pg_stat_all_tables scheint mir eine besonders interessante Quelle zu sein...

Die View pg_stat_all_tables enthält eine ganze Reihe interessanter Informationen zur Nutzung von Tabellen. Unter anderem weist sie hin auf:
  • Zugriffsinformationen: die View enthalt Informationen zur Anzahl sequentieller Scans (also Full (more...)

Meet me in St. Louie, Louie.

Join me on May 2nd in St. Louis for the SilverLinings event where I will give three talks!

COLLECT DISTINCT in PL/SQL Works in Oracle 12.2

About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there:

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

And I showed an example that was executed in and in (more...)

postgres Extensions

Zwei interessante Hinweise findet man im neusten postgres-Artikel von Daniel Westermann:
  • das data dicitionary von postgres liefert zahlreiche Informationen zu den verfügbaren und den installierten Extensions:
  •  pg_available_extensions: zeigt die verfügbaren und die installierten Extensions inklusive eines Kommentars zu ihrer Funktion.
  • pg_available_extension_versions: liefert weitere Detailinformationen zu den Extensions, unter anderem zu den Abhängigkeiten, die zwischen den Erweiterungen bestehen.
  • pg_extension: liefert Informationen zu den installierten Extensions. Dabei weichen die Informationen von denen ab, die der psql-Shortcut (more...)

(Lack of) Optimization of Unique Constraint Creation

The Constraint Optimization series:

In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new (nullable with no default value) column (more...)

ORDS Standalone and URI Rewrites

My last post How to add an NCSA style Access Log to ORDS Standalone explained what the ORDS standalone is and that is based on Eclipse Jetty.  Jetty offers far more than ORDS exposed in it's standalone.  There's a long list of all the features and configuration options listed in the documentation, A recent question came up for doing