ORA-01723: zero-length columns are not allowed

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

Example to reproduce ORA-01723 -

SQL> Create table mytable as
select
        ename,
        null age,
    null doj,
    null mgrname
from
        scott.emp ;

        null age
        *
ERROR at line 4:
ORA-01723: zero-length columns are not allowed

Solution to avoid ORA-01723 exception use cast:

SQL> Create table mytable as
select
        ename,
        cast(null as number) age,
        cast(null as date) doj,
        cast(null as varchar2(10)) mgrname
from
        scott.emp ;

Table created.


SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 AGE                                                NUMBER
 DOJ                                                DATE
 MGRNAME                                            VARCHAR2(10)

Related Posts:
- ORA-02303: cannot drop or replace a type with type or table dependents
- ORA-01460 unimplemented or unreasonable conversion requested
- ORA-01489: result of string concatenation is too long
- ORA-06502: PL/SQL: numeric or value errorstring
- ORA-01722: invalid number