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!

Programming with Oracle SQL TYPE constructs, Part 2

In this two-part series I am following up on my UKOUG Annual conference presentation in an attempt to answer a question from Sue Harper on why more people don't program with Oracle's SQL Types. In the previous article I showed how Types still lack some crucial features which expereinced object-oriented programmers would expect. In this article I am going to address the other half of the question: whether PL/SQL programmers don't use Types because they do not understand when they would be appropriate. I hope to show why Types can still be a useful addition to the PL/SQL programmer's arsenal.

TYPE: what is it good for?

Let's look at the places where Types are used:

• Collections in PL/SQL
  
• Message payloads in Advanced Queuing
  
• XML DB
  

These have one thing in common: Types are used to allow programs to handle different kinds of data in a generic fashion. For instance, a collection can be a table of NUMBER or a table of a Complex Data Type. In either case we can manipulate the set of records using standard collection methods such as count(), first(), last(), next(), prior, extend() and delete(). This flexibility has seen programmers to adopt collections in a wide variety of situations: Steven Feuerstein has a whole presentation devoted to nothing but collections. The application of Types in AQ and XML DB similarly provide a framework to handle data as an undifferentiated splodge which we can pass to consuming procedures which understand the data's actual structure.

This gives us a clue as to the kind of application which is suited to a Type application: when we have data with different structures which we want to process in the same general fashion. At the UKOUG conference I discussed my current project. This is a datawarehouse. Source systems provide daily feeds which have to be loaded in to a history schema. Each table has a different structure and so has its own set of DML statements but the whole process is entirely generic. This means that the decision making logic can be stored and executed in a supertype, which calls on the subtypes' overriding implementations to execute specific logic for each individual table. As I discovered at the conference the details of the process are too complex to explain in a presentation. Even in an article like this I don't think it is practical. Instead I am going to use a simpler example which we all understand: customers.

Customers: a worked example

I recently got told off for using contrived examples in my code so I hope the following business scenario doesn't seem too spurious (in real life I would not expect addresses to be stored in quite the way I show here). The business is a mail order company selling something like artists' supplies. It keeps a record of every customer. Some regular customers have accounts; these can either be retail customers (probably professional artists) or wholesalers. The system keeps additional information about its account customers. Wholesale customers may have multiple contact addresses, e.g. for payments and deliveries.

I am not going to build a whole implementation but just enough code to print the shipping address for all the customers. Furthermore, in order to avoid irrelevant complexity I am going to sidestep the whole issue of persistence and just focus on the Types I need to create an API. The first step is to create the Types for addresses and contacts. The ADDRESS Type has a couple of member functions to print the address's components as formatted string.

SQL> create or replace type address as object (
 2      line_1 varchar2(100)
 3      ,  line_2 varchar2(100)
 4      ,  post_town varchar2(50)
 5      ,  post_code varchar2(10)
 6      ,  member function formatted_address return varchar2
 7      ,  member function formatted_address
 8             ( name in varchar2, fao in varchar := null)
 9             return varchar2
10  );
11  /

Type created.

SQL> create or replace type body address as
 2      member function formatted_address
 3          return varchar2
 4      is
 5          return_value varchar2(4000);
 6      begin
 7          return_value := self.line_1;
 8          if self.line_2 is not null then
 9              return_value := return_value
10                  ||chr(10)|| self.line_2;
11          end if;
12          if self.post_town is not null then
13              return_value := return_value
14                  ||chr(10)|| self.post_town;
15          end if;
16          if self.post_code is not null then
17              return_value := return_value
18                  ||chr(10)|| self.post_code;
19          end if;
20          return return_value;
21      end formatted_address;
22      member function formatted_address
23         ( name in varchar2, fao in varchar := null)
24          return varchar2
25      is
26          return_value varchar2(4000);
27      begin
28          return_value := name||chr(10);
29          if fao is not null then
30              return_value := return_value
31                  || 'FAO: '||fao||chr(10);
32          end if;
33          return_value := return_value||self.formatted_address();
34          return return_value;
35      end formatted_address;
36  end;
37  /

Type body created.

SQL>

Wholesale customers may have multiple addresses, which I am representing as a collection. The collection type has methods to retrieve a specific contact address.

SQL> create or replace type contact as object (
 2      cntct_code varchar2(10)
 3      ,  name varchar2(50)
 4      ,  cntct_address address
 5  );
 6  /

Type created.

SQL> create or replace type contact_nt as table of contact
 2  /

Type created.

SQL> create or replace type contacts_list as object (
 2    contacts contact_nt
 3    , member function get_contact_by_code
 4     (cntct_type varchar2) return contact
 5    , member procedure get_contact_by_code
 6        (self in contacts_list, cntct_type in varchar2, addr out address, fao out varchar2)
 7  );
 8  /

Type created.

SQL> create or replace type body contacts_list as
 2    member function get_contact_by_code
 3     (cntct_type varchar2) return contact
 4    is
 5        return_value contact;
 6    begin
 7        if self.contacts.count() > 0 then
 8     for i in self.contacts.first()..self.contacts.last()
 9     loop
10         if self.contacts(i).cntct_code = upper(trim(cntct_type)) then
11      return_value := self.contacts(i);
12      exit;
13          end if;
14     end loop;
15        end if;
16        return return_value;
17    end get_contact_by_code;
18    member procedure get_contact_by_code
19        (self in contacts_list, cntct_type in varchar2, addr out address, fao out varchar2)
20    is
21        l_cntct contact;
22    begin
23        l_cntct := self.get_contact_by_code(cntct_type);
24        addr := l_cntct.cntct_address;
25        fao := l_cntct.name;
26    end get_contact_by_code;
27  end ;
28  /

Type body created.

SQL>

Now I create the base type of Customer. This Type is used to represent the non-account customers. I have overloaded the get_mailing_address() function to provide a hook for extending the functionality in the subtypes.

SQL> create or replace type customer as object (
 2    id number
 3    , name varchar2(50)
 4    , legal_address address
 5    ,  final member function get_mailing_address return varchar2
 6    ,  member function get_mailing_address
 7          (addr_code in varchar2) return varchar2
 8  ) not final;
 9  /

Type created.

SQL> create or replace type body customer as
 2    final member function get_mailing_address
 3        return varchar2
 4    is
 5    begin
 6        return self.legal_address.formatted_address(self.name);
 7    end get_mailing_address;
 8    member function get_mailing_address
 9        (addr_code in varchar2) return varchar2
10    is
11    begin
12        return self.get_mailing_address();
13    end get_mailing_address;
14  end;
15  /

Type body created.

SQL>

To model the account customers I have an Account_Customer Type. It has member functions to get the payment and delivery addresses. This Type is not instantiable, which means that I will have to implement subtypes derived from it.

SQL> create or replace type account_customer under customer (
 2    acct_no varchar2(20)
 3    , credit_limit number
 4    ,  member function get_billing_address return varchar2
 5    ,  member function get_shipping_address return varchar2
 6    ,  overriding member function get_mailing_address
 7        (addr_code in varchar2) return varchar2
 8  ) not final not instantiable;
 9  /

