What is the Data Type of NULL?
In Tech Speack, NULL is a age old concept of nothing. This is not true in case of oracle. Oracle treats NULL as character value of length of 0.
So, the default data type of NULL is a character data type and to prove it, we create a view on a null column with an alias a, and then describe it to see the datatype and length of the string. Here is the code.....
create view myview
select null a from dual;
The describe command shows that the column a has a data type of a varchar2(0).
So the Answer is : Character Type
So, if Oracle treats null as an empty string, what is the value of a number column, that "allows nulls", in a new record before any number is entered?
I think you will find that a view tends to have character/varchar fields, as do unions.
A quasi technical answer to 'what is a null' is 0xff. If you look at how Oracle stores data, at least in the older character sets, if the null field occurs before the last field in a table, the space holder for the field is 0xff (hex 255). This is, of course, unprintable for ASCII7 and 'undefined' for straight ASCII (hence assorted fonts, etc.)
If the null field is after the last field with a value in a row, then nothing is stored because the row header has the number of fields to be read in the row (not how many fields defined.) This saves space by allowing more rows to be stored per block. Of course, it causes chaining problems when null fields are updated, but that is another discussion.
Datatype of NULL
From a theoretical standpoint, the datatype of null is not a valid concept. The way I think about it is as having whatever datatype is necessary for the result of the operation being performed. Where the datatype of the result can't be determined, the operation supplies a default. To muddy the waters further, SQL*plus and PL/SQL have lots of implicit type conversion rules, so it's virtually impossible to say for sure what is going on. Fortunately, I can't think of any real-world examples where it makes a difference.
As a result, I think that the apparent datatypes say more about the operations involved than about NULL. For example, the CREATE VIEW....AS SELECT above has no choice but to give the resulting column a datatype. Since NULL doesn't really have one, it must apply a default - in this case, it seems to be VARCHAR2(0).
You can certainly be forgiven for thinking that NULL is a varchar2 because of all the operations that can be carried out on it as if it were a varchar2; null || 'string', nvl( null, 'string'), etc.
However, some look wrong: Given table t with a varchar2 column c, you can 'select to_char( c ) from t' if all the rows have a null in them for col or, significantly, if the value evaluates to a number. Of course, the same statement fails as soon as one of the rows contains a value which does not evaluate to a number. This shows two things: 1) Oracle doesn't check the datataype of the column when evaluating the SQL statement (i.e. it doesn't implement strong typing) and 2) a NULL value is not treated as a VARCHAR2 in this case.
NULL in a row of a table is implemented either as an offset of zero(?) (for nulls which are not at the end of the row) or a non-existent offset (for nulls at the end of the row). It seems that a zero-length character string (VARCHAR2, etc.) is treated the same way.
NULL in SQL or PL/SQL is a bit more difficult. Mostly, an operation involving null returns a null (with the obvious exception if NVL). However, sometimes, NULL behaves like a zero-length string. eg NULL || 'string' returns 'string'.
The zero-length character string ('') seems to behave in the same way as NULL. This is reasonable given that NULL sometimes behaves like ''.
Datatype of NULL
Friends, I have seen all the postings on this item. All except EmmersonAD do not have the slightest of the idea of what they are talking about. Well, the short and sweet answer, which can be tested any time, is the datatype of NULL is the datatype of the COLUMN to which it belongs. Hint : Try nvl(MyDate,'01/01/01') and nvl(MyDate,to_date('01/01/01','dd/mm/yy')) in a select statement, where in the table the column MyDate is a Date datatype column.
How Oracle stores a null value - it just does not store any value for the NULL value in the row which is stored in an Oracle Block. Understanding the Block level concepts of PCTFREE and PCTUSED better, can help you guys here.
Datatype of null
I think EmmersonAD has tried hard enough to give a logical answer to the debate. And he has done a good job out of it.