Create table as select and NULL columns views

Sometimes it’s usefull to define NULL columns in views. But there is problem to use such view for CREATE TABLE AS SELECT

--simple view with 3 null columns
create or replace view test_vw
as
select null a, null b, null c 
from dual;

it’s impossible to create table based on such view

desc test_vw
Name Null Type       
---- ---- ---------- 
A         VARCHAR2() 
B         VARCHAR2() 
C         VARCHAR2() 

create table test_tbl
as
select * from test_vw;

SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 -  "zero-length columns are not allowed"

to solve this problem define column size for null columns

create or replace view test_vw
as
select 
  cast( null as varchar2(10) ) a, 
  cast( null as date ) b, 
  cast( null as number ) c
from dual;

desc test_vw

Name Null Type         
---- ---- ------------ 
A         VARCHAR2(10) 
B         DATE         
C         NUMBER     

create table test_tbl
as
select * from test_vw;

table TEST_TBL created

Hope it helps 🙂

Tomasz

7 thoughts on “Create table as select and NULL columns views

  1. But when you “create as select” some table and in a very rare case some column contains ONLY nulls – you are in trouble… So I must cast EVERY suspicious field?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.