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

14 comments:

  1. it is really nice use of cast, thanks for sharing this information.

    ReplyDelete
  2. its really helpful thanks...

    ReplyDelete
  3. hey, it helped me for one of my issue...thanks

    ReplyDelete
  4. Hi,
    it's really notable while creating tables.

    Since we can't leave the datatype of column.

    Good.

    Thanks

    ReplyDelete
  5. Awesome! Thank you so much. Delighted to see this simple workaround to a frustrating problem.

    ReplyDelete
  6. This helped me a lot. Thank you!

    ReplyDelete
  7. Thanks, Helped me a lot!

    ReplyDelete