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!
Thursday, March 22, 2018
Tuesday, February 13, 2018
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...
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...
Friday, January 12, 2018
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.
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.
Thursday, December 21, 2017
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.
Monday, October 9, 2017
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.
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.
Thursday, September 28, 2017
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.
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.
Saturday, August 19, 2017
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.
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.
Subscribe to:
Posts (Atom)