It’s been a month since I started playing an involving adventure game “Get a root password for a weekend” with a very large multinational consulting company. That quest requires carrying out a complex sequence of actions, each of which is unknown in advance. A single error leads to a failure and necessity to start all over again. Do not pass Go, do not collect $200. Now I am almost through my second attempt. And I almost failed again.

So, to get a root password you have to:

1. No less than in 2 weeks before day “D” create a change docket in a change management system.
2. Fill a couple of 15-pages documents, describing in details what we need to do, why and how.
3. Obtain approvals from our and their management.
4. Obtain sign-offs from the downstream systems, even the ones that would not be affected.
5. Attach all the approvals to the change docket.
6. Create a task to issue a temporary root password to us.
7. Send a request to the service delivery manager, asking to approve the task and assign in to a responsible person.
8. Attend the Change Review Board and get the change approved.
9. Find out that the task assigned to a wrong group. Reassign.
10. Find out that in order to get a root password you need to fill a form.
11. Obtain the form from a Security group.
12. Fill the form.
13. Get the form signed by 3 different people in 3 different buildings.
14. Submit the form.
15. In a few days get a reply from the Security group, telling that the form was filled incorrectly - a tick was put into a different box.
16. Fill the form again.
17. Get the form signed by 3 different people in 3 different buildings.
18. Submit the form.
19. After a few day’s silence, start nagging the Service Delivery Manager.
20. Find out that another Security group is responsible for granting root passwords.
21. Reassign the task to the new group and forward the form to them.
22. After a few day’s silence, start nagging the Service Delivery Manager.
23. Find out that yet another User Admin Security Group is responsible for granting the root passwords.
24. Reassign the task to the new group and forward the form to them.
25. Find out that the submitted form is outdated. The User Admin Security Group no longer accepts outdated forms. (The form that those guys themselves sent 3 weeks ago was outdated).
26. Download the new form. The difference with the old one is just that the checkboxes are positioned differently.
27. Fill the form again.
28. Get the form signed.
29. Submit the form.
30. Find out that the form hasn’t changed for the last 4 years.

Now I think that those guys are actually Vogons.

They wouldn't even lift a finger to save their own grandmothers from the Ravenous Bugblatter Beast of Traal without orders signed in triplicate, sent in, sent back, queried, lost, found, subjected to public inquiry, lost again, and finally buried in soft peat for three months and recycled as firelighters.

It’s a follow-up to the previous post.

As it turned out, implicit date conversions may also prevent Oracle from doing the partition pruning. For example, if you have a table INVOICES with a range partition on INVOICE_DATE field, then expression

SELECT
...
WHERE invoice_date >= '01-MAR-09'
  AND invoice_date <  '02-MAR-09'

will not perform the partition pruning, whereas

SELECT
...
WHERE invoice_date >= TO_DATE('01/03/2009', 'DD/MM/YYYY')
 AND invoice_date <  TO_DATE('02/03/2009', 'DD/MM/YYYY')

will.

Because the efficiency of partition pruning is usually why partitioning is used in the first place, the choice is obvious.

But after all, I’d use

SELECT
...
WHERE invoice_date BETWEEN TO_DATE( '01/03/2009', 'DD/MM/YYYY')
AND TO_DATE( '01/03/2009', 'DD/MM/YYYY') + 1 - 1/24/3600

since BETWEEN operation is specifically tailored for such situations. “1/24/3600” here represents 1 second, and the whole statement should be read as “From 01 March 2009 0:00am to 01 March 2009 11:59pm”.

When I was going through PL/SQL procedures written by some of my colleagues, I noticed a few mistakes made around the Oracle’s date conversion functions. There are some peculiarities about those functions that I thought everyone knew about. But I reckon if I write about it, it may help others to avoid such mistakes. I also allowed myself to outline a few rules that, if you adhere to them, will help you to write better programmes.

There are 2 functions in Oracle to convert strings to dates and back.

The first one is TO_DATE – it takes a string parameter and returns a date. Ok, it’s actually adate/time combination enclosed into a single data type – Oracle’s DATE.

The second one is TO_CHAR – does the opposite: it takes date/time as Oracle’s DATE data type and converts it to string.

Actually, these functions are bit more complex than that, but for our purpose that will do. What’s important to understand here is the distinction between date as a DATE data type and its string representation.

For example, when you type ’01-APR-09’ in the procedure’s text, that’s a string, representing a date. Pay attention here: although you meant a date, Oracle sees a string. For Oracle everything that is enclosed in single quotation marks is a string. To make it a date, we need to convert this string to a DATE data type. Such conversion can be carried out by 2 possible ways: explicitly and implicitly.

Explicit conversion is when we apply the TO_DATE function to the string:

v_date DATE;
v_date := TO_DATE('01/04/2009', 'DD/MM/YYYY');

Now v_date is a date, representing April 1st, 2009.

Implicit conversion is when we let Oracle to perform the conversion:

v_date DATE;
v_date := '01-APR-09';

It has the same effect. Every time Oracle sees a string in place where it expects a date, it is smart enough to perform the conversion for us. “Well”, you may think, - “That’s great. Oracle does it all for us, so we don’t have to do it. Life is easier, let’s go for another coffee break”.

Not quite.

You see, when Oracle does such implicit conversion, it relies on some assumptions. If you read the documentation for TO_DATE and TO_CHAR functions, you’ll find that they take another optional parameter – the date format. That format tells Oracle how the string representing the date/time should be treated. If the format parameter is not specified, it is taken from NLS_DATE_FORMAT Oracle parameter. Here’s the crux: We can’t assume that this parameter will be the same on all Oracle systems. Although it is ‘DD-MON-RR’ by default and it is left like that on most Oracle systems, we can’t assume that it’s going to be like this always and everywhere. And if you rely on implicit date conversions and some DBA changes NLS_DATE_FORMAT parameter – WHAM! – All your programs will stop working.

So, a good practice and rule of thumb for you should be:

Never ever rely on implicit date conversions! Whenever you need to convert date to string or vice versa, use an appropriate TO_DATE or TO_CHAR function and always specify a date format.

Just like this:

v_date DATE;
v_date := TO_DATE('01/04/2009', 'DD/MM/YYYY');

The danger of NLS_DATE_FORMAT being changed is the biggest threat but not the only one.

Pay attention to the default date format I provided just above – ‘DD/MM/RR’. Do you notice anything suspicious? The year is 2 digits. Here Oracle tries to be smartass and tries to guess whether you mean XX or XXI century. Your only hope that it can figure out what you meant and doesn’t make a mistake. But if it mistakes – oops, welcome back the Millennium Bug. This brings us to the second rule:

Always specify the 4-digit year.

Another dangerous programming technique is trying to convert Date to Date where no conversion is necessary.

Let’s have a look at the following example, or should I say a puzzle?

DECLARE
   v_date DATE := '01-APR-09';
   v_date_2 DATE := TO_DATE (v_date, 'DD/MM/YYYY');
BEGIN
   dbms_output.put_line (TO_CHAR (v_date_2, 'DD/MM/YYYY'));
END;

Try to guess what will be printed as a result.

If you think ‘01/04/2009’, you’ve just screwed your business critical application and have sent it two thousand years back in time.

In fact, you’ll get ‘01/04/0009’.

This is where it all goes bad:

v_date_2 DATE := TO_DATE (v_date, 'DD/MM/YYYY');

And here’s why:

The first thing Oracle tries to do is to execute TO_DATE function. There is only one TO_DATE function in Oracle – the one that takes a string and converts it to a date. Despite we know that v_date is not a string, Oracle still proceeds with its logic. If you run this code, it won’t produce an error.

Oracle successfully convinces itself that it sees a String where it has a Date. That happens because Oracle is able to implicitly convert that date to a string, effectively turning that line into

v_date_2 DATE := TO_DATE (TO_CHAR(v_date), 'DD/MM/YYYY');

But, as we’ve already learned, implicit date to string conversions are performed using the date format recorded in NLS_DATE_FORMAT Oracle parameter, which is by default set to ‘DD-MM-RR’. Hence, what Oracle effectively does is this:

v_date_2 DATE := TO_DATE (TO_CHAR(v_date, 'DD-MM-RR'), 'DD/MM/YYYY');

Can you spot the error already? The date formats are inconsistent! This is what you get when you don’t pay attention to the details.

So, here comes rule 3:

Avoid unnecessary conversions. Never convert dates to dates.

If you think that all this stuff is pretty confusing, that’s because it indeed is. The good news is that you can avoid the confusion altogether by learning to program in a more clear, more concise way. That is a foundation of a good programming style.

Virtually every software development methodology stresses a need for specifying how the software end product is expected to work before starting the programming. But the truth is that writing specifications is boring. And most programmers, being lazy people (in good meaning of “lazy”. Yes, I believe there is a good meaning) try to avoid boring work. So, when the specs are written, they are often incomplete and don’t cover all the possible scenarios. Moreover, as the requirements change, these specs become outdated and, hence, even less useful.

I wonder if there is a way to replace the boring specs with something which is fun.

One possible way to go may be a “Role play-style modelling”. Just think about it. Usually software product consists of a number of modules communicating to each other. Each module does a specific task. In a typical software project, different developers are assigned to implement different modules.

Now imaging that before diving into coding, you have a kind of a role-playing game, where each of the programmers is actually pretending to be a module he is going to program. It goes like that:

Dave is a module that reads an XML file and converts it into a spreadsheet, Bob is a module that is responsible for taking the spreadsheet and sending it to users as an e-mail attachment.

Dave: Trying to open the XML file. Wait a minute, what if it’s not a well-formed XML, what am I supposed to do then? I should probably send an error message back. Ok, I read it and successfully transformed. Now I notify Bob that it’s ready. Bob, we need to decide on this notification protocol. Now, it’s your turn.

Bob: Ok, first I need to create an e-mail. Where do we get the e-mail address and subject line from – should we ask the user? This is something to find out. Then I need to check that the e-mail address is well-formed and the subject line is not empty. Then I read the spreadsheet file Dave provided and attach it. Dave, how big this file can be?

Dave: I reckon it’s up to 1 Mb, but I need to clarify it.

Bob: I need to find out if I need to compress it. Then I try to send the e-mail. If it is sent successfully, I report Ok status back to Dave. If it’s failed, I report failure status with the error message.

…

And so on.

That would give all the participants the understanding of how the end product should work. And, assuming the programmers are taking notes as they do it, every one will end up with a mini-spec just for themselves.

Another advantage of such approach is that it would encourage people to discuss every possible situation and ask “what if” questions. And asking them is the most important part of the whole process. While there is at least one outstanding question, the spec is incomplete. So, after our game, Bob and Dave would depart on harassing whoever appropriate, like business analysts or even end users, to extract the answers from them. And once they have the answers, we would repeat the game again.

And would do it over and over again, until everything unknown is ruled out.
It sounds like a odd approach, but it just might be odd enough to work.

A few days ago I went to a presentation by an Oracle recruiter. He talked about the ways Oracle searches for the potential candidates and and hires them. One particular thing that struck me was that Australian Oracle recruitment team does not use specialised recruitment web sites.

About a decade ago, looking for a job was all about reading newspaper ads. Then along came job web sites, and the recruitment landscape changed forever.

I am myself of a younger generation, and looking for a job for me was never associated with newspapers. When I look for a job, I go to a web sites such as Seek or Monster.com and search there. I take it as a given that all the jobs I may be interested in are posted there, either directly of through recruitment agents. This approach is more convenient for both job advertisers and seekers, because it allows them to find each other more easily. And most of the activity is concentrated around a few dominant players such as aforementioned Seek and Monster. This is because of so-called network effect: such web sites are created to connect people to each other, in our case job seekers and advertisers, and because the number of possible connections is proportional to the square of participants, the probability of “getting connected” through a large job web site is much higher than through a small one. Thus, size does matter. Indeed, if I want to find a job, the first thing I do is look at the largest local job site because this is where most jobs are, much like Willie Sutton robbing banks “Because that’s where the money is”.

But that is changing too.

As I have already said, I was surprised to hear that Australian Oracle recruiters do not use job web sites. Instead, they exploit a relatively new phenomenon of Internet social network services.

Social networks benefit from network effect at the scale of magnitude comparing to the traditional job sites. This is because the network size of job sites depend on the number of job advertisers paying for placing their ads. In other words, id depends on supply of ads. And if supply side does not increase, the network will not grow.

On the other hand, the growth rate of a social network is not limited by either supply or demand. As long as new members keep subscribing, network will grow. Even if they stop, the existing members will still be able to establish new connections until all the members are connected to each other.

A good example is LinkedIn, which is effectively a tool for building a professional social network. It allows you to easily find your co-workers, provided they are registered with the site, as well as connect with potential employers.

So, what is there for employers? The benefit for them is that they are able to build their network, even if they are not actively hiring. And after that, when they need it, they will be able to instantly find the right candidates through their connections. It is like having a job seekers bank at their disposals, a readily available database of resumes. And that bank will update and grow by itself.

That leads us to an interesting conclusion: within this new paradigm if you’re not online, you cannot be found. And if you cannot be found, you can’t be hired. Just because you are outside of the network.

All in all, we are witnessing a birth of a new recruitment methods. Who knows where it is going to lead us. Maybe in 5 years it will be mandatory for job seekers to have a Second Life avatar and all the interviews will be conducted in virtual reality.

Australian Oracle recruitment team has a blog, they also follow LinkedIn, Twitter and MySpace networks.