Type created.

SQL> create or replace type body account_customer as
 2    member function get_billing_address return varchar2
 3    is
 4    begin
 5        return null;
 6    end get_billing_address;
 7    member function get_shipping_address return varchar2
 8    is
 9    begin
10        return null;
11    end get_shipping_address;
12    overriding member function get_mailing_address
13        (addr_code in varchar2) return varchar2
14    is
15    begin
16        case addr_code
17     when 'SHIPPING' then
18         return self.get_shipping_address();
19     when 'BILLING' then
20         return self.get_billing_address();
21     else
22         return self.get_mailing_address();
23        end case;
24    end get_mailing_address;
25  end;
26  /

Type body created.

SQL>

Now I will create two subtypes of Account_Customer to model retail and wholesale customers. These need to have code to implement the member functions specified in the parent Type.

SQL> create or replace type retail_customer under account_customer(
 2   overriding member function get_billing_address return varchar2
 3    , overriding member function get_shipping_address return varchar2
 4  );
 5  /

Type created.

SQL> create or replace type body retail_customer as
 2    overriding member function get_billing_address return varchar2
 3    is
 4    begin
 5        return self.get_mailing_address();
 6    end get_billing_address;
 7    overriding member function get_shipping_address return varchar2
 8    is
 9    begin
10        return self.get_mailing_address();
11    end get_shipping_address;
12  end;
13  /

Type body created.

SQL> create or replace type wholesale_customer under account_customer (
 2    contact_addresses contacts
 3    , overriding member function get_billing_address return varchar2
 4    , overriding member function get_shipping_address return varchar2
 5    , member procedure find_address
 6        (self in wholesale_customer, cntct_type in varchar2
     , addr out address, fao out varchar2)
 7  );
 8  /

Type created.

SQL> create or replace type body wholesale_customer
 2  as
 3    overriding member function get_billing_address
 4        return varchar2
 5    is
 6        l_fao varchar2(50);
 7        l_addr address;
 8    begin
 9        find_address('BILLING', l_addr, l_fao);
10        return l_addr.formatted_address(self.name, l_fao);
11    end get_billing_address;
12    overriding member function get_shipping_address
13        return varchar2
14    is
15        l_fao varchar2(50);
16        l_addr address;
17    begin
18        find_address('SHIPPING', l_addr, l_fao);
19        return l_addr.formatted_address(self.name, l_fao);
20    end get_shipping_address;
21    member procedure find_address
22        (self in wholesale_customer, cntct_type in varchar2, addr out address, fao out varchar2)
23    is
24    begin
25        if self.contact_addresses.contacts.count() = 0 then
26     addr := self.legal_address;
27     fao := null;
28        else
29     contact_addresses.get_contact_by_code(cntct_type, addr, fao);
30        end if;
31    end find_address;
32  end;
33  /

Type body created.

SQL>

In order to avoid having duplicated code in the get_billing_address() and get_shipping_address() functions I have introduced the find_address() procedure to hold the shared code. As discussed in Part 1 I must include this procedure in the Type specification, which clutters the interface but that is just the way it is.

Finally I need to create a nested table type which I can use for holding a collecton of customers.

SQL> create or replace type customer_nt as table of customer
 2  /

Type created.

SQL>

The actual procedure to print the delivery addresses is very simple, with a straightforward interface. It takes a list of Customer objects and calls the get_mailing_address() member function to display the address. Because this function was overloaded in the Customer Type the procedure does not need to distinguish betweeen account and non-account customers.

SQL> create or replace procedure print_delivery_addresses (p_list in customer_nt)
 2  is
 3  begin
 4    for k in p_list.first()..p_list.last()
 5    loop
 6        dbms_output.put_line(p_list(k).get_mailing_address('SHIPPING'));
 7    end loop;
 8  end print_delivery_addresses;
 9  /

Procedure created.

SQL>

To use this procedure I instantiate four customers of three different Types. I pass them as a collection of Customer objects to my procedure.

SQL> set serveroutput on size 1000000
SQL> declare
 2    --  sample address objects
 3    a1 address :=
 4        new address('1 Lister Road', 'Balham', 'London', 'SW12 8MM');
 5    a2 address :=
 6        new address('34 William St', 'Tooting', 'London', 'SW17 8YY');
 7    a3 address :=
 8        new address('124 Legal Road', null, 'London', 'NE4 7AA');
 9    a4 address :=
10        new address('The Old Warehouse', '23 Museum Street', 'London', 'WC1 8UU');
11    a5 address :=
12        new address('Piggybank House', '86 Bill Row', 'London', 'WC2 8CC');
13    a6 address :=
14        new address('Stapler Mansions', '124 Monet Road', 'London', 'SW4 7GG');
15    c1 contacts_list := new contacts_list(contact_nt());
16    c2 contacts_list := new contacts_list(
17       contact_nt(contact('SHIPPING', 'Reg', a4)
18           , contact('BILLING', 'Raj Pasanda', a5)));
19    --  sample customer objects
20    cus1 customer :=
21        new customer(1111, 'Mr O Knox', a1);
22    rcus1 retail_customer :=
23        new retail_customer(2222, 'Ms Cecilia Foxx', a2, 'A/C 1234P', 1000);
24    wcus1 wholesale_customer :=
25        new wholesale_customer(3333, 'Cornelian '||chr(38)||' Sons', a3
                                                , 'A/C 7890C',10000, c1);
26    wcus2 wholesale_customer :=
27        new wholesale_customer(4444, 'Brushes R Us', a6, 'A/C 9876C',200000, c2);
28    mailing_list customer_nt := new customer_nt (cus1, rcus1, wcus1, wcus2);
29  begin
30    print_delivery_addresses(mailing_list);
31  end;
32  /
Mr O Knox
1 Lister Road
Balham
London
SW12 8MM                                 

Ms Cecilia Foxx
34 William St
Tooting
London
SW17 8YY                          

Cornelian & Sons
124 Legal Road
London
NE4 7AA                                 

Brushes R Us
FAO: Raj Pasanda
Piggybank House
86 Bill Row
London
WC2 8CC       

PL/SQL procedure successfully completed.

SQL>

Note that I edited the DBMS_OUTPUT output for clarity.

How it works as a Type implementation

The procedure print_delivery_addresses issues a call on the method Customer.get_mailing_address(): that is encapsulation. This method is implemented in different fashions in the Customer and Account_Customer Types. The version defined in Account_Customer is the one used by the Reatil_Customer anmd Wholesale_Customer subtypes: that is inheritance. The procedure takes a list of Customer Types but executes the code appropriate for the actual subtype: that is polymorphism.

Conclusion

Assembling this example was an interesting exercise. The starting idea was simple enough but modelling it in objects proved tricky. For instance I am not altogether happy about overloading the get_mailing_address() function in the Customer Type. But the only alternative seemed to be putting an IS OF test in the print_delivery_addresses() procedure to check each Customer object to see whether it is an account customer.

This may seem like a lot of complexity to do something so simple. What needs to be borne in mind is that this complexity is largely overhead. Writing a procedure to print out the billing addresses is another single call. We can submit a list of any or all kinds of Customer objects and be confident that the correct address will be printed. We can extend Customer or AccountCustomer with different implementations of get_mailing_address() and the original procedure will consume them quite happily.

Whether these benefits are sufficient to justify using Types is debatable. Modelling in Types requires a different approach from modelling with packages. Moreover it requires perseverence and flexibility. It is very easy to go off down one route and then find oneself derailed by some limitation of Oracle's Type implementation, or just by some unanticipated aspect of object behaviour. But it is worth having a play around with Types. Because some day you might find yourself designing an application with some complicated generic logic and lots of repetitious specific behaviours and Types may just fit the scenario.

What is the point of SQL%NOTFOUND?

A recent post on Oracle Brains reminded me of how the above question used to puzzle me. Then one day I stumbled across the answer in the documentation while I was looking for an answer to some other problem. There are no secrets, only parts of the manual we have yet to read.

%NOTFOUND is one of the four cursor attributes (%FOUND, %ISOPEN and %ROWCOUNT are the others) which give us information about the state of our cursors. The more common situation is to monitor the status of an explicit cursor. In the following example (all examples were run on Oracle 9.2.0.6) I use the %NOTFOUND attribute to determine whether my fetch has returned a row:

SQL> declare
  2    cursor cur is 
  3      select * 
  4      from emp
  5      where ename = 'APC';
  6    rec cur%rowtype;
  7  begin
  8    open cur;
  9    fetch cur into rec;
 10    if cur%notfound then
 11      dbms_output.put_line('There is no employee called APC');
 12    end if;
 13    close cur;
 14  exception
 15    when no_data_found then
 16      dbms_output.put_line('EXCEPTION: no data found');
 17  end;
 18  /
There is no employee called APC

PL/SQL procedure successfully completed.

SQL> 

%FOUND does the reverse of %NOTFOUND, returning true if the fetch succeeded. %ISOPEN allows us to test whether a cursor is open before we attempt to close it. And %ROWCOUNT gives us a running total of the number of rows fetched so far.

The attributes SQL%NOTFOUND, SQL%FOUND, SQL%ISOPEN and SQL%ROWCOUNT perform exactly the same functions but for implicit cursors. Except that SQL%NOTFOUND is ignored, because the NO_DATA_FOUND exception takes precedence:

SQL> declare
  2    rec emp%rowtype;
  3  begin
  4    select * into rec
  5    from emp
  6    where ename = 'APC';
  7    if sql%notfound then
  8      dbms_output.put_line('There is no employee called APC');
  9    end if;
 10  exception
 11    when no_data_found then
 12      dbms_output.put_line('EXCEPTION: no data found');
 13  end;
 14  /
EXCEPTION: no data found

PL/SQL procedure successfully completed.

SQL> 

So what is the point of SQL%NOTFOUND? Well, selects are not the only type of statement which open a cursor. DML statements do too. In the following example I use that cursor attribute to see whether my statement updated any rows:

SQL> begin
  2    update emp
  3    set sal = 10000
  4    where ename = 'APC';
  5    if sql%notfound then
  6      dbms_output.put_line('There is no employee called APC ...');
  7    end if;
  8  exception
  9    when no_data_found then
 10      dbms_output.put_line('EXCEPTION: no data found');
 11  end;
 12  /
There is no employee called APC ...

PL/SQL procedure successfully completed.

SQL> 

I could have used if sql%rowcount = 0 to achieve the same effect.

The PL/SQL documentation has more information on these attributes, including some useful advice on the positioning of calls in relation to implicit cursors.

Why DUAL?

One of my colleagues has just started reading Oracle Insights: Tales From The Oak Table. He is halfway through Dave Ensor's chapter on the history of Oracle. This explains many things - why that ubiquitous file is called afiedt.buf, why Oracle had the world's first commercial RDBMS even though it was based on IBM's System-R, why nobody ever used MTS. One thing it doesn't cover is why the DUAL table is so named.

Now the reason for this appeared in an Oracle Magazine column a few years back. I clicked on my bookmark so I could forward the URL only to get a 404 error. Turns out there's been a recent purge of the Oracle Magazine online archive. I presume this is because of a desire to retire stale and potentially misleading information rather than because OTN is running out of disk space.

Fortunately the Wayback Machine still has a copy of the web page in question. But it is the Internet Archive only works if we know what and when we know what we are looking for. So, s a public service and to keep this information in a Google-isable domain, I reproduce the text here.

The History of Dual
In the November/December issue of Oracle Magazine you recount the history of the company, so I'm wondering if you can tell me how the DUAL table got its name. People I've asked, including seasoned Oracle gurus, have not been able to answer this.
Sean O'Neill

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.
Chuck Weiss, Oracle

Source: Oracle Magazine "Sendmail column" January 2002

Update

In the Comments Marco Gralike linked to a thread on AskTom. That URL was broken by the migration of AskTom to a new version of ApEx. Here is the new URL for the thread.

Title is NULL

Gary Myers has just written about NULL, making the great point that NULL does not mean "unknown". He cites his middle name as proof of this. His parents never gave him one. Hence, a NULL for his middle name does not represent an unknown value because Gary knows his middle name does not exist. But how can we tell that from a SELECT on the CUSTOMER_NAMES table? This is why NULL is such a slippery customer: it is not just the absence of value, it is the absence of meaning too. A NULL in a column is fundamentally uninterpretable.

For instance, a NULL in the column MIDDLE_NAME might represent non-existence, someone like Gary who doesn't have a middle name. Alternatively it might mean inapplicability, because the entity we are recording is an organisation for which the concept of middle names is nonsensical. It might indicate data withheld because the person has an embarrassing middle name and declined to tell us what it is. It might portend cultural incomprehension, when the respondee literally does not understand the concept of "middle name". It might simply be a case of slack input: the operator forgot to ask or forgot to enter the value.

Hugh Darwen, in his UKOUG 2005 presentation, said that this aspect of NULL really bothered Ted Codd and that in his later life Codd devised a scheme for representing all these different flavours of NULL. Apparently Codd had identified twelve or thirteen distinct "meanings" of NULL. A project that surely invites an index finger twirling by one's temple.

The fact that NULL is not just an absence of value but an absence of meaning is one that often escapes people. Once on a project I had the task of editing a whole slew of Forms modules, setting every mandatory Y/N flag to be optional and changing the default value to be NULL instead of N. For the customer it was quite obvious that the default value for the question "Have we received the letter?" should be NULL because the answer is unknowable until the operator actually gets to that page and sets a value. So for them the meaning of NULL was "This question has not been answered". Unfortunately, in practice NULL could also mean "The operator doesn't know whether we have received the letter". Or "The operator skipped this question". Or "The customer has not sent us a letter." Or ... well, you get the drift.

