Dept. Of Greener Grass

In a refreshing change from developers wanting to become DBAs Padraig O'Sullivan is a young man who doesn't want to become stereotyped as a DBA:

"I guess I am only 23 but I don't want to become labelled as a DBA and therefore someone who cant program or develop to save his life."

I think Padraig's attitude is sensible. As Robert A. Heinlein said, "Specialization is for insects." The trick is to find a boss who is happy to allow us to be generalists but pay us the wages of a specialist.

I have one piece of advice for Padraig: most managers looking for somebody to work in an IT role will plump for somebody who actually knows their own age over somebody who can only guess at it.

Explicit Cursors RIP? Not Quite

Congratulations to Misbah Jalil over at the Oracle Contractors blog who has just heard the news that implicit cursors are more performant than explicit ones. I think rather unfairly he sticks Steven Feuerstein with the blame for the propagation of the idea that explicit cursors were more efficient. In the days of Oracle 7 lots of Oracle tuning books promoted this shibboleth. It just so happens that Oracle PL/SQL Programming was a bestseller.

Steven long ago issued a mea culpa for his bloomer. In fact he uses it now in his presentations as a classic example of why we should never take a guru's word for anything. We ought always to spike a test case to prove that it is true for our situation. Trust but verify.

The title of Misbah's article is "Explicit Cursors are Dead - Long live the Implicit Cursor". Is this true? Should we really never choose to use an explicit cursor rather than an implicit cursor? I can think of two clear cases when we still need explicit cursors. One is when we want to use BULK COLLECT INTO and the other is when we need to use a dynamic Ref Cursor. There is also the WHERE CURRENT OF clause for use with UPDATE and DELETE statements but I don't think it is mentioned in polite society anymore.

A fuzzier case is the use of a cursor attribute rather than trapping a NO_DATA_FOUND exception. I think exceptions ought to be raised for actual exceptions, that is non-standard states. So when I am expecting a query to return zero rows the proper thing is to use an explicit cursor and raise an exception if the cursor %FOUND attribute returns true. I think this is clearer about the intended meaning of the code than using an implicit cursor and a NO_DATA_FOUND handler to handle the expected path. Of course, most of the time NO_DATA_FOUND is the exception and so the implicit cursor is usually the way to go.

In the comments Paul Driver says he still uses explicit cursors to avoid TOO_MANY_ROWS exceptions being raised when the query returns duplicate rows. Personally I think this is a bug: SELECT ... INTO queries really ought to return a single row. If there is some very good reason why the query does return duplicate rows and we really don't care which row we get then our code should make this clear. Which is why Nature gave us the ability to filter by ROWNUM = 1.

So, does anybody out there still use for explicit cursors for regular querying? If so, what benefits do they offer over the performance of implicit cursors?

Online participation survey

O'Reilly are running a short survey into the motivation of people who contribute online information about software and projects (for reasons other than being paid to do so). The factors listed don't include "getting a little ACE symbol whenever I post in the forums" but it's still worth completing the survey. Check it out.

Why ctrl+S should always mean "save"

Fadi Hasweh introduces a primer on keyboard remapping in Oracle Forms with the following scenario:

"my development team asked me to disable ctlr+s for saving they don’t want the end user to you use it to save, he must click the save button, they want to make sure that he want to save and its not just an habit."

I must admit I find such user hostile action rather provoking. This action just creates a system which fools its users into thinking they have saved their work when in fact they haven't.

Our users live in a world of applications they use all the time. They bring that experience to the systems we build. If our systems don't work the way the way our users expect (i.e. like all those other apps) they will hate our systems, and, by extension, us. Apart from anything else, disabling certain keyboard input punishes those people who prefer to type rather than drive a mouse. We might as well build an annoying pop-up widget to wag a graphical finger and remind them that they didn't click the magic button.

Besides, what is the danger of a user saving too often? Such behaviour usually indicates previous traumatic experiences (such as major loss of work due to persistent network outages). Or else it might indicate a badly designed system with workflow chunks that are too big and an insufficient number of POST calls. If anything, users tend to save too infrequently rather than the other way round.

Of course, users are often unreasonable in what they expect of in-house applications compared to professional products. Like demanding rich-client levels of interactivity from an HTML interface. But that doesn't mean we developers should go out of our way to patronise, annoy or confuse our users. As the author Spider Robinson once observed, "Me, I have a science fiction writer's conviction that the damn robot is supposed to speak human, not the other way around."

Update

This is just a specific example of a general principle: that users who are familiar with computers are much less likely to read the manual than neophytes. A phenomenom known as The Paradox Of The Active User.

Would you ask Wally?

