This is my table:
-- Create table
create table aug.ext_aug_data_1
(
year NUMBER,
fund VARCHAR2(8),
area VARCHAR2(8),
code VARCHAR2(8),
metric VARCHAR2(30),
amount NUMBER(14,2)
)
organization external
(
type ORACLE_LOADER
default directory AUG_DIR
access parameters
(
records delimited by '\r\n'
badfile EPT_DIR:'aug_data_1.bad'
skip 1
fields terminated by ','
optionally enclosed by '"'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
location (AUG_DIR:'aug_data_1.csv')
)
reject limit UNLIMITED;
With the table we are on 12.2.0.1.0
When I pass in a CSV of
YEAR,FUND,AREA,CODE,METRIC,AMOUNT
2019,132,LEVEL1,830,Base,936.84
2019,132,LEVEL2,830,Base,351.62
The aug_data_1.bad file comes back with
T
and the log file says
error processing column YEAR in row 2 for datafile aug_data_1.csv
ORA-01722: invalid number
The T is the last character of the first row. Is this a known bug? I cant find anything referencing the skip function causing issues.
I changed my skip 1 to skip 2 I still got 2 lines back but the first line only contained the number 4 (last character of 2nd row)
I changed to unix endings, and I got back NT (skip 1) and 84 (skip 2). So, it looks like the skip isn't skipping the last 3 characters of the row for some reason? Anyone know how to resolve this?