Unfortunately the solution is not as simple as adopting Fabian Pascal's policy of no NULLs in the database. Because it is not just enough to slap a NOT NULL constraint on every column: that path leads to magic values and they are worse than NULLs. We need to rejig our data models to properly handle those legitimate situations for which no value exists. Which ultimately means having a MIDDLE_NAMES table, consisting of keys and a solitary MIDDLE_NAME column. But we also need better joining syntax to handle all those SELECT statements attempting to retrieve data from the MIDDLE_NAMES table. Outer joins are no good: they just re-introduce null values into the result set, so we are no better off. Modern SQL databases - not just Oracle - are not capable of representing all the different nuances of NULL.

Given the current state of SQL I think we have no choice but to live with NULLs. We just need to remember that when we find a NULL in our data it is not just that we don't have a value in this instance; we also don't know why we don't have a value in this instance.

Further reading

'NULL: Nothing to worry about' by Lex de Haan and Jonathan Gennick
'How to Handle Missing Information without Using NULL' by Hugh Darwen

Back to the Future of Forms Pt.94

An old chestnut from the UKOUG Development SIG has appeared in a thread on the ODTUG listserv: the Future of Forms. Specifically whether it is worthwhile doing new development work in Oracle Forms. The Tools Statement Of Direction states that extended support for Forms 11g will be available until 2013. There is no statement beyond that, but seven years is a long time in technology.

We do know that by the time Project Fusion has finished in 2013 the whole of the Oracle E-Business Suite will be all Java web stuff, no Forms whatsoever. But the next version of Oracle Applications (12) will still have a hefty slice of Forms in it. Whilst Apps is a consumer of Forms there will be support.

What happens to Forms after Apps no longer uses them is a different matter. Presumably as long as enough people are prepared to keep paying for the licences I guess Oracle will provide support, maybe even enhancements. Which is, I suppose, the real reason for the de-support of client/server Forms.

In the old days Oracle could afford to just licence the Forms IDE, as a way of subsidising the development environment for Oracle Apps. But once Oracle had decided to take the Java open road that stopped being such a big concern. It became more important that Forms customers pay their own way. By requiring Forms users to stump up for Application Server licences Oracle have turned the product into a sustainable revenue stream. Or killed it off. Take your pick.

Update: 19-DEC-2006

I have just noticed that Forms Product Manager Grant Ronald recently wrote on The 2013 Conspiracy.

Repetition

"This is the three Rs:
Repetition Repetition Repetition"

Repetition - The Fall

Bob Baillie's most recent blog entry reminds us of the Pragmatic Programmers' DRY principle: Don't Repeat Yourself. In other words, don't have duplicated code. Repetitious code is harder to maintain. It can also be harder to understand, because it is more verbose. Code shorn of duplication tends to be more modular and so lends itself to reuse.

These are all noble aims. The trick is to identify the duplicated code.

If you're using cut'n'paste you're probably making a design error

It is relatively easy to spot the duplication in a chunk of code like this:

    ...
    x := get_some_value(4);
    y := my_array.next();
    call_some_routine(x,y,z);
    if z = 0 then
        x := get_some_value(5);
        y := my_array.next();
        call_some_routine(x,y,z);
        if z = 0 then
            x := get_some_value(6);
            y := my_array.next();
            call_some_routine(x,y,z);
            ...
        end if;
    end if;

There has to be a better way to manipulate that array. And those nested IF statements are the Suck! We can extract some of the duplicated code into a common function and call it from inside a loop

    function dry (p1 in number, p2 in number) return number 
    is
       n number;
       return_value number;
    begin
       n := get_some_value(p1);
       call_some_routine(n,p2,return_value);
       return return_value;
    end;
    ...
    for i in my_array.first()..my_array.last() 
    loop
        dry(i,my_array(i), z);
        if z != 0 then
            exit;
        end if;
    end loop;

Spotting repetitious code like that is fairly easy because the duplication is co-located and the same shape. Repetition can be harder to spot when the problem is code in different PL/SQL packages. Or when the duplication occurs in different shapes. For instance we might write an insert statement which assigns a sequence.nextval to the primary key column when the table owner has already created a before insert trigger to do precisely the same thing.

Digging out repetition

Repetition can arise in two ways: we are writing a new piece of functionality and we find ourselves doing cut'n'paste a lot. Or when related pieces of functionality get written by different people or at different times.

When we find repeating code in existing systems we can decide to leave it be or to remove it. Normally this happens because we are working on the program, during a bug fix or enhancement. So it makes sense to tackle it now by (dread word) refactoring the code. There are a number of different approaches to refactor the code, depending upon the precise form of the repetition. The important thing is to ensure that deduplicated code works exactly the same as the duplicated code. This means we need to have tests - preferably automated ones - which we can run before and after we change the code.

It is preferable to avoid repetitious code in the first case. Easier said than done. It can be hard to write shared code and get it right first time. Carl Manaster has written a very interesting article on a technique he calls Triangulation. The basic principle is:

1. Write the code for the first case in full, to get the solution right.
   
2. Write the code for the second case in full, to identify the common features.
   
3. Extract the duplication into a common function and refactor the two cases to use it.
   

In other words, to avoid repetition we must first introduce it. Otherwise we cannot be sure that our common function correctly and completely captures the duplicated functionality. Carl's article again emphasises the importance of having automated unit tests to demonstrate that removing the repetition has not broken our code. There are just some things we can't repeat often enough.

A tale of two blog sites (updated)

Update 08-OCT-2007

In the original version of this article I named somebody who had posted other people's articles verbatim and without proper attribution. This person claims that it was an honest mistake and they had no intention of passing off other people's work as their own. They asked me to remove their name from this article. Originally I simply added a postscript. However they have now repeated the request. After due consideration I have decided to remove their name and the link to their blog. In all honesty I should have done so sooner. However, the Internet being what it is (i.e. a hall of mirrors) I'm sure multiple versions of the original wording will be around for ages.

I visited two new Oracle blogs today. The first, Oracle Brains, by Rajender Singh is workmanlike, and to be frank, doesn't contain much that is strange or startling. It will be a while before the site lives up to its name. But everybody has to start somewhere. Also, Raj showed initiative by advertising his blog in the OTN forums. More importantly, it is all his own work.

Unlike the second blog I visited this lunchtime. [name deleted] claims he will use his [name deleted][url deleted] blog "to keep my knowledge on Oracle RDBMS spread over this blog and share some light moments". But what he is actually sharing are articles by Jonathan Lewis, Fairlie Rego and Steve Adams, although attributing them to himself. So that's light as in light-fingered rather than light-hearted.

This is not as blatant as the nimrod who recently cloned Tim Hall's entire Oracle Base site but it is still pretty stupid. Passing off other people's work as your own is dishonest. Doing so on a web site merely shows a complete ignorance of the power of search engines. It took me a couple of minutes to identify the real authors of those pieces, and I'm not even a black belt in Google Fu.

