Home » SQL & PL/SQL » SQL & PL/SQL » EXTERNAL TABLE CREATION ERROR
EXTERNAL TABLE CREATION ERROR [message #659006] Tue, 03 January 2017 16:53 Go to next message
vharish006
Messages: 9
Registered: August 2015
Location: Chicago
Junior Member
Hi ALL,

I'm trying to create External Table and it shows TABLE created after executing the below script but when query it throws below error
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

Below is the script:

CREATE TABLE EMP_HEALTH
(EMP_NUM   VARCHAR2(8),
FULL_NAME  VARCHAR2(50),
HIRE_DATE  DATE,
EMP_CATEGORY VARCHAR2(10),
 HEALTH_PROVIDER VARCHAR2(10),
COVERAGE  VARCHAR2(10),
 INSCOST  NUMBER(10)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORACLE_REPTEMP
 ACCESS PARAMETERS 
 ( RECORDS DELIMITED BY NEWLINE 
 FIELDS TERMINATED BY   ' , '(
 EMP_NUM CHAR(32),
FULL_NAME CHAR(32),
HIRE_DATE  DATE  "MM-DD-YYYY",
EMP_CATEGORY CHAR(32),
HEALTH_PROVIDER CHAR(32),
COVERAGE  CHAR(32),
 INSCOST  CHAR(32)
 )
 )
 LOCATION (ORACLE_REPTEMP:'Copy of EE_Health_Ins_2016.csv')
 );

Log file has rejects with below

field formatting error for field EMP_NUM
KUP-04026: field too long for datatype
KUP-04101: record 4 rejected in file /utl/temp/Copy of EE_Health_Ins_2016.csv
KUP-04021: field formatting error for field EMP_NUM
KUP-04026: field too

Please suggest what is causing the error.Thanks


[mod-edit: code tags added by bb]

[Updated on: Tue, 03 January 2017 20:28] by Moderator

Report message to a moderator

Re: EXTERNAL TABLE CREATION ERROR [message #659007 is a reply to message #659006] Tue, 03 January 2017 20:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
According to your error messages, there are a whole bunch of times where your field data is longer than your column length and there are so many such times that it has exceeded the default errors allowed, since you have not specified a reject limit. It may just be that you need to make your column lengths longer. You appear to be trying to load values that are 32 characters long into columns that are 10 characters long. However, it may be that your delimiter is specified wrong, thus causing it to concatenate fields together. Do your delimiters really have a space on either side of the comma or should it be just a comma? Can you post a few lines of data from your csv file?
Re: EXTERNAL TABLE CREATION ERROR [message #659023 is a reply to message #659007] Wed, 04 January 2017 08:57 Go to previous messageGo to next message
vharish006
Messages: 9
Registered: August 2015
Location: Chicago
Junior Member
Hi Barbara,

Below is some data from CSV

EMP_NUM,FULL_NAME,HIRE_DATE,EMP_CATEGORY,HEALTH_PROVIDER,COVERAGE,INSCOST
1112712,ABAJIAN RACHEL M,7/7/2003,Fulltime - Exempt,Blue Cross HMO,EMP/FAMILY,18792.00
102640,ABREU MARIA L,5/9/2005,Fulltime-Regular,Blue Cross HMO,EMP ONLY,7140.00
116984,ACEVEDO LOURDES,9/11/2000,Fulltime - Exempt,Blue Cross PPO,EMP/FAMILY,18492.00
1115863,ACOSTA SANTOS III,11/30/2006,Fulltime-Regular,Blue Cross HMO,EMP/SPOUSE,13308.00
120536,ADAMS ANDREA C,11/12/1998,Fulltime - Exempt,Blue Cross HMO,EMP ONLY,7140.00
Re: EXTERNAL TABLE CREATION ERROR [message #659024 is a reply to message #659023] Wed, 04 January 2017 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
why do you have different sizes & different datatype in the external table DDL?

BTW, CHAR() datatype should be avoided & not used.
Re: EXTERNAL TABLE CREATION ERROR [message #659025 is a reply to message #659024] Wed, 04 January 2017 09:21 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well that just confirms what Barbara said.
Your delimiter needs to be ',' not ' , '
Currently it's trying to stick the entire row into emp_num.

Once you've fixed that you need to look at the other columns and work out how long they really need to be.
'Fulltime - Exempt' won't fit in in char(10), neither will 'Blue Cross HMO'.
'EMP/FAMILY' does fit, just, but if there are any values for coverage that are longer they will fail as well.

[Updated on: Wed, 04 January 2017 09:22]

Report message to a moderator

Re: EXTERNAL TABLE CREATION ERROR [message #659028 is a reply to message #659025] Wed, 04 January 2017 10:23 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
https://community.oracle.com/thread/4004266

Re: EXTERNAL TABLE CREATION ERROR [message #659029 is a reply to message #659028] Wed, 04 January 2017 10:59 Go to previous messageGo to next message
vharish006
Messages: 9
Registered: August 2015
Location: Chicago
Junior Member
I posted it on the oracle community and following up here too.
Re: EXTERNAL TABLE CREATION ERROR [message #659043 is a reply to message #659029] Wed, 04 January 2017 16:38 Go to previous message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
The following loads the data provided, but you may need to make the column lengths larger to accommodate data not provided.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY oracle_reptemp AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE EMP_HEALTH
  2    (EMP_NUM 	 VARCHAR2(8),
  3  	FULL_NAME	 VARCHAR2(50),
  4  	HIRE_DATE	 DATE,
  5  	EMP_CATEGORY	 VARCHAR2(17),
  6  	HEALTH_PROVIDER  VARCHAR2(14),
  7  	COVERAGE	 VARCHAR2(10),
  8  	INSCOST 	 NUMBER(10))
  9  ORGANIZATION EXTERNAL
 10    (TYPE ORACLE_LOADER
 11  	DEFAULT DIRECTORY ORACLE_REPTEMP
 12  	ACCESS PARAMETERS
 13  	  (RECORDS DELIMITED BY NEWLINE
 14  	   BADFILE 'ORACLE_REPTEMP':'Copy of EE_Health_Ins_2016_Bad.txt'
 15  	   DISCARDFILE 'ORACLE_REPTEMP':'Copy of EE_Health_Ins_2016_Discard.txt'
 16  	   LOGFILE 'ORACLE_REPTEMP':'Copy of EE_Health_Ins_2016_Log.txt'
 17  	   SKIP 1
 18  	   FIELDS TERMINATED BY   ','
 19  	   MISSING FIELD VALUES ARE NULL
 20  	   REJECT ROWS WITH ALL NULL FIELDS
 21  	     (EMP_NUM,
 22  	      FULL_NAME,
 23  	      HIRE_DATE        DATE  "MM/DD/YYYY",
 24  	      EMP_CATEGORY,
 25  	      HEALTH_PROVIDER,
 26  	      COVERAGE,
 27  	      INSCOST))
 28  	LOCATION (ORACLE_REPTEMP:'Copy of EE_Health_Ins_2016.csv'))
 29  REJECT LIMIT UNLIMITED
 30  /

Table created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM emp_health
  2  /

EMP_NUM  FULL_NAME                                          HIRE_DATE
-------- -------------------------------------------------- ---------------
EMP_CATEGORY      HEALTH_PROVIDE COVERAGE      INSCOST
----------------- -------------- ---------- ----------
1112712  ABAJIAN RACHEL M                                   Mon 07-Jul-2003
Fulltime - Exempt Blue Cross HMO EMP/FAMILY      18792

102640   ABREU MARIA L                                      Mon 09-May-2005
Fulltime-Regular  Blue Cross HMO EMP ONLY         7140

116984   ACEVEDO LOURDES                                    Mon 11-Sep-2000
Fulltime - Exempt Blue Cross PPO EMP/FAMILY      18492

1115863  ACOSTA SANTOS III                                  Thu 30-Nov-2006
Fulltime-Regular  Blue Cross HMO EMP/SPOUSE      13308

120536   ADAMS ANDREA C                                     Thu 12-Nov-1998
Fulltime - Exempt Blue Cross HMO EMP ONLY         7140


5 rows selected.
Previous Topic: JSON_TABLE concurrent access issue
Next Topic: Collections - Nested Tables and VARRAY
Goto Forum:
  


Current Time: Sun Feb 25 04:25:25 CST 2018

Total time taken to generate the page: 0.24948 seconds