#Oracle # SQL Covert view to table issue: ORA-01723: zero-length columns are not allowed

In an Oracle database I was trying to convert an existing view to a table and preserve the data in it.

For most of my views the following SQL worked perfectly:

CREATE TABLE mytable as select * FROM myview;

Then in one on the instances the following error occurred:

Error report -
ORA-01723: zero-length columns are not allowed
01723. 00000 -  "zero-length columns are not allowed"
*Cause:    Columns with zero length were not allowed.
*Action:   Correct the use of the column.

While inspecting the SQL code of the views I noticed that the view with the problem was defined like:

 CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "MYVIEW" ("ID", "VERSION","USERNAME","TIMESTAMP","DATA") AS 
  SELECT
        id, version, username, timestamp, null as data
    FROM
        remote.MYTABLE;

Notice that the offending column is the last one null as data. It looks like Oracle is not able to infer the column type from that view definition.

The solution is simple, just add a cast to the CREATE TABLE statement.

CREATE TABLE mytable AS SELECT id, version, username, timestamp, CAST(null as VARCHAR2(100 CHAR)) AS data;

The above CAST will indicate to Oracle what is the type of the data column.