Jakob Nielsen advised us to remember that our future bosses might be reading our blogs and post accordingly. Advertising oneself as someone who takes the credit for other peoples' efforts and knows nothing about how the Internet works strikes me as a bad tactic for impressing people looking to hire good technical staff. A Dilbert-esque line about people looking to hire managers has occurred to me, but my future - or even my current - boss may be reading.

Update

[name deleted] has contacted me to say that he was not intending to pass off other peoples' works as his own. He has amended his blog to acknowledge the original inspirations for those articles. So I am happy to accept [name deleted]'s explanation and I withdraw any imputation of dishonesty on his part.

Programming with Oracle SQL TYPE constructs, Part 1

In my presentation at the UKOUG, "At last! A use for TYPEs!", I tried to explain why a sensible person might choose to write an API using Oracle's SQL Types rather than Plain Old PL/SQL Packages. My current project uses an API built out of Type objects but it is the only such implementation I have know.

During the Q&A at the end Oracle's Sue Harper asked a pertinent question. When she had worked on Oracle Designer they had introduced the ability to work with Types but there had been no real take-up amongst the Designer users. Was this because the Oracle Type implementation was not very good or was it that nobody really knew what to do with Types? In my opinion the answer is both.

This article is an extended (two part) response to Sue's question. In this part I will briefly describe Oracle SQL Types; I am not trying to provide a substitute for the documentation, I outline what I think are the shortcomings with the existing Oracle Implementation which may deter people from using them. In the second part I will I have addressed Sue's request for a simple yet non-trivial example of the advantages to programming with Types.

An old COBOL hack explains OO programming in less than 200 words

In procedural programming we have data in tables and executable code in packages. In Object-Oriented programs we have objects which combine data (attributes) with procedural code (methods) to manipulate that data. OO programming has three principles:
Encapsulation. By gathering code and related data into a single object we shield the application developer from the gnarly details of our implementation. We all understand encapsulation: PL/SQL packages do this very well.
Inheritance. We can define a hierarchy of objects starting with the general and getting more specific. For instance we might have a type called Customer to represent generic behaviours and extend that to two sub-types CompanyCustomer and IndividualCustomer to model more specialised behaviours. We can add new attributes and methods in the sub-types and also override methods defined higher up the hierarchy.
Polymorphism. This is the ability to treat an object as different types according to our needs. For instance we can instantiate an IndividualCustomer object and pass it to a program which takes a Customer object. In my opinion it is polymorphism and substitutability which make programming with types an interesting proposition.

A quick note on terminology. The Object-Oriented vocabulary is ripe for confusion. I will use Type to as shorthand for Oracle SQL TYPE (also known as User Defined Types or Complex Data Types). A Type is a definition of something (other OO languages use Class to signify the same thing). When I use object I am talking about an instance of a Type, that is, an actual thing populated with data. Unless of course I am talking about objects in the regular Oracle meaning of tables, sequences, etc. :D

A brief history of TYPE

Oracle introduced Types in 8.0, branding the product as an Object-Relational database. You will no doubt be shocked to learn that the whole ORDBMS thang was largely marketing. The initial Type offering was very incomplete, lacking inheritance and polymorphism. So experienced OO programmers looking at Oracle 8.0 found so much missing that they could not work in PL/SQL and experienced PL/SQL programmers couldn't see what all the fuss was about. Oracle 8i added Java as an option for writing the code behind the methods but otherwise didn't extend the OO functionality.

In Oracle 9i we were given the inheritance and polymorphism. We also gained the ability to evolve our type definitions with the ALTER TYPE syntax. In Oracle 8 the only option at our disposal was the CREATE OR REPLACE TYPE syntax, which hurled with types that had dependents (other types and tables). The constant need to drop dependent objects was a major impediment to building APIs with types. The other major impediment was relieved in 9iR2 when Oracle gave us the ability to define our own constructor functions. The constructor is the method called when we instantiate an object. This is crucial because the constructor is the place to set default values and apply business rules. So really it only was with Oracle 9iR2 that Types became a viable programming option.

No additional object-relational features were added in Oracle 10g. Does this mean we now have a complete OO implementation? Heck no!

What remains to be done

We cannot create types that consist of methods only...

SQL> CREATE OR REPLACE TYPE abstract_type AS OBJECT
 2  ( MEMBER PROCEDURE do_this
 3      , MEMBER PROCEDURE do_that
 4      , MEMBER FUNCTION get_whatever RETURN varchar2)
 5  NOT FINAL NOT INSTANTIABLE;
 6  /

Warning: Type created with compilation errors.

SQL> Show errors
Errors for TYPE ABSTRACT_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLS-00589: no attributes found in object type "ABSTRACT_TYPE"
SQL>

So we cannot create abstract types or interfaces. Furthermore we cannot have private variables in our type bodies ...

SQL> CREATE OR REPLACE TYPE simple_type AS OBJECT
 2  ( attr number
 3      , MEMBER PROCEDURE keep_count)
 4  NOT FINAL;
 5  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY simple_type
 2  AS
 3      private_attr number ;
 4 
 5      MEMBER PROCEDURE keep_count
 6      IS
 7      BEGIN
 8          private_attr := private_attr+1;
 9      END keep_count;
10  END:
11  /

Warning: Type Body created with compilation errors.

SQL> Show errors
Errors for TYPE BODY SIMPLE_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5      PLS-00103: Encountered the symbol "PRIVATE_ATTR" when expecting
        one of the following:
        not final instantiable order overriding static member
        constructor map

SQL>

Nor can we have private functions ...

SQL> CREATE OR REPLACE TYPE BODY simple_type
 2  AS
 3      MEMBER FUNCTION cntr
 4          RETURN number
 5      IS
 6          private_attr number ;
 7      BEGIN
 8          private_attr := private_attr+1;
 9          RETURN private_attr;
10      END;
11      MEMBER PROCEDURE keep_count
12      IS
13      BEGIN
14          SELF.attr := SELF.cntr();
15      END keep_count;
16  END;
17  /

Warning: Type Body created with compilation errors.

SQL> Show errors
Errors for TYPE BODY SIMPLE_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/21     PLS-00539: subprogram 'CNTR' is declared in an object type body
        and must be defined in the object type specification

SQL>

There is also a minor problem with OVERRIDING methods in subtypes. We lack a mechanism for inheriting behaviour from the parent's implementation (equivalent to Java's super() command)¹. The upshot is, we cannot properly encapsulate our business logic inside our types. If we want to have private attributes or private methods we have to use tables and PL/SQL packages, which rather defeats the purpose.

So I hope this answers the first part of Sue's question. It has taken a long time for Oracle to provide a viable Type implementation and even now it is incomplete. So that is a possible explanantion for the low take-up of programming with Types. But are programmers missing a trick? This is the question I will attempt to answer in Part 2.

---

^{1. In one of his Open World 2006 sessions PL/SQL product manager Bryn Llewellyn included support for super() as one of the new features in next generation Oracle (the usual BETA! caveats apply).}

UKOUG 2006: In review

So another UKOUG conference is over. Now begins the task of downloading all the presentations I couldn't see this year because they clashed with the ones I did see.

