Primary Note - ORA-01723: Zero-length Columns are not Allowed
(Doc ID 335600.1)
Last updated on NOVEMBER 07, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
This primary 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 @<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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |