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

Last updated on NOVEMBER 07, 2023

Applies to:

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     @<dblink_name>    
    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 => '<user_name>', tabname => 'TABLE1',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 <user_name>.TABLE1 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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.