• Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    ODC Appreciation Day: Cursor Variables

    You know everything is Tim Hall s 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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    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...
  • Kim Berg Hansen

    Analytic picking route

    In my previous post I showed how to use analytic rolling sums to do FIFO picking of items. But the final picking list of the solution had a weakness - the forklift driver picking the items would have to drive back and start from the beginning of each...
  • Kim Berg Hansen

    DATE and DST

    Recently on the ODTUG e-mail list ODTUG-SQLPLUS-L a user highcharge asked how to take daylight savings time into consideration when calculating number of hours between two DATE values. If you merely subtract the dates, twice a year you will be one hour...
  • Kim Berg Hansen

    Analytic sales forecast

    Analytic functions have been very helpful at my work. One of the good examples is trying to forecast next years sales for each item taking into consideration seasonal variations and whether the item has been going up in sales the last years or declining...
  • Kim Berg Hansen

    Top selling items

    A classic task given to a programmer is to make a TOP-N report of some data. Often a TOP within each group of some defined grouping (department, country, product type, etc.) And many times the report should also include the percentage of the total - even...
  • Kim Berg Hansen

    Conway's Game of Life in a MODEL clause

    This post has no serious purpose. I was just fooling around with the MODEL clause when I got the idea that ITERATE could be used for modelling Conway's Game of Life. So that's what I did - just a little fun example of what MODEL can be used for...
  • Kim Berg Hansen

    INSERT ALL master/detail data from XML

    This is something I actually made for a quiz on PL/SQL Challenge , but I think the technique could be useful for others as well :-) The idea is you may have some master/detail data (in this case orders and orderlines) for which you get XML with such data...
  • Kim Berg Hansen

    Fiddling with MONTHS_BETWEEN

    A few days ago I answered a question on OTN SQL and PL/SQL forum that prompted me to fiddle around with MONTHS_BETWEEN. I did discover something new that I wasn't aware of while developing an alternative MONTHS_BETWEEN implementation. The forum poster...
  • Kim Berg Hansen

    Group by Groups

    For some time now I have struggled to efficiently "group by groups" on data containing references between our suppliers item numbers and the original equipment manufacturer (OEM) number. I can group those data by supplier and their item number...
  • Kim Berg Hansen

    Find your way with HttpUriType and Google Maps

    Recently I read Duke Ganote writing about using UTL_HTTP to get stock quote from Yahoo. (Duke must have a thing for authorities, particularly Marshalls of Legoredo ;-) Anyway, I posted a comment how to do a similar thing with HttpUriType. And that reminded...
  • Kim Berg Hansen

    RANGE BETWEEN and leap years

    Answering a question on the OTN forum was a bit tricky to get an analytic sum using a RANGE BETWEEN that would handle leap years, but in the end I came up with a workaround that satisfies the requirement. Along the way I realized why there are two different...
  • Kim Berg Hansen

    A bit of fun expressing ratios

    Sometimes answering questions on the OTN forum leads to a little fun trying to be creative in SQL ;-) A user wished to express a ratio as 1:1 or 1:2. That lead to a little fun with CONNECT BY on DUAL for recursion. This is the SQL I ended up creating...
Page 1 of 2 (39 items) 12