UKOUG followed hard on the heels of Open World, and I was still a bit jaded last week. Birmingham is different from San Francisco. For instance, the Fujitsu-Siemens branded umbrella was a wholly irrelevant inclusion in the OOW2K6 conference bag. Whereas a similar giveaway in the UKOUG bag would be genuinely useful. Anybody who sponsored such an item would likely get a great deal of brand exposure over the week, not to mention actual gratitude from the delegates.

Another difference from Open World is that the UKOUG agenda is much more substantial, at least for DBAs. Lots of juicy topics and a wide variety of perspectives. So it was a shame that the schedule was loaded with the bulk of the Technology presentations in the first day. I met several people who were only attending on the Tuesday. For me, the Development side of things seemed even thinner than usual. The topics on offer were good, but there was not much choice. There seemed to be very little on process, as opposed to functionality braindumps and cookbook-style presentations. It was the usual lean pickings for those of us who don't build front-end applications. Of course, if developers won't submit papers there's not much which the selection committee can do about it.

Because I didn't do the full four days I missed out on much of the socialising this year. Still, I enjoyed the Bloggers/SIG Chairs' do. Organisationally, one successful innovation was the no-comps policy for the event dinner. Apparently if you make people pay for the ticket they will actually turn up. Who'd a thunk it?

I thought the round table sessions worked quite well. With the Dev Tools one, as is often the case, it took a long time for the conversation to warm up. In fact it was just towards the end of the session that people started to offer opinions rather than just asking questions of the "experts". We really could have done with another forty-five minutes. Perhaps next year we should start with some beers beforehand. Unfortunately I failed in my role as facilitator and didn't remind people to fill in the evaluation sheets; I hope other facilitators were more diligent.

Vignettes

Overall in was an enjoyable conference but not a classic. The following are some random things which have stuck in my mind from last week.

The look on Tim Hall's face. He really should have stopped asking Doug Burns about those furry housemates of his. Mind you, as Tim has just read all ten of Anne Rice's Vampire Chronicles books in two months I'm not sure he is in a postition to accuse anybody else of having weird or unhealthy obsessions.

A copy of Oracle Scene on the floor of a toilet cubicle. To be honest this is not the sort of magazine one expects to find discarded in the Gents.

Tom Kyte's Instrumentation 1010 presentation saved by a Windows bug. Tom's laptop went into hibernation mode just as he was about to run his last demo. I was chairing this session so I pointed out he only had a minute to go anyway. Then the hibernation failed due to a Windows error so Tom was able to run the script and finish his presentation tidily.

"We're not wearing a company uniform, no siree." Both SolStonePlus and IBM decided to kit out their employees in snazzy dress shirts rather than polo shirts or business suits. It was unfortunate (in more than one sense) that both companies had gone for a pyjama-style design with stripes of varying hues of blue. Even more unfortunate that both companies stands were adjacent. I smirk because I know how these things happen. The first time I did a presentation at Open World I was issued with a branded, canary yellow rugby shirt. I held out for the black polo shirt with a discreet logo. Only the Dalai Lama can carry off yellow.

And finally

Because this year's conference theme was art related ("Create your masterpiece with Oracle"? What was that about?) the ambient music filling the space between sessions in the main hall was dominated by a tune called Leftbank II. Which is better know to Brits of a certain age as the music from the Vision On gallery. I have still that mellow jazz groove and those ethereal vibes slinking around my brain. All together now: do-di do-di DO do-do do DO do do do-do DI-di-di DI di-di DI di di di di-di ...

UKOUG 2006: Day Three

Just after I finished writing up Wednesday I met Mark Rittman. He was still shellshocked from discovering that his ISP had failed to back up his site properly and so he had lost three years worth of blogs, articles and other stuff. This is not just gutting for Mark, it is a loss of a major web resource for us all. By Thursday he had become more philosophical: the forcible purge of the site at least gives him an opportunity to reassess what he does with it and cleared away some of his more embarrassing early posts. Not that I'm sure Mark had much to be embarrassed about. Anyway, in future he will be adopting a belt and braces approach to backing up his articles. A lesson for us all.

Sessions

I was actually on leave on Thursday. So I only attended a couple of sessions in the morning and made a last tour of the exhibition hall before coming back to London.

The first session I attended was Mike Hichwa, the Oracle VP for SQL Developer, ApEx and sundry other tools, offering some insights into his various projects. This is the sort of thing that justifies these conferences. Mike is the guy who wrote the first version of HTMLDB, having been the guy who wrote its predecessor, WebDB. So it was great to hear what he has to say about his thought processes as he started the development of HTMLDB.

The original model for HTMLDB was the ATM - a page flow with validation and decision points. From the beginning Mike wanted the tool to be declarative for ease of coding and consistency of execution. This also promotes modularity. He also wanted it to be more flexible than Forms, which is great for building standard apps but gets very truculent when we want to go off-piste. Another important decision was that HTMLDB would build metadata driven applications rather than being a code generator. This makes it much more interactive. During the earlier days of development Mike was working in the same room as Tom Kyte. So he had instant access to AskTom, as a result of which ApEx is highly optimised for assembling apps on the fly.

Also Mike and his team are committed to building a community around ApEx as a tool. For instance, they actively participate in the forums. They are also looking to build up a library of packaged applications which can be downloaded from SourceForge and installed for free.

The next major release of ApEx is like to feature Flash widgets for generating charts, integration with XML Publisher and an AJAX-driven WYSIWYG form layout editor.
Version control still seems somewhat clunky, requiring as it does several manual steps to get the application out of the workspace and into the source control repository. I think this is a barrier to its acceptance as an enterprise tool; I will be waiting to see whether the ApEx team manage to take advantage of the 11g (BETA!) object versioning feature.

The second session I attended was Cecil Hamilton-Jones talking on The Search For A Generic Data Model: Introducing The Associative Data Model. This was a quest for "life beyond the relational data model" and was always going to be a provocation. Which is fine: we can all benefit from having our assumptions challenged by new ideas. The problem with Cecil's presentation was that it was that was riddled with inaccuracies, confusions and unsubstantiated assertions. He repeatedly blurred the distinction between the relational data model and the process of building applications. He alleged that every time we start a new project we build a new set of tables. He also said that existing RDBMS products were not fit for the Internet business model; this will be news to Google, Amazon and all those other outfits limping along. As for his statement that the relational model was "a perfect fit for SQL", well, it was a shame Hugh Darwen was not present.

The associative data model itself is an interesting theory. It builds on concepts from social science - triple stores, semantic networks and other interesting stuff - to organise data. Everything is either an entity or an association. Associations specify the links between entities. The resulting patterns of data are built up like sentences: subject verb object. This is recursive, so that a subject can itself be built up of prior triple stores. However, as Noam Chomsky once pointed out, it is perfectly possible to write sentences which are syntactically correct but meaningless. Without relational integrity and candidate keys how does the ADM ensure meaning? Apparently it is handled by the application. Hmmm.... Of course the real reason why similar key-value pair solutions have failed in the past is that they fail to scale. Cecil interpreted this question as a matter of number of users but the problem is one of querying large tables with no meaningful indexing.

