OracleBlog

"What's wrong?" the guru asked as he sat down next to the company's newest database analyst. "I heard you were having some trouble with the room booking application."

"Yep," said Chad. "I just can't seem to get it right."

"Don't put it all on yourself," said the guru. "What can I do to help?"

"Well, I've got it narrowed down to this one procedure, which is responsible for checking if the room is available or not. It takes a start time, and end time, and the room number. For some reason it sometimes says the room is available when it isn't."

The wise guru studied the code, scratched his beard, and finally provided his much anticipated wisdom. "Have you ever heard of OVERLAPS?"

The well-trained database analyst dutifully opened up the Oracle documentation. He knew the Oracle guru strongly believed in the completeness and accuracy of the Oracle documentation, and it would certainly win him some points to be seen going directly to the guru's favourite source of information.

Unfortunately Chad was a little embarrassed when he couldn't find OVERLAPS anywhere. Finally the guru broke the tension with a hearty laugh. "You won't find it in the Oracle documentation, but you were right to look there first!" Chad smiled sheepishly. "For some reason Oracle has chosen not to advertise the existence of this command, but here's how it works."

The guru slid the keyboard over, cracked his knuckles, and typed out this query.

SQL> SELECT 'Yes' FROM DUAL
2 WHERE (DATE '2009-01-01', DATE '2009-01-03')
3 OVERLAPS (DATE '2009-01-02', DATE '2009-01-04');

'YE
---
Yes

"Wow, that's neat! But without documentation, how am I supposed to know how this works?"

"One of the many advantages to using Oracle is its popularity. As a result, there is an abundance of Oracle experts around the world, most of whom are quite generous with their knowledge and experience. You should be sure to include some of the more popular and trusted forums and sites to your daily reading list."

Chad looked optimistic, but still a little shaken. "Hey, don't feel so bad," said the guru as he placed his hand on the shoulder. "Open up your copy of Expert One-on-One Oracle, and check out Chapter 1. Flip to page 31."

Chad studied the code, which attacked the very same problem. Finally he looked up, obviously still confused. The guru smiled at him. "Study the query carefully. What happens when a meeting request comes in that both starts and ends while another meeting is running?"

Chad studied the code with furrowed eyebrows, but eventually his face broke out into a broad smile. "The room gets double-booked! Wow, even Tom Kyte got this wrong!"

"See? It happens to the best of us."

"Once I test this, should I rewrite my procedure to use OVERLAPS?"

"Personally, I wouldn't recommend using any undocumented feature. Furthermore, my own experience with OVERLAPS is that it doesn't consider equality as an overlap, and that may not satisfy your business requirements."

SQL> SELECT 'Yes' FROM DUAL
2 WHERE (DATE '2009-01-01', DATE '2009-01-03')
3 OVERLAPS (DATE '2009-01-03', DATE '2009-01-04');

no rows selected

"Instead, just consider OVERLAPS a handy tool to help you with your testing."

"Thanks!"

The guru turned up to leave, but just as he was about to leave sight, he turned around in a fashion reminiscent of Peter Falk's Columbo. "You know, I could use some lunch ..."


---
To the Chads out there, here are some authors with more information on OVERLAPS:
Eddie Awad
Kevin Meade
David Aldridge