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 (more...)

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 (more...)

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 (more...)

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 (more...)

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 (more...)

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 (more...)

Sunday speaking at Oracle OpenWorld 2016

You all know Oracle OpenWorld, right? It's a behemoth of a conference filling up the streets and hotels of San Francisco with many thousands of people moving like ants between conference rooms, checking out exhibitors, hanging around lounging areas, networking, talking, listening, eating, drinking, learning and having fun. And it happens every year...



Each year it seems to get tougher and tougher to become accepted as a speaker at OOW - Oracle needs to reserve (more...)

Oracle Documentation permalink creation

Tim Hall of ORACLE-BASE spoke for a lot of people when he (yet again) wrote about Oracle Documentation links changing all the time. There exists a method for a more permanent link to Oracle Documentation, but it can be a hazzle to create such a permalink involving manually searching HTML code of the doc source (see my comment to Tim's blog post.)


Then I thought that this manual creation of permalink probably could be (more...)

OUGN Spring Conference 2016

I'm back after one of the best ocean voyages ever - the OUGN Spring Conference 2016. That conference is arranged by Oracle User Group Norway and is two days from Thursday before lunch-time to Saturday before lunch-time. The special thing is, that it all happens on board a cruise ship that uses 20 hours to sail from Oslo, Norway to Kiel, Germany - stays in Kiel 4 hours - then 20 hours to sail back. (more...)

The upcoming year

Right smack in the middle of the busiest time of the year for the business (this time of year we sell fireworks for New Year) I have opportunity to reflect a bit on the upcoming year and what 2016 will bring of challenges and joys.

Of course things will happen in the Oracle world that'll indirectly influence my year, as always. Not always possible to predict here near the start of the year, but most (more...)

Vessel Fuel Consumption – or Restarting Running Sum

I got a question recently from Morten Braten whether my FIFO analytic techniques could be helpful for calculating the value of fuel consumption for cargo vessels. It turned out not to be quite the same, but similar. Analytic functions definitely could help.

I'll show how in two parts. First this blog post will calculate the fuel consumption in quantity, then part two will calculate the value by FIFO.

So let's setup a slightly simplified version (more...)

Bloggers Meetup at #OOW15

The last time I went to Oracle OpenWorld, I hadn't started blogging yet. Now I think it fair to call myself "blogger", even though I don't blog very regularly.

So I will try to attend the Bloggers Meetup:

http://www.pythian.com/blog/oracle-openworld-2015-bloggers-meetup/
https://community.oracle.com/events/1099

I have heard a lot of good about it, so I am looking forward to see for myself ;-) Viewing who has RSVP'ed at either link, I know I spot some (more...)

Oracle OpenWorld 2015

In 2 days time I fly off to San Francisco for Oracle OpenWorld 2015. Last time I went to OpenWorld was 2008, so I'm looking forward to a re-visit and see how much bigger it has become :-)

Like everybody else I've been making a schedule for the week. Of course things will happen to change the schedule along the way, but some things I definitely have on the must-do list...

Thank You for the Votes

The Oracle Database Developer Choice Awards voting is now closed.

I thank all of you who voted for me. It was an honor and privilege just to be nominated, and then even getting votes from somebody, well that feels good :-)

My final ranking is middling, but the competitors were all worthy and good people, so I have no complaints :-)

CATEGORY  NAME              FINAL_RANK POSITIVE_VOTES
--------- ----------------- ---------- --------------
apex Morten Braten 1 186
apex (more...)

Oracle Database Developer Choice Awards Voting Status

The Oracle Database Developer Choice Awards voting will go on until October 15th 2015. If you haven't already, you should go vote now!

Here is the present status of how many votes have been submitted per category, including how many votes the category has increased with since "approximately yesterday" (since last time I downloaded the data, which I plan to do approximately daily ;-), and including how many votes the category has increased with since (more...)

Scraping and querying Oracle Database Developer Choice Awards votes – Part 2

In Part 1 I demonstrated how to "scrape" data from the live webpages with the votes of the Oracle Database Developer Choice Awards. Unfortunately those webpages are not ideal for scraping, so I promised a workaround. Here I do a semi-manual scraping af data by using a browser to retrieve the actual HTML, save it to a file, and then let the code parse out the data from the file.



In the live version, the (more...)

Scraping and querying Oracle Database Developer Choice Awards votes – Part 1

You know about the Oracle Database Developer Choice Awards voting, right? If not, then go vote now, please, and then come back and read on :-)

I want to be able to query the voting status, who is ranked what in each category and so on. But I don't want to type in votes in a table, especially not when they change all the time. Instead I'd like to query the live data in (more...)

Oracle Database Developer Choice Award Nominee = Me



Oracle is celebrating the Oracle Community by having us developers nominate and vote for
Oracle Database Developer Choice Awards to our fellow developers in five database development technologies:
SQL, PL/SQL, ORDS, APEX, Db-Design

The nomination period is over and it has been possible to vote since September 15th. Voting is open until October 15th and winners will be announced during Oracle OpenWorld 2015.

The nominees are all active in the Oracle Community helping others as (more...)

Row pattern matching nested within hierarchy

I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12.

Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches.

But can there be uses where we want (more...)

ODCI dynamic table function parser

Supposing you've got data as a text string with "row" and "column" delimiters and you would like to parse out those columns and rows. The external table syntax would actually be great for this, but that requires writing the text out to a file first, and then you can import it back again as columns and rows.

There are various other alternatives using SQL and/or XML manipulations, but one method that I don't see much (more...)