Record 1: Rejected - Error on table PERSON, column DATETIME_OF_BIRTH. ORA-00984: column not allowed here

DATETIME_OF_BIRTH "TO_DATE(substr(DATETIME_OF_BIRTH,1,10),'YYYY-MM-DD')",

  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);

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)

SQL> select * from PERSON;

no rows selected

SQL> host type person.ctl
load data
infile 'person.csv' "str '\r\n'"
append
into table PERSON
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           (
PERSON_ID char,
GENDER_CONCEPT_ID char,
YEAR_OF_BIRTH char,
MONTH_OF_BIRTH char,
DAY_OF_BIRTH char,
DATETIME_OF_BIRTH char "TO_DATE(substr(:DATETIME_OF_BIRTH,1,10),'YYYY-MM-DD')",
RACE_CONCEPT_ID char,
ETHNICITY_CONCEPT_ID char,
LOCATION_ID char,
PROVIDER_ID char,
CARE_SITE_ID char,
PERSON_SOURCE_VALUE CHAR(4000),
GENDER_SOURCE_VALUE CHAR(4000),
GENDER_SOURCE_CONCEPT_ID char,
RACE_SOURCE_VALUE CHAR(4000),
RACE_SOURCE_CONCEPT_ID char,
ETHNICITY_SOURCE_VALUE  CHAR(4000),
ETHNICITY_SOURCE_CONCEPT_ID char)

SQL> host sqlldr userid=michel/michel control=person.ctl skip=1

SQL*Loader: Release 11.2.0.4.0 - Production on Mar. FÚvr. 6 22:13:07 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 10

SQL> select * from PERSON;
 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 30/06/1934 00:00:00            8527
            38003564           0      180930            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000004460              8532          1945              2           16 16/02/1945 00:00:00            8527
            38003564           0      174233        72096

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000005889              8532          1974              5           21 21/05/1974 00:00:00            8527
            38003564           0      111675            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000010952              8532          1958              2            8 08/02/1958 00:00:00        38003598
            38003564           0      195136            0

1:FEMALE
                   44394
23:BLACK
                 47286
10:NOT HISPANIC OR LATINO
                      44331
1000023634              8532          1972              1            2 02/01/1972 00:00:00            8527
            38003564           0      165217        72694

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000024310              8532          1954              7           14 14/07/1954 00:00:00            8527
            38003564           0      179070        72666

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000026317              8532          1990             10           22 22/10/1990 00:00:00            8527
            38003564           0      177766            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000026722              8507          1946             11           27 27/11/1946 00:00:00        38003598
            38003564           0      140269        64602

2:MALE
                   44395
23:BLACK
                 47286
10:NOT HISPANIC OR LATINO
                      44331
1000031488              8532          1995              5           15 15/05/1995 00:00:00            8527
            38003564           0      117196            0

1:FEMALE
                   44394
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331
1000035184              8507          1990             11           19 19/11/1990 00:00:00            8527
            38003564           0      117791        72064

2:MALE
                   44395
26:WHITE
                 47289
10:NOT HISPANIC OR LATINO
                      44331

10 rows selected.

SQL> host type person.log

SQL*Loader: Release 11.2.0.4.0 - Production on Mar. FÚvr. 6 22:13:07 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   person.ctl
Data File:      person.csv
  File processing option string: "str '\r\n'"
  Bad File:     person.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table PERSON, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PERSON_ID                           FIRST     *   ,  O(") CHARACTER
                                                      O(")
GENDER_CONCEPT_ID                    NEXT     *   ,  O(") CHARACTER
                                                      O(")
YEAR_OF_BIRTH                        NEXT     *   ,  O(") CHARACTER
                                                      O(")
MONTH_OF_BIRTH                       NEXT     *   ,  O(") CHARACTER
                                                      O(")
DAY_OF_BIRTH                         NEXT     *   ,  O(") CHARACTER
                                                      O(")
DATETIME_OF_BIRTH                    NEXT     *   ,  O(") CHARACTER
                                                      O(")
    SQL string for column : "TO_DATE(substr(:DATETIME_OF_BIRTH,1,10),'YYYY-MM-DD')"
RACE_CONCEPT_ID                      NEXT     *   ,  O(") CHARACTER
                                                      O(")
ETHNICITY_CONCEPT_ID                 NEXT     *   ,  O(") CHARACTER
                                                      O(")
LOCATION_ID                          NEXT     *   ,  O(") CHARACTER
                                                      O(")
PROVIDER_ID                          NEXT     *   ,  O(") CHARACTER
                                                      O(")
CARE_SITE_ID                         NEXT     *   ,  O(") CHARACTER
                                                      O(")
PERSON_SOURCE_VALUE                  NEXT  4000   ,  O(") CHARACTER
                                                      O(")
GENDER_SOURCE_VALUE                  NEXT  4000   ,  O(") CHARACTER
                                                      O(")
GENDER_SOURCE_CONCEPT_ID             NEXT     *   ,  O(") CHARACTER
                                                      O(")
RACE_SOURCE_VALUE                    NEXT  4000   ,  O(") CHARACTER
                                                      O(")
RACE_SOURCE_CONCEPT_ID               NEXT     *   ,  O(") CHARACTER
                                                      O(")
ETHNICITY_SOURCE_VALUE               NEXT  4000   ,  O(") CHARACTER
                                                      O(")
ETHNICITY_SOURCE_CONCEPT_ID          NEXT     *   ,  O(") CHARACTER
                                                      O(")

value used for ROWS parameter changed from 64 to 13

Table PERSON:
  10 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 255060 bytes(13 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:            10
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Mar. FÚvr. 06 22:13:07 2018
Run ended on Mar. FÚvr. 06 22:13:07 2018

Elapsed time was:     00:00:00.09
CPU time was:         00:00:00.07

SQL> create table t (
  2  DATETIME_OF_BIRTH DATE,
  3  YEAR_OF_BIRTH number(4) as (extract(year from DATETIME_OF_BIRTH)),
  4  MONTH_OF_BIRTH number(2) as (extract(month from DATETIME_OF_BIRTH)),
  5  DAY_OF_BIRTH number(2) as (extract(day from DATETIME_OF_BIRTH))
  6  )
  7  /

Table created.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 DATETIME_OF_BIRTH                         DATE
 YEAR_OF_BIRTH                             NUMBER(4)
 MONTH_OF_BIRTH                            NUMBER(2)
 DAY_OF_BIRTH                              NUMBER(2)

SQL> insert into t (DATETIME_OF_BIRTH) values (date '1956-11-13');

1 row created.

SQL> select * from t;
DATETIME_OF_BIRTH   YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH
------------------- ------------- -------------- ------------
13/11/1956 00:00:00          1956             11           13

1 row selected.