More than once I have encountered tables with columns defined as:
... column_x VARCHAR2(10) DEFAULT ' ' ...
Yep, that’s a nullable column with a default of a single space! Any time I see a nullable column with a default value I immediately think it’s a logical bug. After all, why define a default for a column and still permit NULLs? I realise that there are scenarios where this combination is legitimate but generally speaking these are rare. The use of a space as the default is to denote “no data” as client UIs will typically trim this down to en empty string.
A nullable column with a default of a space character will end up with a NULL if an insert specifies the column but sets the inserted value to NULL. This could be either explicitly assigning NULL or via an expression that evaluates to NULL.
The problem of having a column declared this way is that when writing queries you need to handle both scenarios. As a result, queries end up either wrapping column references with NVL(column_x, ‘ ‘) or trimming the column convert the single space entries to NULL. Either way, the queries end up looking confusing and can easily hamper the query optimiser.
It may appear that the obvious solution to the “does the column have a value or not” problem is to perform an update and translate all NULLs to spaces and then mark the column as NOT NULL. However, the problem is then how to find and modify the SQL statements that were inserting the NULLs in the first place… and here’s where the new ON NULL clause for column defaults in Oracle 12c helps us out.
In Oracle 12c, when a column default is defined with the ON NULL clause the default is also used if the inserted value evaluates to NULL and not only when no value for the column is specified. For our problematic column, we would define it as:
... column_x VARCHAR2(10) DEFAULT ' ' ON NULL ...
We can easily demonstrate the impact of this clause. First we use a default without the ON NULL clause to illustrate how the column can end up with a NULL value by creating a table and explicitly inserting two rows; one that uses the default and one that sets the column to NULL:
SQL>SET NULL <<null>>
SQL>CREATE TABLE test_table
2 (id NUMBER(10)
3 ,column_x VARCHAR2(10) DEFAULT ' ')
4 /
Table created.
SQL>INSERT INTO test_table (id)
2 VALUES (1)
3 /
1 row created.
SQL>INSERT INTO test_table (id, column_x)
2 VALUES (2, NULL)
3 /
1 row created.
SQL>SELECT id
2 , column_x
3 , NVL(LENGTH(column_x),0) AS len_column_x
4 FROM test_table
5 /
ID COLUMN_X LEN_COLUMN_X
---------- ---------- ------------
1 1
2 <<null>> 0
We can see from the above how NULLs manage to get into our column even with a default in place. Now we repeat the test with the ON NULL clause:
SQL>ALTER TABLE test_table
2 MODIFY column_x DEFAULT ON NULL ' '
3 /
Table altered.
SQL>INSERT INTO test_table (id)
2 VALUES (1)
3 /
1 row created.
SQL>INSERT INTO test_table (id, column_x)
2 VALUES (2, NULL)
3 /
1 row created.
SQL>SELECT id
2 , column_x
3 , NVL(LENGTH(column_x),0) AS len_column_x
4 FROM test_table
5 /
ID COLUMN_X LEN_COLUMN_X
---------- ---------- ------------
1 1
2 1
The above shows that the default value, a single space, is now being used when the column is not specified in the INSERT or if it is specified but the value inserted is NULL.
So, the solution to our problem of nullable columns with a default is to update the table and set all the NULLs to the default value and then change the default to make use of the ON NULL clause. No search of the codebase for SQL that generated the NULLs in the first place is required. Of course, you might have to upgrade to Oracle 12c first…
It should also be noted that Oracle has taken the liberty of defining a column with an ON NULL default to be NOT NULL:
SQL>DESCRIBE test_table Name Null? Type --------------------- -------- ------------ ID NUMBER(10) COLUMN_X NOT NULL VARCHAR2(10)
which means that attempting to enable the ON NULL clause when there exists NULLs in the column will result in an error, ORA-02296.
Mark Hoxey 