Master Note for ORA-01723: Zero-length Columns are not Allowed (Doc ID 335600.1)

Last updated on MAY 17, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.2 and later
Information in this document applies to any platform.
This master note serves as a guide for all cases where ORA-01723 error is observed.

Symptoms

1. Creating a VIEW on a remote table using a database link is successful.

There are no lobs in remote table.

Types present in remote table are : nvarchar2, number as well as date.

Creating a MATERIALIZED VIEW using the same select as in the view results in :

ORA-01723: zero-length columns are not allowed

2. Creating a MODEL DIMENSION on a remote table using a database link results in :

    SELECT dummy FROM DUAL             
    UNION ALL                          
    SELECT y FROM DUAL     @nl_test    
    MODEL DIMENSION BY (dummy )        
    MEASURES (TO_NUMBER (NULL) x,'Y' y)
    RULES()                            
 ERROR:                                
 ORA-01723: zero-length columns are notallowed
 ORA-02063: preceding line from NL_TEST

3. Running dbms_stats.gather_table_stats on table having index with NULL

exec dbms_stats.gather_table_stats(ownname => 'CMS_DATA', tabname => 'CUSTOMER_TBL',estimate_percent => 5, cascade => true);

ERROR at line 1:
ORA-01723: zero-length columns are not allowed
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

Please refer to [ID 1280890.1] for detailed information.


4. Running  ANALYZE TABLE <Table_Name> VALIDATE STRUCTURE CASCADE on table having index with NULL

ANALYZE TABLE CMS_DATA.CUSTOMER_TBL VALIDATE STRUCTURE CASCADE;

ERROR at line 1:
ORA-01723: zero-length columns are not allowed
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms