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.

20130314-165609.jpg

TO_DATE or not TO_DATE

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!

UKOUG APEX SIG – 31st March 2010

Just a quick note to say that I will be presenting at the next UKOUG APEX SIG at the end of March.

The presentation will demonstrate the creation of an ExtJS based ‘webtop’ in APEX. It won’t be massively technical and will show the process of going from the ExtJS example to something that is generated and controlled by APEX.

The desktop can act as a portal to other APEX applications and also just looks quite cool!

Hope to see you there!

New book on APEX coming soon!

Arie Geller and Matthew Lyon are nearing completion of the new book “Oracle Application Express 3.2 – The Essentials and More”, published through Packt Publishing.

I’ve had the pleasure of reviewing of this and it’s going to be essential reading for all of you getting into APEX or wanting to expand your horizons after getting to grips with the fundamentals. No spoilers here, you can check out the details on the Packt site.

Well done Arie and Matthew, nearly there!

The book is due for release January 2010

Flexible AJAX Calls In APEX – Reports

Started to get fed up sticking the same old javascript in my HTML header on lots of pages so I decided to make my report refresh call a little more generic… now I can just have it in my general JS library and call it however I want…

This is used with a background report page that is fed details by the calling page and the report region is displayed in the current page with no page submit. I generally use it for a summary report, with a ‘Details’ column link that drills down to a further report with more info. The report’s actually in another page but is displayed on the same page with no refresh.

With this function you input the page number of your target report, the name of the div that you want it displayed in on the current page and then an array of the items (could include column substitutions etc.) on the current page and an array of the items of the target page that need to be populated.

Very flexible now – you can derive the target page and even where it should be displayed, not necessarily from static calls but even by item values (checkboxes, LOVs, whatever you want!).


<script type="text/javascript">
<!--
function get_tasty_report(pTargetPage,
                          pRegionDiv,
			  pSourceItems,
			  pTargetItems){

if (pTargetPage == null) {
alert('No target page is defined');
return;
}

if (pRegionDiv == null) {
alert('No region div name is defined');
return;
}

var get = new htmldb_Get(null,$x('pFlowId').value,null,pTargetPage);

if (pSourceItems !== undefined || pTargetItems !== undefined) {
	if (pSourceItems.length != pTargetItems.length) {
		alert('Wrong number of arguments in call to get_tasty_report');
		return;
	}

	for (var ii = 0; ii < pSourceItems.length; ii++) {
		get.add(pTargetItems[ii], pSourceItems[ii]);
	}
}

$x(pRegionDiv).innerHTML = get.get(null,'<ajax:TASTY_BODY>','</ajax:TASTY_BODY>');
}
//-->
</script>

You can see a little bit of rudimentary error handling in there that you can adapt if needed or simply remove – just there to throw things out to me when I was messing about with it.

Anyway, here’s a simple example using scott/tiger

http://apex.oracle.com/pls/otn/f?p=293:6

So all I’ve done is create a

SELECT *
FROM dept
WHERE deptno = : P5_DEPTNO

report region on page 5. I’ve also added a Display As Text(does not save state) item called P5_DEPTNO. Display condition is ‘Never’.

Next, I’ve copied the Printer Friendly template and called it AJAX Report. Then I replaced the ‘Body’ definition in my new template with…

<ajax:TASTY_BODY>
<div id="TASTY_BODY">#BOX_BODY#</div>
</ajax:TASTY_BODY>

and associated page 5 with the new AJAX Report template.

This (page 5) is the background page.

Now on page 6, I have a

SELECT *
FROM emp

report region.

I’ve also created a blank HTML region with a region source of…

<div id="my_div">
</div>

Then I’ve defined a ‘Department Details’ column with this as the link…

javascript:get_tasty_report('5','my_div',['#DEPTNO#'],['P5_DEPTNO']);

Note that the source items and target items are arrays – you can stick as many in there as you like, you can even target different pages and reports and display them in different regions on your page!