Create Table from View - Oracle SQL SQL Error: ORA-01723: zero-length columns are not allowed

Question

I need to create a table from view with statement like

CREATE  TABLE NEW_TABLE AS
    SELECT *
    from VIEW

It is giving error message as below. It is not possible to create table from view (with Select * statement)?

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

Answer 1

I have this problem when NULL is specified for a column, but the type is not specified. Arrgh!

You will need to look at the code. This often happens when I use:

select '' as x

Because I think '' should have the right type.

In any case, the solution is simple:

select cast(NULL as varchar2(255)),
       cast(NULL as number)

or whatever the type is.

You'll need to either change the view definition, or use a query with a subquery with explicit casts.