I'm trying not make this seem like a hatchet job but it's difficult. I'm totally in favour of the UKOUG offering presentations which fall outside the realm of Oracle technology and applications. I think they provide interesting perspectives on the main topic. But the standard of proof for theoretical papers needs to be as high as that for practical papers on query optimisation and the like.

UKOUG 2006: Day Two

Having arrived at the ICC way too early I steeled myself to attend the keynotes. Ronan Miles, the Old Master, confounded my expectations by not pronouncing this conference as the biggest and best ever, as he had done every year since I started attending. In fact he seemed slightly concerned about it. He wants everybody's opinion on the four day conference (after Friday). Next year's conference is also going to be four days long so I think it is important that everybody contribute (constructive) observations and suggestions for improving the structure of the 2007 event.

Ian Smith (wearing brown shoes in town, tsk tsk) gave a very polished speech - from a teleprompt - on the future of applications. Which boils down to Fusion With Choice. Oracle Apps under the Fusion brand will be the main way ahead but Oracle will continue to maintain and enhance PeopleSoft, JDEdwards and Seibel, on DB2 and MSSQL, for the long term. Apparently JDE World A9.1 will be the first new version of that product since 1998. Guess the users must like it. There were a few words that recurred through Ian's speech. See if you can spot a pattern: integrated; consolidated; coherent; focused; sincere; passionate; standards-based. There was a digression on Web 2.0 but I'm not sure quite how that applies to ERP applications. I mean, just what Sarbanes-Oxley compliance is possible in Wikipedia or MySpace?

Exhibition Hall

The exhibition hall opened today. Hurrah! But nobody seemed to be giving away promotional T-Shirts. Alas! But Oracle University were. Hurrah! So I will be sporting an their T-Shirt tomorrow. I was right that nobody was giving away promotional socks, although I may have planted the idea in the minds of the folks on the OU stand.

After Open World the hall seems terribly small and sedate.

Sessions

I chaired four sessions today. The first was Tom Kyte talking about Instrumentation 101. For the benefit of anybody who hasn't experienced the American education system, 101 means an Introductory course. Instrumentation is embedding trace and debug messages in our applications from the word go. Specifically it means having a debug message after every line of "real" code, with the ability to easily switch things on and off. Tom covered DBMS_APPLICATION_INFO, SQL Trace, DBMS_MONITOR and auditing. No mention of DBMS_OUTPUT. Strange that.

After lunch I facilitated (dread word) the Tools Round Table. It quickly became obvious that almost everybody had turned up to give Grant Ronald a hard time about the future of Forms. So we had just the one big circle rather than several separate tables. Once we had got the FOF question out of the way the conversation roamed widely over the various options for developing applications. I was interested to hear from Mike Hichwa that the Chicago Police Dept are running a system with hundreds of multi-page modules for thousands of users, all built out of ApEx. That's an enterprise level tool. We had the traditional bashing of Java developers over the question of whether business logic belongs in the database or the middle tier. But the session finished with Sten Vesterli advocating the need for communication and understanding between javaheads and database people.

The third session was Hugh Darwen on The Importance Of Column Names. This was an entertaining dissection of the third most severe mistake in SQL (after nulls and duplicate rows). It was a (revised) version of a keynote Hugh gave to the Ingres User Group a few years back, which is probably why it contained a blatent plug for Ingres as an open source database. Amongst many things he said that may strike some people as controversial was this gem:

"It is debatable whether a 'nanny state' attitude is appropriate for a computer language."

This was in the context of SELECT * being a bug. As Hugh points out, the problem really lies with the way people implement their FETCHes.

From the theoretical to the down-and-dirty technical. All the chip geeks turned out for James Morle speaking on Database System Architectures For The Commodity Age. I'm not sure I entirely understood the relevance of Julian Clancy and Chuck Norris as the avatars of commodity and non-commodity products respectively (I would have chosen Chantelle as the embodiment of commodisation) but then there were several other things which I didn't understand. One thing which I did find illuminating was this analysis:

• Servers: commoditised
  
• Operating systems: commoditised
  
• Database software: on the way to commoditisation
  
• App Servers: not really commodities yet.
  
• Very far from being commodities.
  

James pointed out how neatly this aligns with Oracle's business strategy. Coherent? Focused? You bet!

UKOUG 2006: Day One

My conference started off badly: I left my suitcase on the tube. A total senior moment: I just stood up, picked up my rucksack and got off at Euston. I only noticed I didn't have my luggage after I'd bought a coffee prior to getting on the train to Birmingham. That was to be my first coffee of the day but caffeine deprivation is really no excuse. With fifteen minutes before my train was due to leave I had to go back to the tube station to admit my bloomer. At this point I was just keen to not be responsible for a major disruption to the rush hour tube service. I now have my Lost Property claim form and I hope to be reunited with my suitcase sometime next week.

So I arrived in Birmingham with only hand luggage. There's further bad news: the exhibition hall was closed on Tuesday which means I won't be able to get through the week by wearing promotional T-Shirts. I'm going to have to go shopping. Besides, I don't expect any vendors will be giving away promotional underpants or socks. Maybe they are missing a trick here. I know people who would wear socks with a cartoon toad on them.

On the other hand at least I didn't have to check into my hotel immediately, so I caught almost the whole of Tom Kyte's keynote, What's Coming Next? This was a whiz through the likely contents of Oracle 11g (subject, of course, to the standard BETA! caveats). Tom devoted quite some time to the version control functionality, which he regards as the killer feature. It does sound quite neat, having an API layer of version controlled views, packages, etc so we can apply patches to our live applications without downtime. Certainly this might have saved me some late hours last month.

There are other features which catch the eye (such as native PL/SQL and Java compilation, virtual columns, flashback data archive) but I remain unconvinced that there is a compelling argument for upgrading. In John King's presentation on XML he did the traditional database version survey: some people on 8i, some more on 10 but roughly two-thirds on 9i. He said he came across the same proportions in the USA. Even Oracle only claim a 50:50 split between 9i and 10g. Oracle need to shift more of their customers onto newer versions for obvious business reasons. But as the major features in 10g (like the automated management stuff) still haven't achieved that it seems likely that Oracle will have to resort to coercion. At the very least they should seriously consider backporting some of the upgrade smoothing features to make it easier to move sites from 9i straight to 11g.

Session chairing

I chaired two sessions yesterday. This amounts to little more than reminding people to switch off their mobile phones and asking them to fill in the evaluation sheets. And giving the speaker time checks at the ten, five and one minute to go points.

Not that this was necessary for the first session. Chris Roderick from CERN spoke on Capturing, Storing and Using Time-Series Data for the World's Largest Scientific Instrument. Maintaining data from the Large Hadron Collider is an interesting business problem: sixty millions records per day (and likely to increase) that have to be searchable online, across a data set which will eventually hold twenty years' worth of data. The LHC Logging architecture is probably as close to a template solution as it is possible to have with such an esoteric scenario. They have an API which batches up the client records to allow bulk DML operations. The data is stored in range-partitioned index organized tables. The queries make extensive use of analytic functions. I would liked to hear more on the decision making process. How did they decide on index organized tables rather than regular ones? How did they model the scalability of the solution? But the UKOUG sessions are only forty-five minutes long and Chris did a very good job of explaining the project in the allotted time.

