The Magic Shed

In keeping with my last post, this is another ‘back to basic’s kind of thing. It’s basically a small metaphor of why we should use constraints, datatypes and other normal database design fundamentals when storing data and is aimed either at the complete newbie to database design or as a handy explanation to your Excel loving business users for more experienced developers…


There was a man who was very much in love with his fiancee. He wanted the very best for her for all things.

She loved gardening and working outside at the weekends but her tools and compost and plant pots were scattered around their garage. In order to make her life easier and happier, he built her a shed.

It was a fine shed for all her gardening things with hooks and shelves for her to put her things on. He roofed it watertight, painted the door her favourite colour and put a sign on it with her name – so that she would know that it was hers. She saw the shed and thought it was wonderful, and vowed to store all her gardening things there and be very content.

The first weekend she carefully placed her pots on one shelf, her compost bags in one corner, her tools in one rack and her gardening clothes on the hooks. She gardened and was delighted to have somewhere to put everything when she finished. She returned to the house aglow with appreciation.

The next weekend she gardened again, but it began to rain and she had to rush back to the house. She threw her things into the shed, making a promise to herself to put everything back in it’s place the next day. Unfortunately, the next day it rained too – so she stayed inside the house.

The third weekend the weather was glorious so she went to her shed to get her tools and pots and composts but she couldn’t find the ones she needed! She wasted half her day finding them and rearranging the shed. She returned to the house annoyed and frustrated.

The man saw her frustration and it pained him. He had built the finest of sheds but still it made her unhappy. He resolved himself to find a solution so that she would not feel this way again.

To this end, he hired a sorcerer. He explained his partner’s plight and the sorcerer said that the solution was simple. He would cast a number of spells on the shed so that she could never lose anything again.

He put a spell on the door so that only the lady could enter.

He put a spell on the whole shed so that only gardening things may be stored there.

He put a spell on the shelves so that only pots may go on one, only hand tools on another.

He put a spell on the hooks so that only clothes may hang there.

The fourth weekend was much like the second, with rain interrupting her work in the garden. As she rushed to the shed to throw her things in, she remembered the spells that had been cast. She spent an extra minute or so being careful to replace everything where it should be under the watertight roof, every time that she went to put something in the wrong place – it bounced back into her hand. She returned to the house. She knew that the shed could never be in disarray again and she was happy. The man saw that she was happy and so he felt happy too.

He resolved to sort out the garage next…


The shed is your database. The spells are your datatypes and constraints and security. Without them, no matter how careful you are, you will lose things and the shed will become a mess.


Look after your shed.




Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…

One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what the TO_DATE function actually does. I think many people that are new to database development and Oracle see TO_DATE and it’s format mask and think – great I can reformat a date!

Well, no. That’s not what it does and is not what its for. Let’s get back to basics.

A date is a date. It’s not ’14-03-2013′, ’03-14-2013′, ’14 Mar 2013′ or any other representation you can think of. It’s a date, and in Oracle’s world, a time too. The moment that you are reading this is a date.

A string (or char or literal or whatever you want to call it – this post will use string from now on) is a series of characters. ‘Ben’ is a string, in the same way that ’14 March 2013′ is a string, they mean very little to the database unless they’re relating to something else.

So, to the purpose of TO_DATE()!

TO_DATE() is a system function that allows you to describe to the database a date that you are passing in as a string. The conversation would go something like this…

Developer: Yo DB! Got an input here, it’s ’14-MAR-2013′ – knock yourself out.

DB: Cool! I like strings.

Developer: But it’s a date?

DB: Oh. You didn’t mention that. What date is it?

Developer: I have to explain everything? You just can’t get the staff these days! It’s in DD-MON-YYYY format obviously…

DB: You should have just said, no need to be abusive.

In code, that would be TO_DATE(’14-MAR-2013′,’DD-MON-YYYY’). The database now knows it’s a date and can treat it as such. The Oracle doc’s and a million blog posts will tell you the many forms in which you can ‘describe’ the date to the database. So, that is TO_DATE(), and that’s what it does.

So why do people get confused? Well, mostly because they see the term ‘format mask’ and they’ve heard of TO_CHAR(). Then think the two functions can be used interchangeably because they both have ‘format masks’.

TO_CHAR() is a system function that allows you to describe to the database how to return something as a string. The conversation would go something like this…

Developer: Yo DB! Got a date here, just pop it back to me as a string so the user can see it please.

DB: Cool! Here you go – ’04-03-2013′

Developer: The user’s American?

DB: Oh, you didn’t mention that. How do they want it?

Developer: I have to explain everything? You just can’t get the staff these days! It’s in MM-DD-YYYY format obviously…

DB: You should have just said, no need to be abusive.

In code, that would be TO_CHAR(the_date,’MM-DD-YYYY’).

By now you probably wondering why the Developer keeps saying ‘Yo’ and the DB keeps replying with ‘Cool!’. That there is implicit conversion. The DB *knows* that the developer is a 1980’s pop rap singer, so it knows how to respond. This is all in your NLS parameters, there you can set the default date format for the database. This is why many people come across the situation of “this always works but suddenly the results are wrong!” when dealing with a predicate like…

my_date_column = :p_myDate

Now, let’s say the NLS parameters are set to ‘DD-MM-YYYY’. All the users input dates in that format. Then they employ an American and they input a date as ’03-04-2013′. The DB thinks that this is 3rd April 2013, but the user meant 4th March 2013.

You should never rely on implicit conversion (the same applies to numbers here), you should ALWAYS describe to the database explicitly what you are giving it.

