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
It did, thanks!
Good! it is working fine at my context. Thanks very much. 🙂
Good! this is working fine at my area. Thanks.
Pomogło dzięki
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?
it’s about missing data type not missing data
exactly what I was looking for 🙂 still relevant. thanks