CeDeT: OIF (Oracle is funny (that way)) #8
I will use this page to give you plenty of reasons to avoid using NULLs!
Please note, that some people do not agree with me on this - see for example Should Nulls be considered harmful?
Last updated 07-02-2007
Strings of Zero Length Are Not Equivalent To a NULL
A string of zero length ('') is not equivalent to a NULL.
According to the ANSI SQL 1992 Transitional standard, a zero-length or empty string is not the same as NULL. The Oracle database server may comply fully with this aspect of the standard in the future. Therefore, it is recommended that applications ensure that empty strings values and NULL are not treated equivalently.
SQL> select 'x' from dual where NULL IS NULL; NULL is NULL! (So far so good)
'
-
x
SQL> select 'x' from dual where '' IS NULL; The empty string IS NULL! (I told you so)
'
-
x
SQL> select 'x' from dual where '' IS ''; The empty string is not a keyword but NULL is! (Well, ok)
select 'x' from dual where '' IS ''
*
FEJL i linie 1:
ORA-00908: manglende NULL-nøgleord
SQL> select 'x' from dual where ''=''; The empty string is not equal to the empty string! (Darn, and see aside below)
Ingen rækker er valgt
SQL> select 'x' from dual where NULL='';
Ingen rækker er valgt
SQL> select 'x' from dual where ''=NULL;
Ingen rækker er valgt
Aside: The empty string is not not equal to the empty string (either) - but that's just 3VL for you...
Read the article Nulls: Nothing to Worry About by Jonathan Gennick and Lex de Haan.
The catch here is that NULL is Nothing and that is what you must Worry About (even Oracle gets it wrong now and then, see Sightings below)!
From Oracle9i Database Concepts manual we have:
Nulls and Foreign Keys
The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key
I do believe that Chris J. Date would have a hard time with this particular reference to the relational model - see his book Database in Depth: The Relational Model for Practitioners (in association with Amazon.co.uk).
From Oracle9i Database Concepts manual we have:
Combine UNIQUE Key and NOT NULL Integrity Constraints
... In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint.
...
Note:
Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.
From The Tom Kyte Blog we have:
The facts are:
- Entirely NULL keys are not entered into a 'normal' B*Tree in Oracle
- Therefore, if you have a concatenated index on say C1 and C2, then you will likely find NULL values in it – since you could have a row where C1 is NULL but C2 is NOT NULL – that key value will be in the index.
Read the rest in Something about nothing... and the follow-up Mull about Null....
wb_truncate_table supplied by Oracle w/Oracle Warehouse Builder 10g Release 1 (10.1) (comments in bold added by me):
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jan 18 20:49:50 2006 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> SHOW USER; USER is "OWBSTG" SQL> DESCRIBE wb_truncate_table; PROCEDURE wb_truncate_table Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_TABLE VARCHAR2 IN SQL> EXECUTE wb_truncate_table(p_table=>NULL); BEGIN wb_truncate_table(p_table=>NULL); END; * ERROR at line 1: ORA-00903: invalid table name OK, but was this the intended behaviour? ORA-06512: at "OWBSTG.WB_TRUNCATE_TABLE", line 67 ORA-06512: at line 1 SQL> SET PAGESIZE 100; SQL> SET LINESIZE 2000; SQL> SELECT text FROM all_source WHERE name='WB_TRUNCATE_TABLE' 2 ORDER BY name,type,line; TEXT ---------------------------------------------------------------------------------------------------- PROCEDURE WB_Truncate_Table( p_table VARCHAR2 ) AUTHID CURRENT_USER AS ... BEGIN BEGIN IF (p_table = null) THEN return; I think not - should have read p_table IS NULL (and just return)! ELSIF (SUBSTR(p_table,1,1) = '"') THEN v_tableName := p_table; ELSE v_tableName := UPPER(p_table); END IF; END; ...