Note to APEX developer’s here – every item that you have is returned as a string, so all of your reports etc. that refer to items as bind variables should be explicitly converted if they are supposed to be numbers or dates – saying “but it works at the moment” is not good enough!. APEX does do some clever things around this but I would always convert explicitly in my code.

Why does this all annoy me so much? Well, I see people doing nested TO_CHAR(TO_DATE(TO_CHAR())) all the time in the forums and wondering why their code isn’t working. Just remember – TO_DATE() describes to the database that you are giving it a date, TO_CHAR() can allow you to return a date as a string in a specified format. Stop being abusive to your database!

Okay, so that’s the blogging hiatus finished – I’ll be back soon!

OUG Ireland 2012


Long time no speaky!

Well, on 21st March 2012, I spoke at the OUG Ireland conference. The title of the presentation was “to iterate is human, to recurse divine” – a touch pretentious, I know. The event started for me the night before, meeting Roel Hartman, Jan Navratil and Frits Hoogland for some beers and food (followed by a few more beers and some pool).

It was even more interesting with it being my future wife’s birthday on the day of the conference. The presentation did not go too badly but was massively under time. This was partly by design but not to *that* extent – lesson learned for next time I present this. Afterwards I believe I did my lady proud and got her so tipsy that she forgot me getting the restaurant to sing her Happy Birthday by the next morning!

This year promises to be rather busy on the old conference front for me and OUG Ireland was a great way to get started! I even got chance to speak to Debra Lilley to float the idea of an Isle of Man based event, who promptly walked me over to Karen McCormack so that she could look into the viability of the idea (once I’ve given here some details). These kind of networking opportunities just don’t happen unless you go to conferences and Special Interest Group meetings, so I encourage all of you to support your local ones as much as you can this year. 

I’m going to try and blog regularly from now on, but I’ve said that before and failed.

No promises, but I WILL TRY!


Ok, here’s my first blog post in a long while.

It’s well overdue, I know. I spurred myself into doing it by proclaiming my intent over twitter, dangerous – no choice now!

So I’ll start myself off with a short and simple that I came across the other day, hopefully it will help someone.

The PROBLEM… Created a new tabular form in the new theme/framework that I’d been working on. All looked well, my CSS and jQuery theming was looking gorgeous. I was patting myself on the back and considering buying a ‘special’ coffee to celebrate. Then something happened… Or rather, something didn’t. I clicked on the checkbox at the top of the row selector column and I got the first row checkbox selected and A JAVASCRIPT ERROR! What the hell had I done to break such a fundamental function in APEX?

Here’s the exact error from Firebug;

gRowActive[a] is undefined

So, I Google and I hit this link… Fantabulous! It’s happened before – a fellow APEX developer has met the same plight and, by golly, they’ve bloody fixed it! What they forgot to do was furnish the rest of us unfortunate souls with their ‘fix’.


Now here we go. It’s not a fix, it’s a hack. because the underlying javascript code from APEX is wrong (sorry – ‘sub optimal’)  – 😉

PUT SOMETHING IN THE… ‘Background color for checked row’

AND IN THE… ‘Background color for current row’


Hmm… why should that break it?

APEX SIG London November 2011

Okay, nearly 1 year since my last post…

I’m presenting at the APEX SIG on 3rd November…

I would love for every attendee to bring one thing that will fit into a hat!

It could be a piece of code or comment on a piece of paper, a memory stick with something on, a photograph, a piece of candy, a used bus ticket – I don’t care – use your imagination. Every single one will be discussed within the context of the presentation.

Please retweet/facebook/e-mail/phone/whatever!


Handling aggregated strings in SQL reports

Well, it’s been a long time without a new post from me so I thought I’d get the ball rolling again!

Having been surfing about and happened upon Adrian Billington’s site again, I spotted something pretty nifty. With a bit of rewriting, I started to see a possible benefit to the APEX community – mostly for the handling of multiselect list values in reports.

A common approach to dealing with them is to concatenate colons to the multiselect list value returned in the bind variable and then concatenating colons to the column being searching and using INSTR. Whilst this is okay for small sets of data, it can become problematic for large ones because it will negate the index on the searched column.

Consider this example of the that approach…

FROM  emp
WHERE INSTR(':'||:LIST_ITEM||':',':'||empno||':')>0

Here, If you have an index on empno, this won’t be used and a full table scan will occur.

Now consider this (and this is seriously quick)…

WITH t_str AS
 SELECT  :LIST_ITEM||':'                                              AS str,
 (LENGTH(:LIST_ITEM) - LENGTH(REPLACE(:LIST_ITEM,':'))) + 1 AS no_of_elements
 FROM    dual
),   t_n_rows AS
 FROM    dual
 CONNECT BY LEVEL <= (SELECT SUM(no_of_elements) FROM t_str)
),   t_in_list AS
 SELECT SUBSTR(str,start_pos,(next_pos - start_pos)) AS element_value
 FROM   (
 SELECT t_str.str,
 nt.i AS element_no,
 DECODE(nt.i,1,1,nt.i - 1)) + 1 AS start_pos,
 INSTR(t_str.str,':',1,DECODE(nt.i,1,1,nt.i))       AS next_pos
 FROM   t_str JOIN t_n_rows nt
 ON nt.i <= t_str.no_of_elements
FROM   emp
WHERE  empno IN (SELECT TO_NUMBER(element_value) FROM t_in_list);

Voila! The index on empno will be used! I know there’s more lines of code here but the performance benefits of it are huge (most of it’s done just using fast dual in the factored subquery). All you need to do is save it in your toolbox and copy & paste when the need arises!

Hope you enjoyed this, more posts coming soon.


PS: Sorry – forgot to link to the actual article by Adrian – hit the word ‘nifty’ now!