Home » SQL & PL/SQL » SQL & PL/SQL » EXTERNAL TABLE CREATION ERROR
EXTERNAL TABLE CREATION ERROR [message #659006] |
Tue, 03 January 2017 16:53 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
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 #659023 is a reply to message #659007] |
Wed, 04 January 2017 08:57 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
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 #659025 is a reply to message #659024] |
Wed, 04 January 2017 09:21 |
cookiemonster
Messages: 13925 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 #659043 is a reply to message #659029] |
Wed, 04 January 2017 16:38 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:54:38 CDT 2024
|