Wednesday, December 27, 2006

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.


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 :=;
26 end get_contact_by_code;
27 end ;
28 /

Type body created.


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(;
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.


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.


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(, 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(, 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.


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.


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.


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
SW12 8MM

Ms Cecilia Foxx
34 William St
SW17 8YY

Cornelian & Sons
124 Legal Road

Brushes R Us
FAO: Raj Pasanda
Piggybank House
86 Bill Row

PL/SQL procedure successfully completed.


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.


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.

Tuesday, December 19, 2006

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 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.


%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.


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.


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.

Thursday, December 14, 2006


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


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.

Tuesday, December 12, 2006

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

Wednesday, December 06, 2006

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.

Friday, December 01, 2006


"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 :=;
if z = 0 then
x := get_some_value(5);
y :=;
if z = 0 then
x := get_some_value(6);
y :=;
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
n number;
return_value number;
n := get_some_value(p1);
return return_value;
for i in my_array.first()..my_array.last()
dry(i,my_array(i), z);
if z != 0 then
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.

Monday, November 27, 2006

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.


[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...
4 , MEMBER FUNCTION get_whatever RETURN varchar2)
6 /

Warning: Type created with compilation errors.

SQL> Show errors
-------- -----------------------------------------------------------------
1/1 PLS-00589: no attributes found in object type "ABSTRACT_TYPE"

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

2 ( attr number
3 , MEMBER PROCEDURE keep_count)
5 /

Type created.

2 AS
3 private_attr number ;
6 IS
8 private_attr := private_attr+1;
9 END keep_count;
10 END:
11 /

Warning: Type Body created with compilation errors.

SQL> Show errors
-------- -----------------------------------------------------------------
3/5 PLS-00103: Encountered the symbol "PRIVATE_ATTR" when expecting
one of the following:
not final instantiable order overriding static member
constructor map


Nor can we have private functions ...

2 AS
4 RETURN number
5 IS
6 private_attr number ;
8 private_attr := private_attr+1;
9 RETURN private_attr;
10 END;
11 MEMBER PROCEDURE keep_count
12 IS
14 SELF.attr := SELF.cntr();
15 END keep_count;
16 END;
17 /

Warning: Type Body created with compilation errors.

SQL> Show errors
-------- -----------------------------------------------------------------
3/21 PLS-00539: subprogram 'CNTR' is declared in an object type body
and must be defined in the object type specification


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)1. 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).

Thursday, November 23, 2006

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.


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 ...

Friday, November 17, 2006

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.


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.

Wednesday, November 15, 2006

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.


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.

Wednesday, November 08, 2006

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.


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.

Tuesday, November 07, 2006

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.

Monday, November 06, 2006

UKOUG: Comin' up fast

"I count as lost any November that does not include a visit to Birmingham."
Gore Vidal

Yes the grand old man of American letters really said that. And even more unbelievably he was talking about Birmingham, England and not some other Birmingham. In other words, whilst I have barely had time to recover from Open World, it's already time to gear up for the UKOUG Annual Conference. The juxtaposition of these two events is an unfortunate piece of timing for me personally: it means I have had to turn down a chance to speak at the LogicaCMG LOUD event in Amsterdam. On the face of it there is no contest between the canals of Birmingham and the canals of Amsterdam. But as a UKOUG committee member I have overriding commitments to the conference. Dedication indeed.

I am still working on my presentation. Originally my submission on Oracle Types didn't make the cut. I was disappointed but over the summer my workload got tough and I found it hard to get the time to finish my paper for Open World let alone anything else. So eventually I had been quite relieved that I wasn't presenting at the UKOUG. Then last month I got an e-mail from Rachel saying my paper had been accepted after all; I was on the reserve list and somebody had dropped out. On top of which I am facilitating a round table session on development tools. But, in the words of Col. "Hannibal" Smith, I'm sure the plan will come together. It certainly gives me the adrenalin jolt I need after Open World.

The conference this year has been stretched to accommodate the additional members we gained due to the various Oracle purchases. Oracle themselves fixed this in San Francisco by hiring additional venues but this option wasn't open to the UKOUG. So the conference now extends over four days. The directors have taken the decision to offer a compelling conference experience for the newer members of the UKOUG family. The schedule has been organised around a core of PeopleSoft, JD Edwards and Apps presentations on Wednesday and Thursday. The sessions for the technology stack have been spread, sometimes very thinly, across the four days. Tuesday is very strong, there's a dip in choice on Wednesday, a single stream on Thursday and an upturn on Friday.

I understand why the schedule has been so structured and I suppose it is the correct decision. But the upshot is that it is tough for technologists who don't use Apps to justify their attendance for all four days. Certainly I will only be doing two whole days. This is a shame because the Annual Conference has always been strong its range and depth of presentations; traditionally it has been much stronger than Open World, which is more of a networking and marketing event (albeit still the most exciting Oracle event I get to attend). So it's ironic that this year OOW2K6 put a lot of effort into attracting developers, with a whole separate series of presentations under the Oracle Develop banner - and a free T-shirt too!

At least the Development Engineering SIG still has a visible presence at the UKOUG Conference. We seem to have subsumed at the poor old Modelling and Design SIG altogether. And, despite all my grumbling, there are still more sessions I want to attend than there is time available. I'm going to miss some of the ones I really want to hear because they clash with my presentation and the SIG Chairs' meeting. Oh well. I'll be chairing a few sessions and propping up the bar at the Bloggers' thrash. So one way or another I hope to meet some of you next week.

Thursday, November 02, 2006

Oracle Guru: is it you?

Robert Vollman has written a response to an article by Don Burleson on "Oracle Gurus". In it he writes:"Strictly translated, guru can mean 'teacher' - and in many places like India and Indonesia I understand that guru is generally used in that sense."

Anu Garg, of A Word A Day, has this to say about the word:
When we talk about a software guru or an economics guru, we're invoking a word from this classical language [Sanskrit - Ed.]. The word "guru" came to English from Sanskrit via Hindi. It literally means "venerable" or "weighty". Going farther back, it descended from the same Indo-European root that gave us "gravity", "engrave", "grave" and "aggravate" to name a few.

It is only in the sense of being "one who is weighty" that I consider myself a guru ;)