Entertainment Rights have licenced Martin Handford's classic Where's Wally, apparently for use in branding a search engine aimed at pre-teenboys. According to chief executive Mike Heap, in the future the phrase 'I'm going to Wally it" will replace "Google it". I think this is not going to happen. The main reason why will already be apparent to my American readers, who are probably asking "Who's Wally?". For them, the series is called Where's Waldo? And the phrase "I'm going to Waldo it" just refuses to trip off the tongue.

The other problem is that the metaphor is all wrong. Jeeves famously had a solution to every scrape into which Bertie Wooster got himself. Therefore the branding of Ask Jeeves made a lot of sense. Wheareas Wally loses stuff. He always loses his key, and in the first book he loses all his possessions at a rate of one per double-page spread. This doesn't inspire confidence in the ability of a Wally-ified search engine to find anything. Of course, the apt metaphor of the Ask Jeeves brand counted for nothing against the uncluttered usability and freakishly useful results of Google. The Wally engine will need an interface which is not as cluttered as one of Handford's trademark scenes.

Management: The Techie's Dilemma

It's not unusual on the Forums to get a young person asking for career advice. Usually they have got a Comp Sci degree and an OCP or a couple of years experience in development and they want to become a DBA (like this guy). It is rarer for somebody at the, er, maturer end of the spectrum asking for advice. But Peter Scott did just that this week: his employer has offered him the post of managing the company's Oracle applications team. Should he take this job or should he stick with the data warehousing job he currently has?

The advice he got back fell into two categories:
(1) Supporting E-Business Suite sucks (I paraphrase)¹, steer well clear of it.
(2) Managing a team will be more boring than being a data warehousing expert.

Here's my take on these arguments.

"E-Business Suite sucks"

Peter pointed out that the job would entail managing the support of applications written in-house as well as (uppercase) Oracle Applications. The underlying message remains the same: supporting systems written by other people is not as much fun as working on our own systems. The key thing is that Peter wouldn't be supporting other people's systems. he would be managing the people who were supporting other people's systems. So the minions have all the aggro and Peter would just have to manage their unhappiness. So it doesn't really matter whether it's just EBS or in-house apps too. The challenges of the post remain the same.

Technical interest

We become techies because we enjoy wrestling with technical issues. It can be tremendously satisfying to track through a system and nail a bug. There is joy to be had in devising an elegant algorithm to implement a gnarly piece of business logic. The hardest part of adjusting to a managerial role will be saying to a team member, "That sounds like a really interesting problem. Let me know when you've solved it." I know I'm not ready to say that (although there are days...) A manager who keeps involving themselves in the low-level details is not only not doing their own job properly but is hindering their staff in the performance of their duties. Which is not to say that management is an inherently boring role. The difference is that the role requires the manager to debug people rather than software or hardware

The sub-text

The thing is, the other reason why many people become techies is they get on better with machines than they do with human beings. Debugging a database performance problem is easy. Run a trace, tkprof the output, look for the heaviest waits, slap on a index. It's altogether more difficult to tune the performance of a person. There are no diagnostic scripts to run, no trace to analyse, no configuration parameters to tweak. Instead you have to take your performance problem down the pub for a lunchtime pint (or two), subtly mine the small talk for the underlying reasons why they are not working to their full potential and figure out what changes you can make. It may be as simple as telling them to buck their ideas up.

The tricky bit is that there is no backup and recovery option. If a manager says something inappropriate or clumsy and their staff member storms out the room in tears or high dudgeon, there is no flashback query to retrieve the situation. Management requires a different set of skills. It requires softer skills, which unfortunately tend to be innate, although it is still possible to work on them, if we have some basic aptitude and the right attitude.

Oh, and management usually requires wearing a suit and a tie, which also grates with many techies.

So why should Peter take the post

As those who have met Peter know he has the requisite people skills, and famously he does own a suit. So what benefits might he get if he should make the jump?

Time. Being a technical expert requires mastery of a lot of low-level details. Because of the nature of our industry, these details keep changing. Furthermore, the number of areas which we have to master is always on the increase. There are only so many hours in the day. Whereas management skills are a lot more transferable. It helps if the manager is generally familiar with the problem space their team works in, but it really isn't essential. And they certainly do not need to keep up with the details. I remember a manager who, having sat through a fifteen minute presentation on Ant, asked, "Isn't this just like make?" Which was really all he needed to know.

Money. We may not agree with the value proposition but it is overwhelmingly the case that managers make more money than the staff they manage.

Career opportunities. In the technical stream there is a limit to the opportunities for career development. There are a few rock stars recognised throughout the world by a single name (Tom, Jonathan, Ritters). They get the travel, the book deals, the glamour, the groupies. For the rest of us there is the daily grind of doing basically the same old thing. Eventually we reach a point at which the current role has lost its spearmint. At the same time we cannot move into different technical space because it would mean taking too big a pay cut (nobody is going to pay data warehouse expert-level wages for a J2EE novice) and we have families to support. The best chance of doing something different is going non-technical.

The standard advice to neophyte developers wanting to become DBAs is that it is easier to manoeuvre oneself into a DBA role with an existing employer than it is to get a new company to take one on without any real actual experience. The same is true for grizzled techies wanting to get into management. Peter has been offered such an opportunity by his current employer. If he is tempted he should take it.

And I'm sure he will still find things to blog about.

---

^{1. For the record, I have never worked with Oracle Apps and have no idea whether supporting EBS sucks as a job. I defer to the opinion of those with experience.}

Yet Another NULL Article

One of the things that seems to puzzle many Oracle newbies who have experience of other programming languages is that Oracle treats a zero length string as NULL. Over the Christmas period the OTN PL/SQL forum featured an entertaining but ultimately futile discussion on the philosophical ramifications of this behaviour. Personally I cannot see why there should be any more meaning in an empty string than in a NULL but many people think otherwise. And if you deeply believe that there is a meaningful distinction between an empty string and NULL then Oracle's indifference must be galling, if not heretical.

Interestingly, some of the people in this camp might be Oracle engineers. As Laurent Schneider has recently pointed out, Oracle have implemented different behaviour for LOBs. A zero length CLOB is not NULL. We could infer from this that, given the opportunity, Oracle might choose to treat empty VARCHAR2 variables this way too. Then again, it may just be an artefact of the LOB implementation. Either way. the upshot is that Oracle is unlikely to change the existing behaviour simply because such a change would probably break too many existing applications.

Of course, every languages have their quirks, especially with something as slippery as NULL. And developers in other programming languages do funny things with NULL, as this posting from the Daily WTF demonstrates.

Bulk fetches and the LIMIT clause gotcha

I have been reading Tim Hall's book on Oracle PL/SQL Tuning (do I know how to have a swinging festive season or what?). I'll write later about the whole book but I noticed a problem in an example in the chapter on bulk binds. It's the sort of thing which is easily missed until it bites you on the backside.

Here is a slightly compacted version of Tim's example...

SQL> create table t262 as select * from all_objects
  2  where rownum <= 262;

Table created.

SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262;
  6  begin
  7      open c_data;
  8      loop
  9          fetch c_data bulk collect into l_data limit 100;
 10          --  Process contents of collection here
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when c_data%notfound;
 13      end loop;
 14      close c_data;
 15  end;
 16  /
rowcount=100
rowcount=100
rowcount=62

PL/SQL procedure successfully completed.

SQL> 

So where is the problem? Well, if we replace Tim's comment with some actual processing and work with a resultset which just happens to be an exact multiple of the LIMIT clause we find this ugliness...

SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262
  6          where rownum <= 200;
  7      n pls_integer := 0;
  8  begin
  9      open c_data;
 10      loop
 11          fetch c_data bulk collect into l_data limit 100;
 12          dbms_output.put_line('rowcount='||l_data.count());
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16          exit when c_data%notfound;
 17      end loop;
 18      dbms_output.put_line('total rows processed ='||n);
 19      close c_data;
 20  end;
 21  /
rowcount=100
rowcount=100
rowcount=0
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

SQL> 

The temptation is to solve this by moving the %NOTFOUND check ahead of the processing. Unfortunately %NOTFOUND checks whether the FETCH returned the exact number of rows specified by the LIMIT clause, not whether it returned any rows at all ...

SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when c_data%notfound;
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;    
 17      dbms_output.put_line('total rows processed ='||n);   
 18      close c_data;
 19  end;
 20  /
rowcount=100
rowcount=100
rowcount=62
total rows processed =200

PL/SQL procedure successfully completed.

SQL> 

So if we want to process the whole resultset we need to use the collection COUNT() built-in to check whether any rows have been found.

SQL> declare
  2      type r1000 is table of t262%rowtype;
  3      l_data r1000;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when l_data.count() = 0;
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;
 17      dbms_output.put_line('total rows processed ='||n);   
 18      close c_data;
 19  end;
 20  /
rowcount=100
rowcount=100
rowcount=62
rowcount=0
total rows processed =262

PL/SQL procedure successfully completed.

SQL> 

This interaction between %NOTFOUND and the LIMIT clause is not entirely intuitive so it is unfortunate that it is not explained in the documentation. The PL/SQL Developer's Guide gives an example which checks for %NOTFOUND right after the FETCH and so falls into the trap of not processing the result set's loose change. I discovered this behaviour the hard way: investigating a bug which was caused by this precise problem. Did my original unit test fixture use a set of data which returned an exact multiple of the LIMIT clause? Yes it did. Should that data set have contained an irregular number of rows? Of course it should have!