Oracle Open World Unconference sessions

Prompted by Laurent Schneider's posting I have also proposed a session on PL/SQL design. Some of the other proposals seem to be presentations by another name, which to my mind is not in the unconference spirit. It's supposed to be an alternative to regular presentations rather than safety-net for people who who didn't make it through the Call For Papers.

My proposal is definitely for a group discussion on PL/SQL design: approach, tools, methods, best practice. I've described myself as a facilitator rather than a presenter. I might try to make it an actual fishbowl session, if the room will permit such a layout.

The thing which puzzles me is how this Wiki stuff is going to link up with the whiteboard on the 3rd floor of Moscone West. Are our proposals actually booking sessions in the Unconference or are we just flaoting ideas? If all the slots get booked up on the wiki, will there be any scope for spontaneity at the Unconference itself?

Report on UKOUG DE SIG 11-OCT-2007

The Oracle City office played host to the last Development Engineering SIG of the year. This meeting focused largely on the Tools aspect of the SIG's remit. I would like to thank all of the presenters for contributing to what I found an interesting and useful day.

Jeremy Duggan of Joraph started the day with an overview of Application Express. This was his first gig as a speaker, although he has been running (or co-running) the Designer SIG for a number of years(1). For a first attempt it was very good. Jeremy has a nice conversational style and a good command of his brief. He also had the right amount of content to fill the time slot without rushing the end. I liked the way Jeremy put Application Express into the context of his own IT career: that helped us to understand why he appreciated the tool. Personally I wanted to hear more about the case studies, but these things are not always easy, especially if the client has made you sign an NDA. Jeremy got confused about his role only once, when at the end of his talked he almost led off his own round of applause.

Simon Day from Borland had the next slot. Simon is another old hand from the Designer SIG. He has a great presenting style. His talk was billed as Requirements Driven Approach to Testing Oracle J2EE applications. The JDev element turned out to be in the demo, which didn't work. This gave me an opportunity for a cheap shot about not testing the demo, which I duly took. Sorry, Simon. He presented a summary of findings from the usual business analysts (Butler, Gartner et alia), which show that the top three reasons for IT projects failing are:

1. Inaccurate requirements;
   
2. Incomplete requirements;
   
3. Changing requirements.

Well, durrr. We all know that. We just pretend these things aren't going to happen this time. It is this apparent inability to properly deal with the errant requirements which causes the problems: forty percent of all project work is re-work. Fortunately there is an answer and it is ... a suite of software produced by Borland. I bet you didn't see that coming. To be fair, managing requirements and the code which satisfies them and the automated tests to validate that code is a complex task, one which obviously demands a tool. I was just hoping for a solution which didn't necessitate a procurement process. I sure elongated and overly-complicated procurement processes is number four on that list.

Next up was Tim Hall, reprazentin' Oracle-Base, talking about Oracle and PHP. Previously I had tried to get a presentation on this topic from two different sources. Both had mucked me about. Then I noticed that Tim's web site was written in PHP, so I asked him if he would do a talk about it. He was reluctant at first, because he doesn't consider himself a PHP expert. But the whole point about PHP is that it allows numpties to build web sites. Er, not that I'm saying Tim is a numpty. So he took the bait. His presentational style is like that of the Cillit Bang adverts: here is a sample of code, this is what it does. Clear, concise and focused. It was just a shame that Tim's demo didn't work either. Still, at least Tim had the consolation of Nietzsche to fall back on.

After lunch we had a second presentation on Application Express. Oracle Consulting's Gus Charnell talked us through some of the New Features in ApEx3.0. This was a marvelous complement to Jeremy's talk, because we could see just how easy it can be to build and change pages with the tool. One of the questions which came up at Mike Hichwa's sessions at the UKOUG conference last year was with regard to ApEx reporting. ApEx is free but Oracle were proposing to implement reporting use the decidedly not-free XML Publisher: how did Oracle propose to resolve this licensing conundrum. Gus's talk gave us the answer. ApEx can produce simple (i.e. single query) reports using Apache FOP style sheets, but if we want anything complicated we'll have to spring for BI Publisher licences. Still, with the increasing functionality of SQL we can pack a lot of sophistication into a single query. Gus finally broke the hex on failing demos. However, it did take a bit of voodoo (changing a variable value, committing and then changing it back) to generate the PDF.