The thing about gurus (in the original sense) is that they are spiritual teachers, people with a handle on the numinous and the ability to transmit their understanding to their students (followers). We should be puzzled by the common use of the term to describe IT experts. After all, computers are machines and programming is an exercise in logic not mysticism. Obviously there are a lot of people in computing who are interested in martial arts and yoga. But there are just as many who are into heavy rock and we don't say Tom Kyte is an Oracle lead guitarist1. So why do we label our experts "guru"? Is there anything less spiritual than tracing 10053 events? Has anybody ever attained satori by ploughing through a hex dump of a rollback segment's block headers?

According to Anthony Storr:
"almost all spiritual gurus are solitary children who have passed through a period of intense personal crisis, often provoked by feelings of isolation, leading to breakdown. They resolve the crisis through a revelation, usually arrived at in private, often on a long and never wholly explained journey."
Actually that does sound an awful lot like ploughing through a hex dump of a rollback segment's block headers.

I suppose the thing is computer systems have become so complicated that they frequently exhibit emergent behaviour. Given all the variables involved - hardware, operating systems, the many layers of software and the vagaries of user activity - it is tempting to think that anybody who can actually explain why our application just did that peculiar thing must be tapping into a store of arcane knowledge. In fact modern systems are generally so well instrumented that most things are (or ought to be) explicable to anybody with a basic level of understanding.

For several years in my twenties I studied T'ai Chi Chuan. My teacher's teacher's teacher was Cheng Man Ching. One of Professor Cheng's aphorisms was "There are no secrets." In other words, as a teacher he had no esoteric transmissions to pass on. He just had the principles of T'ai Chi and everything else preceded from them. To become a master in T'ai Chi is simply a matter of practicing the principles and reflecting on our practice. Or, as another guru recently put it:"forget the mystic, magic, cool in theory internal stuff - learn and master the basics and you’ll blow way past someone who has learned a handful of really cool internal tricks." That sounds simple enough. "Master the basics"? Well we can all read that fine manual. But it takes a lot of practice and reflection until we have the sort of real understanding that constitutes mastery.

