More on DISABLE VALIDATE

Yesterday I blogged about a "bug" with relational integrity. Due to lack of time I didn't really have time to explain why this behaviour occurs.

When we disable the constraint with the proper syntax the constraint is marked as NOT VALIDATED:

SQL> ALTER TABLE temp_child DISABLE CONSTRAINT what_the_fk;

Table altered.

SQL> SELECT status, validated FROM user_constraints u
  2  WHERE table_name= 'TEMP_CHILD'
  3  AND u.constraint_type ='R';
STATUS   VALIDATED
-------- -------------
DISABLED NOT VALIDATED

SQL> 

Whereas, when we disable it the wrong way this doesn't happen:

SQL> ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT what_the_fk;

Table altered.

SQL> SELECT status, validated FROM user_constraints u
  2  WHERE table_name= 'TEMP_CHILD'
  3  AND u.constraint_type ='R';
STATUS   VALIDATED
-------- -------------
DISABLED VALIDATED

SQL> 

The bug occurs because the database trusts the value of VALIDATED rather than actually validating the constraint. So I thought I should see whether it applies to other kinds of constraints. This is what happens with a check constraint:

SQL> create table nn (col1 number)
  2  /

Table created.

SQL> alter table nn add constraint nn_ck check (col1 is not null)
  2  /

Table altered.

SQL> insert into nn values (null)
  2  /
insert into nn values (null)
*
ERROR at line 1:
ORA-02290: check constraint (APC.NN_CK) violated


SQL> alter table nn disable validate constraint nn_ck
  2  /

Table altered.

SQL> insert into nn values (null)
  2  /
insert into nn values (null)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.NN_CK)
disabled and validated


SQL> 

And this with a unique key:

SQL> create table my_nu_tab (col1 number)
  2  /

Table created.

SQL> alter table my_nu_tab add constraint nu_uk unique (col1)
  2  /

Table altered.

SQL> insert into my_nu_tab values (1)
  2  /

1 row created.

SQL> insert into my_nu_tab values (1)
  2  /
insert into my_nu_tab values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.NU_UK) violated


SQL> alter table my_nu_tab disable validate constraint nu_uk
  2  /

Table altered.

SQL> insert into my_nu_tab values (1)
  2  /
insert into my_nu_tab values (1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.NU_UK)
disabled and validated


SQL> 

Interesting. So what happens when we try to insert into the child table when the foreign key is disabled yet validated?

SQL> insert into temp_child values (2, 11)
  2  /
insert into temp_child values (2, 11)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.WHAT_THE_FK)
disabled and validated


SQL> 

So at least the database prevents us from inserting orphaned records into the referencing table. It just doesn't protect the integrity of the referenced table. Which is fair enough I suppose - the foreign key is disabled after all.

I'm trying to think of a scenario in which we would want to disable a constraint but still keep it validated but so far nothing has occurred to me. Obviously there must be a good reason for this. Any suggestions?

Interesting relational integrity bug

Over in the OTN forums, Wilhelm demonstrates a method for disabling foreign key constraints in such a way that we can delete the parent data and then re-enable the foreign key without throwing an ORA-02298 exception.

The proper syntax for disabling a constraint is this:

SQL> ALTER TABLE temp_child DISABLE CONSTRAINT what_the_fk;

Table altered.

SQL>  delete from temp_parent;

2 rows deleted.

SQL>  ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT what_the_fk;
 ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT what_the_fk
                                                   *
ERROR at line 1:
ORA-02298: cannot validate (APC.WHAT_THE_FK) - parent keys not found


SQL>

But if we include the mystical keyword VALIDATE in the disabling command we can corrupt our relational integrity quite nicely:

SQL> INSERT INTO temp_parent VALUES(1);

1 row created.

SQL> INSERT INTO temp_parent VALUES(2);

1 row created.

SQL> ALTER TABLE temp_child ENABLE CONSTRAINT what_the_fk;

Table altered.

SQL> ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT what_the_fk;

Table altered.

SQL> delete from temp_parent;

2 rows deleted.

SQL> ALTER TABLE temp_child ENABLE CONSTRAINT what_the_fk;

Table altered.

SQL> select * from temp_parent;

no rows selected

SQL> 

Questionnaire on Virtual Communities

Imène Ben Yahia, a doctoral student at L'Université Paris IX Dauphine, has posted a questionnaire about participation in online communities, meaning discussion board, newsgroups, usersgroups, etc, focused on software products. That is, they are collecting data on things like the OTN Forums rather than Second Life.

If you feel like helping out there is an English version here. The translation from the original French is a bit odd (it says "editor" meaning "software vendor" or "forum moderator"). The Francophone amongst you may want to check out the French version instead.

OOW2K7 Scheduler Builder open

Those of us fortunate enough to be going to Open World this year can now start planning our days with the conference Schedule Builder. I've begun the process but be warned: it requires a lot of time and mental stamina. In a world which has gone Web2.0 mad, the Open World Schedule Builder is proudly, defiantly Web1.0.

Yes, it's the user-unfriendly single long HTML table which makes it impossible to see which sessions you've booked and which sessions clash. It's the agonising network round trip every time you book a session. And it's the long scroll back from the top of the page after every booking. Oh, and Firefox users will need to use the IE Tab add-on to see the popup boxes but you probably guessed that already. In short, a trying experience, and one which shows the UKOUG agenda builder in a very good light (to be fair the UKOUG sites require the IE Tab too).

Whilst choosing sessions I noticed that just about all the developer sessions are in the Oracle Develop stream. At first I thought this was just Oracle's policy of making all the best stuff a chargeable extra. But it's actually more complicated than that. Looking at the web site it seems that Oracle Develop is not a licenceable pack. Rather it's an semi-autonomous event running alongside Open World, with some overlap (access to the OTN social events, the exhibition hall and the keynotes). So I guess Oracle have decided that not only is there a difference between developers and DBAs, the difference is so vast the two groups require separate conferences.

Web Conferencing with FireFox

I have just signed up for a web conference about the Lifetime Support Policy for 9i Database Release 9.2. Naturally enough Oracle host their own web conferences using Oracle Collaboration Suite. The registration process includes an option to test the configuration of your desktop, to ascertain whether it is compatible with Collab Suite.

Inevitably the test software doesn't work with Firefox. In fact it fails so badly that I couldn't resize the pop-up window to read the failure message. This is the second time this week I have had this problem with web conference software. Earlier this week the culprit host was Interwise. Thank goodness for the Firefox IE add-on. In both cases all I needed to do was add the conference site to IE tab's list of sites.

Firefox now has 20% of the UK browser market and 30% in Europe, and 17% worldwide. Judging by my immediate circle, the take-up is much higher amongst IT professionals. These are precisely the sort of people most likely to sign up for web conferences. Isn't it about time web conferencing started to catch up?

There is a final irony. The Oracle site does have a Quick Tutorial, which specifies Browser—Internet Explorer 5.5 or higher Java—Enabled in the system requirements. Unfortunately the Quick Tutorial (and the Help text) only work in IE. Even the Firefox IE tab renders them as blank pages.

What's up with Oracle WTF?

Last week William Robertson invited me to join the Oracle WTF crew for a drink. This was quite a daunting prospect. They've known each other - socially and as co-workers - for a long time. They're all very sharp, both in Oracle and in communication skills. In short I felt a bit like the new boy at school being invited to join the gang of the coolest kids. Or at least the snarkiest.

As it turned out only William and Adrian Billington could make it, which was a manageable number. This was the first time I had actually met William or Adrian in meatspace. However, our paths have crossed on the web and obviously William is a regular in the OTN forums. So we did already kind of know each other. It was a good evening.

One of the things we talked about was the current stasis in the Oracle WTF blog. William et al had started the enterprise in a flurry of excitement and with the blessing of the owner of a code base full of howlers. So initially they had lots of examples to draw on. The problem is, that as contractors, they have to move on to other clients. It is a bad business strategy for them to make fun of their clients code. This is why the original WTF site tends to feature war stories from years ago and guarantees the anonymity of its submitters.

I understand this completely. As as consultant I operate under the same strictures. True, the other day I did write about a system which had been written for my current client. But I think putting zeroes instead of nulls in a number column is an understandable design decision. A misguided decision but not WTF material.

The upshot is, Oracle WTF needs contributions. And those contributions will have to come from its readership. So please send your Oracle WTFs to William. He'll be most grateful.

Worse than J2EE

There's an interesting thread on the OTN PL/SQL Forum about Real Time Database Design. At least that's how it started. It has now morphed into another pop at J2EE and .NET developers. Zlatko Sirotic has posted a nice satire of the average OO developer's approach to databases:

METHOD 4: sending XML files to OO2EE.Net;
super-fast and clean method that ensures database independence;
we need database independence - maybe one day we must replace our RDBMS with flat files

