Tag Archives: developing

Opinion

Blessed is the Tool Maker

In the fifth part of Douglas Adams’ Hitchhiker trilogy, Arthur Dent makes his living among a group of stone-age settlers by utilising the one skill he had that was relevant to that world: sandwich making.

I guess we all have a special skill. But my point in this article is that if you’re a software professional, your special skill (unless you’re stranded on a stone-age planet) should be making software tools.

This realisation dawned on me over a long time. It’s well known that developers range in ability by at least an order of magnitude but less clear why. I’m certainly not qualified to answer that question in its entirety, however I’m convinced that developers can punch well over their weight simply by learning to write software that automates dull tasks, such as writing other programs.

Now a lot of people reading this will be thinking, “What’s the big deal?” Is this not obvious? Does every developer not do this? Originally I thought they did but recently became clear to me that that is not the case.

As is my habit, I had written a small utility program. A colleague was going through the same problem so I shared it with him. Even if we didn’t work for the same company, the script had only taken me a couple of hours to write. I made no claims that it was perfect but I knew that it did most of the heavy lifting required.

Next came the surprise.

A couple days later I conversationally asked how he’d got on with my script. It turns out that it didn’t work straight away; there was a minor bug. As far as I can tell he immediately abandoned my code and spent five hours doing the same thing by hand. He’d not mentioned the bug to me before and, for the record, it took about ten minutes for me to fix the problem that he’d encountered.

So, let’s make things very clear here. This otherwise smart person had done five hours work rather than spend a few minutes looking through a program or, even more bafflingly, ask me a simple question ((There’s an argument that the problem here is his communication skills. I’m not convinced that is the case in this circumstance. He’s normally fine. And I think I’m fairly approachable!)).

What’s the difference? Why do some people go out of their way to write simple programs while others go out of their way to do repetitive, mind-numbing tasks?

I think it’s simple. Writing a program to write another program just does not occur to many people, they have difficulty thinking in abstract terms and, in general, can’t go “meta.”

Looking back to my university days I remember a number of parallels. A number of people had problems with the assembler course, but the most telling in many ways was on our compilers course. A compiler is a literally a program that writes another program, normally from a high level language such as C++ to assembler or machine code, and to do this you need to keep two separate worlds in your head simultaneously. Firstly you need to consider the program that you’re writing, that is you need to remember all the lexical tokens and the grammar as well as the memory you’re allocating and using. Secondly, you need to keep track of what’s going on in the program that the compiler will be writing when it runs, stuff like the stack, the memory that it will need to allocate at run-time. Since both are just computer programs, many, if not most, of the people on the course had some difficulty getting their head around either the concept or at some level of the implementation.

So far you might think that I’m bragging, but really I’m not. There’s another key part of this. It’s not just a matter of building software tools, it’s knowing when it’s not worth the effort. Had my colleague known that it would take me five hours to fix the problem then he would have made the right move (if we discount the possibility that the script could help other people if fixed).

Similarly, on a number of occasions I have spent far longer building a tool than it would have taken to do it by hand. Sometimes the desire to solve a programming challenge means that I lack the perspective to see when it’s not such a good idea to script the task. Tool building is not without risk. Those truly great developers, no doubt, have both the tool building ability and the ability to recognise when it makes sense to do so.

But overall I still think that one very significant factor in making some developers more productive than others is their ability to write software tools, particularly programs that write other programs.

PL/SQL

Output

Introduction

PL/SQL is good for many things. It has a structured, Ada-like syntax and can have SQL statements seamlessly embedded in it. Although not compiled right down to machine code, it’s even fairly quick.

But one thing that most people initially have difficulty doing is displaying information on the screen. The first thing I did was to enter what I wanted into a temporary table and use SQL*Plus to select from it. This works well for reports at the like, but it doesn’t work at all if you want to display something independently of a transaction or halfway through a function.

I was frustrated that there wasn’t a better way of feeding back information to end users. I was wrong to be frustrated: there is a better way.