The next session I chaired went right to the wire. John King's gave us an overview of XML in Oracle. This was a (coherent) gallop through the entire feature set from simple basic SQL support through to WebDAV. It was like having the entire XML DB guide distilled and injected straight into your veins. John thinks that XML DB is primarily a marketing thing, gathering up a whole bunch of features under a name than is trademarkable. Oracle indulging in marketing? Surely not. One other he said that struck me was that XML in the database is used primarily for backup, in case people need to reproduce XML documents. I would like to think this is true but I suspect all sorts of nasty implementations are lurking out there.

My presentation

In the film of The Right Stuff (and possibly in real life too), the first Mercury astronaut Alan Shepherd sits in the capsule on the launch pad and recites the following prayer: "Dear Lord, please don't let me f**k up". This is a great prayer for presenters too. I skipped lunch to run through my presentation one more time. Which was just as well because I discovered that I had left a whole bunch of DBMS_OUTPUT messages in one of my demo scripts.

From my side of the podium I think the actual presentation went well. The scripts ran, my mind didn't go blank and the questions at the end suggested that at least some people had been paying attention. There was one minor blip: when I submitted the paper it turn's out I had called it At Last! A Use For Objects whereas my slides used TYPE, because I was speaking about programming with SQL TYPEs. Nobody seemed bothered, but you can never tell: some OO people get terribly particular about terminology.

Time passes

We're S-H-O-P-P-I-N-G, we're shopping

The bloggers' meeting

After the SIG Chairs' meeting we decamped to All Bar One for a joint SIG Chairs/Bloggers thrash. I discussed PowerPoint technique with Peter Scott. I'm moving towards a style that has fewer slides and fewer - or no - words on the slides I do have. I think Peter thought I was criticising him for having 55 slides in his session but I wasn't. Tim Hall is still jetlagged, poor guy. At least he's got his luggage back, whichI hope is a good omen for me. I completely failed to engage Babette Turner-Underwood in an interesting conversation about Data Pump. But she forgave me and we moved on to Funny Things Children Say. And Mike Durran asked me a very pertinent question: "So what do you blog about?" Mike, I'll get back to you when I find out.

OOW2K6: The presentations we can't see

As several other bloggers have noted the Open World presentations are now online. The "attendees only" password is widely circulated. It also is derivable from the passwords for previous years: no doubt what the experts call security through over-familiarity. My slides and supporting collateral are there, although it looks like you will have to use your skill and judgement to figure out which paperclip is the Powerpoint and which is a SQL script.

One category of presentations which is not available are any Oracle presentations alluding to features which may or may not be in the next release of Oracle. It would seem the legalistic get-out at the start of each presentation covers the session itself but not subsequent publication of the slides anywhere else. So if like me you missed a presentation or just didn't take adequate notes, well, you'll know better next time.

Update

Since posting this I have tried to download some other presentations. For some reason PowerPoint will not open Oracle-sourced presentations. I don't think it is due to corruption in the content management system as I could open a non-Oracle PPT file. It may be a trojan macro embedded in the template by Oracle's legal department ;) Anyway, this is another good reason to convert presentations to PDF. I've been using Primo PDF, as recently recommended by Tom Kyte, and it really does the business.

UKOUG Conference: Development Tools Round Table

The guy who taught the first Unix course I ever attended could edit command line strings faster then we could read them. After one particular bravura display of text manipulation his audience gave a collective gasp. David (I think that was his name) looked up and said, almost sheepishly, "Well vi is the only word processor I know". Even in 1992 that seemed impressive. Bizarrely, in his spare time this bloke wrote software for the Apple Newton, a gadget about as far from the Unix command line as it was possible to get at the time. If I had have read Neal Stephenson's In the Beginning Was The Command Line then, I would have had a finer appreciation of the nuances of this contradiction.

Tools are one of the things that unite and divide developers. Spats frequently break out in the forums over the vexed matter of PL/SQL IDEs vs text editors and SQL*Plus. On the one hand GUIs allow us to be more productive, by automating tedious or complicated processes. On the other hand they shield us from an understanding of the task in hand, which makes it harder for us to work when we have to deal with something the GUI cannot handle. The question of which text editor is another one which frequently causes the hackles to rise. The alpha geeks argue whether vim or emacs is the most studly. Whereas my penchant for TextPad marks me out as a wussy Windows user. I am just starting to use UltraEdit and appreciate some of its features (the hex mode saved my bacon recently) but I don't think I will grow to love it like I love TextPad.

Of course in the Oracle tools realm there has been a lot of activity in the last couple of years. On the one hand the traditional tools like Forms, Reports and Designer are in various levels of neglect, as newer technologies like JDeveloper and XML Publisher come into the ascendant. On the other hand there has been a flurry of activity on the part of Oracle to hook in developers early: SQL Developer, ApEx, Oracle XE. This is a belated recognition by Oracle that at least part of the reason for Microsoft's dominance has been its wooing of developers. Getting developers' mind share is crucial in the uptake of development tools for one reason: recruitment. Nobody ever said "We can't build this in VB, where we find people who know the language?" Perhaps the question "Where will find VB developers with sufficient smarts to build a half-decent application?" should have been asked more often, but the same is true of almost any programming language.

At the UKOUG Conference this year I will be facilitating a round table session on Development Tools. This is a new format for the UKOUG this year and I don't think anybody is quite sure how it is going to work in practice. But the theory is simple: a room of, er, round tables at which people sit and discuss the topic in hand. No podiums, no Powerpoint, just discussion. It might turn out to be the Focus Pubs without the beer. I'm not sure whether the tables will be switched at fixed intervals (like speed dating)

We have on hand four experts to assist me in the generation of debate:

• Sue Harper, Oracle, SQL Developer Product Manager
  
• Grant Ronald, Oracle, Forms Product Manager
  
• Omar Tazi, Oracle, Open Source Evangelist
  
• Sten Vesterli, Scott/Tiger, consultant
  

Each of us will host a table dedicated to one topic in the Tools space. Grant will be majoring on the roadmap for Oracle development tools. Sue will be drawing on her experience in Designer, JDeveloper and SQL Dev to assess options for modelling database applications. Omar will be there to discuss open source tools for Oracle, both what's available and opportunities for participation. Sten will be bringing his expertise in Comparative Tools Studies - Forms, J2EE, even Oracle Power Objects. And I will be hosting a table on miscellaneous tools and utilities which may just turn into a session of What's On Your USB Drive?

Of course this is all irrelevant if nobody else turns up. So please come along. Bring your questions, your suggestions, your opinions. Bring your USB drives if you must :). The more fervent your opinions and the more searching your questions the better the session will be.