Which is almost too true to be funny.

I fear my latest contribution to the debate is likely to spoil my chances of getting a Director's tiara to go with my ACE of spades.

It is worth bearing in mind that it is not just OO developers who have a poor understanding of databases. This recent WTF story shows how a VB developer with just the wrong amount of knowledge can inflict a lot worse than J2EE on your system.

What's worse than finding a NULL in a NUMBER column?

Finding half a worm! Oops, wrong punchline. No, the worst thing is finding a zero. Twice in the last week I have had to rewrite some code to handle an application which was using zero as a magic value instead of populating a numeric column with null.

One of these columns was a code which described the nature of some free text held in another column. 1 through 6 were assigned to pre-determined categories but the users can enter text outside of those categories. My program did a lookup to expand those codes with the category description; what I hadn't anticipated was that the rows for extra-mural text would have a code of 0 instead of null. NO_DATA_FOUND. Irritating but simple enough to handle.

The other case was slightly more worrying. Here the column was one of two; the other column had a data type of date. One or the other column or neither could be populated, but not both. Overwhelmingly the date column was populated . But when the date column was null roughly have the number columns were zero. Given the nature of the data it was not credible that half those rows would really have a value of zero. Most of them should have been null. However, for a handful of those records zero was probably a legitimate value. It was just impossible to tell which rows they were.

And that is the problem with "handling" nulls by using magic values instead. The magic values often need special handling themselves. Also they can subvert our data integrity. For instance substituting a null with zero may not affect the calculation of a total but will it will change the value of an average.

Nulls are annoying and it is a good idea to avoid them in a data model. But few of us are prepared to go to 6NF. Thus we have columns for which we cannot assign values. So be it. Much better to face the fact and use nulls than to fill our columns with magic values. In a very real sense disguising nulls in such a fashion is just like having an exception handler to suppress exceptions:

    ...
exception
    when no_data_found then
        null;
    ....

We need to know.

Stupid things to do with triggers: Pt.42 in a series of 94

As is well known, the concept of :OLD values in an INSERT trigger makes no sense. Obviously there is no OLD record in such a scenario case. What is perhaps less well known is that Oracle will let us build an INSERT trigger which references an :OLD value.

SQL> create or replace trigger t1
  2  before insert on t1
  3  for each row
  4  when ((old.col2 is null) or (new.col3 is null))
  5  begin
  6      if :old.col2 is null then
  7          :new.col3 := 'COL2 is null';
  8      elsif :new.col3 is null then
  9          :new.col3 := 'COL3 is null';
 10      end if;
 11  end;
 12  /

Trigger created.

SQL> 

This means we can use the trigger to corrupt our own data without really trying...

SQL> insert into t1 (col1) values (1)
  2  /

1 row created.

SQL> insert into t1 (col1,col2) values (99,56)
  2  /

1 row created.

SQL> select * from t1
  2  /
      COL1       COL2 COL3
---------- ---------- --------------------------------
         1            COL2 is null
        99         56 COL2 is null

SQL>

Obviously it is unlikely that anybody would deliberately set out to do this. But it is the sort of mistake we might make by confusing the scope of variables between the INSERT and UPDATE events. Another good reason for having separate triggers for each event. Or indeed, if you are of the anti-trigger persuasion, another good reason why triggers should never have been invented in the first place.

UKOUG 2007: Chair registration

Yesterday I wrote a blog article saying that the registration to chair conference sessions was open. Unfortunately I was premature on this and have deleted the original article. The UKOUG asks its volunteers who assist throughout the year with SIGs and the conference agenda to select sessions first. Once they have made their selections, the UKOUG may be able to open up the opportunity to members outside of the UKOUG's regular volunteer community.

So I really shouldn't have said it was open to all. I wish to apologise to the UKOUG staff for jumping the gun, and also to anybody who experiences inconvenience or frustration because they acted on my advice.

Now, where did I put my sackcloth and ashes?

Keep clear

Last night I went to the ICA cinema to see Helvetica, a documentary about the typeface. No doubt some of you are wondering why I didn't go to see The Bourne Ultimatum instead, but honestly, what would be the point? I'm sure it's well done, but you know, some fisticuffs, some car chases, Matt Damon pulling that face for the whole film... What is the point? Escapism? Well, this is a world where a man sets himself on fire and then drives a Jeep stuffed with gas canisters through the main entrance of an airline terminal. So in what sense is The Bourne Ultimatum escapist?

Whereas immersing yourself in the slightly arcane world of typography is completely delightful. This is a film which makes you look at the world about you in a completely different way. Unless you're the sort of person who obsesses about fonts you probably aren't conscious of the ubiquity of Helvetica. It is the film's task to make you see how widely-used is the type, and explain why this is the case.

Eduard Hoffman and Max Miedinger consciously designed Helvetica in 1957 to be a modern typeface. It is sans serif, with special attention given to the space around the letter. Each letter has a uniformity. It is clear, simple and straightforward. It is a typeface which is ideal for signage, corporate logos and general usage.

Helvetica has been almost ridiculously successful. The film is stuffed full of different usages of the typeface. Corporate logos, concert flyers, t-shirt slogans, posters, shop fronts, road signs and vehicle labels. The rest of the film features a number of designers talking passionately for or against Helvetica. These contributions are nicely judged: everybody cares deeply about typography but demonstrates an awareness that this might make them seem slightly unhinged.

It is just a joy to listen to articulate intelligent people talk about a topic with passion and humour. Michael Beirut has a fantastic riff on the parlous state of fifties advertising with its jumble of typefaces (especially "nuptial") scripts, goofy logos and lots! of! exclamation! marks! Jonathan Hoefler, pondering the difficulty of evaluating Helvetica, says it's like having an opinion about off-white paint. Inevitably the people who are against Helvetica have the best lines. Erik Spiekermann says he hates Helvetica because it doesn't break any rules, despite the fact that he's German and he likes rules. Paula Scher started designing in the seventies and regarded liking Helvetica in the same league as supporting the Vietnam war. "And the current war?" prompts the interviewer. "Helvetica caused the Iraq war!" she laughs.

Stefan Sagmeister rails against the corporate brochure whose front page consists of lots of white space, with six lines of Helvetica-set text, a small quirky logo in the bottom right hand corner and a picture of a business man. It says "Don't read me. I will bore the shit out of you." I have a lot of sympathy for this view. (Disclosure: LCMG is a company which specifies Helvetica for its corporate publications).

My favourite anecdote came from David Carson, of Raygun magazine. He describes how he was faced with laying out an excruciatingly banal interview with Bryan Ferry. He went through hundreds of fonts trying to find the ideal one to express his feelings for this interview. Finally, at the end of the alphabet, up came Zapf Wingding. Perfect. He showed us the double page spread. A photo of Ferry and - apart from the singer's name in the title - all the text was set in the wingding font. He said we could highlight all the text and convert it to another font - Helvetica? - but we shouldn't bother: it's not worth reading.

As IT people we use fonts all the time: in GUI boilerplate, in documentation, in PowerPoint slides. How often do we actively consider which font to use instead of just accepting the defaults? Well, I expect most of us like to use a mono-spaced font such as Courier when we are programming or including code samples in documents. But beyond that IT is a dreary world of Arial and Times New Roman. Helvetica is a film which tries to communicate why that is a bad thing, why typeface matters and why we should take the time to choose the right one for the task.

Last word. Most of the designers had computers on their desks. Every single desktop or laptop - regardless of whether its owner loved Helvetica's clean simplicity or loathed its corporate conformity - was the same brand. You know which one. I just hope Steve Jobs coughed up some sponds for all that product placement.

Of fake blogs and search engines

A couple of prominent Oracle blog sites have suffered from spam bloggers and plagiarists in the recent past. So we know there is a problem.

Gadi Evron at Securiteam has written a commentary on just how sophisticated spam bloggers are getting these days. I think everybody will find this a worthwhile read, because such sting operations can affect people who comment on blogs as well as people who own them.

Night of the living spamtards

So are there any lessons to be learned from the spamtard ORACLE's adventures in the OTN forums yesterday? Not really. We all know little corners of the internet are vulnerable to individuals with the time and the motivation to be anti-social. This was one adolescent sitting in a Comp Sci class with too much time on their hands (I think in this case we can justly blame the teachers). ORACLE (and at least one other spamtard) posted a lot of silly comments in a number of active threads and started up many more threads of their own. So what? It's not like it was a bot-driven DoS assault. The DB General forum was still usable and none of the other important forums were hit at all. Frankly the forums being down for "routine maintenance" is a bigger pain.

"One monkey don't stop no show" -- Joe Tex

