Announcing the results of the Oracle Dev Gym SQL Championship for 2017

A huge round of applause to the top players in the championship:

1st Place: mentzel.iudith of Israel

2nd Place: Pavel Zeman of Czech Republic

3rd Place: Andrey Zaytsev of Russia

Especially noteworthy is the accomplishment of Iudith Mentzel, who also won the DB Design Championship and has been a very regular Dev Gym participant for years. Congratulations, Iudith!

SQL Magic Squares - or Why the Optimizer does not like Magic

A long-time player at the Oracle Dev Gym tried his hand at generating Magic Squares using SQL.

When he attempted to tune his statement a bit, he was surprised that it didn't go a little faster as expected, rather it went from 2 minutes to 45 minutes? At that point he contacted me to see if I could explain the mystery...

Announcing participants in Oracle Dev Gym SQL Championship 2017

Another year, another Oracle Dev Gym SQL Annual Championship. Woohoo!

Players have exercised their mental muscles with the SQL puzzles throughout 2017. Congratulations to everybody digging in all year, trying to figure out the pretzelbenders of me and other quiz authors. And hopefully you've learned something as well, improving your SQL skills.

And a very special congratulations to the Top 50 in the "Select for SQL" Tournament in Oracle Dev Gym in 2017. It takes an effort solving the quizzes succesfully every week to be ranked high at the end of the year.

Object type "nested" implicit grants

A colleague got an "ORA-01720: grant option does not exist" error and couldn't understand why. So together we researched a bit and learned some things about how object type grants across schemas works - including a small surprise that was the reason for our puzzlement.

ODC Appreciation Day: Cursor Variables

You know everything is Tim Halls fault, right? Including this blog post? Oh yes it is, he came up with the idea of OTN ODC Appreciation Day - a day where Oracle Bloggers all over the world show their appreciation of the Oracle Developer Community by blogging about some favorite Oracle feature.

So what should I pick this year of the multitude of possible favorites? Something brand new? No, I think I'll write about something that for almost a couple decades has been a trusted friend when I develop - and still can keep up without getting outdated:

Cursor variables - also known as REF CURSORs.

Avoiding reinventing the wheel - use MULTISET EXCEPT to get set relative complement

Today in an application I stumbled upon a PL/SQL function created to return those elements of one nested table that did not exist in another nested table. Not a huge function, but still a bit of work and some thinking that had been done some while ago to compare the elements and create the desired output nested table.

The trouble is, that this functionality already natively exists in PL/SQL (and SQL) - the developer had actually reinvented the wheel. In set theory this is called a relative complement and PL/SQL and SQL has operator MULTISET EXCEPT to perform this set operation.

So just as a warning to all that you should really be aware of the built-in functionality of any programming language, I'll show you this function and how I replaced it.

Partitioning external tables in 12.2

One of the new features in Oracle 12.2 is partitioning of external tables - a quite useful feature if you have multiple identical files of data, for example from multiple sources.

But how can you partition external files, you ask? Well, you don't partition the files, but each file can be considered a partition by the database. I'll show you an example.