Sunday, January 20, 2013
Sunday, December 09, 2012
One of the most interesting things in the thread was Steve Cosner's observations on designing code "to be called by non-database, and ... non-Oracle software (Example: Oracle Forms, or a web process)". He uses WHEN OTHERS to produce a return code. Now return codes are often cited as an example of bad practice. Return codes disguise the error and the allow the calling program to proceed as though nothing had gone wrong in the called unit. Exceptions, on the other hand, cannot be ignored.
But Steve goes on to make a crucial point: we must make allowances for how our code will be called. For instance, one time I was writing PL/SQL packages for an E-Business Suite application and I was doing my usual thing, coding procedures which raised exceptions on failure. This was fine for the low level routines which were only used in other PL/SQL routines. But the procedures which ran as concurrent jobs had to finish cleanly, no matter what happened inside; each passed a return code to the concurrent job manager, and logged the details. Similarly the procedures used by Forms passed back an outcome message, prefixed with "Success" or"Error" as appropriate.
This rankled at first but it is undoubtedly true that exceptions make demands on the calling program. Although the demands are not as high in PL/SQL as in say C++ or Java, raising exceptions still changes how the calling function must be coded. We have to work with the grain of the existing code base when introducing new functionality. (Interestingly, Google has a fiat against exceptions in its C++ code base, and developed its Go language to support return codes as the default error handling mechanism. Find out more.)
The point is APIs are a contract. On its side the called program can enforce rules about how it is called - number and validity of input parameters, return values. But it cannot impose rules about what the calling program does with the outcome. So there's no point in exposing a function externally if its behaviour is unacceptable to the program which wants to call it. When the calling program wants to use return codes there's little point in raising exceptions instead. Sure the coder writing the calling program can ignore the value in the return code, but that is why we need code reviews.
So, is WHEN OTHERS a bug? The answer is, as so often, it depends.
Sunday, December 02, 2012
The Transgressive Manifesto is quite short:
It's okay to useThe single underlying principle is that we value willful controversy over mindless conformity.
I do have a serious point here. Even programmers who haven't read the original article (because they can't spell Dijkstra and so can't find it through Google) know that
GOTOs are "considered harmful". But as Marshall and Webber point out, "the problem lies ... in the loss of knowledge and experience. If something is forbidden for long enough, it becomes difficult to resurrect the knowledge of how to use it."
How many Oracle developers even realise PL/SQL supports
GOTO? It does, of course. Why wouldn't it? PL/SQL is a proper programming language.
The standard objection is that there is no role for
GOTO because PL/SQL has loops, procedures,
CASE, etc. But sometimes we need to explicitly transfer control. In recent times I have have across these examples:
- a loop which raised a user-defined exception to skip to the
END LOOP;point when the data was in certain ststes, thus avoiding large chunk of processing. A
GOTOwould have have been cleaner, because it is poor practice to represent normal business states as exceptions.
- a huge function with more than a dozen separate
GOTOs directing flow to a single
RETURNcall would have been really helpful, because I needed to log the returned value.
- a condition which set a counter variable to a large number so as to short-circuit a loop. Here a
GOTOwould simply have been more honest.
GOTOwould have redeemed a function with 800 LOC ; clearly there'e a lot more refactoring to be done there. But it would have been better.
Here is a situation I have come across a few times. The spec is to implement a series of searches of increasing coarseness, depending on which arguments are passed; the users want the most focused set of records available, so once a specific search gets some hits we don't need to run the more general searches.
IF statements provide one way to do this:
result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5); if result_set.count() = 0 then result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4); if result_set.count() = 0 then result_set := sieve_3(p1=>var3, p2=>var5); if result_set.count() = 0 then .... end if; end if; end if; return result_set;Obviously as the number of distinct searches increases the nested indentation drives the code towards the right-hand side of the page. Here is an alternative implementation which breaks the taboo and does away with the tabs.
result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5); if result_set.count() > 0 then goto return_point; end if; result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4); if result_set.count() > 0 then goto return_point; end if; result_set := sieve_3(p1=>var3, p2=>var5); if result_set.count() > 0 then goto return_point; end if; ... << return_point >> return result_set;I think the second version has a clearer expression of intent. Did we find any records? Yes we did, job's a good'un, let's crack on.
GOTO: not as evil as triggers.
Sunday, November 25, 2012
A new career in a new town
Going freelance has been a big step, as I was with Logica for over seventeen years. Actually, the company I joined was CMG, which through a chain of mergers, acquisitions and re-brandings became CMG Admiral, LogicaCMG, Logica and is now part of CGI. Contracting is something I have been considering for
a while years but the actual decision almost took me by surprise. It's two weeks now, and I'm still enjoying it.
The "new town" part of the title is not quite accurate either, as Croydon is a town with a long and venerable history. But it's not a place I had been to before, although I have driven through it. This is not a crack at Croydon, it's just that sort of place; Wikipedia describes it as "a natural transport corridor between London and England's south coast".
Croydon is a bit odd, comprising zones of office blocks, shopping malls, car parks and entertainment complexes delineated by dual carriage ways and fly-overs. For me its chief virtue is a shortish commute. My last engagement for Logica entailed working away from home. After several months of living in a hotel room it's just nice to come back the family every evening.
Tuesday, July 17, 2012
Oracle and ANSI joins
FULL OUTER JOIN. To be fair, the person who asked me the question is working with a venerable codebase and the project standard is to use the old joining syntax. There is one other thing. Last year I worked on a SQL Server project. It was my first taste of T-SQL. Although overall I agree with Marvin the Paranoid Android - “How can you live in anything so small?” - I was most taken with its support for ANSI syntax in DML statements. For instance, to update one table with values from another is a simple matter:
update t1 set t1.col_a = t1.col_a + t2.col_b from my_table t1 join some_other_table t2 on ( t1.id = t2.id )Or to delete rows from one table if they don't match rows in another table, use an anti-join:
delete t1 from target_table t1 left outer join check_table t2 on ( t1.pk_col = t2.pk_col ) where t2.pk_col is nullThe update is particularly elegant compared to the cumbersome sub-queries Oracle still demands. I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.
Thursday, May 31, 2012
Application integration: reorganise, recycle, repurpose
The last UKOUG Development SIG focused on Forms. Its theme was Forms in the 21st Century: modernise, integrate, migrate? I think we covered modernisation and migration very well. But even in a packed day we couldn't cover everything, and I fear integration was not done full justice.
It wasn't planned this way, but it so happens the upcoming UKOUG Development SIG is all about Application Integraton and SOA. Integration is a topic which is in everybody's baliwick. The business people want to get the best value from their existing IT investments. The architects need to understand the interfaces bewteen the silos and across the layers. The developers have to implement it.
Every stakeholder has to appreciate the others' concerns. SOA isn't any different from other IT endeavours in this respect, but it is still relatively unfamiliar. So the SIG's agenda covers integration from both technical and business perspectives. Check it out.
And, of course, please register for the day. The date is Thursday 14-JUN-2012, and it will happen at the Oracle City Office in London. Sign up here.
Saturday, December 31, 2011
Closing out the year
The effect is exacerbated in many organisations which run their leave year from January to December, with a "use it or lose it" policy. So many people finished working a week or even a fortnight before Christmas. Which is nice for the people involved, but it does cause problems for the rest of us.
For instance, my final project of the year was a data cleansing exercise, applied in-flight to a production database. The cleansing was done in overnight batches, which we estimated would take about two weeks to run. The initial deployment was a time consuming exercise, which required a large chunk of downtime and also applied a massive change to the database. If it went wrong, we would have to rollback everything and start all over again. So that meant we could only deploy at a weekend. As if that wasn't exciting enough, the exercise absolutely had to be completed by 31-DEC-2011.
The advent season added further spice. Office parties, nativity plays, Christmas shopping and using up leave all made it harder to arrange meetings. We couldn't rely on finding the necessary people to sign off documents, answer questions, provide technical support or undertake UAT. Plus of course the entire development team wanted to celebrate Winterval. If we didn't have made a successful deployment early in December we wouldn't be able to get the cleansing finished before Christmas. That would have thrown we did a lot of people's Christmas plans into chaos.
Fortunately we deployed cleanly first time. And the cleansing proceeded smoothly. In fact we made it with, oh, days to spare. So that was a nice way to round off the year, but it felt pretty hairy at the the time.
Next year I will be grateful if the Grinch isn't in charge of project scheduling.