what is the type of NULL

I was a bit surprised to see a VARCHAR2(0) column in my schema


select column_name, data_type, data_length
from user_tab_columns
where table_name='V';
COLUMN_NAME DATA_TYPE DATA_LENGTH
--------------- -------------------- -----------
X VARCHAR2 0

What’s this datatype? It is the datatype of NULL !!!

SQL> create or replace view v as select null x from dual
View created.
SQL> desc v
VIEW v
Name Null? Type
----------------- -------- ------------
X VARCHAR2(0)

7 thoughts on “what is the type of NULL”

  1. John Flack

    I’ve noticed this, especially when I’m doing a union like this where my_date is a DATE column:
    select my_pk, my_date from my_table
    union
    select 1, null from dual;
    According to the rules of unions, the columns of the queries have to be of compatible data types. You’ll get an error, because NULL isn’t a DATE. You have to do TO_DATE(NULL).

  2. Laurent Schneider Post author

    Really? this has been fixed 🙂

    select 1, sysdate from dual
    union
    select 2, null from dual

    1 SYSDATE
    ---------- ---------
    1 10-FEB-10
    2

    2 rows selected.

  4. John Flack

    Guess I haven’t tried it recently. And I’m snowed in – 7 snowy miles from my nearest Oracle database to try it on. Except… I just realized – I have Oracle XE on my laptop.

  6. Sokrates

    11.2.0.1.0 > REM the following is not rather understandable since views with varchar2(0) do exist
    11.2.0.1.0 > select cast(null as varchar2(0)) from dual;
    select cast(null as varchar2(0)) from dual
    *
    ERROR at line 1:
    ORA-01723: zero-length columns are not allowed

    11.2.0.1.0 > REM the following is just funny
    11.2.0.1.0 > select cast(null as varchar2(null)) from dual;
    select cast(null as varchar2(null)) from dual
    *
    ERROR at line 1:
    ORA-00910: specified length too long for its datatype
    11.2.0.1.0 > REM for numeric datatype, exceptions behave differently …
    11.2.0.1.0 > select cast(null as number(0)) from dual;
    select cast(null as number(0)) from dual
    *
    ERROR at line 1:
    ORA-01727: numeric precision specifier is out of range (1 to 38)

    11.2.0.1.0 > select cast(null as number(null)) from dual;
    select cast(null as number(null)) from dual
    *
    ERROR at line 1:
    ORA-01727: numeric precision specifier is out of range (1 to 38)

  7. Laurent Schneider Post author

    @John Flack apparently fixed in 9i or 9iR2 one more reason to upgrade your oracle 8i

    but yes VARCHAR2(0) is not a valid datatype for your table. Same as TIME 😉

    create view v as select time '12:00:00' t from dual;

    View created.

    desc v;
    Name Null? Type
    -------------------------------------- -------- -----------
    T TIME(9)

    create table t as select time '12:00:00' t from dual;
    create table t as select time '12:00:00' t from dual
    *
    ERROR at line 1:
    ORA-00902: invalid datatype

Comments are closed.