I am therefore surprised by the over-reaction of some of the forum regulars. I agree it would be nice if we could prevent such incidents but actually doing so would be quite difficult. If we trash ORACLE's account today they're back tomorrow with a different Hotmail address. Somebody suggested OTN could block their IP. Well, given that they seem to be posting from a school that might also block other more benignly-motivated students from asking questions. And it certainly won't prevent the spamtard from using some other place - such as an internet cafe - to continue the games.

In a recent article in the Guardian Cory Doctorow explained why effective DRM is impossible to achieve. All it does is punish those people who have actually paid for their digital media. Stopping low level trolls like ORACLE is basically the same case. OTN could put up loads of road blocks which would inconvenience us but won't stop the spamtards. Yes, OTN could appoint trustees to zap every post these gits make, but who wants to be on spam patrol for the whole afternoon? Not me. Much better just to let the spamtards have their fun and wait for the housekeeping bot to tidy up automagically. In the meantime, just ignore them. They'll get bored if nobody reacts to their jibes.

But above all, keep it in perspective. This is not poisoning the well, more like piddling in the paddling pool.

Trolling for laughs

Just recently we have had a troll visiting the OTN DB General forum. They call themselves ORACLE. They're annoying but harmless. Still every evening the Forum moderators eradicate their posts but the next day ORACLE is back. It's just like the Do Long bridge but with disposable e-mail addresses.

Here's an example from today (I won't bother posting the URL, it'll be gone by tomorrow):

Just got my new Oracle today

Oracle 12g, OMG it is so freaking cool.

I can write 9 queries in 5 seconds.

Then I laugh at all of you noobs.

^_^V 

I don't know about you but it reminds me of this recent posting from Dizwell....

Update

Since I posted this I have noticed that ORACLE is really blitzing the forum with postings. I guess they want to be considered more than just an annoying idiot. Still at the rate they're going they should have enough posts to qualify for ACE-hood by the end of the day.

Update 2

Well ORACLE now has control of the OTN forum. So undoubtedly something must be done. Rather helpfully, ORACLE has posted a list of actions which OTN ought to undertake to make life harder for trolls:

On the subject of spammers I suggest that you make so you have responsible oracle users able to ban and delete certain posts. For example, If you where honestly going to stop my spamming you should ban the account instead of just resetting all of the posts the spamming user makes and resetting the name, that is hardly dealing with the problem effectively.
Also, whoever the Forum administrator is. They should try giving more power to users they can trust such as
-N. Gasparatto
-Paul M
-Blushadow
-Hans Forbich (hes funny =P)
You should give them the position know as moderator (these are used on most forums). They should have a certain amount of control on who uses the forum. They should be able to ban (permently immoblilize the account, dont just reset the posts) and delete posts when necessary. Therefore you won't have to wait till your stupid oracle mod comes in and sweeps the place every 24 hours.

I'm slightly miffed that I'm not included in the list of trustees.

Final update of the day

Well the OTN forum housekeeper has woken up and cleared out all the messages posted by ORACLE. But it remains to be seen whether the troll will be back tomorrow.

Meeting William Gibson

Not many novelists bring out the fanboy in me, but Gibson is one of them. Neuromancer was, literally, the book that changed my life. It's why I decided to become a computer programmer.

In the summer of 1986 I was a disillusioned History student about to become a disillusioned History graduate. I had finished my finals, I had no job lined up and I had no idea what I wanted to do with my life. It was in this state of aimlessness that - inspired by an article by William Leith in the NME - I purchased Neuromancer. It was the first pure SF novel I had read in years and it was completely gripping. I can still recall the adrenal rush of the last few chapters, racing through them just to discover how the story ended.

I had read SF as an adolescent but discovering JG Ballard had derailed me: nothing else in the genre could quite match the intensity of his prose. At university I had read mainly non-fiction: there was so much reality to discover, it seemed pointless to read to stuff people had made up. When I was studying European heresy my tutor John Critchley advised me to read Umberto Eco's The Name of the Rose (I was chuffed to explain to him that the blind librarian Jorge of Burgos was a reference to Jorge Luis Borges, because that joke had passed him by). I also read Mishima because I did courses on both medieval and modern Japan, and he seemed appropriate. For light relief I read Italo Calvino's short stories and the Blandings novels of P.G.Wodehouse.

I realise that list probably makes me sound like an insufferable prick. But then I probably was an insufferable prick at university, so that's fair.

I'm not going to do a review here. I presume everybody who reads blogs has read Neuromancer. If that's not the case stop reading this right now and go read it. Like most SF it has dated but not as badly as some. Few SF authors have changed the world. My quasi-namesake Arthur C Clarke did, with his invention of the geosynchronous satellite, but largely his vision has not been realised. No lunar explorers have disinterred mysterious black slabs because there are no bases on the moon. Heck, nobody's even been to the moon for over thirty years. Whereas it's a measure of the impact Gibson's writing has had on the world that he has stopped writing SF and moved into regular fiction without any meaningful change of tone or subject matter. (I guess Bill Gates, Steve Jobs, Michael Dell, Larry Ellison and others have also had something to do with it).

Gibson's insight was to take the glamour which traditionally attached to spaceships and astronauts and transfer it to computers and programmers, who were - it's fair to say - not glamourous. At Exeter the Comp Sci students were pimply-faced youths in brown and beige, as boring as accountants. In fact, more boring: at least there were some girls amongst the accountancy students. Neuromancer changed that image by making computers cool, sexy and edgy. Which is why it has had such a profound effect on modern life. All over the world, pimply-faced youths in brown and beige set about immanentising a world in which computers - and by extension the people who worked with them - were cool sexy and edgy.

Neuromancer was Gibson's first novel. In 1986 it was a bit frustrating to discover that there wasn't anything else to read by him. I just had to wait until Count Zero came out. On the other hand it does mean that every few years there is a new novel to look forward to.

So today I joined the line of people to buy a signed copy of Gibson's new novel, Spook Country, at Forbidden Planet. I told him I was grateful to him for writing Neuromancer because it had directed me into a career in software development. His face crinkled with recognition. "Oh you're one of those guys," he said. Yep, I'm one of those guys.

Open World 2007

In the Comments section of Tim Hall's recent musing on the new two-tier ACE programme, Doug Burns said:

"Once you put together lost wages or fees, accommodation and flights, it doesn’t take long before this becomes a serious financial commitment. I wonder sometimes whether people who attend presentations whilst being on company wages and expenses really appreciate that."

What contractors sometimes fail to appreciate is that it isn't necessarily easier for permies, just different. We still have to ask the company to pick up the tab for our expenses (not to mention lost billing for us consultants), and the company reserves the right to say, "No, you cannot go". For some reason Open World is a harder sale than UKOUG. Perhaps our bosses think going to San Francisco might be more of a jolly than going to Birmingham....

Fortunately I have have some supportive managers at LogicaCMG and my business case has finally been approved. So I'll have to dig out that company polo shirt again.

Oracle WTF is back!

Jubilation sweeps the blogosphere, or at least the small corner of it where Oracleers hang out. Oracle WTF is back in the hands of its rightful owners. Huzzah!

William and others (not least the WordPress groupies out there) might be amused by the news that Blogger's Anti-Spam bots recently managed to suspend one of Google's own blogs. At least we can see that the ASbots act without fear or favour.

Satire

Fake Steve Jobs has been channelling Larry Ellison over the weekend.

"I’m the guy who screwed IBM out of its own database business with its own technology. It was a beautiful thing. I invented Oracle. Maybe you’ve heard of it."

FLE has a pop at Sir Elton John and Marc Benioff; it's not often you'll see those two names in the same sentence.

Asking the right question

