Thursday, December 13, 2012

Formspider comes to Denmark

For the Danish Oracle User Group (DOUG) I'll be hosting a Formspider event in Copenhagen January 21st 2013. I look forward to seeing Yalim Gerger demonstrate this alternative to APEX or ADF or Forms.

The event will take place in my company's classroom at Banestrøget 17, 2. th., 2630 Tåstrup, which is 2 minutes walk from Høje Tåstrup train station.

Program for the afternoon:
  • 13:30 - 14:30 Introducing Formspider, the Web 2.0 framework for PL/SQL developers
Yalim Gerger, Founder&CEO of Formspider talks about the vision and the benefits of the Formspider Framework.
  • 14:40 - 15:10 Formspider Features & Applications
Demonstration of several production applications built with Formspider and a closer look to the features used in these applications.
  • 15:15 - 16:15 Case Study: Modernizing a Forms-based ERP with Formspider
Michiel Arentsen, Manager of Application Development of Rhenus Netherlands, shares his experiences with Formspider.
  • 16:20 - 17:00 Hand on with Formspider
Let's build an application. Experience first hand why building an application with Formspider is so much better than anything else.

Formspider kindly handles the registration of attendance for us, so for further information and registration please go to this link:

Hope to see you there if you are interested in PL/SQL programming :-)

Merry Christmas

Wednesday, December 5, 2012

Thank you, UKOUG 2012

So, I'm about to leave UKOUG 2012. I had a good time and learned quite a bit from the smart people gathered in Birmingham ;-)

Thank you to those attending my presentation on analytic functions - I hope you learned something from it. If you need to take a closer look, both presentation and scripts can be found here.

Birmingham Airport next stop...

Friday, November 23, 2012

Analytic FIFO multiplied - part 3

This is part 3 of a three part posting on analytic FIFO picking of multiple orders. Part 3 shows how to combine the FIFO developed in part 1 with the analytics used for the better route calculation in an earlier blog post.

Analytic FIFO multiplied - part 2

This is part 2 of a three part posting on analytic FIFO picking of multiple orders. Part 2 shows an alternative way of doing the same thing as part 1 did - but this time using recursive subquery factoring in Oracle v. 11.2.

Analytic FIFO multiplied - part 1

I have blogged before about Analytic FIFO picking as well as talked about it at KScope12 and will do again at UKOUG2012.

A few days ago Monty Latiolais, the president of ODTUG, had a need to do this - not just for one order which he already had developed the technique for, but for multiple orders, where the FIFO picking for the second order should not consider the inventory that was already allocated to the first order, and so on.

So here is a three-part demo of how to do this.

Wednesday, November 21, 2012

Ready for UKOUG2012

I think I am about ready for UKOUG conference 2012. Hope I haven't forgotten something :-)

  • Train ticket to airport - check
  • Plane ticket to get to Birmingham - check
  • Hotel reservation - check
  • UKOUG2012 registration - check
  • Planned my agenda - check
  • Chairing a session - check
  • Uploaded presentation for my session - check
  • Discovered where to get Oracle beer near ICC - check
Yup - checklist done :-)

If you're interested, come to my session Wednesday Dec. 5th at 12:10 and see if I can speak fast enough to go though 130 slides in an hour showing these cases of using analytic functions:
  • Case 1: Top selling items 
  • Case 2: Picking by FIFO 
  • Case 3: Efficient picking route 
  • Case 4: Picking efficiency 
  • Case 5: Forecasting sales 
  • Case 6: Forecast zero firework stock

See you in Birmingham :-D

Thursday, November 15, 2012

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.

Thursday, October 18, 2012

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 INTERVAL datatypes :-)

Wednesday, October 17, 2012

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 me that long time ago I reminded myself that I should blog about how we use HttpUriType to query driving distance and time from Google Maps. (I have even tried to submit abstract to KScope and UKOUG on getting data with HttpUriType, UTL_HTTP or UTL_FTP, but no go so far...)

Monday, October 8, 2012

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 and get a "group of OEM numbers." That group I will call a unique item and I will group the data once more by that group to find which suppliers agree on an item having the same set (group) of OEM numbers.

