19c
Sqldev 19c
Hi ALL,
I have a CSV file, which I used to import and created a table in our 18c database.
Normally since this is a CSV file, Oracle will create a table and column datatype based on its file contents.
So if Oracle finds that Name Column has characters, it will define its datatype as VARCHAR2,
and like if Oracle finds the the column data has all numbers, it will define it as NUMBER datatype.
My question it, why is that when I import the CSV file and created a new table based on the data contents, it has error below:
--Insert failed for rows 2001 through 3000
--ORA-01722: invalid number
--Row 2,388
INSERT INTO RA_ADDRESSES_ALL (ROW_ID, PARTY_SITE_ID, PARTY_ID, PARTY_LOCATION_ID, KEY_ACCOUNT_FLAG, PROGRAM_UPDATE_DATE, TERRITORY_ID, ADDRESS_KEY, BILL_TO_FLAG, MARKET_FLAG, SHIP_TO_FLAG, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, LOCATION_ID, SERVICE_TERRITORY_ID, PRIMARY_SPECIALIST_ID, SECONDARY_SPECIALIST_ID, CUSTOMER_CATEGORY_CODE, CUSTOMER_GROUP_CODE, CUSTOMER_SUBGROUP_CODE, REFERENCE_USE_FLAG, ANALYSIS_FY, FISCAL_YEAREND_MONTH, NET_WORTH, NUM_OF_EMPLOYEES, POTENTIAL_REVENUE_CURR_FY, POTENTIAL_REVENUE_NEXT_FY, RANK, COMPETITOR_FLAG, THIRD_PARTY_FLAG, YEAR_ESTABLISHED, DO_NOT_MAIL_FLAG, ADDRESS_STYLE, LANGUAGE, ADDRESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, CUSTOMER_ID, STATUS, ORIG_SYSTEM_REFERENCE, ORG_ID, COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY, LAST_UPDATE_LOGIN, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, ADDRESS_LINES_PHONETIC, GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6, GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18, GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE_CATEGORY, TP_HEADER_ID, WH_UPDATE_DATE, GLOBAL_ATTRIBUTE20, ECE_TP_LOCATION_CODE, SALES_TAX_GEOCODE, SALES_TAX_INSIDE_CITY_LIMITS, TERRITORY, TRANSLATED_CUSTOMER_NAME, ADDRESS_TEXT, SITE_NUMBER, IDENTIFYING_ADDRESS_FLAG, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20, PARTY_SITE_LAST_UPDATE_DATE, LOC_LAST_UPDATE_DATE, ADDRESSEE) VALUES ('AAAHjuAAAAAABjSAAU',150079.0,136525.0,5575.0,'','','','VMF45DYS','Y','','Y','','','','','',1000.0,'','','','','','','','','','','','','','','','','','','','',5358.0,to_date('01-APR-14', 'DD-MON-RR'),2068.0,to_date('20-AUG-07', 'DD-MON-RR'),1112.0,129105.0,'A',10056-D02,105.0,'PH','VM FE - 45 Days','Catigan, Toril, Davao City','','','','','','','',3504701.0,'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',1.0,'','','',5298.0,'Y','','','','','',to_date('29-JUL-15', 'DD-MON-RR'),to_date('01-APR-14', 'DD-MON-RR'),'');
Is it not Oracle the one who created the table and knows better the data inside the CSV?
--ORA-01722: invalid number
Since the data has Character, why did Oracle created it as NUMBER datatype in the first place?
Please help....
Kind regards,
jc