On the matter of how we can tell a good Oracle guru from a bad 'un all I can say is that anybody who styles themselves "guru" almost certainly ain't. This unattributed article has some sound guidelines for spotting fake Buddhist gurus which I think apply to gurus in other areas. Remember, a guru is not just somebody who offers you advice: they are teaching you an approach to living (or a methodology for tuning Oracle databases) which will require you to make changes to who you are. At some point in your practice you stop learning the teachings and start learning the teacher. So always ask yourself if you want to be like this person. If the answer is "No" then they are not the guru for you.

1. Of course, if Tom was a lead guitarist it would be in an analytics rock'n'roll band.

Saturday, October 28, 2006

OOW2K6: In the rearview mirror

Back in the UK, all the leaves are brown and the sky is grey. Hmmm, that sounds like an idea for a song...

I survived my Meet The Experts session alongside Tom Kyte with my dignity intact. The relief that Tom recognised me was only slightly diminished by the fact that he confused my blog with that of Andy Campbell. To be fair a lot of people do. Still at least I managed to get a word in edgeways, which puts me one up on Tim Hall ;)

I also managed to get the six (six!) freebie T-shirts home without bursting my suitcase. It was only an hour in the queue to get through the security checkpoint. A minor spat broke out because the officials fast tracked some people whose flight was about to take off. The bloke who had been at the front got quite vocal and it was about to kick off: "Flip you!" "No, flip you!" (or words to that effect). Fortunately the staff calmed it down. But, really, picking a fight in a place where the cops are armed and on edge is just such a dumb idea.

With hindsight it was a really great conference. On Monday it seemed like the infrastructure might crack under the strain but the organisers had done a superb job. It rarely seemed like there might be forty thousand people in the Moscone Center - except possibly whilst queuing for lunch on the Wednesday before Larry's keynote. The conference seemed to lack a big idea. The announcements there had been on the Next Generation database seemed more like 10gR3 rather than version 11. All that changed with the Unbreakable Linux announcement. Suddenly it's penguins all over the place. So how does Oracle 11L sound?

Thursday, October 26, 2006

OOW2K6: If it's Wednesady this must be SF

Jonathan Schwartz was the first keynote I attended this conference and it was very impressive. He wore a sharp suit with a well-chosen tie, designer specs and a neat pony tail. His delivery was smooth and business-like, with the occasional wry aside. In short very much not Scott McNealy. The other difference was that Scwartz seems more interested in the process of IT than in the servers as such.

In his analysis Moore's Law means that selling servers to shops and small businesses is a shrinking market. Eventually those people will ditch their servers altogether and just use networked services to run their business. So Sun are going to focus on the segment of the market whose needs are going to grow - that is, anybody with a data centre. The highlight of his presentation was the unveiling of Project Blackbox. The maximum size for a data centre ought to be the biggest thing you can move, which is a shipping container. And that is precisely what Project Blackbox is: a data centre in a shipping container. Have a lorry deliver it to your site and just plumb it in. If you need to move your data centre unplumb the box and put it on another lorry (or train or cargo ship). Now that's commodity IT.

I've already blogged about Larry's keynote but I should have confirmed that, yes, they did have live penguins on stage. Weird. Afterwards I was talking with Todd Trichler . A couple of months back I had been joshing him about an Oracle distro and he had told me not to believe everything I read. Now I know what he meant.

Before Larry's keynote there was a ceremony to recognise the twentieth anniversary of Oracle's listing on NASDAQ. Apparently this is a big deal over here but I found the whole thing slightly bizarre. Still, the crowd in Hall D seemed to enjoy it. Maybe it was one of those things where you really had to be there.