Hello world!

The best example I can think of doing is the world famous “Hello, World!” program, so here goes:

CREATE OR REPLACE PROCEDURE hello_world AS
BEGIN
     DBMS_OUTPUT.PUT_LINE ('Hello, world!');
END hello_world;
/

In SQL*Plus you can run that procedure by typing “exec hello_world” and pressing return.

You may be surprised to see that it says “PL/SQL procedure successfully completed.” and nothing else; there’s no “hello world” text. Surely it’s lying, it hasn’t successfully executed anything, has it?!

As with most things Oracle, things are not quite as simple as they first appear. In this case you need to tell SQL*Plus that you’re going to use the DBMS_OUTPUT package. To do that, you need the following command:

set serveroutput on

You might want to put this in your “login.sql” file so that it gets executed every time you log in. You can also add the “size” parameter to the end to indicate how much output you’ll be producing. The most you’re allowed is around 1Mb so I usually type “set serveroutput on size 1000000”.

If you try executing the procedure now, you’ll find that it works correctly.

A longer example

For our second and final example, we’ll use one of the tables that Oracle installs by default. It’s called EMP and should be in the SCOTT schema (password TIGER). The important fact here is that there are a number of different columns types, NUMBER’s, VARCHAR2’s and DATE’s.

On to the example:

CREATE OR REPLACE PROCEDURE example2 AS
     CURSOR emp_cur IS
         SELECT e.empno, e.ename, e.sal, e.hiredate
         FROM   emp e
         WHERE  e.sal > 2500;
    BEGIN
     FOR current_employee IN emp_cur LOOP
         DBMS_OUTPUT.PUT_LINE ('-------------------');     -- string literal
         DBMS_OUTPUT.PUT_LINE (current_employee.empno);    -- number(4)
         DBMS_OUTPUT.PUT_LINE (current_employee.ename);    -- varchar2(10)
         DBMS_OUTPUT.PUT_LINE (current_employee.sal);      -- number(7,2)
         DBMS_OUTPUT.PUT_LINE (current_employee.hiredate); -- date
         DBMS_OUTPUT.PUT_LINE ('Year: ' ||
                               TO_CHAR(current_employee.hiredate, 'YYYY'));
     END LOOP;
 END;
 /

This code should be pretty much self-explanatory. We first start a loop over a table. Inside the loop we output the content of it. The first PUT_LINE statement shows that you can output a string literal (we know this as ‘Hello world’ is one too). The second shows that you can output numbers without first converting them to a string — the function is overloaded and will accept most standard types.

Following that theme, the third, fourth and fifth lines output a string, a different number and a date, in all cases using the default format string. The last PUT_LINE statement shows that you can output expressions, but note that it all needs to be of the same type (so in this case I convert a date to a string so I can concatenate it to another string).

On my machine, the output is as follows:

SQL> set serveroutput on
SQL> exec example2
 -------------------
 7566 
JONES 
2975 
02-APR-81 
Year: 1981 
------------------- 
7698
 BLAKE 
2850 
01-MAY-81
 Year: 1981 
------------------- 
7788 
SCOTT 
3000 
19-APR-87
 Year: 1987 
------------------- 
7839 
KING 5000 
17-NOV-81 
Year: 1981 
------------------- 
7902
 FORD 
3000 
03-DEC-81 
Year: 1981
PL/SQL procedure successfully completed.  
SQL>

Conclusion

As we’ve come to expect from Oracle over the years, simply outputting a little text to the screen isn’t quite as easy as you might wish for.

And it’s not without it’s problems. If you want to send more than a megabyte to the screen, you’re still going to have difficulty and all the playing around with “set serveroutput” is just plain tedious.

However, the important point is that you can do it and, programatically, it’s (basically) just a single command.

If you want more information on this kind of thing, have a look in chapter 6 of Oracle Builtin Packages, a whole book dedicated to this kind of thing.