what is the type of NULL
February 9th, 2010
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)
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).
Really? this has been fixed
thank god you didn’t see a VARCHAR2(NULL) !
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.
Just hit the issue
create table t as select * from v
*
ERROR at line 1:
ORA-01723: zero-length columns are not allowed
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)
@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