Many years ago, before I had even heard of Oracle the database, I studied the I Ching, a different kind of oracle. This book is one of the many products of the period in ancient Chinese history known as The Hundred Schools of Thought. Its authorship is ascribed to the legendary Yellow Emperor various legendary figures (see Chris Gait's comment below) but it is actually the collected thoughts and commentaries of various unknown seers. The text distils Taoism, Confucianism and other strands of Chinese philosophy into an esoteric instruction manual. It is organised into sixty-four chapters, each represented by a symbol consisting of six lines (hence called hexagrams). Each line is either Yin or Yang, and the different proportions of each type of line gives each hexagram its particular nature.

Consulting the I Ching requires us first to frame a question. We then employ a randomness-generating mechanism (such as flipping coins) to find which hexagram will answer our question. The commentaries on the hexagrams use imagery from nature, mythology and society to provide gnomic advice:

• "The dragon that goes too high has regrets."
  
• "When there is hoarfrost underfoot solid ice is not far off."
  
• "Whoever hunts deer without the forester only loses his way in the forest."

We then have to apply these answers to our original question by translating the metaphors. So, if I am hunting a deer, who (or what) is the forester who can guide me through the forest? Perhaps I need to call off the chase until I have found a verderer.

Friends would occasionally ask me to "do" the I Ching for them, because they misunderstood the nature of the book. Although it is often described as a book of divination it is not a fortune telling device like horoscopes or tarot cards. It does not violate the principle of causality; its predictions can only come true if we behave in the way it suggests. A dragon who knows when to stop ascending will have no regrets. So I always told my friends they needed to have a question in mind before they flipped the coins (they didn't have to tell me the question, by the way). If there is no question there can be no answer.

So does it work? Well, the meaning of the answer derives from the interpretation of it in the light of the original question. A focused request such as "Should I take this new job?" is more likely to produce illumination than something vague like "Why am I unhappy?". But meaning is in the mind of the questioner. Sometimes the hexagrams seemed spookily appropriate to the question. Other times they seemed to have no relevance at all. On one occasion my interpretation of the chosen text differed sharply from the questioner's interpretation.

The I Ching has a world view and a specific definition of correct behaviour. But I don't think somebody has to be a practicing Taoist or believe in the literal existence of Yin and Yang in order to gain benefit from the book. Finding harmony between contending forces is a common idea. For example, here is an article by WTF's Alex Papadimoulis on striking the balance between hard coding and soft coding.

I don't do I Ching consultations any more. Now I just spend my time in the OTN forums instructing herberts like this and this in the art of asking questions properly. Different oracle, same old stuff.

Software Contract Law

I hesitate to describe an article on computer law, especially one about the enforcement of software contracts, as "interesting" but there is no doubt that this is an important area for all of us. Of course, one side applies primarily to product vendors and consulting developers but we are all of us on the customer side of the fence. So everybody ought to read Cem Kaner's piece on an American Law Institute initiative to write a new Principles of the Law of Software Contracts.

He has also published his presentation to the Conference of the Association for Software Testing on this topic. This contains lots of insights into the evaluation of licensing law but it does also contain screens full of legal small print (literally).

The focus of Cem's analysis is on the enforcement of warranties. In particular, the proposals would make software suppliers liable for damages suffered by their customers as a result of bugs which were known to the supplier but not disclosed to the customer. You can see why lawyers might be keen on this proposal: Have you suffered from Blue Screen Of Death? Turn those bugs into £££! Calls Claims Direct now!

On the other hand, certain things would become freer. For instance, under a "standard-form transaction" (i.e. one used by the general public, as opposed to an individually negotiated one) companies such as Oracle would be unable to prevent the publication of the unauthorised benchmarks. There might also be implications for certain aspects of the OTN licence, such as the recently discussed confusion over whether we can use a database downloaded from OTN at home for instructional purposes.

Roy Batty writes ...

It's official - I am not a robot. Blogger's team of blade runners has given my blog a Voigt-Kampff test and determined that it is indeed the work of a human being. This verification was necessary because last week Blog*Spot put me under an Anti-Spam-Blog Order. Their anti-spam bots ( ASbots???) had locked my blog as it has "characteristics of a spam blog." Hmmpfff. So what are those characteristics?

"spam blogs ... can be recognized by their irrelevant, repetitive, or nonsensical text", which is possibly the rudest thing anybody has ever said about Radio Free Tooting. It was the other characteristic which rang a bell: "...along with a large number of links, usually all pointing to a single site." The opening paragraph of my last post before the ASBO descended contained three links to pages on BCS SPA web-sites so I guess that's why the ASbots suspended my posting rights. This seems a rather tenuous reason but it's the best I can find. The ASbots don't have to offer explanations.

Spam blogs or - ugly neologism alert! - splogs are an increasing nuisance^[1]. Given the number of sites which require monitoring it was inevitable that the task of policing Blog*Spot would be delegated to automatons. Still it seems strange that the giant brains who invented page-ranking can't come up with some better heuristics: perhaps checking the Google Analytics reports and my Technorati rankings, or even just looking at my posting history. On the bright side, I was only off the air for a day or so, and it's not like I had anything I needed to say.

One final thing. The ASbots don't send an e-mail. I suppose this is because most of the sites they bust really are sploggers, so notification would just generate a wasteful amount of spam e-mails. You only find out you've been ASBO-ed when you sign in to post something. The order includes this sentence:

"If we do not hear from you, though, we will remove your blog from Blog*Spot within a few weeks."

Another good reason to have a backup of your deathless prose.

---

{1} - Especially for Blog*Spot. Apparently it's not such a big deal for WordPress. Although whether this is due to WordPress's superior technology or just because Blogger has the biggest mindshare is open to question.

MiniSPA 2007: Better than working

I took yesterday off to attend the 2007 MiniSPA conference. This was a greatest hits compilation of sessions from this year's SPA conference. I almost typed "presentation" but relatively few sessions actually consist of a speaker at the front of the room reading PowerPoint slides to a passive audience. The SPA principles value interactivity and participation. Instead there is a variety of sessions types, most of which involve the audience in some way; tutorials, case studies, workshops, panel discussions, simulations and goldfish bowls. So agenda decisions have another dimension ("how much thinking do I feel like doing?").

The opening session was a think tank. We were divided into teams and each team had to complete the following sentence in no more than ten words:

"Hey, I hear your business depends on delivering software. Well the most important thing about software development is ..."

Each team had to agree on its own phrase; then everybody voted on the phrase they liked the best. The secret of these things is to come up with something snappy and comprehensible. One team came up with a suggestion which had three clauses and used bullet points. That is a dead loss. You can't buttonhole somebody in the corridor and then ask them to wait whilst you boot your laptop and fire up PowerPoint. Here are a few of the final sentences.

• If it's any good, software lasts a long time.
  
• It encodes people's knowledge of the business to provide value.
  
• Get the best project manager you can.
  
• It's about people not computers.
  

That last slogan was the one for which most people voted, thus proving that people repsond to the clarity and focus which abstraction provides.

Designing Collaborative Workspaces

Mike Hunt (Mandu Ltd) led this workshop. Working in teams we had to build models of workspaces out of art card, Lego, Plasticine, Playmobil and various other craft materials. In the first round we had to build a bad working environment, then in the second round we had to build a good workspace. Although each team had a different scenario, the various models had certain common traits. The bad environments all tended to be cramped with people working in fenced-off isolation and with inadequate space for intra-team communication. Fans and other symptoms of poor air-condition featured a lot. The good workspaces were all spacious, with different rooms for different work modes. Bespoke desks and lots of whiteboards were common, and fresh fruit and/or baked goods were provided.

The striking thing was how much closer to real life the cartoon bad environments were. Most of the laughs came from recognition. The good environments on the other hand were largely wish fulfillment, and not just the ready availability of pastries. Floor space is expensive, particularly in London, and I doubt whether most companies would be prepared to devote so much square footage to pamper the developers. It is also unlikely that minions would be given so control over their own working environments. I was also amused to see that I had collaborated in designing an office which was basically open plan. I currently work in an open plan office (albeit cramped), and it can be very hard to concentrate. Sometimes a walled off cubicle seems a very attractive notion,

One interesting aspect of the process was how each team used the materials provided. In the first round we all tended to use the stuff closest to hand. The team with the Plasticine on its table had lots of blobs. Another team had been very creative with card. My team had the Lego so our office was largely built out of plastic bricks. However in the second round we had seen how the other teams had used different materials, so the good workspaces were a lot more heterogeneous in their construction. One team devoted lots of time to writing down the characteristics of their environments. Not surprisingly their models were the least-well realised. An object lesson in the perils of Big Design Up Front.

This session vindicated my decision to take a day's leave to attend the conference. "Logo, Plasticine and Playmobil? And you're trying to say you're working? Get outta here!"

Key lesson: developers want to be free range

Serious JavaScript

This tutorial was an attempt by David Harvey (Sibelius Software) and Peter Marks (indie) to persuade us that JavaScript is a proper richly-featured programming language, and not just a mechanism for undermining the robustness and security of web sites. I'm not sure they entirely succeeded. It didn't take long to discover some quirks in the language. For instance how JavaScript doesn't seem to be consistent in how it evaluates empty or undefined things.

JavaScript is an embedded language. It has no I/O routines of its own and a very small core library. It inherits most of its capabilities from the environment it runs in, which is part of the reason it has such a poor press. We normally come across it in browsers, which are Teh Suck as programming platforms.

JavaScript rarely raises exceptions. For instance strings are immutable, but we can assign to a string without an exception being raised (the assignment is just ignored). Another example: dividing 1 by zero returns infinity, a magic value like undefined or NaN. I suspect this tolerance of wrong code explains why so many JS enabled web-pages seem to hurl.

Key lesson: JavaScript - it's not just for irritating pop ups.

The Scoping Game

The last session I attended was a simulation run by Mark Delgarno (Acumen Software). Again we were in teams. The point of the game was to develop a range of mobile telephones. Marketing had provided us with a list of models, with mandatory and optional features and development costs. Each team started with the same pot of money. The challenge was to decide which features we would develop for a specific product, which we develop as reusable components and which we wouldn't bother with at all. The game was played over two rounds, with our revenue from the first round's models funding working on the second round's new models. Part of the fun was that we only knew the specs for the first three models. So we could hazard guesses about which features would be useful in the second round but we couldn't be sure.

Our team (Yodafone) spent a lot of time discussing the merits of developing Java as a reusable component in the first round, because the marketing brief hinted that it would be on increasing importance in the second round models. However, of the first round models, only one could use Java, which meant a return of a $2m on an outlay of $10m. So we decided instead to develop a reusable SMS component and a product-specific monochrome display for the starter model. We were the only team to bother with the entry-level model (I blame Idiot Toys). But our strategy paid off because Yodafone won the first round which meant we had more money than the others to spend in the second round.

When we got to see the next three models we had to develop a Java component, because it was mandatory for one of the models. However it was also a lucrative optional feature for the other two. So developing it as a reusable component in the second round still generated a profit. Building it earlier would have cost too much money without generating compensating revenue, whereas the reusable SMS component paid for itself in the first round and made lots of money in the second round too. The upshot was Yodafone actually increased its lead at the end of the game. Win or not win, there is no try.

Obviously this simulation is not very realistic. In real life it is often difficult to assign costs and predict returns in such a clear cut fashion. But it was a pointed lesson in making the best of constrained resources. I think something like this game would be helpful as a warming-up exercise to play with users at the start of a requirements workshop.

Key lesson: YAGNI rules in even the unlikeliest of places

Lessons for the UKOUG

Overall I found this to be a very invigorating day. I'll be honest and say that not much of it will be immediately relevant to me in my day job, but the MiniSPA experience gives me some lessons for me as chair of the UKOUG Dvelopment Engineering SIG.

With the UKOUG the rooms always have serried ranks of chairs facing a screen. By contrast the SPA rooms are setup on the presumption of interactivity, with six chairs arranged around smallish tables. In the UKOUG we ask every presenter for their PowerPoint before the day, so we can print them out and put them in the delegates' packs. The feedback form even includes a question about the quality of the presenter's slides. At the SPA only one of the three sessions I attended featured any PowerPoint at all, and that was just to set the scene. The session chairs often weren't presenting at all; rather they were acting as facilitators.

The other thing was that the sessions were about a variety of things. In the UKOUG we tend to have presentations about Oracle's product sets. So it's usually a cookbook format: how to tune a query, how to code a drop down list in JSF, how to call a web service. It's all good, useful stuff which people need to know but it does rather tend to induce a passive receptivity in the audience. By contrast, these SPA sessions tended to deal with practices, and in particular practice improvement. The games and exercises weren't a gimmick: active engagement with the topics stimulated creative thinking, which is the start of the improvement process.

So the challenge for me is to inject some of this excitement into the UKOUG or at least the DE SIG.

The Unanswered Question

In his introduction to the day, the SPA chair David Harvey said that developing software was the third most fun thing we could do with other people. I never did manage to ask him what the second thing was....

A fresh view on Oracle licencing

Oracle licencing is an arcane area, which has given us all much puzzlement and hilarity over the years. The abstracts submitted for the UKOUG conference included two papers attempting to explain Oracle's policies. Recently, Mark Brinstead has stirred up the Oracle blogosphere with his open letter on the licencing of the AWR and ASH packages. Personally I would like to see Partitioning brought into the Enterprise Edition but I'm not holding my breath.

Niall Litchfield has come up with an alternative insight into the relative value of Oracle licences. His article comments on Kevin Closson's analysis of a recent TPC-C benchmark which compared Oracle and MS SQL Server. Niall noted that the benchmark was undertaken using Oracle Standard Edition rather than Enterprise Edition. Kevin's conclusions related to value for money so Niall has done the maths for an EE licence instead, with interesting results. Not the least of which is:

"One Oracle EE license equates to 32gb Ram and 400 disk drives in round numbers."

Update

One of my colleagues points out that IBM's Quad-Core servers qualify for Standard Edition licencing, which could push the SE price performance even further.

Don't only CONNECT

What's wrong with this code?

create user joe_soap identified by password
/
grant create session, connect to joe_soap
/

Of course, CONNECT is a role which (prior to 10g) has a standard set of privileges granted to it, including CREATE SESSION as well as CREATE TABLE, CREATE SEQUENCE, etc. So there is no need to also explicitly grant CREATE SESSION.

The use of CONNECT role has long been discouraged because lazy DBAs and system designers - present company excepted - tended to grant it by default rather than considering which system privileges a user actually needed. As it happens, the CONNECT set of privileges is wrong for both developer accounts and regular user accounts in almost all cases.

CONNECT is wrong for developers because it is incomplete - it doesn't include more recent privileges like CREATE TRIGGER or CREATE TYPE. Also, developers ought to have the privileges granted to their accounts directly, so that they can build stored procedures on those privileges. CONNECT is wrong for users because most users should not be creating tables or other objects. Users who are application owners and need to create database objects should be granted the necessary minimum of system privileges.

In 10g Oracle officially deprecated the CONNECT role. It now just has the CREATE SESSION privilege. This is sensible but I know from the OTN forums that it catches out some people. These are the people who haven't read the Upgrade docs. So reactively lazy DBAs will be protected from one small consequence of their (in)action. Proactively lazy DBAs will probably just write a script to create a replacement role and grant all the privileges to that instead. But at least they've chosen that path.

Marketing WTF

The Register reports on a bizarre Oracle marketing campaign: Alan Armstrong received a cardboard "laptop" full of shredded paper. It appears to be part of a marketing campaign for Oracle's Enterprise Content Management product. The trouble is, stunts like this rather invite waggish retorts about the cost of Oracle licences.

Papa's got a brand new BAAG

The BAAG Party (Battle Against Any Guess) is a recent initiative by Pythian's Alex Gorbachev. As the name suggests, its prime purpose is to promote the solving of database performance problems through evidence gathering and informed deduction rather than by guessing. After all, as Alex wrote in reply to an Oracle-L posting yesterday:

"Why on earth do we still rely on guesswork in other troubleshooting tasks especially when more deterministic paths are available?"

The thread in question had been started by somebody who had a query which had started running slowly after a a couple of months of satisfactory performance; furthermore the errant query requires a lot of CPU. What, asked Joe Armstrong-Champ, might be the cause? Several Oracle-L regulars replied with things which could cause excessive CPU consumption. Even Cary Milsap, who through his work with Hotsos might be regarded as the Pope of evidence-based tuning chipped in a few guesses of his own. As Alex pointed out, nobody replied with suggestions for investigating the problem in a methodical fashion.

Thinking - we've heard of it

So guessing is obviously innate. Why might that be? Well, Michael "Rands" Lopp, in a classic Rands In Repose article, has the answer:

"Why can't you think when you're busy? Because you're not thinking.. you're reacting."

Rands' argument is that in times of emergency we don't think. Rather, we flip through a mental rolodex of similar experiences and do whatever it was we did last time. In his words, "Panic is the mother of the path of least resistance". This is why the forums and listservers are the first resort of weak swimmers. They do not have a catalogue of previous experiences to draw on. So they simply post a half-baked question, probably with an generic subject - URGENT! PLZ HELP!!!!! - and a message usually lacking both useful information and any regard for mixed case. The galling thing is that we often respond to their panic with guesses, which is Teh Suck. There has to be a better way.

In his article, Rands proposes that we do are thinking at the most conducive time. Coming from a product perspective, his time for deep thinking is right after a major release, when the lessons are still fresh in everybody's mind and nobody is yet panicking about the next deliverable. Then is the time to think about what went wrong, how things could have been done better and to put a plan of action in place to avoid those things next time. Other jobs have other reflection points.

Red Five, where are you?

A couple of weeks back I got to hear Spike Jepson talk about his time as leader of the Red Arrows, the RAF's acrobatic display team. After every sortie (practice flight or real display) the team gathers for a debriefing. The team leader kicks off the session by going over his own mistakes. This creates an atmosphere in which the other team members can admit their bloomers without recrimination. This is crucial for the next step, which is to address the underlying issues and devise strategies for avoiding them next time.

The debriefing session finishes off with the team leader lobbing a problem at somebody - "Red two, we're coming out of a diagonal somerset into an obverse corkscrew over the heads of ten thousand people at Eastbourne when your engine catches fire. What do you do?" The team member then has to step through every action he would take to minimise the risks to himself, the rest of the team and the watching crowds, whilst not disrupting the display too much. In other words, rehearse the emergency procedure in a situation of calm, when no lives are at stake. Consequently, everybody knows what to do in a real emergency. Because when you're hurtling through the air at 400mph mere inches from several other airplanes there is no time for thinking, only for reacting.

Obviously we don't operate at such extremes. But the principle still holds true. We all have some form of mental toolkit which we bring to bear on tuning problems on our own databases. The most advanced will have a set of Perl utilities, tailored SQL scripts and an archive of benchmarks; the stragglers will have bookmarked some URLs on the Wait Event interface. The thing is, when we are faced with somebody else's problem it is quicker to toss out a few guesses than it is to properly engage with them. If your MO is predicated on having archived performance profiles which you have custom generated for your system it is hard to know where to start with somebody who hasn't even heard of Statspack. After all, this response is an e-mail or a forum post squeezed in during coffee time: nobody has the time to write a comprehensive explanation targeted to the original poster's specific problem.

This is where BAAG can help. It provides an obvious focal point for people who want to make things better and who have the required expertise. In the long run BAAG ought to be a compendium of resources and advice for the guess-prone. But as a starting point I think it should have a comprehensive (and comprehensible) list of questions we need to ask every poster with a performance issue. For instance

• Which version of the database are you using?
  
• Which version of which operating system?
  
• What sort of application is it?
  
• Is this problem intermittent or reliably reproducible?
  
• Does this problem occur on all instances of your application or just in live?
  
• Did this query used to perform well? If so, what has changed?
  
• Do you have benchmarks (eg Statspack archives) for your system?
  
• Have you run 10046 and 10053 traces against this statement?
  

Once BAAG has such a list of questions we can respond to ill-formed posts simply by linking to it. Requiring the OP to answer these questions means we are forcing them to learn how to solve their own problems. Of course, it is incumbent on BAAG members to exercise willpower and not succumb to offering random suggestions.

After it, if it is truly the Battle Against Any Guess, then it doesn't just apply to when we ourselves are in shtook. We have to fight guesswork when we're troubleshooting other people's problems too.

Where does he find the time?

Jonathan Lewis has started answering questions in the OTN Forums. A normal person might think that running the Co-operative FAQ site, writing a blog, answering questions in the Oracle-L listserver and the comp.database.oracle.server forum, being a director of the UKOUG, writing award-winning books and holding down a proper job might be enough to fill all the hours of the day.

But not for Jonathan. He obviously has a few minutes left over which he wants to spend explaining the pointlessness of rebuilding indexes to newbies. Or perhaps he uses voice-recognition software to dictate articles in his sleep. Whatever, I guess us forum old lags will have to look to our game.

SET TRANSACTION READ TFM

Recently I've been having to log on the production server as SYSTEM (don't ask why). Obviously, this is a risky thing to do, so I have taken to running

set transaction read only
/

at the start of the session. This prevents me accidentally dropping a key table due to a sudden moment of madness (or just because I have lost track of which PuTTY session is logged on to which database).

This morning I was monitoring the status of a job going through the system. It seemed to have been stuck on one stage for over half an hour. I was worried because this stage ought to have taken a few seconds. I checked the V$SESSION_WAIT view and there was loads of activity: a different wait event every time I queried on that session.

Of course, what I had forgotten was that setting the transaction to READ ONLY doesn't just prevent me executing DML or DDL [see update below - APC] in the session: it also sets the isolation level. So (in the words of the documentation) "All subsequent queries in that transaction only see changes committed before the transaction began." No wonder I wasn't seeing any changes in the status table data! Obviously the V$ views, being dynamic views on the X$ tables, have a back door through the isolation level. Which I suppose makes sense but it could confuse a stupid person.

One to file under Oracle Things I Shouldn't Forget.

Update

In his comment Hemant Chitale points out that SET TRANSACTION READ ONLY does not prevent DDL (because DDL issues an implicit commit and so ends the transaction). Actually in real life I really just worry about changing production data; I just used the DROP TABLE example for dramatic effect. But of course, this is no excuse for not writing something wrong in article, especially as the documentation I linked to makes that very point.

As an ironist I can only applaud the name I gave the article.

UKOUG 2007: Abstracts redux

Well, I have finished judging all the abstracts in my stack (214 of the blighters!). How are you all doing? According to the latest e-mail from the UKOUG back office there's still a big chunk of abstracts which nobody has evaluated. I would guess these are mainly in the Apps domain, but I could be wrong. As I mentioned previously it is important for as many people as possible to vote on the abstracts, so if you are in the eligible camp and still haven't checked in, please find the time over the next few days (judging closes Monday 2nd July, 10.00am BST).

This week I have been looking at the database server papers. The usual suspects - tuning, RAC, RMAN - are present in the usual numbers. But there are some new hot topics (several people submitting papers in the same vein) for the database too. They are:

• Virtualisation
  
• Data and Audit Vault
  
• Linux administration
  
• 11g New Features
  

Hmmm, there must be a good reason why we haven't had papers on that last one before now....

Evaluating the abstracts is an interesting exercise. We get to see the range of topics which people feel strongly about (because nobody bothers to volunteer to spend the time and effort to talk about something which bores them). And those of us who have also submitted papers get to size up the opposition.

UKOUG DE SIG 14-JUN-2007: The Correct use of SOAP

I didn't plan for this SIG to be streamed around Web Services and SOA. But I got offered three presentations which focused on this space. So I made a virtue of it, and requested my last two volunteers to shape their talks around the concept too. This was a bit risky but SOA is a mature concept now and people are actually implementing web services in anger, so it seemed like a good idea. In the end I'm not sure whether it paid off. On the one hand the turnout was quite low, even by the standards of the Birmingham SIG (which is the least-well attended of the three meetings we run). On the other hand pretty much everybody - delegates and speakers - seemed to like the idea of a coherent agenda. Most of the attendees were still there for the final session, which to be frank is a rather rare occurrence.

The presentations

The opening session was a SIG stalwart, Oracle's own Grant Ronald. One of Grant's recurring themes is the importance of extracting additional value from Forms apps migrated to the web by taking advantage of the additional capabilities of the application server. To this end he talked about including Forms applications in BPEL workflows. The actual mechanics of this are a snip: a JDeveloper wizard generates the Java client from the WSDL URL and the Forms Builder java importer wizard generates the PL/SQL wrapper for it.

My heart sank a bit when I saw that Grant's powerpoint included the orchestration of the Star Loans/United Loans demo: if I had a loyalty stamp for every time I've sat through that I'd be entitled to a free cappuccino by now. Fortunately for us Grant had actually worked up a new demo. Unfortunately for him this demo was rather ambitious. There were two laptops, one running Forms server and the other running a BPEL server, and a web service client calling an external web service (i.e. on the actual interweb) which sent an SMS message to his mobile phone. Even the simplest demos can go wrong, but that was just asking for trouble. All of which explains why Grant was a trifle stressed by the time the meeting opened. He had been wrestling with his set-up for a couple of hours whilst we stood around drinking coffee and relaxing after our journeys to BVP. In the end Grant just about got away with it.

Jonathan Elland from Oracle talked about E-Content Management, which turned out to be Enterprise Content Management. Yep, a new meaning for "e-"! This is set to be a major thing in Oracle EMEA. ECM is about managing unstructured information (documents, email, images, etc) which represent roughly 80% of the information owned by most organisations, and which tends to be unmanaged in most organisations. This is the "infoglut" (thank you Gartner). ECM consists mainly of putting this stuff in a database and managing it the way we manage structured information (data). There's more to than that of course, because the tools are different (MS Word rather than a data input form). Most users won't want to know about ECM, they won't want to go through additional hoops before they can read a report from Pat in Accounting. ECM is on the cusp of becoming a service like e-mail. It's infrastructure rather an application per se. At this point Jonathan mentioned Web 2.0 and Enterprise 2.0, thereby ensuring that the SIG was on the cutting edge. Anyway, ECM is a happening thing and the big vendors are buying up smaller niche companies with a view to offering complete content management packages. Oracle have just purchased Stellent, for the products and expertise rather the customer base. This will considerably boost Oracle's ECM profile, once they have got Stellent's products integrated into Oracle's architecture. So be prepared to hear lots about "Fusion Content Server" (before we've even got Fusion Middleware sorted). In the meantime some of the demos still haven't been branded with Oracle logos throughout.

