How to load common flat file data to two different table using single control file?

Question

I'm unable to load the data to different tables using single control file and .csv data file. only one table data is loading perfectly but the second table is not loading any values apart from constant values defined in the .ctl file

Please find the code I used:

OPTIONS ( ERRORS=1000000, DIRECT=FALSE, SKIP=1)
load data 
INFILE *
append into table TB1
fields terminated by "," 
optionally enclosed by '"'
trailing nullcols
(
WORK_ORDER_ID                   "TRIM(:WORK_ORDER_ID)",
REQUEST_DATE                    FILLER,
REQUEST_TYPE                    FILLER,
INSTALLATION_TYPE               "TRIM(:INSTALLATION_TYPE)",
REQUEST_STATUS                  FILLER,
WORK_STEP                       FILLER,
PROPERTY_NAME                   FILLER,
ADDRESS                         "TRIM(:ADDRESS)",
PROPERTY_CITY                   FILLER,
PROPERTY_STATE                  FILLER,
PROPERTY_ZIP                    FILLER,
DL_MARKET                       FILLER,
DL_DISPATCH_ZONE                FILLER,
PROPERTY_PHONE_NO               FILLER,
REQUEST_DESCP_NOTES             FILLER,
SITE_ESCORTS                    FILLER,
TOTAL_ROOMS                     "TRIM(:TOTAL_ROOMS)",
LOCATION_BEACONS                FILLER,
ACCESS_POINTS                   FILLER,
CREATED_BY                      CONSTANT "lv_user_id", 
LAST_UPDATED_BY                 CONSTANT "lv_user_id", 
LAST_UPDATE_DATE                SYSDATE,  
LAST_UPDATE_LOGIN               CONSTANT "lv_user_id",    
CREATION_DATE                   SYSDATE 
)

into table TB2
fields terminated by "," 
optionally enclosed by '"'
trailing nullcols
(
REQUEST_ID                      "TRIM(:REQUEST_ID)",
REQUEST_DATE                    "TRUNC(TO_DATE(TRIM(:REQUEST_DATE),'MM/DD/YYYY HH24:MI:SS'))",
REQUEST_TYPE                    "TRIM(:REQUEST_TYPE)",
INSTALLATION_TYPE               "TRIM(:INSTALLATION_TYPE)",
REQUEST_STATUS                  "TRIM(:REQUEST_STATUS)",
WORK_STEP                       "TRIM(:WORK_STEP)",
ON_PROPERTY_CONTACT             "TRIM(:ON_PROPERTY_CONTACT)",
ON_PROPERTY_EMAIL               "TRIM(:ON_PROPERTY_EMAIL)",
ON_PROPERTY_POC_MOBILE          "TRIM(:ON_PROPERTY_POC_MOBILE)",
PROPERTY_ADDRESS                "TRIM(:PROPERTY_ADDRESS)",
CREATED_BY                      CONSTANT "lv_user_id", 
LAST_UPDATED_BY                 CONSTANT "lv_user_id", 
LAST_UPDATE_DATE                SYSDATE,  
LAST_UPDATE_LOGIN               CONSTANT "lv_user_id",    
CREATION_DATE                   SYSDATE, 
STG_SEQ_ID                      "XXMTZ_AT_OP_MAS_STG_SEQ.NEXTVAL"
)

TB1 data loads perfectly but TB2 doesn't load any data to table apart from constant values

Answer 1

Such a behavior is described in documentation; it says that - when SQL*Loader gets to the second INSERT INTO, it scans input data from where it left during loading the first INSERT INTO.

In other words, it scans nothing (as it reached the end of the logical record during append into table TB1) so all values in the second table are NULL.

You have to "reset" it to the beginning of the record by specifying the position, e.g.

into table TB2
...
(
REQUEST_ID   position(1)      "TRIM(:REQUEST_ID)",
...