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



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)
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google