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

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:

with r as (
select .2233 ratio from dual union all
select .2500 ratio from dual union all
select .2666 ratio from dual union all
select .2750 ratio from dual union all
select .2828 ratio from dual
select r.ratio ratio_num
, (
select to_char(
, 'TM9'

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

Let's make a sales table to demo this:

create table sales (
day date
, qty number

And populate with some data for specific days in 2010, 2011 and 2012:

insert into sales values (date '2010-10-01', 1);
insert into sales values (date '2010-10-02', 2);
insert (more...)

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

Let's imagine Larry needs directions to (more...)

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