The final presentation of the day was Harvey Raja, also of Oracle Consulting. Harvey attempted to compress about a day's worth of content on JDeveloper 11g into forty-five minutes. He made a good stab at it. Java these days is a bewildering torrent of TLAs, standards and bizarrely named libraries (ICE Faces, JGoodies). On the other hand, the applications we can build are slick and flexible almost beyond belief. The cynic in me wanted to know the spec of Harvey's machine: JDeveloper never runs that fast on my PC and I don't even have the ADF version installed.

I must start thinking about the next SIG soon. It's earlier in the year than usual (last week of February) so I really need to get most of the sessions confirmed before Christmas. If anybody would like to present please contact me or Julius. We promise not to kill you, so it can only make you stronger.

It's That Thread Again

There's a thread which has been running in PL/SQL for several months. It started off as a spoof on the URGENT PLZ HELP type threads we get from time to time. I blogged about it in June. After a long hiatus the OP has changed their handle to John Titor, Time Traveller and posted some more nonsense. I don't mind that. Some of the responses were quite entertaining, and the thing was mostly harmless.

The problem is, they are now masquerading as venerable lead-pipe swinger Billy Verreynne. They did this by the simple mechanism of replacing lower case Ls with capital is in their handle(curse those sans serif fonts!). Furthermore they have changed the subject matter and text of the original post to make it look as though Billy is offering Oracle's own internal PL/SQL coding standards. This is an homage to a notorious thread from the forum's history. This leads credence to the suggestion that the post is a mischievous forum regular with a sound knowledge of the other denizens. Unfortunately some of the less knowledgeable visitors are falling for this scam and are posting their e-mail addresses.

This is a thread which now, alas, has to die. Anybody got any zombie poison?

Post Scriptum

I haven't bothered putting a link in to this thread because I expect it to be pulled soon enough.

xkcd on SQL injection

Top geek humour comic xkcd has just published a strip about SQL injection. Check it out.

Update

Other people enjoyed this strip too. John Emmons for instance. And Tom Kyte, of course. And Boing Boing, ditto. And Pete Finnegan. In fact I was probably the last person on the whole internet to post about it. Sigh.

At last, the Oct-2007 DE SIG!

Next week the third UKOUG Development Engineering SIG of the year happens at the Oracle London office. I should have blogged about it sooner but we experienced a slight agenda malfunction hem hem. Fortunately our friends in Oracle Consulting have come through to plug the gaps and spare my blushes. It's ended up being themed around some of the newer tools in the Oracle development space. We have two presentations on Application Express, two on JDeveloper and one on PHP.

I have been trying for ages to get somebody to talk about PHP and Oracle without success. So I am grateful to Tim Hall for accepting the challenge. I'm hoping that he'll turn up in his full ACE Director fig: tiara, ermine robe and chain of office. Also speaking is Jeremy Duggan, better known as the Chair of the Modelling & Design SIG. I think this is Jeremy's first time on the other side of the fence , so I'll keep my fingers crossed for him during his presentation. The session which most intrigues me is Simon Day, talking about requirements and testing with JDeveloper. Mapping user requirements to delivered code is one of IT's black holes, so I am keen to known what JDev offers us in this regard.

One of the talks I tried to get but which didn't materialise was an introduction to the Microsoft development environment. It occurred to me whilst listening to Xen Lategan's presentation at BVP in June that I knew very little about the Microsoft architecture. I could guess the function most of the boxes in the architectural diagram because the Oracle/J2EE stacks have similarly-shaped boxes in similar places. As the October agenda seemed to be moving towards a tools theme I thought it would be an ideal opportunity for Microsoft to evangelise their toolset to a fresh audience.

Unfortunately the only presentation they could offer me was a talk on Visual Studio 2008 New Features, which apparently would enthuse the audience to upgrade immediately from VS2005. As I doubt that any of the expected audience uses VS of any flavour I had to regretfully turn this down. Still it is good news for those people who regard Microsoft as an all-devouring beast. The Borg has assimilated all the customers it wants right now, at least on the development tools front. Presumably the unassimilated remnants are too hardcore (or too few!) to be worthwhile. So we're safe ... for the time being.

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>