It took me some time to figure out how to do this efficiently (the data was several million records and the "unique groups" to be found was about 3/4 million groups.) But after several tries, some good help from the OTN forums and some reading in my Tom Kyte books, I finally got a solution that seems to work well ;-)

Thursday, October 4, 2012

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 had a requirement where employee allowance was to be calculated based on a from and to date. A fixed monthly allowance was given which then could be multiplied to get the total allowance, but if 20 days had been worked in April allowance was to be 20/30 of the monthly figure, while 20 days in May would give 20/31 of the monthly figure.

Monday, July 30, 2012

Going to UKOUG 2012

My presentation on analytic functions has been accepted for UK Oracle User Group Conference 2012 ( #UKOUG2012 ) - awesome \o/

Now I just need to tighten up my show and remove all the beginner bits - I've only got 45 minutes for something I could easily use 1½ hour for ;-)

See you all in Birmingham in December...

Friday, July 20, 2012

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 that needs to be inserted into two relational tables. Many might be tempted to parse the XML client side or in PL/SQL, loop through the data, and then insert the orders and lines row by row (or perhaps bulk insert from arrays.)

But it can be done in a single statement if you combine XMLTABLE function to read the XML relationally and INSERT ALL statement to do a multi-table insert.

Wednesday, July 18, 2012

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. Sure it could be done in any number of other ways, I don't claim this to be a smart or efficient way, just fun ;-)

Tuesday, July 3, 2012

A fabulous KScope12 is over

KScope12 is over. What a fabulous conference that was!

This was the first time I did a presentation. I am not quite satisfied with my performance - I spent too much time on the basic examples in the beginning of my presentation, so I only had time to do 4 out of my planned 6 demos. I know I can do better than that, so I just need to try it again some time :-)

On the plus side everyone can read my paper where I go through all the 6 demos in detail. That paper made it to the top 5 in the Editors Choice Award (thank you, editors :-) so if you didn't get it all at my presentation, you can get it all in the paper.

And thank you to the fellow who the day after told me: "After your presentation I have seen the light. Now I understand that analytic functions are really not as difficult as I thought they were." That cheered me up very much! And that is in my opinion the whole point about KScope - sharing experiences so other people can learn from you as you learn from other people. Ain't no better place on earth to do that than KScope :-)

Apart from my presentation I had a blast. Content wise I always learn a lot at KScope. And KScope also understands that you learn a lot better if you also have fun - that rodeo was a brilliant idea for the wednesday night event. The people gathering at KScope also make it awesome - both organizers and attendees - so a big thank you to everybody who was there.

I can only recommend that if you can, then come to KScope13 in New Orleans. You will learn a lot, you will have fun, and if you engage yourself and do stuff like presenting or being ambassador or whatever, you can end up with a name tag almost long enough to trip over :-)

Sunday, June 17, 2012

KScope12... Ready... Set.. Go... \o/

ODTUG Kscope12 is just around the corner, yay!

  • Plane ticket - check.
  • ESTA - check.
  • Passport - check.
  • Hotel - check.
  • Schedule - check.
  • Ambassador signup - check.
  • Community Service Day signup - check.
  • Presentation prepared - check.
  • Paper written - check.
  • Demoscripts prepared - check.

Oh my... I think I am ready :-)

I will be seeing you all in Texas, right? If not, then you must start planning to go to Kscope13 ;-)

If you are interested in analytic functions you might consider attending Session 10 Tuesday June 26th 3:45PM to 4:45PM in Grand Oaks B. I'll be there - for the first time on the hot side of the podium...


Monday, April 9, 2012

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 though you only display the TOP records.

Many ways can be devised for this and many ways has been used and blogged about and given lectures on. This is nothing new, but it is a technique we often use in many practical cases. One typical case is a top list of best selling items within product groups.

Tuesday, February 14, 2012

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. In this blog post I demonstrate how to use REGR_SLOPE for that purpose.

Friday, February 10, 2012


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

Ideally you might switch to datatype TIMESTAMP WITH TIMEZONE, but sometimes that is not an option (or sometimes perhaps "overkill" for the situation :-) Fortunately there is another way.