SOLVED

ORA-01723: zero-length columns are not allowed

Asked by OracleDba••18 views•oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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)
OracleDba•

Post Your Solution