Blog

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)

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.