view counter


Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Gaps in Date Ranges: when are you free?

Calculating “free time” in Calendars is a very common task. Did you know it can be done by a SQL statement less than 100 bytes long? This post pays homage to an incredibly neat and concise use of analytic functions. The Question “Free time” in a Calendar means the unused time slots between events; in […]

SQL Quiz: How did my primary key get duplicate values?

Somehow I ended up with two rows in my table both with the same primary key value:
select the_pk
from dodgy_data;


The primary key does exist and THE_PK is the only column in it, so we can’t insert another row with THE_PK = 1:

How Humans Lost Their Hands (and saved the world)

My first creation (and destruction) mythstory....

SQL for date ranges, gaps and overlaps

[Update 2014-07-04] Since this is my most popular post, I’m adding an index to all my posts about date ranges: This post right here explains terms like “gap, meet, overlap”, shows how to design date ranges in tables and explains what null values should mean. SQL and date ranges: don’t make NULL mean something argues […]

Speaking at UTOUG 2014

Oracle Database 12c PL./SQL ProgrammingI’m off shortly to attend and speak at the Utah Oracle User Group’s Spring Training Days 2014, which is held at Salt Lake Community College – Larry H. Miller Campus.

Results of 2013 Q4 PL/SQL Championship

You will find below the rankings for the 2013 Q4 PL/SQL championship; the number next to the player's name is the number of times that player has participated in a championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Peter Auer of Germany

2nd Place: Mike Pargeter of United Kingdom

Instant Column Addition

alter table from_11g add
column_with_a varchar2(100) default
'is instant provided you set it' not null;

Prior to 11g the above statement could take a very long time if the table was “big”. I was reminded of this by a recent discussion on Oracle-L.

Word 2010 – Add Template

You can add a Word Template without a problem until you get to Word 2010 because they’re hidden under the Developer ribbon, which is disabled by default. It’s funny something so widely used would be placed on a ribbon that is disabled by default.

It didn’t come up in the first few Google search pages. I figured that I’d blog it because it took me more than a couple minutes.

You can enable the ribbon with the following steps:

Oracle 12c Technical Hands-On Workshop

I have been running many tech events and briefings on Oracle 12c for India Partners. This time around, I thought of posting my latest event reviews. This week, I wrapped up the Oracle 12c Technical Hands-On workshop in Oracle facility, Gurgaon. It was a 2-day event from Oracle Database Product Management.

Find Type Dependents

Somebody thought it was nice to show how to drop object type dependents in Oracle Database 11g, but they thought I should show how you discover dependent object types first. More or less, they were concerned how they discover type dependents when they raise the following error:

view counter