In the evening I met up with my other LogicaCMG colleagues. There were eight from the Netherlands, seven from the Scandanavian countries (from our recent acquistion of WMData) and one other Brit besides me, which is a good presence here. The evening was great. Everybody had shamingly good English. I drank beer and wine and I do not have a hangover. This is a bad sign so it's just as well that I'm flying back to the UK this afternoon.

Wednesday, October 25, 2006

OOW2K6: Larry's keynote

So the big news is Oracle are going to support Red Hat Enterprise Linux for 60% less than Red Hat charge. That will teach Matthew Szulik to buy JBoss. Oracle Unbreakable Linux will be the RHEL source code stripped of proprietary text and images and with Oracle bug fixes added.

Larry was adamant that this was not about Red Hat, it was about business. Oracle want their clients to be able to use Oracle on grids of Linux clusters. One of the barriers to adoption of Linux is the problem of having bug fixes backported to existing versions of Linux. This is the key differentiator of the Oracle service. Oracle's Linux Engineering division will fix bugs which will be available as patches for Support customers. This is not just Oracle database customers using RHEL, but anybody using RHEL who wants to sign up. Oracle will release the bug fixes to the Linux community including the distro vendors. Finally they will incorporate them into every extant version of RHEL and compile the binaries. The resulting RPMs will be available on the Oracle web site to be downloaded by anybody for free. As the icing on the cake Oracle are going to indemnify their Support customers against any legal fall-out from the SCO court case(s).

There were video messages of support from Dell, HP, AMD, Intel, Accenture, EMC, NetApp, etc. The glaring omission was Sun. And apparently nobody from Red Hat was available to comment.

In the Q&A someone asked whether the death of Red Hat was just an unintended side effect of this. Larry replied, "This is capitalism. We are competing." Yeah, right. Someone else asked about adding storage to the Oracle technology stack, like Network Appliance. Larry grinned, "there's always next year".

UKOUG: Linux Installfest

The Linux Installfests are a great feature of OTN night at Open World and I thought it would be brilliant if we had something similar at the Conference. Fortunately Todd Trichler thought the same and it's happening on the Friday of the conference (November 17th). Bring your laptop and get expert help to install Linux and Oracle. Or just turn up to acquire free Linux distro packs and Oracle software. Sue Harper will be giving a presentation about running SQL*Developer on Linux. Also Bryce from Linux engineering will be on hand to explain the details of the Linux kernel to anybody who wants to go deeper.

In short, something for everybody whether you're a Linux virgin or a battle-scarred veteran. The Install fest is free but you do need to book your attendance during registration. If you already have booked your place for the UKOUG 2006 and would like to add the Oracle on Linux Install-fest to your booking, call the registration office on 01252 77 14 93. If you have any other questions please contact Julius Kisielius.

UKOUG: The conference needs you!

In the midst of Open World as I am I'm stil preparing for the UKOUG Annual Conference which is in November. There are still a number of sessions which require chairing. If you would like to attend the conference but your company's free places are taken or if you are an individual who doesn't belong to the UKOUG then volunteering to chair some sessions is a great way to get into the conference for free.

What is Session Chairing?

The UKOUG like to ensure that every presenter is introduced and that each session is managed well and kept to time. All session chairs will be given guidelines on what to say or do, and you will be given the speaker introductions in advance.

If you have any further queries regarding what session chairing entails, please contact Lauren Gee-Edwards on 020 8545 9689

Session Terms & Conditions:

Please check the amount of sessions needed to be chaired throughout your attendance at conference. All Volunteers will only receive a full conference pass when chairing the minimum amount of sessions.
  • 2 Day attendance = 4 Sessions (minimum)
  • 3/4 Day attendance = 6 Sessions (minimum)

Please be sure to go in and check your selected sessions on your personal 'session chair agenda' to confirm that you can be dedicated to all the sessions you have selected.

If you have any problems please contact Lauren immediately.

How to sign up

There are 2 steps to complete:

A) Click this link to be taken to the Session Chair registration page. Please ensure you fill out all information within the correct fields.

