ORA-00984: column not allowed here in SQL*Loader

I'm trying to use SQLLoader to load csv data into a table but i am receiving the following error - Record 1: Rejected - Error on table PMI_DATA_SPRINT_REPORTER.PERSON, column DATETIME_OF_BIRTH. ORA-00984: column not allowed here

Tried to subvstr and to_date the data due to only needing YYYY-MM-DD - DATETIME_OF_BIRTH "TO_DATE(substr(DATETIME_OF_BIRTH,1,10),'YYYY-MM-DD')", but received the same error as above.

Any suggestions are appreciated, thanks.

DDL of the table

CREATE TABLE "PERSON" ( "PERSON_ID" NUMBER(19,0), "GENDER_CONCEPT_ID" NUMBER(19,0), "YEAR_OF_BIRTH" NUMBER(19,0), "MONTH_OF_BIRTH" NUMBER(19,0), "DAY_OF_BIRTH" NUMBER(19,0), "DATETIME_OF_BIRTH" DATE, "RACE_CONCEPT_ID" NUMBER(19,0), "ETHNICITY_CONCEPT_ID" NUMBER(19,0), "LOCATION_ID" NUMBER(19,0), "PROVIDER_ID" NUMBER(19,0), "CARE_SITE_ID" NUMBER(19,0), "PERSON_SOURCE_VALUE" VARCHAR2(500 CHAR), "GENDER_SOURCE_VALUE" VARCHAR2(500 CHAR), "GENDER_SOURCE_CONCEPT_ID" NUMBER(19,0), "RACE_SOURCE_VALUE" VARCHAR2(500 CHAR), "RACE_SOURCE_CONCEPT_ID" NUMBER(19,0), "ETHNICITY_SOURCE_VALUE" VARCHAR2(500 CHAR), "ETHNICITY_SOURCE_CONCEPT_ID" NUMBER(19,0) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; ALTER TABLE "PERSON" MODIFY ("ETHNICITY_CONCEPT_ID" NOT NULL ENABLE); ALTER TABLE "PERSON" MODIFY ("RACE_CONCEPT_ID" NOT NULL ENABLE); ALTER TABLE "PERSON" MODIFY ("YEAR_OF_BIRTH" NOT NULL ENABLE); ALTER TABLE "PERSON" MODIFY ("GENDER_CONCEPT_ID" NOT NULL ENABLE); ALTER TABLE "PERSON" MODIFY ("PERSON_ID" NOT NULL ENABLE);

Control file

load data infile 'filename.csv' "str '\r\n'" append into table PERSON fields terminated by ',' OPTIONALLY ENCLOSED BY '"' AND '"' trailing nullcols ( PERSON_ID integer, GENDER_CONCEPT_ID integer, YEAR_OF_BIRTH integer, MONTH_OF_BIRTH integer, DAY_OF_BIRTH integer, DATETIME_OF_BIRTH DATE "YYYY-MM-DD", RACE_CONCEPT_ID integer, ETHNICITY_CONCEPT_ID integer, LOCATION_ID integer, PROVIDER_ID integer, CARE_SITE_ID integer, PERSON_SOURCE_VALUE CHAR(4000), GENDER_SOURCE_VALUE CHAR(4000), GENDER_SOURCE_CONCEPT_ID integer, RACE_SOURCE_VALUE CHAR(4000), RACE_SOURCE_CONCEPT_ID integer, ETHNICITY_SOURCE_VALUE CHAR(4000), ETHNICITY_SOURCE_CONCEPT_ID integer)

CSV with an example record(unsure how to put an actual file here..)

PERSON_ID GENDER_CONCEPT_ID YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH DATETIME_OF_BIRTH RACE_CONCEPT_ID ETHNICITY_CONCEPT_ID LOCATION_ID PROVIDER_ID CARE_SITE_ID PERSON_SOURCE_VALUE GENDER_SOURCE_VALUE GENDER_SOURCE_CONCEPT_ID RACE_SOURCE_VALUE RACE_SOURCE_CONCEPT_ID ETHNICITY_SOURCE_VALUE ETHNICITY_SOURCE_CONCEPT_ID 1000003049 8532 1934 6 30 1934-06-30T00:00:00-05:00 8527 38003564 0 180930 0 1:FEMALE 44394 26:WHITE 47289 10:NOT HISPANIC OR LATINO 44331

Last updated on FEBRUARY 23, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Symptoms

Using the control file:

LOAD DATA
INFILE '\<DIR>\TST_SQLLoader.TXT'
BADFILE '%BAD_FILE%'
DISCARDFILE '%DIS_FILE%'
REPLACE
INTO TABLE <TABLE_NAME>
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
SUBLOB,
LOB,
ACCOUNT,
ACCOUNT_TYPE,
TRANSACTION_ID,
PROPERTY,
BREAKOUT,
FLATPERIODUS,
PERIOD,
VALUE,
IDB_CREAT_TS "SYSTIMESTAMP",
FILE_NM "TST_SQLLoader"
)

to load the data into a table the following error is reported by SQL*Loader:

 
FILE_NM "TST_SQLLoader" column is causing the error. The load works fine Without "_".

Cause

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

How do I fix column not allowed here in Oracle?

What does column not allowed here mean?