Oracle Create table, Materialized View Command Fails with ORA-01723: zero-length columns are not allowed

advertisements

_____________________________________________________________________________________________________________________

Error Description:

Oracle Create As Select Command (CTAS) failed with following error.

SQL> create table emp as select name, null age from candidate;

create table emp as select name, null age from candidate

                                 *

ERROR at line 1:

ORA-01723: zero-length columns are not allowed

Oracle Materialized View creation Fails with same error.

SQL> create materialized view my_objects  as select name, null age from candidate;

create materialized view my_objects  as select name, null age from candidate

                                                                   *

ERROR at line 1:

ORA-01723: zero-length columns are not allowed

Solution Description:

You have to use cast function to solve this error. See some examples on Oracle CAST function.

SQL> select length (cast (null as number(30))) from dual;

LENGTH(CAST(NULLASNUMBER(30)))

------------------------------

SQL> select cast (null as char(25)) from dual;

CAST(NULLASCHAR(25))

-------------------------

SQL> select length (cast (3*4 as  char(25))) from dual;

LENGTH(CAST(3*4ASCHAR(25)))

---------------------------

                         25

SQL> select cast (3*4 as  char(25)) from dual;

CAST(3*4ASCHAR(25))

-------------------------

12

Cast Function Mappings

TO	FROM
	char, varchar2	number	datetime / interval	raw	rowid, urowid	nchar, nvarchar2
char, varchar2	X	X	X	X	X
number	X	X
datetime / interval	X		X
raw	X			X
rowid, urowid	X				X
nchar, nvarchar2		X	X	X	X	X

You have to create the table or materialized view in following method.

SQL> create table emp as select name, cast (null as number) age from candidate;

Table created.

SQL>  select * from emp;

NAME              AGE

---------- ----------

James

Scott

SQL>  create materialized view emp as select name, cast (null as number) age from candidate;

Materialized view created.

Another Method is using NVL function. Which is not exact correct for this situation anyways…

SQL>  create table emp as select name, nvl(null,' ' ) age  from candidate;

Table created.

SQL> desc emp

 Name                                                              Null?    Type

 ----------------------------------------------------------------- -------- --------------------------------------------

 NAME                                                                       VARCHAR2(10)

 AGE                                                                        VARCHAR2(1)

_____________________________________________________________________________________________________________________

Website Stats