CREATE TABLE AS SELECT creates non-nullable columns

Question

I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link.

Oracle creates all these tables with non-nullable columns and that causes me problems later when I try to update them.

How can I prevent this behaviour in Oracle and make resulting columns nullable?

Answer 1

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement:

create table blah2 
( 
  ctascolumn1 not null,
  ctascolumn2 null
) 
as 
select col1, col2 from blah;