After Coffee 2.0 it was time for David Rowe from Rocela to talk about securing web services. The first half of Dave's presentation dealt with the web security standards. The purpose of web services is to allow clients to communicate with servers in a loosely coupled fashion. This is done through SOAP, the Simple Object Access Protocol; the server has to specify all the information it requires, which the client has to include in the message header. However, the initial web service protocols didn't cover security, so transactions were anonymous, unauditable and in clear. No wonder early implementations went no further than checking the weather in Alicante. Then in 2004 OASIS published a series of web security standards. These specify protocols to meet the ISO requirements for secure systems: identification, authentication, authorisation, integrity, confidentiality, auditing and non-repudiation. The protocols are quite complicated. Being XML the end product is extremely verbose. Fortunately JDeveloper 10.3.1 provides wizards to generate any the necessary tokens. David was in the unenviable task of presenting a technical presentation when the techie person who wrote it can't actually make the meeting. So under the circumstances he did well with the demonstration. He rounded off with a discussion of OWSM, Oracle's Web Service Manager (and not Optimized Weapon System Management as you may have thought). It certainly looks as though secure web services are viable; now all I need to know is how to decide between all the different options. X.509? SAML? XML Signature? It's like they speak some kind of crazy moon language!

Oracle's Danny Roach talked about consuming web services in PL/SQL. This was a case study based on work done for a charity. The charity has a website with a shop and a CRM database to manage both the website's customers and the charity's members. The CRM database is a proprietary third-party product; the vendors would not allow the website to communicate directly with the datavase. So the process looks like this:

website -> e-mail -> human operator -> CRM database

However, the database vendor was prepared to expose certain functions through a web service interface which the website could then use. Danny's presentation covered the steps necessary to implement the service calls in PL/SQL using the UTL_HTTP package. Refreshingly the code was written by human beings rather than being generated through a wizard. This was a useful talk, because it shows how modern techniques can be used to solve old problems in fresh ways.

The afternoon was rounded off by Microsoft's Xen Lategan walking bravely into the lion's den. He was good-humoured about his company's "harmonious co-existence" with Oracle. This was the first time the DE SIG had hosted a talk from a Microsoftee and I think it's an experiment we need to repeat. Xen was talking about BizTalk server, which is the MS equivalent of BPEL. Indeed the entire Microsoft stack is full of boxes whose names are unfamiliar but which are equivalent to something in the Oracle or J2EE architectures. Along the way Xen explained the mystery of Microsoft's naming mechanism. The year in the product names - Word 2003, BizTalk Server 2006 - has nothing to do with the year of release. Rather it refers to the MS engineering compliancies to which the product has to conform. Making an internal QA conformance part of a product's branding reveals a lot about Microsoft's attitude towards the market. Anyway, Xen's presentation gave us an opportunity to compare Visual Studio, the .NET IDE, with Oracle's JDeveloper IDE. In terms of layout, they look quite similar, but VS's deep integration with the Windows OS gives it a slicker look and (probably) quicker responses times. The "Oracle.Net 3.0 Adapter experience" refers to the fact that prior to this latest release, BizTalk only supported an Oracle ODBC connection, which is a rather unsatisfactory way to manage enterprise-level applications. The new version has a connector which wraps Oracle's own .NET data provider. Apart from supporting transactions, this means the applications talk to the database directly through TNS rather than needing an ODBC DNS. Xen's demonstration showed how easy it is to switch a BizTalk service from writing to a MSSQL database to an Oracle database. As Xen joked, it's a very easy migration to make.

