Home » SQL & PL/SQL » SQL & PL/SQL » External Tables (Oracle Loader) (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: XP Window )
External Tables (Oracle Loader) [message #575314] Tue, 22 January 2013 03:14 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

hi i am trying to create a external table.Table is getting created but there is no data.While checking the log file, its saying
KUP-04021: field formatting error for field CUST_CODE
KUP-04026: field too long for datatype
KUP-04101: record 1 rejected in file /data/mmi_mig_data/PCOM_CUSTOMER_1.csv
KUP-04021: field formatting error for field CUST_CODE
KUP-04026: field too long for datatype
KUP-04101: record 2 rejected in file /data/mmi_mig_data/PCOM_CUSTOMER_1.csv

But according to me in CSV file everything looks to be okay. Kindly Advice me.
External Table:
  CREATE TABLE ET_PCOM_CUSTOMER(CUST_CODE VARCHAR2(12), CUST_TITLE VARCHAR2(12), 
                                CUST_NAME VARCHAR2(240), CUST_MIDDLE_NAME VARCHAR2(240), 
                                CUST_FIRST_NAME VARCHAR2(240), CUST_LAST_NAME VARCHAR2(240), 
                                CUST_GENDER VARCHAR2(12), CUST_MARITAL_STS VARCHAR2(12), 
                                CUST_NIC_NO VARCHAR2(60), CUST_ADDR_01 VARCHAR2(240), 
                                CUST_ADDR_02 VARCHAR2(240), CUST_ADDR_03 VARCHAR2(240), 
                                CUST_ATTACHED_TO VARCHAR2(12), CUST_CC_CODE VARCHAR2(12), 
                                CUST_CITY VARCHAR2(12), CUST_CIVIL_ID VARCHAR2(60), 
                                CUST_COMMERCIAL_YN VARCHAR2(1), CUST_COUNTRY VARCHAR2(12), 
                                CUST_DFLT_ASSR_YN VARCHAR2(1), CUST_DFLT_BROK_CODE VARCHAR2(12), 
                                CUST_DOB VARCHAR2(20), CUST_EFF_FM_DT VARCHAR2(20), CUST_EFF_TO_DT VARCHAR2(20), 
                                CUST_EMAIL1 VARCHAR2(240), CUST_EMAIL2_BL VARCHAR2(240), CUST_FLEX_10 VARCHAR2(240), 
                                CUST_FLEX_12 VARCHAR2(240), CUST_FLEX_13 VARCHAR2(240), CUST_FLEX_15 VARCHAR2(240), 
                                CUST_LEAD_YN VARCHAR2(1), CUST_LOCAL_YN VARCHAR2(1), CUST_MAILING_ADDR VARCHAR2(12), 
                                CUST_OFF_ADDR_01 VARCHAR2(240), CUST_OFF_ADDR_02 VARCHAR2(240), CUST_OFF_ADDR_03 VARCHAR2(240), 
                                CUST_OFF_CITY VARCHAR2(12), CUST_OFF_COUNTRY VARCHAR2(12), CUST_OFF_PIN_CODE VARCHAR2(12), 
                                CUST_OFF_STATE VARCHAR2(12), CUST_PIN_CODE VARCHAR2(12), CUST_PREFER_COMM_ADDR VARCHAR2(240), 
                                CUST_REF_NO VARCHAR2(60), CUST_STATE VARCHAR2(12), CUST_TAX_YN VARCHAR2(1)) 
                                ORGANIZATION EXTERNAL(DEFAULT DIRECTORY MMI_DATA_MIG 
                                ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE BADFILE 'ET_PCOM_CUSTOMER.bad' LOGFILE 'ET_PCOM_CUSTOMER.log' 
                                FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL REJECT ROWS
                                WITH ALL NULL FIELDS) LOCATION(
                                'PCOM_CUSTOMER_1.csv')) REJECT LIMIT UNLIMITED;

