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. 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. 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.

  3. 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.

  4. 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)

  5. @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
    

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>