The wash-up

I think the themed day worked well. A couple of questions to Grant at the end of the first presentation were actually covered in later presentations. Similarly speakers in subsequent talks were able to reinforce or contrast points they wanted to make by referring to earlier sessions. During the day several delegates asked me whether I was going to run further themed days, because they were finding this one so useful. I am tempted. There are just two sticking points. I need to find themes which can be supported across an entire day without boring people And I need to find themes which will attract more than twelve people. I am open to suggestions.

UKOUG 2007: Judging the abstracts

The Call For Papers for the 2007 UKOUG Annual Conference has closed and the next phase has begun: judging the submissions. Unlike certain other conferences I could mention the judging process for the UKOUG is quite open. If you are on the UKOUG committee or if you submitted a paper you are entitled to act as a judge (you won't be allowed to mark your own abstracts). If you fall into either category and you haven't already registered you can do so on the conference website. Please do so: the more people who evaluate the submissions, the more useful the marks will be when it comes to making the final selection.

The marking categories have been expanded. Last year there were only four gradations and the majority of presentations were marked as 1. Which makes it hard to distinguish the very good from the run-of-the-mill. This year there are six grades, ranging from Must have to Don't bother, with some helpful variations in the middle range. So, with a bit of luck we'll get better differentiation of marks.

I have just been doing my bit. There are some very interesting submissions (and a few dogs). In the development stream the hot topics (the ones with the most submissions by different people) are:

• Application Express
  
• Incorporating AJAX into ADF applications
  
• Migrating client/server Forms apps to the web
  

Once again, not that much on PL/SQL or Java outside of building UIs. On the other hand this year had several submissions on process (as opposed to "cookbook" presentations) which is a hopeful sign.

I have tried to reflect the feedback from the DE SIGs, to select papers which I know address common concerns of the delegates, even though they might bore me. But after a while the abstracts for certain topics (such as AJAX and ADF) all start to read the same. And of course, my take on what constitutes a Must have presentation is idiosyncratic. That's why we need more judges.

Announcement: UKOUG DE SIG 14-JUN-2007

It's just over four weeks until the second DE SIG of the year. It's the June meeting so it must be the Midlands; specifically the Oracle offices at Blythe Valley Park.

The agenda is focused on Web Services and Service Oriented Architecture. I didn't really plan it that way. It just so happened that the range of presentations I was offered seemed to coalesce around this space. There has been a lot of hype around SOA for several years. This intensified a couple of years ago when Oracle acquired Collaxa BPEL Server (now BPEL Process Manager). I think it's fair to say that the buzzstorm was met with a certain amount of scepticism from the more jaded amongst us. However, SOA and BPEL are still here, and probably most of us have at least considered using a web service to share data or functionality between disjunct systems. So now seems like a good time to catch up with what's going on.

Jon Ellard from Oracle kicks off the SIG with a presentation on their offerings in the E-Content Management arena. Then Grant Ronald continues his crusade to pull Forms users into the twenty-first century with a presentation on integrating BPEL with Oracle Forms. From the opposing camp we are going to have Xen Lategan from Microsoft talking on integrating applications with BizTalk; I'm not sure what the Oracle spin is on that one, but I'm assured there will be one. We have a yet-to-be-named speaker, this time from Rocela, presenting on securing Web Services. This is a very interesting area, because I think the apparent insecurity of web services has been a barrier to more widespread adoption. We have a third speaker from Oracle, Danny Roach (who has obviously got a taste for presenting) giving us a case study on embedding web services within web-based applications.

It would be nice to see lots of people in BVP on June 14th. Every organisation which has a UKOUG membership can send one delegate for free to this meeting, so petition your boss to attend. It's not a jolly, it's training on the cheap ;-) If your organisation is based in the UK and uses Oracle but isn't a member of the UKOUG, why the heck isn't it?

Update

Xen's talk on Microsoft BizTalk now has a title which makes plain the Oracle spin: BizTalk Integration and the Oracle NET 3.0 Adapter Experience. I hope to understand what that means by the end of the SIG ;)

You wouldn't let it lie!

The OTN forum is still seeing activity on the notorious long running thread URGENT URGENT PLZ READ B4 OTHERS VERY URGENT NO TIME WASTERS. OTN regular Simon Galaxy asks why.

"I think that sometimes the most irrelevant threads present the most posts.....!!!!!!!"

I don't think this is wholly fair. Some of the long running threads contain useful nuggets of learning and opinion for which they are worth mining. They usually start with a wacky theory or application architecture proposed by somebody is not familiar with Oracle in particular or RDBMS in general. Examples include the 'code class' rdbms, nulls and empty strings or the key-value pair design. Usually such threads die out quickly after the initial response. But sometimes, as with the threads cited above, the OP is possessed of evangelical wrongheadedness: they need to prove us wrong and we need to get them to see sense. That's where the momentum comes from. Eventually the posts snowball because they persistently feature in the Popular Discussions panel, so more people read them and respond. Threads like these become too unwieldy to follow, but often William Roberston and his gang fillet the best ones for posting on Oracle WTF.

On the other hand there are zombie threads which are just fuelled by stupidity, no - let's be generous - naivety. One such was the gone but not missed "plz send me the coding standards" thread, Somebody searching the web for PL%2FSQL+coding+standards finds a seemingly helpful post. Unfortunately they just respond to the OP without registering the thread's bloated number of posts or its original posting date. These can run on for years, although occasionally the OTN moderators do terminate them.

The NO TIME WASTERS thread is really just an opportunity for some old forum lags to let off some steam. And why not. It was a free shot and better than RTFM-ing a genuine but hapless seeker. The interesting thing about this thread is how many people who have never or only rarely posted to the OTN forums felt the need to weigh in with their opinion of the OP. That's the Tom Kyte effect. Whilst it's always nice to see have visitors it seems a pity that these espontaneos couldn't find something more worthwhile to do with their first post than abuse some dumb troll who had already been roundly abused by many others.