LOGIN: Type in your username and password that will consist of your email address and a password of your choice. This will take you directly to an agenda that all volunteers will have access to.

INSTRUCTIONS: Please ensure you read the 'instructions for use' before selecting your sessions as this is a new process.

B) You must register yourself for your conference pass. The registration page is here. Whilst there you can book accommodation & dinner tickets if you wish to do so.

OOW2K6: Freebie frenzy

Tuesday was definitely more chilled than Monday. Most people have registered, figured out the spatial relationship between the various venues, sussed how to use Schedule Builder and just generally got over the "OMG I'm at Open World" rush. So although there were probably even more people it didn't seem as crowded. The city is noticing the effect. Open World was the lead story on the front page of the San Francisco Chronicle. It wasn't a hatchet job but there was a distinct edge to the story.

My big mission for Tuesday was to scope out the exhibition halls. The main hall is the size of an aircraft hanger and crammed with stands vying for our attention. Several companies are using golfing games as a way of attracting people. Must be some kind of putting meme in circulation. Other stands have draws for a Harley Davidson, a Vespa scooter or a Segway. But most are just giving stuff away: T-shirts, stress balls, wooden puzzles and flashing geegaws. The coolest freebie I got came from Unisys, who have a 256M Flash drive with dimensions of 1*3*0.2 centimetres. In fact it is so small that they have to give you a special wallet to hold it lest you lose it.

Brian Behlendorf on Open Source software

The most interesting presentation I attended yesterday was Brian Behlendorf, ex of Apache and now of Collabnet, talking about what makes open source software so successful and how companies can apply the same techniques to their own internal projects. Brian described open source projects as a laboratory for exploring ways of collaborating. These projects are loosely coupled networks of creativity and sharing, supported by tools and practices to encourage participation. Like Soylent Green, open source is made of people. It has to work with the grain of human psychology because there is no way to coerce coders to work on the software. Good software is just an emergent property of such networks.

Brian listed his three principles of Open Source:
  1. Complete transparency into
    • code authoring and decision making
    • scheduling and prioritisation (the roadmap)
    • discussions around technical trade off

  2. Participation at every level from casual user to core developer
  3. The right to fork

I was particularly taken with his analysis of e-mail usage. Open Source project members are dispersed around the world and working in their own time so e-mail is the only feasible means to hold discussions on any aspect of the project. Because e-mail is asynchronous people are not pressured into answering immediately. So, although the medium of the discussion is casual, the actual content is considered and researched. Furthermore the emails form an audit trail of every discussion and decision, complete in a way that meeting minutes and formal design documents just aren't. The email archive is a tremendously valuable resource when a new person joins the project and needs to understand a why a particular feature works the way it does. This runs counter to the traditional argument against working at home, which is that we get better communication and better decisions when people are together in the same office. Perhaps we should always communicate by email, even when discussing something with the person at the next desk.

Brian's suggestions for turning internal projects into open source projects are:

  1. Break silos by implementing transparency, not just to the code but to all the project's collateral.
  2. Encourage cross-team collaboration with ad hoc projects and special interest groups.
  3. Tackle monolithic apps and inertia, from the bottom up.

This all requires a different approach to project management, with less focus on Gantt charts and more on managing complex processes (like gardening or air traffic control). So that should be easy then.

Bloggers offline

A number of us Oracle bloggers congregated at the Thirsty Bear for a few sherbets. Strangely few American bloggers showed and none of the Oracle employees. I met Steve Karam, the Oracle Alchemist, who is ridiculously young to be an OCM, and the amazing Lucas Jellema, who being Dutch pronounces his name like a cough and not the way I've been saying it all this time. The party split at 09:00pm into those who wanted to hear Sir Elton and those who didn't. We in the latter camp rounded off the drinking in the traditional British way, which is with a curry. The first place we tried had, bizarrely, stopped serving food (this was before ten). Fortunately the 24-hour Naan and Curry house opposite the Hilton did us proud. It was almost like being in Tooting.