CSV File is attached
Re: External Tables (Oracle Loader) [message #575315 is a reply to message #575314] Tue, 22 January 2013 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
FIELDS TERMINATED BY '|'

There's none in your CSV file.
Re: External Tables (Oracle Loader) [message #575318 is a reply to message #575315] Tue, 22 January 2013 03:27 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Hi,
Corrected your external table script. Few columns having data size problem... now you can check data.
 CREATE TABLE ET_PCOM_CUSTOMER(CUST_CODE VARCHAR2(12), 
CUST_TITLE VARCHAR2(12), 
CUST_NAME VARCHAR2(240), CUST_MIDDLE_NAME VARCHAR2(240), 
CUST_FIRST_NAME VARCHAR2(240), CUST_LAST_NAME VARCHAR2(240), 
CUST_GENDER VARCHAR2(12),
CUST_MARITAL_STS VARCHAR2(12), 
CUST_NIC_NO VARCHAR2(60), CUST_ADDR_01 VARCHAR2(240), 
CUST_ADDR_02 VARCHAR2(240), CUST_ADDR_03 VARCHAR2(240), 
CUST_ATTACHED_TO VARCHAR2(12), CUST_CC_CODE VARCHAR2(12), 
CUST_CITY VARCHAR2(12), CUST_CIVIL_ID VARCHAR2(60), 
CUST_COMMERCIAL_YN VARCHAR2(10), CUST_COUNTRY VARCHAR2(12), 
CUST_DFLT_ASSR_YN VARCHAR2(10), CUST_DFLT_BROK_CODE VARCHAR2(12), 
CUST_DOB VARCHAR2(20), CUST_EFF_FM_DT VARCHAR2(20), CUST_EFF_TO_DT VARCHAR2(20), 
CUST_EMAIL1 VARCHAR2(240), CUST_EMAIL2_BL VARCHAR2(240), CUST_FLEX_10 VARCHAR2(240), 
CUST_FLEX_12 VARCHAR2(240), CUST_FLEX_13 VARCHAR2(240), CUST_FLEX_15 VARCHAR2(240), 
CUST_LEAD_YN VARCHAR2(10), CUST_LOCAL_YN VARCHAR2(1), CUST_MAILING_ADDR VARCHAR2(12), 
CUST_OFF_ADDR_01 VARCHAR2(240), CUST_OFF_ADDR_02 VARCHAR2(240), CUST_OFF_ADDR_03 VARCHAR2(240), 
CUST_OFF_CITY VARCHAR2(12), CUST_OFF_COUNTRY VARCHAR2(12), CUST_OFF_PIN_CODE VARCHAR2(12), 
CUST_OFF_STATE VARCHAR2(12), CUST_PIN_CODE VARCHAR2(12), CUST_PREFER_COMM_ADDR VARCHAR2(240), 
CUST_REF_NO VARCHAR2(60), CUST_STATE VARCHAR2(12), CUST_TAX_YN VARCHAR2(1)) 
ORGANIZATION EXTERNAL(DEFAULT DIRECTORY AD_DATA_EXT 
ACCESS PARAMETERS( RECORDS DELIMITED BY NEWLINE  SKIP 1
BADFILE 'ET_PCOM_CUSTOMER.bad' LOGFILE 'ET_PCOM_CUSTOMER.log'                                 
FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL REJECT ROWS
WITH ALL NULL FIELDS) LOCATION(
'PCOM_CUSTOMER_1.csv')) REJECT LIMIT UNLIMITED; 
Re: External Tables (Oracle Loader) [message #575319 is a reply to message #575318] Tue, 22 January 2013 03:32 Go to previous message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Thanks Muralikri.Now resolved.
Previous Topic: Help in building batch logic
Next Topic: RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT
Goto Forum:
  


Current Time: Tue Sep 30 17:46:49 CDT 2014

Total time taken to generate the page: 0.08928 seconds