Completely new index page

Wow... a blog post from me... not done one in years. Anyway, just thought I'd let people know that I've done a major redesign of the front page of oratechinfo.co.uk. It's now a CSS grid layout, so currently will work for Firefox/Chrome/Safari and Opera. IE and Edge will default to a stacked div layout (like on a mobile device). The additional change is the move to HTTP/2.0.

Multi-table INSERT and handling of NULL values in the WHEN condition

Thought I'd just blog this to ensure I don't forget it. Basically, I had a problem with a multi-table INSERT with some very basic conditional processing. One of the conditions was a very simple equality comparison between a column and a literal, but astonishingly Oracle treats this as "true" if the column is NULL (unlike in all other circumstances where expression = value is false if "expression" is NULL. My test case is shown here :

CREATE TABLE mti_test ( a VARCHAR2(10), b VARCHAR2(10) )
/

CREATE TABLE temp_mc ( a VARCHAR2(100) )
/

INSERT INTO mti_test (a,b)
SELECT 'NUMBER', '1' FROM dual 
UNION ALL
SELECT 'CHAR', 'B' FROM dual
/

Now, running the following multi-table insert has no problems :

SQL> INSERT ALL
  2    WHEN a = 'NUMBER'
  3     AND 1 = TO_NUMBER(b)
  4    THEN
  5      INTO TEMP_MC VALUES ( 'Y' )
  6  SELECT a,b 
  7    FROM mti_test
  8  /

1 row created.

However, now introduce a row with a NULL value for "a", i.e. :

INSERT INTO mti_test (a,b)
SELECT NULL, 'C' FROM dual
/

and suddenly :

SQL> INSERT ALL
  2    WHEN a = 'NUMBER'
  3     AND 1 = TO_NUMBER(b)
  4    THEN
  5      INTO TEMP_MC VALUES ( 'Y' )
  6  SELECT a,b 
  7    FROM mti_test
  8  /
   AND 1 = TO_NUMBER(b)
           *
ERROR at line 3:
ORA-01722: invalid number

So, now Oracle thinks that NULL = 'NUMBER' is true! The immediate fix is to also include a "AND a IS NOT NULL" predicate, i.e. :

SQL> INSERT ALL
  2    WHEN a = 'NUMBER'
  3     AND a IS NOT NULL
  4     AND 1 = TO_NUMBER(b)
  5    THEN
  6      INTO TEMP_MC VALUES ( 'Y' )
  7  SELECT a,b 
  8    FROM mti_test
  9  /

1 row created.

Just something to be aware of when doing multi-table inserts.

New Look

Well.. I've done the lick of paint I've been promising on the main page now. What do you all think?

Twitter and web 2.0

Okay, I've grown with the times and added the customary twitter widget to the blog. I'll be re-designing the main site soon, giving it a little bit of a lick of paint.

Further tidying

Had a bit of a blitz on the CSS file, removing all unused entries. Also, I made some changes to the main page, making it easier to change the layout in future. You shouldn't see any changes, although some of the spacing between menu items has decreased by a few pixels.

I also added a link to the excellent 11g new features series by Arup Nanda on oracle.com, as well as removing some dead links.

A long time and a tidy up

Well, to say it's been a long time since I did anything on oratechinfo.co.uk would be an understatement. To be perfectly honest, it's been a busy few months with one thing or another, so I simply haven't found the time. Anyway... enough of the excuses.

The recent changes have been mostly tidying up of dead links and slight wording changes. The main page has had a couple of the links to technical articles removed, one of which was a link to Howard Rogers' excellent article on index clustering factors. This was removed because Howard has taken the decision to remove public access to dizwell.com, which is a shame, but his reasons are his own, I guess. I'm not privy to what happened to warrant this change.

Anyway, to cut a long story short, I've modified the message queueing, NULLs and FGAC pages, removing a few dead links. I've also expanded on the handling of multiple namespaces within XML documents via SQL/XML, removing a few dead links while doing so. The main thrust of the SQL/XML change is the difficulty in extracting information from XML documents where there are two or more namespaces defined. I've shown an example of doing this using the XPath local-name() function, but, as I explain in the section, there's no way of doing this which is logically correct, you have to introduce mechanisms for "ignoring" the namespace at some point, which is what
local-name() does.

I've also changed the XHTML / CSS and firefox links on the front page, removing the thunderbird
logo as well. The main reason for this was that I think they look better (especially the firefox one), and I don't really use Mozilla Thunderbird anymore, plus who am I to dictate what email client you use? ;-)

The order of triggers...

It was always considered bad design to design a system which relied on the order of the same type of triggers, i.e.
having two BEFORE UPDATE triggers where trigger 2 relied on trigger 1 having fired. The reason for this is that Oracle doesn't guarantee the order in which these two triggers should fire. Sure, BEFORE UPDATE trigger(s) will fire before AFTER UPDATE trigger(s), but two BEFORE UPDATE triggers can fire in any order.

Well, along come Oracle again and rewrite the rulebook, well.. sort of. As part of 11g, the trigger definition can now contain the FOLLOWS keyword, which takes an argument of a trigger name. This keyword allows you to specify a trigger which this trigger is guaranteed fire after (i.e. this trigger follows another...).

I've updated the trigger firing sequence section of the main site to include a mention of this and a reference to the above page.