Data file: BATCHFEED.csv
1001,John,Doe,Legal
1002,Maria,Armstrong,Legal
1003,Mark,Joiner,Legal

DDLs
----
CREATE TABLE PATIENT_EXT (
Identifier_Value VARCHAR2(128),
Identifier_DBKey NUMBER(10),
Name_DBKey NUMBER(10),
First_Name VARCHAR2(64),
Last_Name VARCHAR2(64),
Name_Type VARCHAR2(64)
)

CREATE TABLE Patient_Identifier (
Identifier_DBKey NUMBER(10),
Identifier_Value VARCHAR2(128)

);

CREATE TABLE Patient_Name (
Name_DBKey NUMBER(10),
Identifier_DBKey NUMBER(10),
First_Name VARCHAR2(64),
Last_Name VARCHAR2(64),
Name_Type VARCHAR2(64)
);

CREATE SEQUENCE ID_DBKEY_SEQ
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;


CREATE TABLE PAT_EXT (
Identifier_Value VARCHAR2(128),
Identifier_DBKey NUMBER(10),
Name_DBKey NUMBER(10),
First_Name VARCHAR2(64),
Last_Name VARCHAR2(64),
Name_Type VARCHAR2(64)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
Identifier_Value,
Identifier_DBKey, -- COMMENT -- to be a computed column from ID_DBKEY_SEQ.NEXTVAL, how?
Name_DBKey, -- COMMENT -- to be a computed column from ID_DBKEY_SEQ.CURRVAL, how?
First_Name,
Last_Name,
Name_Type
))
LOCATION ('BATCHFEED.csv'))
PARALLEL
REJECT LIMIT 0;