Code I Regret: Refactoring as Penance

Recently I refactored some PL/SQL for sending emails – code that I wrote way back in 2004. The number of “WTF“‘s per minute has not been too high; however, I’ve cringed more times than I’d like…

1. Overly-generic parameter types

When you send an email, it will have at least one recipient, and it may have many recipients. However, no email will have more than one sender. Yet, I wrote the package procedure like this:

TYPE address_type IS RECORD
 (name          VARCHAR2(100)
 ,email_address VARCHAR2(200)
 );
TYPE address_list_type IS TABLE OF address_type
 INDEX BY BINARY_INTEGER;
PROCEDURE send
 (i_sender     IN address_list_type
 ,i_recipients IN address_list_type
 ,i_subject    IN VARCHAR2
 ,i_message    IN VARCHAR2
 );

Why I didn’t have i_sender be a simple address_type, I can’t remember. Internally, the procedure only looks at i_sender(1) – if a caller were to pass in a table of more than one sender, it raises an exception.

2. Functional programming to avoid local variables

Simple is best, and there’s nothing wrong with using local variables. I wish I’d realised these facts when I wrote functions like this:

FUNCTION address
 (i_name          IN VARCHAR2
 ,i_email_address IN VARCHAR2
 ) RETURN address_list_type;
FUNCTION address
 (i_address       IN address_list_type
 ,i_name          IN VARCHAR2
 ,i_email_address IN VARCHAR2
 ) RETURN address_list_type;

All that so that callers can avoid *one local variable*:

EMAIL_PKG.send
 (i_sender     => EMAIL_PKG.address('joe','joe@company.com')
 ,i_recipients => EMAIL_PKG.address(
                  EMAIL_PKG.address(
                  'jill', 'jill@company.com')
                 ,'bob', 'bob@company.com')
 ,i_subject    => 'hello'
 ,i_message    => 'world'
 );

See what I did there with the recipients? Populating an array on the fly with just function calls. Smart eh? But rather useless, as it turns out; when we need to send multiple recipients, it’s usually populated within a loop of unknown sized, so this method doesn’t work anyway.

Go ahead – face your past and dig up some code you wrote 5 years ago or more. I think, if you don’t go “WTF!” every now and then, you probably haven’t learned anything or improved yourself in the intervening years. Just saying 🙂


Ten Years on Oracle

Almost exactly ten years ago, as all the Y2K bug projects were quietly winding down, I’d been getting ready for a new decade, which for me was full of uncertainty and promise. It was in 2000 that I got married, and started my first job in the I.T. industry (but not in that order). Many people thought the world was going to end, but for me it felt like the dawn of a new personal era.

After eight years of tertiary study, I’d finally gotten my degree and was fortunate enough to have landed a job as an Oracle analyst programmer at the Valuer General’s Office in Perth. Working there turned out to be a perfect introduction to the industry – I learned a lot about Oracle, about running I.T. projects, and about working productively.

There were some great people there, who mentored me and gently guided me in how to think about database programming. Specifically, it was there I started throwing off the shackles of procedural thinking, to be replaced with a relational approach, thinking about sets of data instead of individual rows.

These mentors never said, “just do it this way because I said so.” They’d allow me to make the mistakes first, then show me the better way, and explain why. For the most part, however, they just let me learn by doing, which (for me at least) is the best way to learn.

Over the last ten years I’ve gained valuable experience in a number of quite different projects, in different types of environments, different team structures, and different kinds of people, all working with Oracle. I’m open to trying new technologies, and always seek opportunities to expand my experience with different languages and software; but I think if I get to the end of my career, having never worked on any system without the Oracle database, I won’t be at all disappointed. It’s certainly not a perfect system, but there’s so much to like, so much to learn and explore.

Only God knows what my future will hold, but if the next ten years are anything like the last, they’ll involve a lot of learning, learning, and more learning. As I get older it might take a greater effort, but I always want to be the kind of person who never takes anything for granted, never assumes something just because someone said it, and who mentors others with patience and grace.

Happy new decade, everyone.


The State Of My Blog

I’ve had a few blogs in my head the last few months but haven’t had a chance to write them up. This is partly affected by the release cycle at work, and some extra-curricular volunteer work, but mostly by the birth of our second child.

[Warning: the next paragraph is not Oracle-related – skip ahead to the following paragraph if you like…]

Daniel’s arrival has probably affected our home routine more than Chloe’s did; at least with Chloe there was only one child requiring attention at any given moment; now there’s two kids screaming and during the week Daddy’s away at work for 10+ hours a day, so Rosalie’s got to handle it all by herself. The pressure isn’t helped much when one or more of us falls sick; last week Chloe and I were beset with headaches and fever, and while we’re almost recovered now, Daniel has started getting blocked up and having trouble sleeping. To top it all off Rosalie’s a bit under the weather as well. The cold and wet weather here in Perth has probably been a contributor – we’ve had temperature ranges as low as 8-17 degrees (Celsius)!

Yet another contributor to my lack of blogging activity was a fair amount of involvement in the State Youth Games earlier this month; this took a lot of my time, especially throughout May. Soon, I’ll blog about the Oracle APEX-driven web site I created for our team, partly on my non-Oracle blog [EDIT: since removed] and on this blog. The most interesting part of that was learning how to allow connections directly to my http server at home.

With all that excitement, I’ve managed to mostly keep up with all the blogging out there, which was massively helped with my home-grown RSS reader. It is also an Oracle APEX-driven web site for my private use, it regularly polls all my RSS feeds, downloads them to my computer, and keeps track of which ones I’ve read yet. RSS items that have enclosures (e.g. Rocketboom) are downloaded as well, so when I’m ready to watch or listen to them there’s less delay. At the moment I’ve got some hard-coded rules that automatically mark some items as “not interesting”, but eventually I hope to implement some kind of guided learning so that it will be able to mark new items as “probably interesting” or “probably not interesting” based on my feedback on prior items. Even with the hard coded rules, however, it cut down my reading time by about 50% which is nice. A few months ago the database had over 1,000 unread items; over the last few weeks I’ve managed to whittle that down quite a bit, and when I was sick I read the last few so now I’m fully up to date – e.g. Oracle 11g is on its way! 🙂 I can barely wait to get that installed.

That’s the State Of My Blog(s). I hope you enjoyed that because I normally avoid blogging-about-blogging like the /*TODO: insert cliche*/.