Thursday, 20 August 2009

Steven Feuerstein Perth Review - Session 2 - Writing Maintainable Code

Steven's second session remained light on for technical details, which was great for a Friday morning. He dealt with a topic that can be applied to any language and one that every developer faces on a day to day basis - the balancing act between deadlines and writing maintainable code.

As I write this I remember the old adage from Microsoft Secrets, and probably numerous software engineering books - every step a bug makes towards production, the exponentially more expensive it will be to fix.

Steven mentioned a few basic mantras and elaborated on each. I think this was well received by the audience and it always makes a difference to hear the argument from authority.
Without elaborating too much, some I managed to note down include:
  • Construct and enforce coding standards - even to the extent of when to utilise SQL. Steven pointed us to a set of standards he follows and has put out for public comment.
  • Personally, I'm in favour of using hungarian notation, including the datatype in the prefix of a variable, eg: ld_dob DATE;
  • - Utilise your development tool's template facilities. Whether it be Toad, SQL Developer, Textpad... they have have facilities to generate say a package with all the stubs, exception handlers, comment headers etc you need to avoid the White Canvas of Death, as he calls a blank .sql file. This helps with the enforcement of coding standards.
  • Encapsulate "stuff" to make it more readable
    • define a package for constants, and don't hardcode ANY value. If there is a slim chance in 100 years it could change, it should be defined somewhere. This also goes for formulas such as a full name evaluation from numerous fields.
    • Use pragma exceptions, so we see pkg_exceptions.e_collection_empty instead of -22166
    • Use PL/SQL APIs to each table (elaborated later in this post)
  • If you ever say Never or Always, it's a bug. Tom Kyte always says ;-)
  • Boolean functions read best when they return a boolean expression such as
    RETURN (this='TEST');
    as opposed to
    if this = 'TEST' then
    return true;
    return false;
    end if;
  • Functions in general should work like a funnel. This way you can avoid ORA-06503 - Function returned without a value. PL/SQL warnings can help with this. Steven's words with this comment obviously help describe what a funnel looking function looks like!
  • Always (is this a bug?) return a subtype - never a specific datatype, ie: VARCHAR2(100)
  • Visit
  • Use instrumentation/assertions. (Hint - I'll be elaborating on this later in the year)
  • And the fastest way to get the job done is to take your time. Don't rush. Mistakes are made when people rush, and experts from any industry will tell you that if you take your time, the job will get done quicker with fewer mistakes. The best analogy I heard on this was (I think) from a lecturer at uni -
    "Two carpenters were asked to make the same cabinet. The first carpenter, and old man, spent the first 2 hours thinking - studying the plans, laying out his tools.
    The second carpenter, an apprentice, (put his cowboy hat on) and got cracking straight away - sawing wood, drilling holes, occasionally swearing and looking for his hammer. He looked over at the old man thinking (chuckling), crikey, I'm halfway through this cabinet already and he's still thinking about the job!
    The old man then starts making the cabinet. All the relevant bits of timber were within arms reach as he built the cabinet, every tool never more than a step away. Actual construction of his cabinet took 30 minutes. Meanwhile, four hours in, the apprentice is still putting 'finishing touches' on his cabinet (working out how to put the doors on straight).
    The old man beamed at his work. Every join flush, every screw lined up. When the apprentice finally finished his, he figured he'd do better next time. Practice makes perfect."
As for coding practices, I had a flick through the book Steven was giving away on the day, and it looks well worth the read. Perhaps a book like that should be a standard text in a university curriculum?

Now to my first major criticism for the day. The concept of PL/SQL APIs to every table. I noticed Tim Hall also describes this sort of utopian development environment where all DML (including SELECT) for a table is done via PL/SQL APIs and any results returned via associative arrays. As I understand it, utopia is like the speed of light, or absolute zero - theoretically you can never reach it. And I think the reason in this particular case is the balance required between a tuned application and an abstracted/encapsulated one. To his credit, Steven himself said his focus is on PL/SQL performance and integrity, he's never been a SQL tuning fanatic.

Steven also mentioned Tom Kyte's mantra of "if it can be done solely in SQL, then do it in SQL". If there is no need to introduce PL/SQL for a solution, then don't.
E-Business Suite has been utilising table APIs for years, and quite successfully. It creates a well placed barrier and internalises all the business logic, turning say the insertion of a new HR employee into a procedure call whose black box touches a dozen tables to manage the setup. It also allows customisations to be neater and separate from the main install.

Software development theory aside, Steven did demonstrate a very nifty facility in his Toad suite to automatically generate all the typical APIs that may be required for a set of tables. Very tidy indeed.

There are many ideals in this session that need to be reiterated to every developer, and every newbie (I feel I should stipulate I think this is not a derogatory term, we should all remember we were all there once) should also cover it thoroughly. 4/5 for this session. Points lost because I think Hungarian notation should be in the major standard ;-), and further elaboration should be made on the utopian ideal of PL/SQL APIs - although time is always pressing for such a theological debate.


Patrick said...

In the development utilities I think you missed PL/SQL Developer. This great tool also provides a nice plug-in framework which can really help in development. Check the plugins I have created at
On the subject of the table API's. I have written a number of blogposts on using CodeGen and especially the QDA (Quest Development Architecture) on my site. It takes some getting used to using the TAPI's, but if you know one, you know 'em all. Check my blog and especially the CodeGen and the QNXO category.
QDA reduces code is about the difference in code doing stuff the 'old' way and when using the QDA.
Otherwise, good post.

Scott Wesley said...

How silly of me to forget PL/SQL Developer! I've used that a few times in the past and personally I prefer it to Toad.

I've never utilised the plugins and templates to their full extent.

I'll have a meander through your blog/website, cheers!

Steven Feuerstein said...

Thanks for the writeup, Scott. Just a couple of quick responses:

1. You wrote "Always (is this a bug?) return a subtype - never a specific datatype, ie: VARCHAR2(100)"

To be clear, what I said and say is: Any declaration of a variable in the form VARCHAR2(N) should be considered a bug. You should anchor every declaration using %TYPE or %ROWTYPE. If that is not possible, then define a SUBTYPE and anchor to that. The objective is to always have a "single point of definition."

1. Table APIs: my impression from talking to developers over the years is that very few of us work in an environment in which the overhead of an API would be unacceptable. That is, very few of us have to worry about microseconds in elapsed time. If that is true for you, then I don't think you will see a noticeable performance impact from a data access layer. In fact, many developers report an overall improvement in performance (fewer parses, easier to find and optimize SQL).