Free Oracle Learning Tools

This post is one of a series on what I learned while not at Kscope18.

Would you like to learn something from the Oracle technology stack?
Here's a slide probably in a bunch of Oracle employee decks.

I think this collection represents the commitment Oracle is making to the developer community, in part thanks to (more...)

Extract from CLOB with JSON objects

On AskTOM, Kim Berg Hansen recently used JSON_OBJECT to parse CLOB data in "name=value" format. I added a variant based on my work with CSV data.

Community Recognition at Kscope18

This post is one of a series on what I learned while not at Kscope18.

Some well deserved people have been recognised by the Oracle ACE program. One in particular I noticed was Daniel, creator of many practical APEX plugins.

Other mentions include, but not limited to, Adrian Png, Maxime Tremblay, Kai Donato, (more...)

The faces of Oracle APEX

This post is one of a series on what I learned while not at Kscope18.

The faces of Oracle APEX.
Well, some of them, at least. There are more listed in this picture, but it's harder to see. The one of Shakeeb comes across (more...)

Oracle XE 18c and #OracleRAD

This post is one of a series on what I learned while not at Kscope18.

Oracle XE (Express Edition) 18c - which is more than just a free, limited db.
Combined with the notion of #OracleRAD, you've got a mongo killer ;p


Quite a few people had a shot of the XE 18c feature list.

ANSI dates make life easier

This post is one of a series on what I learned while not at Kscope18.

Dimitri mentioned that he learned about the ANSI date format that allows you to return a date with the expression.

Which means this
date '2018-06-10'

Is the same as

And you'll never want to type (more...)

Things I learned while not at Kscope18

Yep, as much as I wanted to be at Kscope18 this year, my abstracts were not accepted.
And it's a long way.
And I'm still waiting for this.

So this is a small collection of stuff I learnt only from Twitter - just by keeping an eye on the #kscope18 hashtag. Anyone can do his, even if you're not on twitter. Try that link and see.


Seriously, Twitter is an effective tool in keeping (more...)

Extract CSV from CLOB with JSON arrays

Marc Bleron blogged about CSV CLOBs and JSON_TABLE two years ago. Here's my contribution to improve on a great idea.

Read CLOBs fast with less memory

Reading a big CLOB is like trying to eat a burger all at once: it will take you forever if you don't choke. Why not cut that CLOB into bite-size chunks? It's faster, uses less memory - and it's good table manners...

dbms_random zur Generierung (ziemlich) eindeutiger Werte

Jonathan Lewis weist in seinem jüngsten Artikel darauf hin, dass man die Länge von Strings, die man per dbms_random.string('U', n) generiert, mit Bedacht wählen sollte: bereits ein relativ niedriger Wert für liefert eine sehr große Zahl unterschiedlicher Permutationen: für 6 Zeichen sind es bereits über 300 Millionen Kombinationen, so dass sich daraus für den im Artikel (und im zugrunde liegenden OTN-Fall) für eine 100M rows Tabelle ein Wert mit sehr seltenen Wiederholungen ergeben würde. (more...)

Generate JOINs among related tables

After finding the shortest "foreign key" path between two tables, I'll try to generate a SELECT statement that joins all the related tables.

Creating an Oracle Database Vagrant box

Oracle recently launched a new GitHub repository for providing people with Oracle software inside Vagrant boxes. If you have ever setup an Oracle database inside a VirtualBox VM, whether it is as your sandbox environment, to explore Oracle database, or to use it as a full-on development environment, things have just gotten a lot easier … Continue reading "Creating an Oracle Database Vagrant box"

Split a string again and again

I recently needed to gather multiple columns from multiple rows into one string, then split them out again. JSON_TABLE did both splits at the same time!

Neue Oracle VM Appliance

Nur damit ich es irgendwo verlinkt habe: Jeff Smith weist darauf hin, dass im OTN eine neue VM mit Oracle 12.2, SQL Developer 18.1, Oracle REST Data Services 18.1 etc. zur Verfügung steht.

Foreign keys between two tables

In a comment on my post about getting all the foreign key relationships, I was asked how to get the shortest path between two tables. Not too easy...

Splitting Strings: a New Champion!

My last post on splitting strings conceded that "a combined SQL + PL/SQL solution beats the best pure SQL solution." Wrong! I didn't try JSON_TABLE.

Wrong Results with IOT, Added Column and Secondary Index

I found a “wrong results” bug yesterday, easily reproduced in 11g, 12c and 18c.

In short, we may get wrong results under the following circumstances:

  • We have an Index-Organized Table (IOT) with multi-column primary key, populated with rows
  • The table has a secondary index on part of the primary key columns
  • We add another column to the existing IOT
  • We select from the IOT while accessing it via the secondary index

Following is a simple (more...)

The Single Responsibility principle

The Single Responsibility principle is the foundation of modular programming, and is probably the most important principle in the SOLID set. Many of the other principles flow from it.

It is quite simple: a program unit should do only one thing. A procedure should implement a single task; a package should gather together procedures which solve a set of related tasks. Consider the Oracle library package UTL_FILE. Its responsibility is quite clear: it is for (more...)

PIVOT Function with Totals

I have updated the ADVANCED_PIVOT function to support row and/or column totals. It's neat, but you need to know what you're doing!

Improved PIVOT Function

My generic PIVOT function only allowed one column in the FOR clause. The modestly named "Advanced" function has no such limit.