Need to have an external table with CSV to have optional columns, if CSV has columns otherwise NULL
I have the external table with table definition as
CREATE TABLE "PRE_STAGE"."CWS_SECURITYDETAILS_VW"
( "TICODE" VARCHAR2(12 CHAR),
"TIDM" CHAR(4 CHAR),
"SEDOL" CHAR(7 CHAR),
"COUNTRYOFREGISTER" CHAR(2 CHAR),
"COUNTRYOFREGISTERNAME" VARCHAR2(255 CHAR),
"CURRENCYCODE" CHAR(3 CHAR),
"MARKETSEGMENTCODE" CHAR(4 CHAR),
"MARKETSECTORCODE" CHAR(4 CHAR),
"MARKETSECTORNAME" VARCHAR2(255 CHAR),
"LISTINGDATE" DATE,
…
"PROFINVESTONLY" VARCHAR2(1 CHAR),
"PVLB" VARCHAR2(1 CHAR),
"ACCESS_BONDS_CREST" VARCHAR2(1 CHAR),
"SPECIFIED_DENOMINATION" NUMBER(18,8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "DUMP_S3_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY '$#@$#@\n'
CHARACTERSET AL32UTF8
STRING SIZES ARE IN CHARACTERS
SKIP 1
BADFILE DUMP_S3_DIR:'BAD_CWS_SECURITYDETAILS_VW_%a_%p.bad'
LOGFILE DUMP_S3_DIR:'LOG_CWS_SECURITYDETAILS_VW_%a_%p.log'