12c
OL6
Hi ALL,
I am creating a test table from a view, using the following code:
create table ar.ra_addresses_all as select * from apps.ra_addresses_all;
Error report -
ORA-01723: zero-length columns are not allowed
01723. 00000 - "zero-length columns are not allowed"
*Cause: Columns with zero length were not allowed.
*Action: Correct the use of the column.
Name Null? Type
---------------------------- -------- -------------
ROW_ID ROWID
PARTY_SITE_ID NOT NULL NUMBER(15)
PARTY_ID NOT NULL NUMBER(15)
PARTY_LOCATION_ID NOT NULL NUMBER(15)
KEY_ACCOUNT_FLAG VARCHAR2(1)
PROGRAM_UPDATE_DATE DATE
TERRITORY_ID NUMBER(15)
ADDRESS_KEY VARCHAR2(500)
BILL_TO_FLAG VARCHAR2(1)
MARKET_FLAG VARCHAR2(1)
SHIP_TO_FLAG VARCHAR2(1)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
LOCATION_ID NUMBER(15)
SERVICE_TERRITORY_ID NUMBER(15)
PRIMARY_SPECIALIST_ID NUMBER(15)
SECONDARY_SPECIALIST_ID NUMBER(15)
CUSTOMER_CATEGORY_CODE VARCHAR2(30)
CUSTOMER_GROUP_CODE VARCHAR2
CUSTOMER_SUBGROUP_CODE VARCHAR2
REFERENCE_USE_FLAG VARCHAR2
ANALYSIS_FY VARCHAR2
FISCAL_YEAREND_MONTH VARCHAR2
NET_WORTH NUMBER
NUM_OF_EMPLOYEES NUMBER
POTENTIAL_REVENUE_CURR_FY NUMBER
POTENTIAL_REVENUE_NEXT_FY NUMBER
RANK VARCHAR2
COMPETITOR_FLAG VARCHAR2
THIRD_PARTY_FLAG VARCHAR2
YEAR_ESTABLISHED NUMBER
DO_NOT_MAIL_FLAG VARCHAR2
ADDRESS_STYLE VARCHAR2(30)
LANGUAGE VARCHAR2(4)
ADDRESS_ID NOT NULL NUMBER(15)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
CUSTOMER_ID NOT NULL NUMBER(15)
STATUS NOT NULL VARCHAR2(1)
ORIG_SYSTEM_REFERENCE NOT NULL VARCHAR2(240)
ORG_ID NUMBER(15)
COUNTRY NOT NULL VARCHAR2(60)
ADDRESS1 NOT NULL VARCHAR2(240)
ADDRESS2 VARCHAR2(240)
ADDRESS3 VARCHAR2(240)
ADDRESS4 VARCHAR2(240)
CITY VARCHAR2(60)
POSTAL_CODE VARCHAR2(60)
STATE VARCHAR2(60)
PROVINCE VARCHAR2(60)
COUNTY VARCHAR2(60)
LAST_UPDATE_LOGIN NUMBER(15)
ATTRIBUTE_CATEGORY VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
REQUEST_ID NUMBER(15)
PROGRAM_APPLICATION_ID NUMBER(15)
PROGRAM_ID NUMBER(15)
ADDRESS_LINES_PHONETIC VARCHAR2(560)
GLOBAL_ATTRIBUTE1 VARCHAR2(150)
GLOBAL_ATTRIBUTE2 VARCHAR2(150)
GLOBAL_ATTRIBUTE3 VARCHAR2(150)
GLOBAL_ATTRIBUTE4 VARCHAR2(150)
GLOBAL_ATTRIBUTE5 VARCHAR2(150)
GLOBAL_ATTRIBUTE6 VARCHAR2(150)
GLOBAL_ATTRIBUTE7 VARCHAR2(150)
GLOBAL_ATTRIBUTE8 VARCHAR2(150)
GLOBAL_ATTRIBUTE9 VARCHAR2(150)
GLOBAL_ATTRIBUTE10 VARCHAR2(150)
GLOBAL_ATTRIBUTE11 VARCHAR2(150)
GLOBAL_ATTRIBUTE12 VARCHAR2(150)
GLOBAL_ATTRIBUTE13 VARCHAR2(150)
GLOBAL_ATTRIBUTE14 VARCHAR2(150)
GLOBAL_ATTRIBUTE15 VARCHAR2(150)
GLOBAL_ATTRIBUTE16 VARCHAR2(150)
GLOBAL_ATTRIBUTE17 VARCHAR2(150)
GLOBAL_ATTRIBUTE18 VARCHAR2(150)
GLOBAL_ATTRIBUTE19 VARCHAR2(150)
GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(30)
TP_HEADER_ID NUMBER
WH_UPDATE_DATE DATE
GLOBAL_ATTRIBUTE20 VARCHAR2(150)
ECE_TP_LOCATION_CODE VARCHAR2(40)
SALES_TAX_GEOCODE VARCHAR2(30)
SALES_TAX_INSIDE_CITY_LIMITS VARCHAR2(30)
TERRITORY VARCHAR2(30)
TRANSLATED_CUSTOMER_NAME VARCHAR2(50)
ADDRESS_TEXT CLOB
SITE_NUMBER NOT NULL VARCHAR2(30)
IDENTIFYING_ADDRESS_FLAG NOT NULL VARCHAR2(1)
ATTRIBUTE16 VARCHAR2(150)
ATTRIBUTE17 VARCHAR2(150)
ATTRIBUTE18 VARCHAR2(150)
ATTRIBUTE19 VARCHAR2(150)
ATTRIBUTE20 VARCHAR2(150)
PARTY_SITE_LAST_UPDATE_DATE NOT NULL DATE
LOC_LAST_UPDATE_DATE NOT NULL DATE
ADDRESSEE VARCHAR2(360)
I searched the solution which is to use "cast".
I described the source table above and the nine(9) red columns are the suspected columns that can be of zero-lenth.
But when I "cast" them there are still errror or zero-length columns.
Oracle is not so kind to show which columns are the suspects.
Please help how to find them.
Kind regards,
jc