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
There are 2 functions in Oracle to convert strings to dates and back.
The first one is
– it takes a string parameter and returns a date. Ok, it’s
actually adate/time combination enclosed into a single data type –
The second one is
– 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
v_date := TO_DATE('01/04/2009', 'DD/MM/YYYY');
v_date is a date, representing April 1st, 2009.
Implicit conversion is when we let Oracle to perform the conversion:
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”.
You see, when Oracle does such implicit conversion, it relies on some
assumptions. If you read the documentation for
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
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
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_CHAR function and always specify a date format.
Just like this:
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
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
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?
v_date DATE := '01-APR-09';
v_date_2 DATE := TO_DATE (v_date, 'DD/MM/YYYY');
dbms_output.put_line (TO_CHAR (v_date_2, 'DD/MM/YYYY'));
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
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
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
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
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
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
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
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
Once during one of the projects I was asked what was the risk of
something going wrong. I remember I answered that we, programmers, do
not assess risks. We just do everything possible to prevent them. “There
is no point”, I said, “to calculate the chances and the impact of a file
not being in the incoming directory upon the start-up of the process if
we can implement a simple check and avoid the disaster”.
Needless to say, I was wrong.
Nowadays I work in a bank. And one of the things I learned about banks,
they all have departments responsible for managing risks. A bank in its
business plays a risky game - it lends money. It is risky because
there’s always a possibility that it will not get them back. And banks
learnt to manage these risks. They ask: “What can go wrong? What can we
do to prevent that? And what can we do to minimize the impact if we
won’t manage to prevent it?”
Software industry is much much younger than banking. But we are proud of
ourselves, because we use cutting-edge technology and most modern
methodologies. Yet, we haven’t learnt some basic principles other
industries employ for decades.
We don’t manage risks.
I’ve seen a few projects screw-ups, all for various reasons: a
technology chosen wasn’t robust enough to support the solution, the
staff’s skills were not strong enough to implement the solutions,
requirements were unrealistic… The cause may be different, but the
result is always the same - failure, or infinite slippage of the
deadline, which is the same as a failure.
And in every case the failure seemed to have been unexpected. It
puzzled me - why could nobody predict it? And it happens over and over
again. A few years ago I was a faithful reader of The Daily WTF blog. Every day it published failure stories
like the ones I witnessed. So, if we are so smart, why does it keep
happening over and over again?
Because in a software project nobody asks what can go wrong.
Not all risks are the same. Some risks can be assessed and eliminated.
Such as a risk that the existing or planned hardware will not be able to
manage the workload. This kind of risk is calculable. We can do a stress
test, measure the execution time, do basic calculations and determine
not only that we need a new server with more powerful CPU, but how
exactly powerful it needs to be.
Others are harder to predict. How would you assess a risk of your
project manager being a crook and ruining a project?
And, finally, there’s a mother of all risks – a highly improbable event,
the Black Swan, as Nassim Nicholas Taleb calls it in his book “The Black Swan: The Impact of the Highly
(I would highly recommend it to everyone who wants to learn more about
risks) What, for example, is going to happen to your project if all the
programmers decide to quit at the same time?
Some time ago I worked on a project that employed so called custom rules engines
for performing complex batch calculations on a huge dataset - about a
hundred million rows. We had a few thousand rules defined by business
people, and in order to get a result we needed to apply every rule to
the source dataset. As you can imagine, it was slow. Scanning a hundred
million rows is not fast, especially when you need to do it a thousand
times. So, a strategic decision was made to speed it up. One of our
local geniuses came up with a brilliant, as he thought, idea: instead of
applying a thousand rules hundred thousand times each, we will reformat
the existing rules so that they form one monstrous rule, containing all
thousand, which can be applied to the source dataset in one pass. And so
we did. One thing, though, we didn’t take into account: when combined
all together, the rules formed a frankensteinian SQL expression more
than 1,000,000 character long (that’s right, more than a million). Yes, it
worked, and the process became reasonably fast. But what was the price
Imagine that you are a support programmer. You are quite unhappy with
your life, and your job in particular, because you’re fed up with those
stupid users and their problems. And on one evening, just as you were
going to go home, you get a call. Apparently, the rules engine failed.
You open the log and what do you see? A million characters long SQL and
at an error message the end, saying that an error happened somewhere in
there. Yep, somewhere in those million chars. You see, that’s the way
Oracle works. It tells you that an error has happened and tells which
one (let’s say, division by zero), but doesn’t tell where exactly within
the given SQL it has happened. So, what are you going to do, step by
step? Don’t know about you, but I would start writing a resignation
letter. And I wasn’t the only one confused. No one I asked could answer
You may ask, what does it have to do with risk? There is a direct
We had certain risk that the system being built couldn’t handle the
workload. The amount of that risk was known. We could calculate how much time it took to process the
data set, extrapolate it to the given requirements and find out that it
took 10 times more time than was acceptable. We could resolve it by
buying 10 times more powerful server, or cluster of less powerful
servers. In any case, the risk and the cost associated with eliminating
those risks were calculable.
After a strategic decision was made to solve the performance problem by
much more complex rules engine, the risk became incalculable. Yes, they
prevented the performance problem, but introduced another and much more
serious: in a case of a single failure the system was going to go down
for undefined time. Because no one would be able to fix it. And the
undefined downtime would mean an unlimited loss for the business,
because in a business world time is always money.
So, what they did is substituted an imminent, but assessable risk, that,
in fact, could be prevented, with a less probable, but potentially much
more severe which that could not be assessed. I don’t believe that
solving a minor problem by introducing a much severe one, and hiding a
possibility of a failure under the rug is a proper risk management. In the long term it is never a good strategy.