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.



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.

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.


Monday, May 29, 2017

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

Wednesday, April 5, 2017

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.

Monday, January 23, 2017

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.