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 danish.

Here's how I tried danish column level collation...

Results of Oracle Dev Gym SQL Annual Championship for 2016

March 29th 2017 a group of 35 database developers competed in the Oracle Dev Gym SQL Annual Championship for the top ranked players of 2016. They worked their little grey cells hard for 45 minutes over 5 quizzes that I had tried to make extra hard for them.

The results have now been made public on the Oracle Dev Gym site, so I'd like here to add my congratulations to everyone who made it to the championship and especially to those who tops the results list.

String to DATE conversion and validation in 12.2

A new little feature in Oracle Database 12.2 is, that you can convert strings to dates without worrying about exception handling. (That goes also for converting to numbers or timestamps or other datatypes, but here I'll concentrate on dates.)

It's just one of a ton of new 12.2 features ranging from minor features that make your daily life slightly easier to major inventive features. I've been part of a Trivadis Team that deep-dived into all of the new features to create the Trivadis TechnoCircle on 12.2 new features and it's been very interesting.

So this is just one new feature, but nice enough for a developer to merit a little blogging about it.

Temporal validity and open/closed intervals

A short while ago I created a SQL quiz for Oracle Dev Gym (PL/SQL Challenge) demonstrating the use of temporal validity and VERSIONS PERIOD FOR syntax to create a "change report" for changes in item prices. Use of temporal validity makes this easier, shorter and more readable code, but even though I thought I had it all covered, player Iudith Mentzel pointed out a tiny quirk I'd overlooked.

You see, the "change in price" wasn't really happening at one specific point in time - the old price was valid up to (but not including) a point in time (open-ended interval) and the new price became valid from (and including) that point in time (close-ended interval). If my "change report" happened to match those "ends" of the validity intervals exactly, it would never catch the "change" happening.

Announcing the SQL Annual Championship for 2016

I've been making SQL quizzes for years now over at the Oracle Dev Gym (or PL/SQL Challenge as it started out as.) Every year I really have to work my few braincells hard when it is time for the annual championships. The players that have made it through at year and managed to rank in the top are awesome at SQL, so I have to write some really hard quizzes for the championship.

But it is fun for me as well as hopefully fun for the dedicated and hard-thinking players of the championship. And it is a pleasure for me to announce the participants of the this coming championship of those who have reached the top ranks of 2016:

A handful of articles

A little while ago a member of the Danish Oracle User Group DOUG asked if anyone had a bit of experience with use of R together with Oracle Database. I remembered an article I wrote for OTech Magazine on forecasting with R and Oracle and thought it would be a good primer for him. OTech Magazine is no longer active, sadly, but I thought I could send him a link anyway to my article. That was not to be :-(

OTN Appreciation Day : GeoJSON and SDO_GEOMETRY marriage in Oracle 12.2

The native JSON functionality in Oracle Database version 12.2 has evolved quite a bit since the JSON functions first appeared in Oracle Database version 12.1.0.2. Just one example is JSON_TABLE can be used as a bridge between the open standard GeoJSON format and the database internal SDO_GEOMETRY format, making it very simple to use externally available geocoding datasources within spatial applications in the database.

You have been able to do this with version 12.2, since it became available at OpenWorld 2016, at least if you have an Exadata Express account in the Oracle Cloud (if you want it on premise, a little more patience is needed ;-)