It's instructive to compare this thread with another recent one: Oracle is the devil itself . The opening post goes:

I hate this stupid database engine and all its relevant stuff. Microsoft SQL Server 2005 is the king! I could kill my boss, because I have to work with this ****! Hate it!

Obviously this chap's real problem is with his boss, who is making him use Oracle without giving him the necessary training and support. But he felt unable to express this to his boss, so he flames the OTN forums instead. The thing is, people engaged with him, calmed him down and eventually he resolved his issue. As you might have guessed, it was a bug in his .Net application and nothing to do with the underlying Oracle database. However, people continued posting to this thread, perhaps because it had a catchy name, and it became one of the Forum's more thoughtful examinations of the relative merits of MS SQL Server and Oracle as RDBMS products.

Working under constraints

This lunchtime I answered a question on the OTN PL/SQL forum asking how best to do something without using triggers, when using triggers was patently the best way to satisfy the task at hand. As I occasionally do in these cases, I asked whether the ban on using triggers was a OuLiPo thing. Ouvroir de Littérature Potentielle was a school of literature in which arbitrary constraints are used to drive creativity. For instance La Disparition by Georges Perec is a novel which contains no words with the letter e. One OuLiPo site features this quote from Igor Stravinsky.

"The more constraints one imposes, the more one frees oneself of the chains that shackle the spirit... the arbitrariness of the constraint only serves to obtain precision of execution."

In a neat piece of synchronicity, Sean McGrath over at IT World wrote about design constraints in this week's E-Business column. He ponders the relative merits of writing baroque code to implement a business process exactly versus simplifying the business process in order to make it easier to automate.

"If your sense of an existing process is that it needs to be changed before it is computerized ... then you need to be careful what tools you put in the hands of those looking at the problem. Tools with constraints that focus attention on simplicity can be excellent catalysts for change."

A few year back Oracle had an interesting idea as part of its managed services proposition to Oracle eE-Business Suite customers. Don't bother customising Apps to fit your business. Instead, let Oracle use its consultants to migrate your business processes to fit "vanilla" Apps workflows. In return for which Oracle would guarantee a 5% year-on-year reduction in management charges for five years. Apaprently it was cheaper for Oracle to do BPR for free than to maintain and support heavily customised code. And as customised code is also likely to be more error-prone than factory standard code, the customers ought to have got more reliable systems for less outlay.

Oracle seem to have been quiet about this recently, so I don't know whether it's still in effect. Probably it's been swamped by the various Fusion initatives. I would be interested in knowing whether there was any great take-up of that offer. TUSC have an paper on the feasibility of "vanilla" E-Business Suite, which suggests that even very the simplest implemenations get complicated very quickly, especially in the area of data mapping and data conversion. Another beautiful idea killed off by an ugly reality?

Another use for INSERT ALL syntax

Over on the Pythian blog Babette Turner-Underwood discusses Oracle's multitable insert syntax. She's been using in on a data conversion project. The advantage of INSERT ALL is that it allows us to insert several different VALUES clauses in a single statement. The documentation shows two uses for multitable inserts:

• transforming a single row of data from one table into several rows of data in a another table;
  
• putting data from a single row into one or more tables, depending upon the values of certain columns (a poor man's partitioining).
  

And, as Babette notes, in data migration we sometimes need to split columns from one old table into more than one new table.

None of these uses are what we might call everyday. So Babette is correct to describe the multitable insert as "little known". However, there is one use for the syntax, which is slightly arcane but is a much more common situation: creating referenced records for foreign keys on the fly. Usually we would expect to have our primary key records before we create child records because they exist as entities in their own right: Customers, Products, etc. But there are others which are only brought into existence by the creation of child records. For instance, the canonical implementation of an order process requires two tables - Order_Headers and Order_Lines. Order_Headers really just acts as an intersection table between Customers and Order_Lines: we only need an Order Header record when a customer places an order for a specific item.

One way of handling this is to have foreign keys which are DEFERRABLE INITIALLY DEFERRED. This means that the foreign key is not enforced until the end of the transaction. So we can insert a Order_Lines record without having a Order_Headers record. This is only a temporary suspension: eventually we will need to create that parent record. Besides, this violation of relational theory will disappoint Hugh Darwen. It would be nice to be able to create the parent record at the moment when we create the child record, so we can enforce the foreign key immediately. Which is where the multitable insert comes in.

Here is a simple orders implementation, with an IMMEDIATE foreign key:

SQL> desc order_headers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 STATUS                                    NOT NULL VARCHAR2(3)

SQL> desc order_lines
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER
 LINE_ID                                   NOT NULL NUMBER
 PROD_ID                                   NOT NULL NUMBER
 QTY                                       NOT NULL NUMBER
 CREATED                                   NOT NULL DATE
 STATUS                                    NOT NULL VARCHAR2(3)
 SHIPPED                                            DATE

SQL> insert into order_lines (order_id, line_id, prod_id, qty)
  2      values (63, ordl_id.nextval, 5000, 2)
  3  /
insert into order_lines (order_id, line_id, prod_id, qty)
*
ERROR at line 1:
ORA-02291: integrity constraint (A.ORDL_ORD_FK) violated - parent key not found


SQL> 

.
What we want to be able to do is use the creation of the first order line to create the order header record too. The multitable syntax has several limitations. The two which are relevant here are: it must be driven off a subquery and we cannot use the RETURNING clause to retrieve the values of any sequence used in the insertion.

SQL> create or replace procedure new_order_line  (
  2      p_cust_id in customers.cust_id%type
  3        , p_prod_id in products.prod_id%type
  4        , p_qty in order_lines.qty%type
  5        , p_ord_id in out order_headers.order_id%type
  6        )
  7  is
  8      l_ord_id order_headers.order_id%type;
  9  begin
 10      if p_ord_id is null then
 11          select ord_id.nextval into l_ord_id from dual;
 12      else
 13          l_ord_id := p_ord_id;
 14      end if;
 15      
 16      insert all
 17        when p_ord_id is null then
 18           into order_headers (order_id, cust_id)
 19           values (new_ord_id, new_cust_id)
 20        when 1=1 then
 21           into order_lines (order_id, line_id, prod_id, qty)
 22           values (new_ord_id, ordl_id.nextval, new_prod_id, new_qty)
 23      select p_cust_id as new_cust_id
 24             , p_prod_id as new_prod_id
 25             , p_qty as new_qty
 26             , l_ord_id as new_ord_id
 27      from dual;
 28      
 29      if p_ord_id is null then
 30          p_ord_id := l_ord_id;
 31      end if;
 32  end new_order_line ;
 33  /

Procedure created.

SQL> 

I admit this is a slighty clunky procedure. My orginal implementation required only one IF statement, but had a single table INSERT statement for Order_Lines as well as the multitable statement. I'm sure if I had more time I could polish this code more. Anyway, let's call this procedure twice:

SQL> declare
  2    n number;
  3  begin
  4    new_order_line(1000, 5000, 2, n);
  5    new_order_line(1000, 5002, 1, n);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from order_headers
  2  /
  ORDER_ID    CUST_ID STA
---------- ---------- ---
     80002       1000 NEW

SQL> select * from order_lines
  2  /
  ORDER_ID    LINE_ID    PROD_ID        QTY CREATED   STA SHIPPED
---------- ---------- ---------- ---------- --------- --- ---------
     80002     250510       5000          2 30-APR-07 NEW
     80002     250511       5002          1 30-APR-07 NEW

SQL>

Of course I could have impelmented this as two separate single table inserts, which would have avoided the some of duplication I mentioned. However there is another advantage to the multitable version. It is a single statement, so if any part of it fails, the whole thing fails:

SQL> declare
  2    n number;
  3  begin
  4    new_order_line(1000, 5001, null, n);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("A"."ORDER_LINES"."QTY")
ORA-06512: at "A.NEW_ORDER_LINE", line 13
ORA-06512: at line 4


SQL> 
SQL> select * from order_headers
  2  /

no rows selected

SQL> 

This is neater because the logic of the business model is that we do not want an order header if there are no order lines for it.

We still don't have a constraint syntax which allows us to enforce parentage. And there is still no sign of a constraint which will allow us to enforce arcs. But there is always hope.

Oracle and AppForge: official at last

Oracle has finally posted a notice about its acquistion of AppForge on its site. As the various rumours made clear, Oracle have only bought the intellectual property and hired some former employees; it will not be supporting the existing applications. I was right about Oracle looking to strengthen its mobile offerings.

There is a set of FAQs for AppForge's customers, which doesn't actually answer the most frequently asked question of all, i.e. "what the flip do I do with my AppForge licences now?" So I will also point out (for the last time) my original post on this topic, which does have some useful advice for AppForge licencees posted by others in the same boat.